Data Backup in MySQL
Backing up the database is such a thing that you always have to configure for already running projects directly on live production servers.
This situation is easily explained. At the very beginning, any project is still empty and there is simply nothing to copy. In the phase of rapid development of the head of a few developers are busy exclusively screwing chips and ruches, as well as fixing critical bugs with a deadline of "day before". And only when the project "takes off", comes the realization that the main value of the system is the accumulated database, and its failure will be a disaster.
This review article is for those whose projects have already reached this point, but the roast rooster has not yet pecked.
You can copy the MySQL database if you temporarily turn off the MySQL server and simply copy the files from the / var / lib / mysql / db / folder. If the server is not turned off, for obvious reasons, data loss and corruption is likely. For large loaded bases, this probability is close to 100%. In addition, when you first start with a “dirty” copy of the database, the MySQL server will begin the process of checking the entire database, which may take hours.
In most “live” projects, regular shutdown of the database server for a long time is unacceptable. A trick based on file system snapshots is used to solve this problem. Snapshot is something like a “photo” of a file system at a certain point in time, taken without actually copying the data (and therefore quickly). Similarly, “lazy copying” of objects works in many modern programming languages.
The general scheme of actions is as follows: all tables are locked, the database file cache is reset, file system snapshot is made, tables are unlocked. After that, the files are quietly copied from the snapshot, after which it is destroyed. The “blocking” part of such a process takes a time of the order of seconds, which is already tolerable. As a reckoning for a while, while snapshot is “alive”, the performance of file operations is reduced, which primarily beats the speed of write operations to the database.
Some file systems, such as ZFS, support snapshot removal natively. If you are not using ZFS, but you have an LVM volume manager on your server, you can also copy the MySQL database through snapshot . Finally, under * nix, you can use the R1Soft Hot Copy snapshot driver, but this method will not work in the openvz container ( the MySQL backup process is described here ).
')
For MyISAM databases, there is an official freeware utility mysqlhotcopy that “correctly” copies MyISAM database files without stopping the server. There is a similar utility for InnoDB , but it is paid, although it has more features.
Copying files is the fastest way to transfer the entire database from one server to another.
In order to read into the backup data from the production-base, it is not necessary to jerk files. You can select data by request and save it to a text file. To do this, use the SQL command SELECT INTO OUTFILE and its pair LOAD DATA INFILE . Unloading is done line by line (you can select to save only the desired line, as in the usual SELECT). The structure of tables is not specified anywhere - a programmer should take care of this. It should also take care to include SELECT INTO OUTFILE commands in the transaction, if necessary to ensure data integrity. In practice, SELECT INTO OUTFILE is used for partial backup of very large tables that cannot be copied in any other way.
In most cases, the mysqldump utility created by Igor Romanenko is much more convenient. The mysqldump utility generates a file containing all the SQL commands necessary to fully restore the database on another server. Separate options can achieve compatibility of this file with almost any DBMS (not just MySQL), in addition, there is the possibility of uploading data in CSV and XML formats. To recover data from these formats, there is a mysqlimport utility.
The mysqldump console utility. There are its add-ins and analogs that allow you to manage backups through a web interface, for example, the Ukrainian tool Sypex Dumper (their representative is zapimir in Habré).
The disadvantages of universal backup utilities to text files are the relatively low performance and the inability to make incremental backups.
Traditionally, it is recommended to keep 10 backups: one for each day of the week, as well as two-week, monthly and quarterly backups - this will allow you to roll back deep enough if any data is corrupted.
Backups should not be stored exactly on the same disk as the live database, and not on the same server. In case of fires and other disasters, it is best to rent a couple of units in a nearby data center.
These requirements can be a problem for large databases. Pumping a backup of a 100-gigabyte base on a 100-mb network will take about three hours, with which the channel will be completely blocked.
Incremental backups allow to partially solve this problem, when a full backup is done, say, only on Sundays, and only data added or modified over the past 24 hours are written on other days. The difficulty is how to identify these same "data that changed in a day."
Here, out of competition, the Percona XtraBackup system, which contains a modified InnoDB engine, analyzes the MySQL binary logs and extracts the necessary information from them. Almost the same features have paid InnoDB Hot Backup, mentioned above.
A common problem with any backups is that they always lag behind. In the event of a fatal failure of the main server, it will be possible to restore the system only with some “rollback” in time, which will very much disappoint its users. If the system somehow affected the financial flows, such a "rollback" can literally fly into a pretty penny.
MySQL replication system is designed to avoid undoing. The idea of ​​replication is based on the fact that, in addition to the “main” server (“Wizard”), MySQL slave servers (“slaves”) are constantly running, which receive incremental backups from the wizard in real time. Thus, the rollback time is reduced almost to the network lag. In the event of a master crash, you can quickly assign one of the slaves to the “new master” and redirect clients to it. In addition, slaves can process read requests for data (SELECTs); This can be used to perform some calculations or reduce the load on the master. MySQL supports out-of-box replication, the process of setting up replication in MySQL is well described by the user whisk . It is possible to launch Master-Master configurations , and with the help of external hardware-software systems - and load balancing between masters. Just do not forget about the limitations imposed by the CAP-theorem .
Replication is great, but you need to use it for its intended purpose. A replica is a complete copy of the database, but it is not a backup copy ! Obviously, if you perform DROP TABLE or UPDATE users SET password = “Haha!” On the wizard, the changes will be immediately copied to the slave, and it will be impossible to roll them back.
Replication can be combined with backup at the database file level, stopping the slave, not the master.
The main thing to remember is that the backup in each case is individual. It can not be set up once and forget. The administrator must at least once a month try to restore the base from scratch from backups. In living developing projects, the database structure is constantly changing, so sooner or later, some data will no longer fall into the backup simply because they forgot to correct the scripts. If it is detected at the time of the fall of the main base, the value of such backups will be zero.
This situation is easily explained. At the very beginning, any project is still empty and there is simply nothing to copy. In the phase of rapid development of the head of a few developers are busy exclusively screwing chips and ruches, as well as fixing critical bugs with a deadline of "day before". And only when the project "takes off", comes the realization that the main value of the system is the accumulated database, and its failure will be a disaster.
This review article is for those whose projects have already reached this point, but the roast rooster has not yet pecked.
1. Copying base files
You can copy the MySQL database if you temporarily turn off the MySQL server and simply copy the files from the / var / lib / mysql / db / folder. If the server is not turned off, for obvious reasons, data loss and corruption is likely. For large loaded bases, this probability is close to 100%. In addition, when you first start with a “dirty” copy of the database, the MySQL server will begin the process of checking the entire database, which may take hours.
In most “live” projects, regular shutdown of the database server for a long time is unacceptable. A trick based on file system snapshots is used to solve this problem. Snapshot is something like a “photo” of a file system at a certain point in time, taken without actually copying the data (and therefore quickly). Similarly, “lazy copying” of objects works in many modern programming languages.
The general scheme of actions is as follows: all tables are locked, the database file cache is reset, file system snapshot is made, tables are unlocked. After that, the files are quietly copied from the snapshot, after which it is destroyed. The “blocking” part of such a process takes a time of the order of seconds, which is already tolerable. As a reckoning for a while, while snapshot is “alive”, the performance of file operations is reduced, which primarily beats the speed of write operations to the database.
Some file systems, such as ZFS, support snapshot removal natively. If you are not using ZFS, but you have an LVM volume manager on your server, you can also copy the MySQL database through snapshot . Finally, under * nix, you can use the R1Soft Hot Copy snapshot driver, but this method will not work in the openvz container ( the MySQL backup process is described here ).
')
For MyISAM databases, there is an official freeware utility mysqlhotcopy that “correctly” copies MyISAM database files without stopping the server. There is a similar utility for InnoDB , but it is paid, although it has more features.
Copying files is the fastest way to transfer the entire database from one server to another.
2. Copying via text files
In order to read into the backup data from the production-base, it is not necessary to jerk files. You can select data by request and save it to a text file. To do this, use the SQL command SELECT INTO OUTFILE and its pair LOAD DATA INFILE . Unloading is done line by line (you can select to save only the desired line, as in the usual SELECT). The structure of tables is not specified anywhere - a programmer should take care of this. It should also take care to include SELECT INTO OUTFILE commands in the transaction, if necessary to ensure data integrity. In practice, SELECT INTO OUTFILE is used for partial backup of very large tables that cannot be copied in any other way.
In most cases, the mysqldump utility created by Igor Romanenko is much more convenient. The mysqldump utility generates a file containing all the SQL commands necessary to fully restore the database on another server. Separate options can achieve compatibility of this file with almost any DBMS (not just MySQL), in addition, there is the possibility of uploading data in CSV and XML formats. To recover data from these formats, there is a mysqlimport utility.
The mysqldump console utility. There are its add-ins and analogs that allow you to manage backups through a web interface, for example, the Ukrainian tool Sypex Dumper (their representative is zapimir in Habré).
The disadvantages of universal backup utilities to text files are the relatively low performance and the inability to make incremental backups.
3. Incremental backups
Traditionally, it is recommended to keep 10 backups: one for each day of the week, as well as two-week, monthly and quarterly backups - this will allow you to roll back deep enough if any data is corrupted.
Backups should not be stored exactly on the same disk as the live database, and not on the same server. In case of fires and other disasters, it is best to rent a couple of units in a nearby data center.
These requirements can be a problem for large databases. Pumping a backup of a 100-gigabyte base on a 100-mb network will take about three hours, with which the channel will be completely blocked.
Incremental backups allow to partially solve this problem, when a full backup is done, say, only on Sundays, and only data added or modified over the past 24 hours are written on other days. The difficulty is how to identify these same "data that changed in a day."
Here, out of competition, the Percona XtraBackup system, which contains a modified InnoDB engine, analyzes the MySQL binary logs and extracts the necessary information from them. Almost the same features have paid InnoDB Hot Backup, mentioned above.
A common problem with any backups is that they always lag behind. In the event of a fatal failure of the main server, it will be possible to restore the system only with some “rollback” in time, which will very much disappoint its users. If the system somehow affected the financial flows, such a "rollback" can literally fly into a pretty penny.
4. Replication
MySQL replication system is designed to avoid undoing. The idea of ​​replication is based on the fact that, in addition to the “main” server (“Wizard”), MySQL slave servers (“slaves”) are constantly running, which receive incremental backups from the wizard in real time. Thus, the rollback time is reduced almost to the network lag. In the event of a master crash, you can quickly assign one of the slaves to the “new master” and redirect clients to it. In addition, slaves can process read requests for data (SELECTs); This can be used to perform some calculations or reduce the load on the master. MySQL supports out-of-box replication, the process of setting up replication in MySQL is well described by the user whisk . It is possible to launch Master-Master configurations , and with the help of external hardware-software systems - and load balancing between masters. Just do not forget about the limitations imposed by the CAP-theorem .
Replication is great, but you need to use it for its intended purpose. A replica is a complete copy of the database, but it is not a backup copy ! Obviously, if you perform DROP TABLE or UPDATE users SET password = “Haha!” On the wizard, the changes will be immediately copied to the slave, and it will be impossible to roll them back.
Replication can be combined with backup at the database file level, stopping the slave, not the master.
Instead of conclusion
The main thing to remember is that the backup in each case is individual. It can not be set up once and forget. The administrator must at least once a month try to restore the base from scratch from backups. In living developing projects, the database structure is constantly changing, so sooner or later, some data will no longer fall into the backup simply because they forgot to correct the scripts. If it is detected at the time of the fall of the main base, the value of such backups will be zero.
Source: https://habr.com/ru/post/137380/
All Articles