MySQL High Availability- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem

MySQL High Availability- P10

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL High Availability- P10: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions.

Chủ đề:

Nội dung Text: MySQL High Availability- P10

  1. Restoring data with ibbackup Restoring data requires applying the log described in the previous section. This oper- ation essentially points the MySQL instance to the backup copies of the database. It does not copy the files to the normal MySQL datadir location. If you want to do this, you must copy the files manually or use the innobackup script. The reason for this is that the ibbackup utility is designed to refuse to overwrite any data. To start a MySQL instance and use a backup of your data, execute a command like the following: mysqld -defaults-file=/home/cbell/backup.cnf The innobackup script The innobackup file is a Perl script designed to automate many of the operations of ibbackup. It can create backups; restore data; start a MySQL instance using the data from a backup; or copy data, index, and logfiles from a backup directory back to their original locations. The innobackup script is currently not available for use on Windows. Unlike the ibbackup utility, you do not need to specify the commands in a separate configuration file for innobackup. Rather, you specify the parameters for the backup or restore using command-line options. These options are described in Table 12-2. Table 12-2. innobackup options Option Function --help Displays a list of all options. --version Displays the version of the script. --apply-log Applies the backup log to the backup in preparation for starting a MySQL server with the backup files. --copy-back Copies data and index files from the backup location to their original locations. --use-memory=MB Passed to ibbackup, this option controls how much memory is used during restoration. --sleep=MS Passed to ibbackup, this option causes the utility to pause after every 1 Mb of data is copied. --compress=LEVEL Passed to ibbackup, this option provides the compression level to use. --include=REGEXP Passed to ibbackup, this option instructs the process to back up only those table files that match the regular expression. This is used for a selective backup. --uncompress Passed to ibbackup, this option uncompresses a compressed backup. --user=NAME Username to use for connecting to the server. --password=PWD Password for the user. --port=PORT Port for the server. --socket=SOCK Socket of the server. Backup Utilities and OS-Level Solutions | 427 Please purchase PDF Split-Merge on to remove this watermark.
  2. Performing a backup with innobackup To create a backup, you need only two options: the configuration file of the server and the location for your backup files: perl innobackup /etc/mysql/my.cnf /home/cbell/backup If you want a consistent backup, specify the --apply-log option as well: perl innobackup --apply-log /etc/mysql/my.cnf /home/cbell/backup Restoring data with innobackup To restore data, apply the log and use the --copy-back option to copy the files to the original location. We show a sample of these commands below. You must stop the server before the copy and then restart it afterward. perl innobackup --apply-log /etc/mysql/my.cnf /home/cbell/backup mysqladmin -uroot shutdown perl innobackup --copy-file /etc/mysql/my.cnf /home/cbell/backup /etc/init.d/mysql start Additional features InnoDB Hot Backup also supports PITR. See the InnoDB Hot Backup documenta tion for more details about this and other advanced features. You can download a trial version of InnoDB Hot Backup for evaluation purposes. This trial is active for 30 days, during which you can use InnoDB Hot Backup without limitations. To sign up for a free trial copy, visit Physical File Copy The easiest and most basic form of backup for MySQL is a simple file copy. Unfortu- nately, this requires you to stop the server for best results. To perform a file copy, simply stop your server and copy the data directory and any setup files on the server. One common method for this is to use the Unix tar command to create an archive. You can then move this archive to another system and restore the data directory. The following is a typical sequence of tar commands that backs up the data from one database server and restores it on another system. Execute the following command on the server you want to back up, where backup_2009_09_09.tar.gz is the file you want to create and /usr/loca/mysql/data is the path for the data directory: tar -czf backup_2009_09_09.tar.gz /usr/loca/mysql/data/* The backup_2009_09_09.tar.gz file must reside on a directory shared by the two servers (or you must physically copy it to the new server). Now, on the server where you want 428 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  3. to restore the data, change to the root installation of your new installation of MySQL. Delete the existing data directory, if it exists, then execute the following: tar -xvf ../backup_2009_09_09.tar.gz As mentioned earlier, it is always a good idea to use meaningful file- names for your backup images. As you can see from this example, it is very easy to back up your data at the operating system level. Not only can you get a single compressed archive file that you can move around easily, you also get the benefit of fast file copy. You can even do a selective backup by simply copying individual files or subdirectories from your data directory. Unfortunately, the tar command is available only on Linux and Unix platforms. If you have Cygwin installed on a Windows system and have included its version of the com- mand, you can also use tar there. Other than Cygwin or another Unix-on-Windows package, the closest equivalent to this command on Windows is the handy folder archive feature of the Explorer or an archive program such as WinZip. To do this, open Windows Explorer and navigate to your data directory. But instead of opening the directory, right-click the data directory and choose an option that compresses the data, which will have a label such as Send To→Compressed (zipped) Folder, and then provide a name for the .zip file. While a physical file copy is the quickest and easiest form of backup, it does require that you shut down the server. But that isn’t necessary if you are careful to ensure there are no updates occurring during the file copy. To do this, you must lock all tables and perform a flush tables command, then take your server offline before making the file copy. This is similar to the process for cloning a slave. See Chapter 2 for more details and an example of cloning a slave using file copy. Additionally, depending on the size of the data, your server must be offline not only for the time to copy the files, but also for any additional data loads like cache entries, the use of memory tables for fast lookups, etc. For this reason, physical copy backup may not be feasible for some installations. Fortunately, there is a Perl script, created by Tim Bunce, to automate this process. The name of the script is and it is located in the ./scripts folder of your MySQL installation. It allows you to make hot copies of databases. However, you can use it only to back up MyISAM or Archive storage engines and it works only on Unix and Netware operating systems. Backup Utilities and OS-Level Solutions | 429 Please purchase PDF Split-Merge on to remove this watermark.
  4. The utility also includes customization features. You can find more information about it at The mysqldump Utility The most popular alternative to the physical file copy feature is the mysqldump client application. It has been part of the MySQL installation for some time and was originally donated to MySQL by Igor Romanenko. mysqldump creates a set of SQL statements that re-create the databases when you rerun them. For example, when you run a backup, the output contains all of the CREATE statements needed to create the databases and the tables they contain, as well as all the INSERT statements needed to re-create the data in those tables. This can be very handy if you need to do a search-and-replace operation in the text of your data. Simply back up your database, edit the resulting file with a text editor, then restore the database to effect the changes. Many MySQL users use this technique to correct all sorts of errors caused by making batch edits to the data. You will find this much easier than writing, say, 1,000 UPDATE statements with complicated WHERE clauses. The drawbacks of using mysqldump are that it takes a lot more time than the binary copies made by file-level (physical) backups like InnoDB Hot Backup, LVM, or a simple offline file copy, and it requires a lot more storage space. This cost in time can be significant if you make frequent backups, want to restore a database quickly after a system failure, or need to transfer the backup file across a network. You can use mysqldump to back up all your databases, a specific subset of databases, or even particular tables within a given database. The following examples show each of these options: mysqldump -uroot -all-databases mysqldump -uroot db1, db2 mysqldump -uroot my_db t1 You can also use mysqldump to do a hot backup of InnoDB tables. The --single-trans action option issues a BEGIN statement at the start of the backup, which signals the InnoDB storage engine to read the tables as a consistent read. Thus, any changes you make are applied to the tables, but the data is frozen at the time of backup. However, no other connection should use data definition language (DDL) statements like ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. This is because a consistent read is not isolated from DDL changes. The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES issues an implicit commit. 430 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  5. The utility has several options that control the backup as well as what is included. Table 12-3 describes some of the more important options. See the online MySQL Ref- erence Manual at for a com- plete set of options. Table 12-3. mysqlbackup options Option Function --add-drop-database Includes a DROP DATABASE statement before each database. --add-drop-table Includes a DROP TABLE statement before each table. --add-locks Surrounds each included table with LOCK TABLES and UNLOCK TABLES. --all-databases Includes all databases. --create-options Includes all MySQL-specific table options in the CREATE TABLE statements. --databases Includes a list of databases only. --delete-master-logs On a master, deletes the binary logs after performing the backup. --events Backs up events from the included databases. --extended-insert Uses the alternative INSERT syntax that includes each row as a VALUES clause. --flush-logs Flushes the logfiles before starting the backup. --flush-privileges Includes a FLUSH PRIVILEGES statement after backing up the mysql database. --ignore-table=db.tbl Does not back up the specified table. --lock-all-tables Locks all tables across all databases during the dump. --lock-tables Locks all tables before including them. --log-error=filename Appends warnings and errors to the specified file. --master-data[=value] Includes the binlog filename and position in the output. --no-data Does not write any table row information (only CREATE statements). --password[=password] The password to use when connecting to the server. --port=port_num The TCP/IP port number to use for the connection. --result-file=filename Outputs to a specific file. --routines Includes stored routines (procedures and functions). --single-transaction Issues a BEGIN SQL statement before dumping data from the server. This allows for a consistent snapshot of the InnoDB tables. --tables Overrides the --databases option. --triggers Includes triggers. --where='condition' Includes only rows selected by the condition. --xml Produces XML output. Backup Utilities and OS-Level Solutions | 431 Please purchase PDF Split-Merge on to remove this watermark.
  6. You can also include these options in a MySQL configuration file under the heading [mysqldump]. In most cases, you can specify the option sim- ply by removing the initial dashes. For example, to always produce XML output, include xml in your configuration file. One very handy feature of mysqldump is the ability to dump a database schema. You can normally do this using a set of the CREATE commands to re-create all of the objects without the INSERT statements that include the data. This usage can be very useful for keeping a historical record of the changes to your schema. If you use the --no-data option along with the options to include all of the objects (e.g., --routines, --triggers), you can use mysqldump to create a database schema. Notice the option --master-data. This option can be very helpful for performing PITR because it saves the binary log information like InnoDB Hot Backup does. There are many more options that allow you to control how the utility works. If creating a backup in the form of SQL statements sounds like the best option for you, feel free to explore the rest of the options for making mysqldump work for you. XtraBackup Percona, an independent open source provider and consulting firm specializing in all things MySQL (LAMP, actually), has created a storage engine called XtraDB, which is an open source storage engine based on the InnoDB storage engine. XtraDB has several improvements for better scaling on modern hardware and is backward compatible with InnoDB. In an effort to create a hot backup solution for XtraDB, Percona has created XtraBackup. This tool is optimized for InnoDB and XtraDB, but can also back up and restore MyISAM tables. It provides many of the features expected of backup solutions, in- cluding compression and incremental backup. You can download and build XtraBackup by getting the source code from Launchpad at You can compile and execute XtraBackup on most platforms. It is compatible with MySQL versions 5.0 and 5.1. The online manual for XtraBackup is located at cona-xtrabackup:xtrabackup_manual. Logical Volume Manager Snapshots Most Linux and some Unix systems provide another powerful method of backing up your MySQL database. It makes use of a technology called the logical volume man- ager (LVM). 432 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  7. Microsoft Windows has a similar technology called Volume Shadow Copy. Unfortunately, there are no generic utilities to make a snapshot of a random partition or folder structure as there are for LVM. You can, however, make snapshots of an entire drive, which can be useful if your database directory is the only thing on that drive. See the Microsoft online documentation for more information. An LVM is a disk subsystem that gives you a lot of administrative power to create, remove, and resize volumes easily and quickly without using the older, often compli- cated and unforgiving disk tools. The added benefit for backup is the concept of taking a snapshot—that is, a copy of an active volume—without disrupting the applications that access the data on that vol- ume. The idea is to take a snapshot, which is a relatively fast operation, and then back up the snapshot instead of the original volume. Deep inside LVM, a snapshot is man- aged using a mechanism that keeps track of the changes since you took the snapshot, so that it stores only the disk segments that have changed. Thus, a snapshot takes up less space than a complete copy of the volume and when the backup is made, the LVM copies the files as they existed at the time of the snapshot. Snapshots effectively freeze the data. Another benefit of using LVM and snapshots for backing up database systems lies in how you use the volumes. The best practice is to use a separate volume for each of your MySQL installations so that all of the data is on the same volume, allowing you to create a backup quickly using a snapshot. Of course, it is also possible to use multiple logical volumes in some situations, such as using one logical volume for each tablespace or even different logical volumes for MyISAM and InnoDB tables. Getting started with LVM If your Linux installation does not have LVM installed, you can install it using your package manager. For example, on Ubuntu you can install LVM using the following command: sudo apt-get install lvm2 Although not all LVM systems are the same, the following procedure is based on a typical Debian distribution and works well on systems like Ubuntu. We don’t mean to write a complete tutorial on LVM but just to give you an idea of the complexity of using LVM for making database backups. Consult your operating system documentation for specifics about the type of LVM your system supports, or simply browse the many how- to documents available on the Web. Before we get started with the details, let’s take a moment to understand the basic concepts of LVM. There is a hierarchy of levels to the LVM implementation. At the lowest level is the disk itself. On top of that are partitions, which allow us to commu- nicate with the disk. On top of the partition we create a physical volume, which is the Backup Utilities and OS-Level Solutions | 433 Please purchase PDF Split-Merge on to remove this watermark.
  8. control mechanism that the LVM provides. You can add a physical volume to a volume group (which can contain multiple physical volumes), and a volume group can contain one or more logical volumes. Figure 12-1 depicts the relationship among filesystems, volume groups, physical volumes, and block devices. Figure 12-1. Anatomy of LVM A logical volume can act as either a normal mounted filesystem or a snapshot. The creation of a snapshot logical volume is the key to using snapshots for backup. The following sections describe how you can get started experimenting with LVM and making backups of your data. There are several useful commands that you should become familiar with. The follow- ing list contains the most frequently used commands and their uses. Be sure to consult the documentation for more information about these commands: pvcreate Creates a physical volume pvscan Shows details about the physical volumes vgcreate Creates volume groups vgscan Shows details about volume groups lvcreate Creates a logical volume 434 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  9. lvscan Shows details about logical volumes lvremove Removes a logical volume mount Mounts a logical volume umount Unmounts a logical volume To use LVM, you need to have either a new disk or a disk device that you can logically unmount. The process is as follows (the output here was generated on a laptop running Ubuntu version 9.04): 1. Create a backup of an existing MySQL data directory. tar -czf ~/my_backups/backup.tar.gz /dev/mysql/datadir 2. Partition the drive. sudo parted select /dev/sdb mklabel msdos mkpart test quit 3. Create a physical volume for the drive. sudo pvcreate /dev/sdb 4. Create a volume group. sudo vgcreate /dev/sdb mysql 5. Create a logical volume for your data. Here we create a 20 GB volume. sudo lvcreate -L20G -ndatadir mysql 6. Create a filesystem on the logical volume. mke2fs /dev/mysql/datadir 7. Mount the logical volume. sudo mkdir /mnt sudo mount /dev/mysql/datadir /mnt 8. Copy the archive and restore your data to the logical volume. sudo cp ~/my_backups/backup.tar.gz sudo tar -xvf backup.tar.gz 9. Create an instance of a MySQL server and use --datadir to point to the folder on the logical volume. ./mysqld --console -uroot --datadir=/mnt Backup Utilities and OS-Level Solutions | 435 Please purchase PDF Split-Merge on to remove this watermark.
  10. If you want to experiment with LVM, we recommend you use a disk whose data you can afford to lose. A good, cheap option is a small USB hard drive. That’s all you need to get started with a logical volume. Take some time to experiment with the LVM tools, until you are certain you can work with them effectively, before you start using them for your production systems. LVM in a backup and restore To do your backup, you need to flush and temporarily lock all of the tables, take the snapshot, and then unlock the tables. The lock is necessary to ensure all of your ongoing transactions are finished. The process is shown here along with the shell-level com- mands that perform the operations: 1. Issue a FLUSH TABLES WITH READ LOCK command in a MySQL client. 2. Create a snapshot of your logical volume (the -s option specifies a snapshot). sudo lvcreate -L20M -s -n backup /dev/mysql/datadir 3. Issue an UNLOCK TABLES command in a MySQL client (your server can now resume its operations). 4. Mount the snapshot. sudo mkdir /mnts sudo mount /dev/mysql/backup /mnts 5. Perform a backup of the snapshot. tar -[FIXTHIS]f snapshot.tar.gz /mnts Of course, the best use of the snapshot is to initiate a copy periodically so that you can do another backup. There are scripts available from volunteers on the Web to automate this process, but the tried and true mechanism is to remove the snapshot and re-create it using the following procedure: 1. Unmount the snapshot. sudo umount /mnts 2. Remove the snapshot (logical volume). sudo lvremove /dev/mysql/backup You can then re-create the snapshot and perform your backup. If you create your own script, we recommend adding the snapshot removal after you have verified the backup archive was created. This will ensure your script performs proper cleanup. If you need to restore the snapshot, simply restore the data. The real benefit of LVM is that all of the operations for creating the snapshot and the backup using the tar utility allow you to create a customized script that you can run periodically (such as a cron job), which can help you automate your backups. 436 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  11. LVM in ZFS The procedure for performing a backup using Sun Microsystems’ ZFS filesystem (avail- able in Solaris 10) is very similar to the Linux LVM procedure. We describe the differ- ences here for those of you using Solaris. In ZFS, you store your logical volumes (which Sun calls filesystems for read/write and snapshots for read-only copies) in a pool (similar to the volume group). To make a copy or backup, simply create a snapshot of a filesystem. Issue the following commands to create a ZFS filesystem that you can manage: zpool create -f mypool c0d0s5 zfs create mypool/mydata Use the following command to make a backup (take a snapshot of the new filesystem): zfs snapshot mypool/mydata@backup_12_Dec_2009 Use the following commands to restore the filesystem to a specific backup: cd /mypool/mydata zfs rollback mypool/mydata@backup_12_Dec_2009 ZFS provides not only full volume (filesystem) backups, but also supports selective file restore. For more information about ZFS and performing backup and restore, visit http: // Comparison of Backup Methods InnoDB Hot Backup, mysqldump, and third-party backup options differ along a number of important dimensions, and there is almost no end to the nuances of how each method works. We provide a comparison of backup methods here based on whether they allow for hot backups, their cost, the speed of the backup, the speed of the restore, the type of backup (logical or physical), platform restrictions (operating system), and supported storage engines. Table 12-4 lists each of the backup methods mentioned in this chapter along with a column for each comparison item. Table 12-4. Comparison of backup methods InnoDB Hot Physical Backup mysqldump copy XtraBackup LVM/ZFS snapshot Hot backup? Yes (InnoDB only) Yes (InnoDB only re- No Yes (InnoDB and Yes (requires table quires --single- XtraDB only) flush with lock) transaction) Cost Paid license Free Free Free Free Backup speed Medium Slow Fast Medium Fast Restore speed Fast Slow Fast Fast Fast Type Physical Logical Physical Physical Physical Backup Utilities and OS-Level Solutions | 437 Please purchase PDF Split-Merge on to remove this watermark.
  12. InnoDB Hot Physical Backup mysqldump copy XtraBackup LVM/ZFS snapshot OS All All All All LVM supported only Engines InnoDB, MyISAM All All InnoDB, XtraDB, All MyISAM InnoDB Hot Backup is not supported fully on Windows. The Perl script does not execute on some Windows configurations. See the online doc- umentation for more details. Table 12-4 can help you plan your data recovery procedures by allowing you to find the best tool for the job given your needs. For example, if you need a hot backup for your InnoDB database and cost is not a factor, the Innobase InnoDB Hot Backup ap- plication is the best choice. On the other hand, if speed is a factor, you need something that can back up all databases (all storage engines), and you work on a Linux system, LVM is a good choice. Backup and MySQL Replication There are two ways to use backup with MySQL replication. In previous chapters, you learned about MySQL replication and its many uses for scale-out and high availability. In this chapter, we examine two more common uses for MySQL replication involving backup. These include using replication to create a backup copy of the data and using backups taken previously for PITR. Backup and recovery Keeping an extra server around for creating backups is very common; it allows you to create your backups without disturbing the main server at all, since you can take the backup server offline and do whatever you like with it. PITR Even if you create your backups regularly, you may have to restore the server to an exact point in time. By administering your backups properly, you can actually restore the server to the granularity of a specific second. This can be very useful in recovering from human error—such as mistyping commands or entering incorrect data—or reverting changes that are not needed anymore. The possibilities are endless, but they require the existence of proper backups. Backup and Recovery with Replication One shortcoming of backups in general is that they are created at a specific time (usually late at night to avoid disturbing other operations). If there is a problem that requires you to restore the master to some point after you created the backup, you will be out 438 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  13. of luck, right? Nope! As it turns out, this is indeed not only possible, but quite easy if you combine the backups with the binary logs. The binary log records all changes that are made to the database while the database is running, so by restoring the correct backup and playing back the binary log up to the appropriate second, you can actually restore a server to a precise moment in time. The most important step in a recovery procedure is, of course, recovery. So let’s focus on performing recovery before outlining the procedure for performing a backup. PITR The most frequent use for backup in replication is PITR, the ability to recover from an error (such as data loss or hardware failure) by restoring the system to a state as close as possible to the most recent correct state, thus minimizing the loss of data. For this to work, you must have performed at least one backup. Once you repair the server, you can restore the latest backup image and apply the binary log using that binary log name and position as the starting point. The following describes one procedure you can use to perform PITR using the backup system: 1. Return your server to an operational state after the event. 2. Find the latest backup for the databases you need to restore. 3. Restore the latest backup image. 4. Apply the binary log using the mysqlbinlog utility using the starting position (or starting date/time) from the last backup. At this point, you may be wondering, “Which binary log do I use for PITR after a backup?” The answer depends on how you performed the backup. If you flushed the binary log prior to running the backup, you need to use the name and position of the current log (the newly opened logfile). If you did not flush the binary log prior to run- ning the backup, use the name and position of the previous log. For easier PITR, always flush the logs prior to a backup. The starting point is then at the start of the file. Restoring after an error is replicated Now let’s see how a backup can help you recover unintentional changes in a replication topology. Suppose one of your users has made a catastrophic (yet valid) change that is replicated to all of your slaves. Replication cannot help you here, but the backup system can come to the rescue. Backup and MySQL Replication | 439 Please purchase PDF Split-Merge on to remove this watermark.
  14. Perform the following steps to recover from unintentional changes to data in a repli- cation topology: 1. Drop the databases on the master. 2. Stop replication. 3. Restore the latest backup image before the event on the master. 4. Record the master’s current binlog position. 5. Restore the latest backup image before the event on the slaves. 6. Perform a PITR on the master, as described in the previous section. 7. Restart replication from the recorded position and allow the slaves to sync. In short, a good backup strategy is not only a necessary protection against data loss, but an important tool in your replication toolbox. Recovery example Now, let’s look at a concrete example. Assume you’re creating backups regularly every morning at 2:00 A.M. and save the backup images away somewhere for later usage. For this example, let’s assume all binary logs are available and that none are removed. In reality, you will prune the binlog files regularly to keep the disk space down, but let’s consider how to handle that later. You have been tasked with recovering the database to its state at 2009-12-19 12:54:23, because that’s when the manager’s favorite pictures were accidentally deleted by his overzealous assistant, who took his “Could you please clean my desk?” request to in- clude the computer desktop as well. 1. Locate the backup image that was taken before 2009-12-19 12:54:23. It does not actually make any difference which one you pick, but to save on the recovery time, you should probably pick the one closest, which would then be the backup image dated in the morning of the same day. 2. Restore the backup image on the machine to create an exact copy of the database at 2009-12-19 02:00:00. 3. Locate all the binlog files that include the entire range from 2009-12-19 02:00:00 to 2009-12-19 12:54:23. It does not matter if there are events from before the start time or after the end time, but it is critical that the binlog files cover the entire range you want. 4. Play the binlog files back using the mysqlbinlog utility and give a start time of 2009-12-19 02:00:00 and an end time of 2009-12-19 12:54:23. You can now tell your manager that his favorite pictures are back. To automate this, it is necessary to do some bookkeeping. This will give you an indi- cation of what you need to save when doing the backup, so let’s go through the infor- mation that you will require when doing a recovery. 440 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  15. • To use the backup images correctly, it is critical to label each with the start and end time it represents. This will help you determine which image to pick. • You also need the binlog position of the backup. This is necessary because the time is not sufficiently precise in deciding where to start playing back the binlog files. • You also need to keep information about what range each binlog file represents. Strictly speaking, this is not required, but it can be quite helpful because it helps you avoid processing all binlog files of a recovery image. This is not something that the MySQL server does automatically, so you have to handle it yourself. • You cannot keep these files around forever, so it is important to sort all the infor- mation, backup images, and binlog files in such a way that you can easily archive them when you need to free up some disk space. Recovery images To help you administer all the information about your backups in manageable chunks, we introduce the concept of a recovery image. The recovery image is just a virtual con- tainer and is not a physical entity: it contains only information about where all the necessary pieces are to be able to perform recovery. Figure 12-2 shows a sequence of recovery images and the contents of each. The final recovery image in the sequence is special and is called the open recovery image. This is the recovery image that you are still adding changes to, hence it does not have an end time yet. The other recovery images are called closed recovery images and these have an end time. Figure 12-2. A sequence of recovery images and contents Backup and MySQL Replication | 441 Please purchase PDF Split-Merge on to remove this watermark.
  16. Each recovery image has some pieces of information that are necessary to perform a recovery: A backup image The backup image is required for restoring the database. A set of binlog files Binlog files must cover the entire range for the recovery image. Start time and an optional end time These are the start and end time that the recovery image represents. If this is the open recovery image, it does not have an end time. This recovery image is not practical to archive, so for our purposes, the end time of this recovery image is the current time. The binlog files usually contain events outside the range of start and end times, but all events in that range should be in the recovery image. A list of the name and start time for each binlog file To extract the correct binlog files from, say, an archive, you need the names and the start and end times for each file. You can extract the start time for the binlog file using mysqlbinlog. Backup procedure The backup procedure gathers all the information required and structures it so that we can use it for both archiving and recovery. For this procedure, assume that we are going to create recovery image n and that we have a sequence of recovery images, Image1 to Imagen–1. 1. Create a backup using your favorite method and note the name of the backup image and the binlog position it represents. The binlog position also contains the binlog filename. If you used an offline backup tool, the backup time is the time when you locked the tables, and the binlog position is the position given by SHOW MASTER STATUS after you locked the database. 2. Create a new open recovery Imagen with the following parameters: • Backup(Imagen) is now the backup image from step 1. • Position(Imagen) is the position from step 1. • BinlogFiles(Imagen) is unknown, but it starts with the filename from the position from step 1. • StartTime(Imagen) is the start time of the image, which is taken from the event at the binlog position from step 1. 442 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  17. 3. Close Imagen–1, noting the following: • BinlogFiles(Imagen–1) is now the binlog files from Position(Imagen–1) to Posi- tion(Imagen). • EndTime(Imagen–1) is now the same as StartTime(Imagen). PITR in Python To manage the various sorts of backup methods in a consistent manner, we’ve created the class PhysicalBackup in Example 12-1. The class holds two methods: class PhysicalBackup.PhysicalBackup(image_name) The constructor for the class takes the name of an image that it will use when backing up or restoring a server. PhysicalBackup.backup_from(server) This method will create a backup of the server and store it under the image name given to the constructor. PhysicalBackup.restore_on(server) This method will use the backup image for the backup and restore it on server. By using a class to represent a backup method in this manner, it is easy to replace the backup method with any other method as long as it has these methods. Example 12-1. Class for representing the physical backup method class BackupImage(object): "Class for representing a backup image" def __init__(self, backup_url): self.url = urlparse.urlparse(backup_url) def backup_server(self, server, db): "Backup databases from a server and add them to the backup image." pass def restore_server(self, server): "Restore the databases in an image on the server" pass class PhysicalBackup(BackupImage): "A physical backup of a database" def backup_server(self, server, db="*"): datadir = server.fetch_config().get('datadir') if db == "*": db = [d for d in os.listdir(datadir) if os.path.isdir(os.path.join(datadir, d))] server.sql("FLUSH TABLES WITH READ LOCK") position = replicant.fetch_master_pos(server) if != "localhost": path = basename(self.url.path) else: Backup and MySQL Replication | 443 Please purchase PDF Split-Merge on to remove this watermark.
  18. path = self.url.path server.ssh(["tar", "zpscf", path, "-C", datadir] + db) if != "localhost":["scp", + ":" + path, self.url.path]) server.sql("UNLOCK TABLES") return position def restore_server(self, server): if == "localhost": path = self.url.path else: path = basename(self.url.path) datadir = server.fetch_config().get('datadir') try: server.stop() if != "localhost": call(["scp", self.url.path, + ":" + path]) server.ssh(["tar", "zxf", path, "-C", datadir]) finally: server.start() The next step is to introduce a representation of the recovery image, as shown in Example 12-2. The recovery image stores the pieces of information as five fields: RecoveryImage.backup_image The backup image to use. RecoveryImage.start_time The start time of the recovery image. RecoveryImage.start_position The binlog position that the backup image represents. Use this instead of the start time as the starting point for playing back the binlog files, since it will be accurate. There can be a lot of transactions executed during a second, and using the start time will fail because it will pick the first event with the start time, while the real start position may be somewhere else. RecoveryImage.binlog_files A list of the binlog files that are part of this recovery image. RecoveryImage.binlog_datetime A dictionary mapping binlog filenames to date/times, which are the date/time of the first event of the binlog file. In addition, the recovery image must have the following utility methods: RecoveryImage.contains(datetime) Decides whether datetime is contained in the recovery image. Since the binlog file may have been rotated mid-second, the end_time is inclusive. 444 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
  19. RecoveryImage.backup_from(server) Creates a new open recovery image by creating a backup of server and collects information about the backup. RecoveryImage.restore_to(server, datetime) Restores the recovery image on the server so that all changes up to and including datetime are applied. This assumes datetime is in the range for the recovery image. If datetime is before the recovery image’s start time, nothing will be applied, and if it is after the recovery image’s end time, all will be applied. Example 12-2. A representation of a recovery image class RecoveryImage(object): def __init__(self, backup_method): self.backup_method = backup_method self.backup_position = None self.start_time = None self.end_time = None self.binlog_files = [] self.binlog_datetime = {} def backup_from(self, server, datetime): self.backup_position = backup_method.backup_from(server) def restore_to(self, server): backup_method.restore_on(server) def contains(self, datetime): if self.end_time: return self.start_time
  20. def point_in_time_recovery(server, datetime): from itertools import takewhile from subprocess import Popen, PIPE for im in images: if im.contains(datetime): image = im break image.restore_on(server) def before(file): return image.binlog_datetime(file) < datetime files = takewhile(before, image.binlog_files) command = ["mysqlbinlog", "--start-position=%s" % (image.backup_position.pos), "--stop-datetime=%s" % (datetime)] mysqlbinlog_proc = Popen(mysqlbinlog_command + files, stdout=PIPE) mysql_command = ["mysql", "--host=%s" % (, "--user=%s" % (, "--password=%s" % (server.sql_user.password)] mysql_proc = Popen(mysql_command, stdin=mysqlbinlog_proc.stdout) output = mysql_proc.communicate()[0] def point_in_time_backup(self, server): new_image = RecoveryImage(self.__backup_method) new_image.backup_position = image.backup_from(server) new_image.start_time = event_datetime(new_image.backup_position) prev_image = self.__images[-1].binlog_files prev_image.binlog_files = binlog_range(prev_image.backup_position.file, new_image.backup_position.file) prev_image.end_time = new_image.start_time self.__images.append(new_image) Automating Backups It is fairly easy to automate backups. In the previous section, we demonstrated how to do a backup and recovery with replication. In this section, we generalize the procedure to make it easier to do nonreplication-related backup and restore. The only issue you may encounter is providing a mechanism to automatically name the backup image file. There are many ways to do this, and Example 12-4 shows a method that names the file using the backup time. You can add this backup method to the Python library to complement your replication methods. This is the same library shown in previous chapters. 446 | Chapter 12: Protecting Your Investment Please purchase PDF Split-Merge on to remove this watermark.
Đồng bộ tài khoản