Hot MySQL BackUp Creation Tools

Good day. Recently, I wondered about how to make hot BackUps `MySQL-servers - below is a compilation of the read. I want to say in advance that this post is rather a big note than a full-fledged article. I deliberately shy away from the description of syntax - a lot has already been written on this topic - I set myself another goal - to make a brief overview of the main methods with characteristic features:

1. Using the mysqldump utility. This program is extremely popular among web hosting users. Reading the contents of the tables, she creates a file with SQL instructions for later filling. But, as a rule, when using people forget about three key points:

• If you do not use table locking, it is quite possible to get a violation of the logical links between the contents of the tables (if in the process of creating a copy, someone decides to leave a record in the database). Here, indirectly, the rolling of the bin-log`a part after recovery from the dump can help. So if for some reason you do not block the tables, use the - flush-log key - when using it, the old log will be closed and a new one will be started. If someone writes something in the process of creating a backup, it will be reflected at the beginning of the journal and you will transfer this change to the database without any problems. I would advise that after finishing the backup, also run mysqladmin-flush-logs and put the penultimate binary log in the backup in addition to the dump-file.
• When using the --lock-tables key, all tables get a write lock, requests are queued. This can lead to client-side timeouts.
• It should also be borne in mind that the rise (as well as the creation of a dump) of a large base saved in this way can be fairly delayed - in the first case, you scrape all the records from the base, and in the opposite case - feed them to it. However, this is one of the few ways to save a database from the console without having root access.

Recovery: by dumping a file to the mysql utility via STDIN.

2. Using the mysqlhotcopy utility. Another tool from the regular set of MySQL. The idea is as follows: the base is put on a lock, after which the cp or scp files are copied to another location.

• Unlike the previous version, it is the table files that are saved, and not a set of instructions for recreating the database, that is, the speed is limited only by the operating system and your hardware.
• In my mind - it is quite suitable for backing up large databases.
• Works only with MyISAM and ARCHIVE-tables.
• It is performed only from the server on which the database rests, provided that you have rights to the files with MySQL tables.

Recovery: by copying the saved files to the MySQL data directory.

3. Using LVM.
LVM is an extra layer between the file system and the hard disk itself. One of the remarkable features of LVM is the ability to remove an image from a volume on the fly. The scheme of actions will be the following: lock all database tables, take a snapshot from a volume, unlock tables.

• This method implies a preliminary FLUSH with locking of all tables (it is better to write a script for this purpose).
• To use this method, it is necessary that the MySQL data (for Linux, they will most likely be stored in the / var / lib / mysql directory) be on the LVM volume (preferably separate, so as not to back up the excess).
• Considering that we are talking about a hot backup - if you are going to use this method - it is better to make the decision on the placement at the server configuration stage.

Recovery: by copying the files saved from the image to the MySQL data directory.
')
4. Using replication. Despite the fact that many consider this option hemorrhoids, this method of reservation seems to me the most correct. The logic of this approach consists in constant synchronization of the main (master) server with the secondary (slave). Read more about replication here .

• A separate MySQL server configuration is required. Moreover, it is desirable - on autonomous hardware.
• Stopping the slave server will not play any role on the master `s - you can do a" cold "backup.
• In the case of the fall of the master`a, it is possible in the shortest possible time (it would be reasonable to automate this process) to transfer all the load on the slave, and after recovery, synchronize with the master and return everything to the previous places.
• Slave can become part-time storage area for backups.
• Important! The existence of a replica does not release you from creating backups. Doing some DROP will affect both servers!

Recovery: output of the slave server to the master`a place, or recovery by one of the above methods (depending on the selected one).

Total: As you can see, each method has its pros and cons: there is hardly any sense in backing up a small forum using replicas, and bases where the count goes to gigabytes will hardly be conveniently picked up from files made by mysqldump, each method is good at certain conditions.

At this point I finish my story, I hope it will be useful for you. Thank you for your attention and see you again on the air. :)