MySQL smart backup

    I think that many people have had a situation in their life when they have a full backup of the MySQL database, and you need to restore only a few tables from it. Or, even worse, you need to restore old records in these tables without overwriting new ones. And if the dump takes hundreds of megabytes, the process is not very inspiring.

    In this article, I will describe the concept of smart backup implemented in Sypex Dumper. And also give examples of use.


    During the development of Sypex Dumper 2, many MySQL backup solutions were analyzed. And first of all, of course, the source code of the complete mysqldump.

    In mysqldump, a file is created with a set of SQL queries, which, when restored, are simply split by the delimiter and "fed" to MySQL. Such a scheme, in principle, has long been working and, in fact, it has become the standard. But such a scheme has the following disadvantages:
    1. All settings are made in mysqldump, which is forced to spend extra time checking all of these options (some options are checked for each row of data).
    2. One of the main drawbacks follows from the first paragraph - if you need to restore a dump with other options, you need to re-backup with these options or manually "pick" in the dump.
    3. Despite the many settings in mysqldump, due to its console nature, it is difficult or even impossible to make difficult the choice of objects for backup (for example, so that tables with cache are not dumped, and only their structure gets into the dump).
    4. Mysqldump works with each object independently, which is why additional garbage often appears in the dump (for example, enabling / disabling indexes for empty tables, adding “fake” tables due to the inability to arrange the Views in the correct order).
    5. When recovering, a program is used that is designed, not for dumps, but for any SQL queries, because of this, time is spent on more thorough parsing.
    6. The problem with all imported software is clumsy work with encodings. This is especially felt in Runet.

    In general, I decided to experiment with the new backup concept.

    Smart backup in theory

    The following principles have been identified:
    1. The dump is done in a format containing only the necessary minimum information.
    2. Minimum backup options, for maximum execution speed.
    3. Remove all auxiliary SQL queries (DROP TABLE, etc.) from the dump and add them automatically during the recovery process.
    4. Adding advanced settings when restoring a dump.
    5. SQL parser exclusively for dumps, and able to break long INSERT queries into shorter ones.
    6. Adding meta-information to the file itself.
    7. Automatic and most importantly correct work with encodings.

    And then - a matter of technology. This concept is implemented in Sypex Dumper 2. A special dump format has also been developed, which uses delimiters and labels with special characters. Which allowed to significantly accelerate the parsing of the SQL file.

    For example, parsing speed was checked on a RAM disk, an 860 MB file was swallowed in 0.5 seconds, and phpMyAdmin took tens of minutes for a similar file (query execution was turned off in both cases).

    Smart backup in practice

    One of the nice features of a smart backup is the ability to restore individual tables (and other objects) from a dump. Moreover, they can also be restored in different ways. The simplest, classic recovery is when the table is deleted, then a new one is created and the data is poured, but more complex options are possible.

    For example, if part of the data in the table has been deleted or changed, and they need to be restored, but at the same time, new rows have been added to the table since the last backup, and they need to not be affected. In this case, it is enough to select the desired table and the REPLACE recovery mode in the dumper. As a result, the dumper will restore only those lines that are in the dump, and new lines will remain untouched. If you need to recover only deleted rows, you can use the INSERT IGNORE mode.

    Also, the dumper can restore data to the table in which the structure was changed (columns added or their order changed). In all recovery modes, it is checked whether the table exists, and if it is missing, it will be automatically created.

    Another useful feature is the ability to restore tables with the replacement of the prefix in the names.
    All recovery settings (as well as backups) can be saved, and then performed in a couple of clicks or on the crown (convenient for demo sites).


    A separate paragraph will be devoted to encodings, since quite a lot of people handle problems.
    Sypex Dumper has pretty advanced encoding features. It quietly automatically “digests” tables with different encodings in one dump, while not converting all the data into UTF-8. It also provides encoding correction functions.

    It is surprising that a lot of software, including well-known commercial scripts, still do not know how to properly work with encodings (for example, the same vBulletin 4). Yes, they make tables in the UTF-8 encoding, do the encoding in the headers and templates of UTF-8, but they forget to set the encoding of the connection to MySQL in UTF-8. As a result, MySQL thinks the data came in latin1, and tries to convert it from latin1 to UTF-8. And the worst thing about such “shoals” is that you don’t immediately notice them, because the forum looks normal, except that searching and sorting by text do not work very well.

    With the help of a damper, these problems can be quite easily solved. But, this is a topic for a separate article.


    As a bonus for the fastest, several promotional codes for obtaining free Sypex Dumper Pro licenses are :

    SX-L9A5- BK2R
    -3 UPPI
    -9 Wlbp
    SX-L9T LT

    Yeah, I didn’t expect that in the evening the first ten codes will fly away in 9 minutes, so I will spread 4 pieces in portions.

    And the last four are

    Unsubscribe about the codes you have taken, I'll add more later.

    Also popular now: