Shulou(Shulou.com)05/31 Report--

Facebook how to do MySQL backup, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

How does Facebook make MySQL backups

From mysqldump to Xtrabackup

We use mysqldump to back up the database every day, and mysqldump backs up the data logically, just as the application accesses the database. Mysqldump reads tables from the database in the way of SQL statements, and transfers the table structure and data to this article. The biggest problem with mysqldump is that it is too slow (for some of our large databases, it usually takes 24 hours or more). And reading data in the way of SQL statement may cause random reading of the disk, which will increase the load of the host and affect the performance. For a long time, we can run multiple instances to do backups concurrently, which can shorten the backup time, but will result in more load and affect the performance of the host.

Another feasible backup method is to do physical backup (we call it binary backup, binarybackup), which reads database disk files at the operating system level, rather than through SQL statements. In this way, during the backup process, the data cannot be transactional consistent as it is when SQL is read. Only when the backed-up data files are restored in the database are they consistent again, similar to the restart of the database down.

We have modified the enhanced Xtrabackup to meet our additional needs:

1. Support for fast table-level restore

two。 Enhanced full and incremental backup

3. Support for mixed incremental backup

Xtrabackup supports incremental backup, that is, backing up data that has changed since the last full backup. In this way, we can reduce backup space (for example, an incremental backup once a day, a full backup once a week). Xtrabackup also supports multi-level incremental backups, but we don't use them to avoid complexity.

How does Facebook make MySQL backups

1. Table level reduction

We wrote a PHP script to read and restore the specified table from the binary backup file. Currently, the script cannot read information from the backup file to create a table structure, so a corresponding empty table must be prepared in advance. We have also made changes to Xtrabackup to support this tool. This change is to support Xtrabackup import and export single table. The restore of a single table is much faster than full restore, because only the corresponding table information needs to be read from the file.

two。 Adjust full and incremental replication

Fb was a user of Xtrabackup's early incremental backup function. At first, Xtrabackup incremental backup did not work for some databases with large scales. Then we worked with percona to solve these problems.

Xtrabackup only has local incremental backup function, that is, incremental backup files must be on the same host as MySQL. We modify it to support remote incremental backup, that is, send the backed-up data to the remote host at the same time through a pipeline-like way. It is not advisable for us to do an incremental backup locally and then transfer it to a remote host over the network, because it will greatly increase local write operations.

Xtrabackup uses 1MB as a chunk to read database files. We find that using 8MB can double the speed of incremental backups and make full backups 40% faster.

3. Let incremental backups become true increments

Xtrabackup's incremental backup reads each page of the database to determine which page has changed. We created a page tracker to track the modified page,. by reading the transaction log and through the bitmap of each table This allows us to keep track of which page has changed and which has not, and we can read only those page that have changed. We call this a real incremental backup.

Ironically, we found that this kind of real incremental backup is slower than ordinary incremental backup.. This is because ordinary incremental backups use 8MB's chunk to read files, while real incremental backups read files of varying sizes, from 16KB (the size of a page in INNODB) to 8MB, depending on how many consecutive page have been modified. So in many of our scenarios (about 10% of the 30% page has been modified since the last full backup), a real incremental backup takes more IO calls than a normal incremental backup.

Because we have made improvements, we have a mixed incremental backup that reduces the number of IO by avoiding reading unmodified page. In our scenario, this hybrid incremental backup reduces the size of IO,IO by 20% to 30%, ranging from 16KB to 8MB.

The following table describes the different results of using the above improved methods to process approximate 750GB data. Because of the speed of mysqldump, mysqldump only runs on a few databases. We use gzip to compress the results of mysqldump, which is very slow, but the compression ratio is very high.

QPress is used to compress binary backups. It is much faster than gzip, but less efficient. Because we often do incremental backups and seldom do full backups, the space required for the entire binary backup is about the same as that required by mysqldump.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.