Hot MySQL BackUp Tools

Good day. Recently I wondered how to make hot MySQL server backups - below is a compilation from what I read. I want to say in advance that this post is more of a big note than a full-fledged article. I intentionally shy away from describing the 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 users of web hosting. Reading the contents of the tables, she creates a file with SQL instructions for subsequent 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 relationships between the contents of the tables (if during the process of creating a copy someone decides to leave a record in the database). Here, indirectly, rolling the bin-log part after recovery from the dump can help. So if for some reason you do not lock the tables - use the --flush-log switch - 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, this will be reflected at the beginning of the log and you can transfer this change to the database without any problems. I would advise you to also run mysqladmin-flush-logs after the end of the backup and put the penultimate binary log in addition to the dump file in the backup.
• When using the --lock-tables key, all tables receive write locks, and 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 quite delayed - in the first case, you rake all the records from the database, and in the opposite case, feed them to it. Nevertheless, this is one of the few ways to backup the database from the console without root access.

Recovery: by feeding the dump file to mysql utility via STDIN.

2. Using the mysqlhotcopy utility. Another tool from the regular set of MySQL. The idea is this: the database is put on a lock, after which the cp or scp tools copy its files to another location.

• Unlike the previous version, it is the table files that are saved, and not the set of instructions for reconstructing the database, that is, the speed is limited only by the operating system and your hardware.
• In my opinion, it is quite suitable for backup of large bases.
• Works only with MyISAM and ARCHIVE tables.
• It is executed only from the server on which the database is located, subject to the availability of rights to 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 drive itself. One of the notable features of LVM is the ability to capture an image from a volume on the fly. The action scheme will be as follows: lock all database tables, remove snapshot from the 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, it will most likely be stored in the / var / lib / mysql directory) be on the LVM volume (preferably a separate one, so as not to back up too much).
• Considering that we are talking about a hot backup - if you intend to use this method - it is better to make a decision about the location 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 to be hemorrhoids, this backup method seems to me the most correct. The logic of this approach is to constantly synchronize the primary (master) server with the secondary (slave). Read more about replication here .

• A separate MySQL server configuration is required. And it is desirable - on an autonomous iron.
• Stopping the slave server will not play any role on the master - you can make a “cold” backup.
• In the event of a master’s crash, it is possible to transfer the entire load to slave as soon as possible (it would be reasonable to automate this process), and after recovery, synchronize the master with it and return everything to its original places.
• Slave can become part-time storage for backups.
• Important! The existence of a replica does not exempt you from creating backups. The execution of a DROP will affect both servers!

Recovery: the output of the slave server to the place of the master, or restoration using one of the above methods (depending on the selected).

Total: As you can see, each method has its pros and cons: it hardly makes sense to back up a small forum using replicas, and it’s unlikely that it will be convenient to raise databases where gigabytes are taken from files made by mysqldump - each method is good in certain conditions.

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