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

This article introduces what the backup and recovery of innobackupex is like, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Principle

Phase: backup backup-pre-restore prepare-restore restore

Table files may contain incomplete transactions, so it is necessary for prepare to change them into consistent data files, so the copied files must be inconsistent, and then crash recovery is performed on each file to achieve consistency.

At startup, a LSN (log sequence number) is recorded, and then all Innodb data files are copied out, so that the copied data files are inconsistent, but XtraBackup runs a process in the background to record all changes to redo log file.

Binary program (such as xtrabackup_55) completed, if you use the innobackupex script, after the completion of the steps, innobackupex will backup the MyISAM table and .frm file, then to ensure the consistency of the data will first lock the table, use the FLUSH TABLES WITH READ LOCK command to lock the table and then copy the file, and then release the lock.

(recovery) and restore. After the end of prepare, Innodb's table is restored to the point in time when copying the Innodb file ends, which is the starting point for locking the table to replicate the MyISAM table, so the final data is consistent. Usually we execute prepare twice during recovery because the second prepare will help us generate redo log files, thus speeding up the startup of the MySQL database.

Put the database backup in the BACKUP-DIR directory, create a new subdirectory by default,-- no-timestamp will skip this function; option to specify the memory used to speed up the progress; default is 100m; read variables such as datadir/innodb_data_home_dir/innodb_data_file_path

The table is the innodb table, and finally the logfile;--data-dir directory must be empty

Incremental backup files, the contents are as follows

The contents of the document are as follows

It's a bit complicated. If you perform a transaction-consistent restore on base backup, it can no longer be used for incremental backup recovery, and the-redo-only option must be specified for this.

The recovery single table provides restore datafile, and there is also blockrecover for bad blocks, that is, to avoid full database recovery as much as possible; it also provides a similar function that allows a single tablespace to be restored; let innodb adopt slow shutdown (full purge + change buffer merge) to ensure that the tablespace is consistent and import

Dump,5.6 of data dictionary is not required

Create tables with the same structure and copy them to the data directory

Based on point-in-time recovery, record the current location of the database at the time of backup binlog, which is also the end point of database consistency recovery

Perform a point-in-time recovery

-- start-position=57-- stop-datetime= "11-12-25 01:00:00" | mysql-u root-p

Perform a backup on slave

Pay attention to the following two parameters

-- dependent information

This option is useful when backing up replication secondary servers. It prints the binary log location and name of the primary server. It also writes this information to the xtrabackup_slave_info file as the change master command. You can set up a new secondary server for this primary server by starting the secondary server on this backup and issuing the CHANGE master command using the binary log location saved in the xtrabackup\ u slave\ u info file.

-- secure slave backup

Stop the dependent SQL thread and wait for the backup to start until the slave open temporary table in the Show state is zero. If there is no open temporary table, a backup will be made, otherwise the SQL thread will be started and stopped until there is no open temporary table. If the Slave_open_temp_tables does not change to zero after-- safe Slave backup timeout seconds, the backup will fail. After the backup completes, the dependent SQL thread restarts.

About how the backup and recovery of innobackupex is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.