We are testing a new type of backup MySQL

MySQL backups come in 2 main types:

Logical backup

A text dump of SQL queries is created, as in mysqldump or Sypex Dumper .

Physical backup

Exact copies of the table files are made, a typical representative of mysqlhotcopy .
')
In the process of working on a new version of Sypex Dumper and Sypex Backuper, I came to another interesting version of the MySQL hot backup. Which represents something between these two options.

But first, consider the main advantages and disadvantages. Who, instead of theory, wants to go straight to practice - at the bottom of the post you will find a link to the test script.

Logical backup

Advantages of logical backup:

• received dump can be restored on any system;
• backup of a remote MySQL server;
• backup of any tabular engines (including MEMORY);
• A backup is created on a running server, without stopping its operation.

Of the main disadvantages:

• a logical backup is made much slower than a physical one, since all the data must be converted into human-readable SQL queries;
• larger file size due to text backup format.

Physical backup

The advantages of physical backup:

• Maximum backup speed, because files are simply copied;
• small file size (as the binary format is used);
• You can back up server log files.

Of the main disadvantages:

• backup only local server;
• There may be difficulties with transferring backup to another machine / system.
• you cannot create backup MEMORY tables (since there are no physical files);
• it is not always possible to restore separate tables (for example, InnoDB tables can be stored in one file).

Sypex MySQL RAW backup

When analyzing logical backup methods, it was noticed that the main loss of speed occurs when receiving data packets from the server, parsing them and converting them to text format. In addition, this parsing is usually done by libmysql, either in the case of new PHP versions of mysqlnd, and results in an additional overhead.

Therefore, I decided to try to get rid of unnecessary conversions, and wrote a test script that connects directly to MySQL (via TCP or to a UNIX socket) without using standard MySQL drivers using MySQL Client / Server Protocol . The script saves the data in the file as binary packages received from the MySQL server ( ProtocolBinary :: Resultset ). Thus, no time is spent on parsing packages, fields, data escaping. And the analysis of packages and the formation of SQL queries occurs already when restoring a backup.

As a result, the backup speed has increased many times, depending on the structure of the table. Also, dumps are very compact. You can compare the speed of RAW backup with backup using mysqldump and SELECT ... INTO OUTFILE.

Multiple execution results on the standard tables of the IPB and phpBB forums.


The main disadvantage of the method is, of course, the impossibility of restoration by standard methods, i.e. need a special script to restore. But in our case, this is not so important, because in any case, this method will work with a special container file that supports deduplication, incremental backup, encryption, and other features.

Download the script for testing here .
The script is a technology demonstrator, not a final product.

On the results, unsubscribe in the comments. Just keep in mind that SELECT ... INTO OUTFILE works only on localhost, plus the MySQL user must have FILE access rights and the backup directory must have access rights 777.

UPD. At the request of workers, a few more tests with more tables:

Backup one of the wikipedia tables (categorylinks) about 1.3 GB


Backup table GeoNames about 1 GB