In this post, we’re comparing native MySQL backups to storage snapshots. Both of them have their own distinct advantages and disadvantages, and we hope that this post helps decide which solution fits your situation best.

What are Native and Storage Snapshot MySQL Backups?

Native MySQL backups refer to backups that are made “by default” (i.e they are backups that are performed via phpMyAdmin, by using mysqldump, by using MySQL Enterprise Backup, or by using other tools). Such MySQL backup types contain all of the SQL commands to recreate the database from scratch.

Storage snapshot (also called image) backup types, on the other hand, are used to create an entire architectural copy of an application, disk, or system. Snapshot or image backup types are used in backup processes to restore the system or disk of a particular device to a specific point in time.

Native Backup Methods in MySQL

In MySQL, native backup methods include mysqldump, backing up data via phpMyAdmin, MySQL Enterprise Backup, etc. Delimited-text file backups are also possible to obtain using the query SELECT * FROM table_name INTO OUTFILE ‘/directory/file.txt’; where table_name represents the table name and /directory/file.txt represents the path of the file in question. There also are other backup methods that enable you to, for example, make use of replication when backing up data.

Storage Snapshot Backup Methods in MySQL

If you find yourself using a Veritas File System, you can also make a backup like this:

  • Open a MySQL connection through a terminal and execute the following queries locking the tables for reading:
    FLUSH TABLES WITH READ LOCK;
    QUIT;
  • Execute the following command through a shell prompt:
    $ mount vxfs snapshot
  • Release the lock:
    UNLOCK TABLES;
    QUIT;
  • Finally, copy files from the snapshot and unmount it.

To make a backup using the LVM filesystem:

  • Log in to MySQL through a terminal;
  • Lock the tables for reading:

FLUSH TABLES WITH READ LOCK;FLUSH LOGS;
QUIT;

  • Run the lvcreate command replacing /dev/mysql with the path to your MySQL database (lvcreate creates a new logical volume):
    $ lvcreate --snapshot --size=1000M --name=mysqlbackup /dev/mysql
  • Log in to MySQL through a terminal again and unlock the tables:
    UNLOCK TABLES;
    QUIT;
  • Move the backup to another server (mount a filesystem, make a backup and unmount it):
    $ mkdir -p /backup/mysql
    $ mount -o ro /dev/mysqlbackup /backup/mysql
    $ cd /backup/mysql
    $ tar czvf mysql.$(date +"%m-%d%-%Y).tar.gz mysql
    $ umount /backup/mysql
    $ lvremove -f /dev/mysqlbackup

Summary

Both native MySQL backups and storage snapshots have their own distinct advantages and disadvantages. Native MySQL backups are frequently used to make logical backups – image backups are used to create an architectural copy of an application, a disk, or a system.

Contributor
Comments to: Storage Snapshot vs Native MySQL Backups

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.