There are a number of ways in which data protection/data mobility can be done with MySQL.

The documentation splits backups into two categories.

• Physical Backups – Raw copies of directories and files which store database contents
• Logical backups – Logical backups save information in the form of reproducing the database object structure  to the point in time when it was taken. This essentially catalogues CREATE <Object> and INSERT INTO <Object> statements.

The tools for each backup category are as follows :

Physical Backup	Logical Backup
mysqlbackup	mysqldump
File system command such as  cp, scp , tar, rsync (Linux) and xCopy (Windows)
LVM snapshots
Storage Appliance Volume Snapshots
NDB Cluster Backups/ ndb_restore

mysqldump 

Mysqldump is a client utility included with any deployment which produces a set of SQL statements from a source system . The SQL statements produced by this utility can be used to  create the same tables and data on a target system.

This tool is useful if an individual database/schema needs to be backed up or copied to a different instance.

To dump an entire  MySQL database to a file :

mysqldump -u [user name] –p [password] [options] [database_name]  > [dumpfilename.sql]

Note : additional database names can be used to backup multiple databases.

To dump a single table from MySQL database to a file :

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

To dump all databases in an instance to a file :

mysqldump -u root -p –all-databases > [dumpfilename.sql]

To restore a database the contents of the file are directed into a mysql session :

mysql -u root -p <database name> < [dumpfilename.sql]

mysqlbackup 

MySQL Enterprise backup or mysqlbackup is a utility offering online , incremental, differential, direct cloud, encryption and compression backup and restore operations.

MySQL Enterprise backup can be obtained by logging into Oracle Software Delivery Cloud and searching for “MySQL Enterprise Edition”. MySQL Enterprise Backup will be one of the downloads which can be selected.

The utility is executed from the command line of the relevant operating system. For linux/unix environments the installation path will be /opt/mysql/meb-8.0 (for version 8.0.x) , add this directory to the PATH environment variable. For Microsoft Windows environments ensure the option Include directory in Windows PATH is checked during installation to have the ability to execute mysqlbackup from any location.

To use the utility , open a command terminal terminal and use any of these methods :

Create a basic backup where an online database provides relevant information about the data files :

mysqlbackup --user=<mysql user> --password --port=<mysql port>   --with-timestamp -backup-dir=<backup location>  backup

Create a basic backup using a configuration file with options under a [mysqlbackup] section (recommended to use existing database option file – my.cnf or my.ini:

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf backup

Create a basic backup using a configuration file while overriding some of the options :

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf  --compress --user=<mysql user> --password --port=<mysql port>  backup

For restore operations the database server needs to be shut down and all the files in the data directory (or other directory locations specified in the options file) needs to be deleted.

An example of a restore command uses the data directory(–datadir) as the location where data will be restored to, a backup image (–backup-image) as the source of the backup data to restore ,a temporary folder(–backup-dir) for temporary files and the restore operation “copy-back-and-apply-logs” to copy the data files and then roll forward any transactions. This command will look similar to this :

mysqlbackup --datadir=<datadir> --backup-image=<location of backup image> --backup-dir=<an empty temporary backup directory>  copy-back-and-apply-lo

See the commands section for more information about MySQL Enterprise backup and how to use it.

File system copy

Using Linux commands such as cp, rsync ,scp and tar or Microsoft Windows utilities such as xCopy to copy files can be used as a backup method.

All of the files which are apart of the database instance should be copied at the same time.

cp

This command is used in linux to copy a file or folder to another location.

To copy a file or folder use the following syntax :

cp /path/to/source /path/to/destination

Wildcards such as * can be used to copy multiple files at a time. In order to preserve file and folder permissions the -p argument may need to be specified.

rsync

This utility can be used to synchronise files and directories between two locations. The locations can be on the same or different system from the source.

To use rsync between two local locations :

rsync /path/to/source /path/to/destination

To use rsync between a local source and remote destination :

rsync /path/to/source <user>@<remote>:/path/to/destination

To use rsync between a remote destination and local source :

rsync <user>@<remote>:/path/to/source /path/to/destination

scp

Scp is based on a secure shell and allows for the copying of files to and from remote sources.

To copy a remote file to a local directory :

scp user@remote:file /local/directory/

To copy a local file to a remote directory :

scp /local/directory/file user@remote:/destination/folder

tar

Tar is an archiving tool which can package files and folders into a single compressed file. There are a number of compression algorithms which can be used such as gzip and bzip2.

To create a simple tar archive :

tar -cvf my-archive.tar /path/to/data

To create a gzip compressed archive :

tar -zcvf my-archive.tar.gz /path/to/data

To create a bzip2 compressed archive :

tar -cvfj my-archive.tar.bz2 /path/to/data

To unzip a tar archive of any compression type use the -x argument:

tar -xvf my-archive.tar.bz2

xcopy

Xcopy is a utility included with Microsoft Windows which copies files and directories, including any subdirectories.

To copy a file or folder from one location to another :

xcopy path\to\source path\to\destination

LVM Snapshots 

LVM or Logical Volume Manager is a block device subsystem for Linux which creates a software layer between block devices and filesystems. Using LVM allows for more complex operations to take place such as combining multiple physical disks into a single volume , allowing for the creation of snapshots and encryption of multiple partitions with a single password.

If intending to create LVM snapshots for MySQL then additional space needs to be provided to the LVM volume group. The amount of space required for an LVM snapshot will vary but assume that at minimum 10% additional space is required.

LVM utilises a copy-on-write snapshot method allowing for snapshots to be created nearly instantaneously.

If application consistency of MySQL is required then the following command needs to be run before creating an LVM snapshot to flush all data to disk and lock the tables , preventing any further changes:

FLUSH TABLES WITH READ LOCK;

Note that using the command will cause any incoming SQL commands which alter data to hang. Ensure the mysql session which issued this command remains open otherwise the read lock is released.

To create an LVM snapshot use the lvcreate command :

lvcreate --size <max size of snapshot> --snapshot --name <LVM snap name> dev/<volume group><logical volume name>

Once the LVM snapshot has been created the tables can be unlocked using this command :

UNLOCK TABLES;

To recover from the LVM snapshot the lvconvert command will be used.The MySQL database should be shutdown before executing any of these commands.  With this command the snapshot will be merged into the original volume and deleted right after it :

lvconvert --mergesnapshot <snapshot_logical_volume>

LVM snapshots cannot be considered backups as the loss of the source system has the likely outcome of the LVM snapshot being lost as well. Rsync can be used to copy LVM snapshots and associated data to a different server but that is beyond the scope of this blog post.

SAN Storage snapshots

Storage appliances that offer volume snapshot capabilities are very useful for scenarios where recovery point objectives and recovery target objectives are very low. Unlike LVM snapshots a volume snapshot created in a storage appliance can offer more advanced mobility techniques such as replication or copy from snapshot capabilities.

Storage snapshots can be created in an application consistent or crash consistent manner. The only difference between the two options is that with an application consistent scenario the FLUSH TABLES WITH READLOCK command is executed on the MySQL instance.

In order for a snapshot to truly be considered a backup it would need to be offloaded from the storage appliance to a third location. This is important as recovery needs to be possible in the event of data loss in the storage appliance.

MySQL Cluster Backup and Recovery

MySQL Clusters(or NDB Clusters)  are distributed databases combining linear scalability and high availability. Typically made up of administration nodes (responsible for starting and restarting the cluster), sql nodes (where clients connect to and execute SQL commands) and data nodes (where the database data is persisted to and retrieved from).

Backups  are created of the data and metadata on data nodes in the cluster. A backup operation can be provisioned from any node in the cluster which contains the ndb_mgm client utility. Issuing the following command using the ndb_mgm utility causes the backup to be created :

ndb_mgm -e “START BACKUP <BackupID>

BackupID is optional and if omitted will be automatically created. Cluster backups are created in a subdirectory of the DataDir or , if specified , the BackupDataDir configuration parameters.

To restore a database the ndb_restore utility needs to be used. Ndb_restore will read the files created as a part of a backup and restores the information into the database.

In this example the connection string for management node(s) needs to be provided along with a data node  id to restore the database to and a backup id to restore :

ndb_restore --ndb_connectstring=<connection string> --nodeid=<the data node id> --backupid=<backup number> --backup_path=<path-to-backup-number>

MySQL Cluster backup and recovery can be more complex than the above commands highlight. Consult the documentation for a deeper understanding of the concepts and tools.