Data Protection for LAMP Applications
Pages: 1, 2, 3

Raw (Physical) Full Backups with mysqlhotcopy

A raw backup consists of the database data in a binary format and will be different for machine architectures. The mysqlhotcopy tool is a Perl script that makes a copy of the data, index, and data dictionary files to the local machine or to a remote machine. This tool takes advantage of the fact that MySQL stores database table data, index, and logfiles in the database data directory configured by the database administrator. The mysqlhotcopy tool works only for storage engines that do not support transactions and will not work with storage engines that support transactions since these tables may not be stored in the database data directory.

The tool obtains a read lock on the tables/database being backed up and flushes the database pages to the disk before doing a backup.

mysqlhotcopy --flushlog db_name_1 ... db_name_n /path/to/new_directory

Restoring from raw backups is significantly faster than the logical backups because there are no SQL statements to execute. The raw backup size is the same as the size of database data and logs. Because it is a raw backup, it is not possible to correct operator errors.

Most of the engines that support transactions provide tools that helps create raw backups. The SOLID storage engine provides its own backup tool that does binary backups. InnoDB provides a commercial tool (ibbackup) that creates raw backups for InnoDB tables. InnoDB also provides the innobackup script that takes care of backing of InnoDB tables, data dictionary (.frm files), and other MyISAM tables using the mysqldump tool.

Incremental Backups

MySQL server can maintain binary logs that contain all events (MySQL statements) or updated data. Binary logging must be enabled to do incremental MySQL backups. Binary logs keep track of all changes to the data in the database and can be replayed later to restore the database, but enabling database logging can reduce the database performance.

You can configure MySQL to put the binary log on a different disk from the MySQL data directory to reduce the performance impact. This will also help improve availability. Here's the command to enable binary logs with the prefix backup_logs:

# mysqld --log-bin=backup_logs

MySQL rotates its binary logs when the server restarts; you can also rotate the logs to mark the end of an incremental backup by using the mysqladmin flush-logs command. You can back up the binary logs since the last full MySQL backup or the incremental backup using any network backup tool.

Data Consistency Using Snapshots

You can store your MySQL database data directory in logical volumes using the Logical Volume Manager (LVM) or the Enterprise Volume Management System (EVMS). Using logical volumes to store data and index files allows the volume to extend across physical disks. (You can even extend filesystems by using the filesystem growfs command).

Mirroring logical volumes can increase database availability, and both LVM and EVMS allow making a snapshot of logical volume contents to another logical volume. The snapshot volume is a copy-on-write volume, so the data gets written to the snapshot volume on when the data block on the original volume changes. No data copying is done when you create the snapshot.

To create a consistent copy of the database, create a snapshot of the logical volume that contains the MySQL database data directory. It is necessary to flush tables and obtain a read lock before taking a snapshot. Obtaining a read lock on an active database might take some time. Because snapshots are copy-on-write copies, a snapshot operation does not take much time. You can release the lock after the snapshot operation and not hold it during the backup processes. It is not necessary to obtain a read lock for tables with transactional storage engines. When you restore a backup copy, the database will recover from the transaction logs. Obtaining a read lock on a transactional storage engine table will commit all pending transactions and there will be a significant impact on the performance.

Here are the steps to take a snapshot.

1. Obtain a read lock on the database and flush the logs. This step is necessary if the database contains tables with non-transactional storage engines, or if you must back up specific tables with non-transactional storage engines.

   mysql> FLUSH TABLES WITH READ LOCK

2. Create a snapshot volume for the logical volume where the database data directory resides (/dev/mysqlvolume in this example). The following LVM command creates a snapshot volume, mysqlbackup, of size 50 MB. The data changes during the backup cannot exceed 50 MB. Usually, a snapshot volume size is 15% to 20% of the original volume size. If the database is really active, you might need a larger snapshot volume size.

   # lvcreate -L50M -s -n mysqlbackup /dev/mysqlvolume

3. Release the read lock on the database if you obtained one in Step 1.

   mysql> UNLOCK TABLES

4. Mount the snapshot volume in a different directory.

   # mount /dev/mysqlbackup /backup

5. Use a network-based backup tool to backup the database backup files from the /backup directory and application files to backup media, such as disks, tapes, or optical media.

6. Remove the snapshot logical volume.

   # lvremove -f /dev/mysqlbackup

7. Using EVMS instead of LVM for logical volumes provides additional advantages and reduces the complexity of the back up procedure. EVMS allows you to expand snapshot volumes. This will take care of long backup windows in case the initial size of snapshot volume is small.

   EVMS also allows you to create a permanent snapshot volume. Reinitialize it as Step 1 and forego removing it at the end of the backup. The snapshot volume acts as a data backup and the snapshot rollback is a quick restore mechanism.

   Using filesystem freeze and unfreeze operations along with data snapshots can provide better data consistency for the database and the application files. This is a feature of filesystems, such as XFS and GFS, and works only in conjunction with LVM snapshots to obtain a consistent view of the database and application files.

   If you use freezes, do one before Step 2 and then unfreeze it after Step 2. If you use the ext3fs filesystem for the LAMP application and the filesystem data and have enabled metadata journaling, you can skip the freeze operation.

   To freeze the XFS filesystem /var/lib/mysql in preparation for the backup:

   # xfs_freeze -f  /var/lib/mysql

   To unfreeze the XFS filesystem:

   # xfs_freeze -u /var/lib/mysql

Data consistency using replication

MySQL allows the creation of replication slaves for the master server. A master can support multiple replication slaves, and a replication server can act as a master server. All the transactions executed on the master server replay on the slave server asynchronously. MySQL commands are sent to the slave server for execution. The slave server can be behind the master replication server if the slave is slow in executing the MySQL commands. This statement-based replication is available for all storage engines.

MySQL 5.1 also supports row-based replication. The master server creates a binary log of how the table rows are affected by each statement and replicates the binary log to the slave. Row-based replication handles replication of stored routines and triggers, such as non-deterministic user defined functions. Statement-based replication produce smaller logs and are easier to audit.

There is another "mixed" mode of replication in MySQL 5.1; it uses statement-based replication by default and row-based replication for MySQL statements that use functions that will give different results when executed on master and slave server.

Replication is often useful for database high availability and load balancing, but another use is for back up. The replication slave has a consistent view of the database, but you can back it up using any of the MySQL backup methods described earlier.

Assuming that you have a replication slave set up for backups, here are the steps.

1. Stop the slave replication and check the slave status on the slave server to see if the replication has stopped:

   mysql> STOP SLAVE;

2. Capture the master logfile name and position:

   mysql> SHOW SLAVE STATUS;

3. Perform any of the previous MySQL backup methods to capture the database to files.
4. Add the CHANGE MASTER MySQL statement to the backup file using the values obtained in Step 2. This step marks the master logfile and the position in the logfile. Marking the log position makes backup restoration easier.
5. Use Amanda to back up the database backup files and application files to backup media, such as disks, tapes, optical media.

6. Start the slave process:

   mysql> START SLAVE;

Using replication for backup does not take care of operator errors because such errors are part of the replication. A replication solution is more expensive than a snapshot solution because it needs additional MySQL hosts. On the other hand, if you use replication for availability or load balancing, it is easy to use it for backup purposes as well.

Pages: 1, 2, 3

Next Page