MySQL data backup

The database backup always has to be adjusted for the existing projects directly to the live production servers.
This situation is easily explained. At the beginning any project is empty and there is simply nothing to copy. In the phase of rapid development the heads of a few developers are busy working on the project and fixing critical bugs with the deadline a day before yesterday. And only when the project has reached its ultimate point, there comes the awareness that the main value of the system is accumulated database, and its failure would be a disaster.
This article is for people whose projects have already reached this point, but they haven’t been dumped on enough.

1. Copying of the database files

MySQL database could be copied if you temporarily disable the MySQL server and just copy the files from folder / var / lib / mysql / db /. If the server will not be disabled then the probable loss and data corruption may happen. This probability is close to 100% for the large databases. In addition, when you first run the dirty copy of the database, MySQL server will start the process of checking the entire database, which can last for hours.

It is unacceptable to shut off regularly the database server in most live projects for a long time. In order to solve this problem is used a trick based on the file system snapshots. A snapshot is something like a picture of the file system at a particular time that is taken without a real data copying. Similarly, the lazy copying of objects works in many modern programming languages.
The general flow pattern is as follows: all tables are locked, file cache of database is reset, snapshot of the file system is taken, and the tables are unlocked. After that the files are easily copied from the snapshot, and then it is destroyed. Blocking part of this process takes seconds, which is tolerable. But, there is also a negative side; it slows the performance of file operations, which primarily hits the writing speed to the database.

Some file systems such as ZFS support snapshots natively. If you do not use ZFS, but your server has LVM, you can also copy MySQL database through a snapshot. Finally, in *nix you can use the snapshot driver R1Soft Hot Copy, but this method does not work in the container openvz (MySQL backup process is described here).

There is an official free utility mysqlhotcopy for MyISAM databases, which correctly copies the files of MyISAM databases without stopping the server. There is a similar utility for InnoDB, but it is not free, although it has more features.

Copying the files is the fastest way to transfer the whole database from one server to another.

2. Copying through the text files

In order to read the backup data from a production base, it is not necessarily using the files. You can query the data and store them in a text file. For this are used SQL commands SELECT INTO OUTFILE and LOAD DATA INFILE. Unloading is performed string by string (you can select to save only the desired strings, as in an ordinary SELECT). The structure of the tables is not specified, this should be taken care by the programmer. The programmer should also make sure to include the command SELECT INTO OUTFILE in a transaction if it is necessary to ensure data integrity. In practice, SELECT INTO OUTFILE is used to backup partially the very large tables that cannot be copied in any other way.

In most cases, it is much more convenient mysqldump utility. Mysqldump utility creates a file containing all SQL-commands needed to fully restore the database on another server. Certain options can provide compatibility of the file with virtually any database management system (not just MySQL). In addition, it is possible to unload data in CSV and XML formats. To restore this data from these formats there is a utility mysqlimport.

Mysqldump is a console utility. There is its add-ins and analogs that allow managing backup through a web interface, for example, Sypex Dumper tool.

There are some disadvantages of universal backup utilities in text files, such as a relatively low speed and lack of ability to do incremental backups.

3. Incremental backups

Traditionally, it is recommended to keep 10 backups: one for each day of the week, as well as biweekly, monthly and quarterly backups, it will help to roll back far enough in the case of damage to any data.
The backups should be stored on a separate drive. In the case of fires and other disasters is best to rent a couple of units in the next data center for this purpose.

These requirements can be a problem for the large databases. Transferring a 100 GB database backup over 100 MB network will take about three hours and make a slow traffic in there.
To solve this problem partially allow incremental backups when a full backup is done only on Sundays, and on other days are written only the data that have been added or changed in the past day. However, it is difficult to identify that data that was changed in the past day.
In this case Percona XtraBackup system practically has no competition, because this set of tools contains a modified engine InnoDB, parses the binary logs of MySQL and extracts from them the necessary information. InnoDB Hot Backup that was mentioned above has almost the same features, but it charges a fee.

All backups have a common problem that they are always lag. In the case of a crash of the master server to restore the system will only be possible with some rollback on time, which is very disappointing for its users. If the system somehow has been affected by financial flows, such a rollback can cost a lot of money.

4. Replication

MySQL replication system is designed to avoid these rollbacks. The idea of replication is based on the fact that besides the master server, there are constantly working the slave servers of MySQL, which getting the incremental backups from the master in real time. Thus, the rollback time is reduced almost to the network lag. In the case of the collapse of the master, one of the slave servers could be quickly assigned as the new master one and the client could be redirected to it. In addition, the slave servers can handle requests for data read (SELECTs), this could be used to perform some calculations or reduce the load on the master server. MySQL supports replication "out of the box". It is possible to run the Master-Master configurations using the help of external hardware and software systems, and load balancing between the masters. Just do not forget about the limitations of CAP theorem.

The replication is a great thing; however it only should be used according to its intended purpose. Replica is a complete copy of the database, but it is not a backup copy! It is obvious, if DROP TABLE or UPDATE users SET password will be done on the master server, then the changes will be immediately copied to the slave server, and it will be impossible roll them back.

The replication can be combined with the backup at level of the file base, stopping the slave server, not the master one.


The main thing is to remember that the backup is individual in each case. It cannot be set once and be forgotten forever. The administrator must at least once a month to try to restore the database from scratch from the backups. In the live projects the database structure of development has been constantly changing, so sooner or later, some data will not anymore fall into the backup, simply because they forgot to fix the scripts. If it will be revealed at the time of the crash of the main database, the value of these backups will be equal to zero.
Siera 29 march 2012, 12:36
Vote for this post
Bring it to the Main Page


0 aakshitr1 January 6, 2021, 12:03

What type of database backup do you have? Are you receiving a specific error message when restoring the databases that lead you to believe it's corrupted? Also, what method are you using to restore the databases?. Use the MySQL Database Recovery tool to perform the task with ease.


Leave a Reply

Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute