Smart backup MySQL
I think many people in life have a situation where there is 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 the old entries in these tables, without overwriting the 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 solutions for MySQL backup were analyzed. And in the first place, of course, the source of the complete mysqldump.
In mysqldump, a file is created with a set of SQL queries that, when restored, are simply split into a separator and “fed” by MySQL. Such a scheme, in principle, has been working for a long time and, in fact, it has become a standard. But this scheme has the following disadvantages:
- All settings are made in mysqldump, which is forced to spend extra time checking all these options (some options are checked for each data row).
- One of the main shortcomings follows from the first point - if you need to restore a dump with other options, then you need to re-do a backup with these options or manually “tinker” in the dump.
- Despite the many settings in mysqldump, due to its console, complicated or even difficult selection of objects for backup is not possible (for example, so that tables with a cache are not dumped, and only their structure got into the dump).
- 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 because of the inability to arrange Views in the correct order).
- When restoring a program is used, which is designed, not for dumps, but for any SQL queries, because of this, time is spent on more thorough parsing.
- The problem of all imported software is clumsy work with encodings. This is especially felt in runet.
In general, I decided to experiment with the new concept of backup.
')
Smart backup in theory
The following principles were identified:
- The dump is done in a format containing only the necessary minimum information.
- Minimum backup options for maximum speed.
- All auxiliary SQL queries (DROP TABLE, etc.) are removed from the dump and added automatically during the recovery process.
- Adding advanced settings when restoring dump.
- SQL parser sharpened solely on dumps, and able to break long INSERT queries into shorter ones.
- Adding meta information to the file itself.
- Automatic and most importantly correct work with encodings.
And then - the matter of technology. This concept is implemented in Sypex Dumper 2. A special dump format was also developed, which uses separators and labels with special characters. This has significantly accelerated the parsing of the SQL file.
For example, the parsing speed was checked on a RAM disk, the 860 MB file was swallowed in 0.5 seconds, and phpMyAdmin took tens of minutes to a similar file (queries were 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 be restored in different ways. The simplest, classic recovery is when a table is deleted, then a new one is created and data is uploaded, but more complex variants are possible.
For example, if part of the data in a table is deleted or changed, and they need to be restored, but new rows have been added to the table since the last backup, and you need to prevent them from being affected. In this case, it is sufficient 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 the new lines will remain intact. If you only need to restore deleted rows, you can use the INSERT IGNORE mode.
Also, the dumper can restore the data to the table in which the structure was changed (added columns or changed their order). In all recovery modes, it is checked whether the table exists, and if it does not exist, it will be automatically created.
Another useful feature is the ability to restore tables with the replacement of the prefix in the titles.
All recovery settings (as well as backup) can be saved, and then performed in a couple of clicks or by cron (convenient for demo sites).
Encodings
I will devote a separate paragraph to encodings, since quite a few people handle problems.
Sypex Dumper has quite advanced functions for working with encodings. It quietly automatically “digests” tables with different encodings in the same dump, without converting all the data into UTF-8. It also provides functions for the correction of encodings.
It is surprising that a lot of software, including well-known commercial scripts, still do not really know how to work with encodings (for example, the same vBulletin 4). Yes, they make tables in UTF-8 encoding, they make encoding in UTF-8 headers and templates, but they forget to set the connection encoding to MySQL to UTF-8. As a result, MySQL thinks that the data came in latin1, and tries to convert them from latin1 to UTF-8. And the worst thing about such “jambs” is that you won't notice them right away, since the forum looks fine, except that searching and sorting by text do not work very well.
With the help of the dumper you can solve these problems quite easily. But, this is a topic for a separate article.
Bonus
As a bonus for the fastest multiple promo codes for getting free Sypex Dumper Pro licenses:
Yeah, I didn’t expect that in the evening the first ten codes would fly away in 9 minutes, so I’ll post it in portions of 4 each.
And the last four
Unsubscribe from the codes taken, later I will add more.
Source: https://habr.com/ru/post/131797/
All Articles