Testing a new type of MySQL backup

    MySQL backups come in 2 main varieties:

    Logical backup

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

    Physical backup

    Exact copies of table files are made, a typical representative of mysqlhotcopy .

    In the process of working on the new version of Sypex Dumper and Sypex Backuper, I came up with another interesting version of the MySQL hot backup. Which is a cross between these two options.

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

    Logical backup

    Advantages of logical backup:

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

    Of the main disadvantages:

    • logical backup is much slower than physical, because you need to convert all the data into human-readable SQL queries;
    • Larger file size due to text backup format.

    Physical backup

    Advantages of physical backup:

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

    Of the main disadvantages:

    • backup of only the local server;
    • There may be difficulties with transferring the backup to another machine / system.
    • You can not backup MEMORY tables (since there are no physical files);
    • It is not always possible to restore individual tables (for example, InnoDB tables can be stored in a single file).

    Sypex MySQL RAW backup

    When analyzing the logical methods of backup, it was noticed that the main loss of speed occurs when receiving data packets from the server, parsing them and converting to a text format. In addition, this analysis is usually done by libmysql either in the case of new PHP versions of mysqlnd, and leads to 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 a file in the form of binary packets received from the MySQL server ( ProtocolBinary :: Resultset) Thus, time is not wasted on parsing packets, fields, shielding data. And the analysis of packages and the formation of SQL-queries occurs already when restoring the backup.

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

    Several run-time results on standard IPB and phpBB forum tables.

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

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

    Unsubscribe about the results in the comments. Just keep in mind that SELECT ... INTO OUTFILE works only on localhost, plus the MySQL user must have FILE permissions and the backup directory must have 777 permissions

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

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

    Backup GeoNames tables about 1 GB

    Also popular now: