November 2, 2011 at 21:18

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.

    Introduction


    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).

    Encodings


    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.

    Bonus


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

    SX-1ADN-ZXJN
    SX-ZIPE-4K4G
    SX-TXFE-ZZUF
    SX-TGGS-TR8F
    SX-OJUS-A9YL
    SX-RGL5-HROR
    SX-L9A5- BK2R
    SX-MNOW-ZOSP
    SX-FLFJ-GWIV
    SX-6B3I-WMWU
    SX-SVZY-TG1K
    SX-AUOR-1FGD
    SX-VJEQ-9FV5
    SX-YTHD-OE9P
    SX-LXXB-PRBQ
    SX-LVB
    -3 UPPI
    SX-PXOI-3SFN
    SX-EUF2-IQWH
    SX-RWJA-JCZ8
    SX-Y4BS-C038
    SX-SSFS-KYV0
    SX-FUJ0-7AZY
    SX-GHLR-8N1Z
    SX-PK15-HRIE
    SX-I3RX
    -9 Wlbp
    SX-6RNH-XY3Q
    SX-FV4W-WBKV
    SX-7DK9-L9BP
    SX-UFNH-MWDG
    SX-V2Q8-NI6H
    SX-PQT8-TLLN
    SX-VSYE-NZOM
    SX-0YM6-PGFS
    SX-L9T LT
    -704TX-L0TD
    SX-8LOQ-YWQC

    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
    SX-IUPV-UUSO
    SX-JBBN-9MD9
    SX-PFCT-QQ4F
    SX-DZZN-9DT1

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

    Also popular now: