The fundamentals of MySQL backup (series one) backup and lock

Database as the only or primary persistent component in a system, the availability of the service and the reliability of the data are very demanding. As a preventive means to effectively deal with the data loss caused by the system hardware and software failure, the manual error operation, Backup is the most common operation of database operations at present. Given the impact of backup operations on database availability, MySQL officially divides backup methods into the following three main categories:

• Hot-Standby: During the backup process, theMySQL instance is always running, and read and write requests from all users are not affected.

• Cold: Before backing up, you need to stop running the MySQL instance, and the user will not be able to access the database during the entire backup process.

• Win Bei: During the backup process, the MySQL instance is running, but in order to ensure the consistency of the data, the user is allowed to lock up the way to prevent possible updates or modification operations. During the backup process, the data is read-only and all write requests are blocked.

for MySQL database, the current mainstream backup software mainly has mysqldump and xtrabackup two kinds. mysqldumpThe mysqldump is a mysql-brought backup tool that exports data from a database to a . sql file by using SQL statements such as CREATE TABLE, drop table, and insert INTO. Because the file is readable, it is called a logical backup. To ensure the consistency of the backup data,mysqldump provides the following parameters to the user:

• --add-locks   to ensure data consistency for a table, perform lock table xxx read for the tables, Either innodb table or myisam table, INSERT, delete, update, read with x Lock ( select for update) and ddl (alter) requests are blocked and do not affect snapshot read and s Lock Read (select lock in Share mode) request. This parameter does not guarantee data consistency between tables, and if queries are involved across tables, backups do not guarantee that data is consistent between tables.

• --lock-tables    locks all tables in a database to guarantee data consistency for all tables in a library. However, there is no guarantee that the data between the libraries is consistent -- add-locks parameter, in addition to the ddl Statement of the library, is blocked.

• --lock-all-tables   locks All tables of an instance to guarantee data consistency for all libraries lock tables parameters, in addition, all ddl statements for instances (create database) are blocked. unless you specify-- single-transaction option, if specified, mysqldump only when the backup starts, add a flush Tables with read lock's global lock prevents all ddl and writes, releasing the lock after the transaction is turned on, and if the innodb table is not affected during the backup process.

• --single-transaction for a storage engine that supports MVCC (multi-versioned) transactions, such as InnoDB,mysqldump provides a transaction to open before exporting the data, with the database guaranteeing consistency of the single export data, at which point All read and write operations on the InnoDB table are not blocked.

• --master-data Gets the binlog location of the backup data and the binlog file name that is used when establishing a replication relationship between instances that are recovered from the backup, which is turned on by default.

From the analysis of the parameters of the mysqldump, we can see that the reason why the backup process to lock, mainly based on the following several reasons:

• The backed-up database contains tables that do not support transactions and requires locks to guarantee a single table, between tables in a library, Data consistency between tables in different libraries of the same instance. The lock can be a table lock, a library-level lock, or even an instance-level lock, should be based on the actual business to the consistency of the need to choose different granularity of the lock, to minimize the impact of the lock on the user read and write requests.

• ddl operation.

• binlog location and file name are obtained, It takes a short time to lock all the libraries for the entire instance because binlog are instance-level, and all libraries of one instance are shared binlog files and locations. It is also worth noting that either the innodb table or the myisam table, this is a must step, just The InnoDB table can release the lock after the transaction is turned on, while the myisam needs to hold the lock for the duration of the entire backup process, differentiating between the length of time the user accesses.

Mysqldump relies on the conversion of the database layer, so does not care about the underlying storage engine, both for the support of transactions, but also for non-transactional, and can be converted between different MySQL versions, because it is a logical backup, the user can in the process of backup, Modify the data at the same time. But it is because of the need to pass the database layer conversion,mysqldump generated backup files are often very large, and slow, the backup process has a large impact on database access, for large data volumes, high business pressure instances do not apply.

Xtrabackup

xtrabackup is Span lang= "en-US" >percona company an open-source database backup software, compared to mysqldump, it is directly through the copy of physical files to achieve database backup, so the speed is much faster. xtrabackup contains two parts: xtrabackup c program and innobackupex Perl script, the former is mainly used to deal with innodb table backup, the latter is the former package, Mainly includes some communication with mysql Server and mysiam table backup.

First let's start with a quick look at trabackup enables a lock-free backup of the innodb table, i.e. all read-write requests, Its implementation is based on the innodb support for transactions, using the functionality of its crash recovery. mysql all update operations are done in memory and then asynchronously brushed into the disk for persistence. The transaction engine log mysql is not lost in memory that has not been brushed out after the storage engine that supports transactions. By logging this part of the update to the log, and then logging the log sequence number lsn while flushing the dirty page to disk, two The difference between LSN is the missing update after the data outage. When the database starts, as long as the transaction log is well-preserved, you can restore the database to a pre-crash consistency state based on redo Log and undo log.

     

By executing the show engine InnoDB status in MySQL, we can clearly see:

• Log Sequencenumber: Indicates the latest LSN of the current redo Log .

• log flushedup to: Indicates the LSN of the redo Log that is currently flushed to disk .

• Last checkpoint at:Redo log Record update has been flushed to the checkpoint LSN on disk , before the LSN of Redo The updates logged on log are all flushed to disk and can be overwritten for reuse.

Xtrabackup A backup, a current redo log is first recorded with the latest LSN, which is the last corrective point for the backup data, which is used to recover from the LSN as a starting point, followed by all redo Log record updates need to be redo or rolled back. After the LSN has been recorded ,Xtrabackup finds a data consistency point and copies the data file directly.

The table files for the InnoDB storage engine mainly include the following categories:

• ibdata1: Default tablespace file if no innodb_file_ Per_table, all tables are shared with the same file. If the innodb_file_per_table is initiated, the index, data, and insert buffers for each table bitmap information are stored separately in separate files according to the table, but the Other information, such as span lang= "en-us" >undo log, is still stored in the default table space.

• table_ Name.bid: A table file that holds data, indexes, and insert buffers for each table.

• ib_logfile0:  redo log files, recorded before backup lsn and the end of the backup lsn between redo log xtrabackup is required to be saved, Used for playback or rollback on recovery.

m Ysql redo log is reused, that is, the redo log between checkpoints is at risk of being overwritten, and redo log relatively small case, there is no end to the backup, originally recorded lsn after the Span lang= "en-us" >redo log is overwritten, causing the problem of update loss. In order to solve this problem, xtrabackup an additional thread, constantly scanning redo log log, once found lsn have propulsion , the part redo log is immediately copied out to avoid being overwritten. xtrabackup then starts copying all the table files including the default tablespace file until the copy is finished and then records lsn, which completes the innodb table backup.

inlog issues. And this problem needs to rely on innobackupex to complete, first in xtrabackup after copying data files, Innobackupex executes flush table with read lock on mysql, which locks all bands of the entire instance x lock read, UPDATE, INSERT, delete, and ddl statements, equivalent to freezing lsn. At this time mysql  current lsn will not advance, and then record lsn, which is the final consistent lsn, then records the file name and location of the current Binlog, and then begins copying mysql the table definition file myisam table file, and finally unlock tables release lock, thus completing a complete backup process.