Shulou(Shulou.com)06/01 Report--

Principle:

A redo log file, which we can also call a transaction log file, is maintained within InnoDB. The transaction log stores recorded changes to each InnoDB table data. When InnoDB starts, InnoDB examines the data file and transaction log and performs two steps: it applies (rolls forward) committed transaction logs to the data file, and rolls back data that has been modified but not committed.

Xtrabackup remembers log sequence number (LSN) at startup and copies all data files. The replication process takes some time, so if the data file changes during this period, it will put the database at a different point in time. At this point, xtrabackup runs a background process that monitors the transaction log and copies the latest changes from the transaction log. Xtrabackup must do this continuously because the transaction log rotates repeated writes and can be reused. So xtrabackup keeps recording changes to each data file in the transaction log since it starts. This is the backup process of xtrabackup.

The next step is the prepare process. In this process, xtrabackup uses the previously replicated transaction log to perform disaster recovery on individual data files (just as mysql would have done when it first started). When this process is over, the database can be restored.

The process is backup-> preparation. That is, copy all the files first, and then roll back some of the operations according to the transaction log.

The above process is implemented in the compiled binary program of xtrabackup. The program innobackupex allows us to back up MyISAM tables and frm files, adding convenience and functionality.

Innobackupex starts xtrabackup until xtrabackup copies the data file, then executes FLUSH TABLES WITH READ LOCK to prevent new writes and brushes the MyISAM data to the hard disk, then copies the MyISAM data file, and finally releases the lock.

The backup MyISAM and InnoDB tables will eventually be consistent, and at the end of the prepare process, the InnoDB table data has been rolled forward to the point where the entire backup ended, rather than where it was at the beginning of xtrabackup. This point in time is the same as the point in time when the FLUSH TABLES WITH READ LOCK was executed, so the myisam table data is synchronized with the InnoDB table data.

Similar to oracle, the prepare process of InnoDB can be called recover, and the data replication process of myisam can be called restore.

Installation of xtrabackup:

Download the rpm package or tar.gz package on the official website and decompress it. In addition, you need to install the dependency package libev through epel's yum.

# yum install libev-y

# rpm-ivh percona-xtrabackup-24-2.4.3-1.el6.x86_64.rpm

The executable files released by the rpm package are as follows:

/ usr/bin/innobackupex # encapsulated perl script

/ usr/bin/xbcloud

/ usr/bin/xbcloud_osenv

/ usr/bin/xbcrypt

/ usr/bin/xbstream

/ usr/bin/xtrabackup # main program

Use of xtrabackup:

1. Full backup

# innobackupex-user=DBUSER-password=SECRET / path/to/backup/dir/

A folder named after the current date is generated.

"if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:"

> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'bkpuser'@'localhost' IDENTIFIED BY' 123456'

> FLUSH PRIVILEGES

When using innobakupex backup, it invokes xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the table structure definition, and related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory named after time.

While backing up, innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

Each InnoDB page (usually 16k in size) contains a log sequence number, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

(2) the binary log files currently in use by the xtrabackup_binlog_info:mysql server and the location of the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb: the current position of the binary log file and the binary log file for the InnoDB or XtraDB table.

(4) xtrabackup_binary: executable file of xtrabackup used in backup

(5) backup-my.cnf: configuration option information used in backup command

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store the backup data.

Full backup\ restore example:

Backup:

Execute on node1:

# / etc/init.d/mysqld stop

# innobackupex-user=root-password=xxxx-no-timestamp / backups/

# scp-rp / backups/ root@node2:/tmp

Restore:

Perform a restore on node2:

1. Stop the mysql service on node2 (if started) and delete all files under the data directory of mysql:

# / etc/init.d/mysqld stop

# rm-fr / data/mysql/*

2. Perform the finishing operation on node2:

# innobackupex-- apply-log / tmp/backups/

3. Perform the restore operation on node2 and modify that the file belongs to the master group:

# innobackupex-- copy-back / tmp/backups/

# chown-R mysql.mysql / data/mysql/*

4. Start the mariadb service on node2:

# / etc/init.d/mysqld start

2. Incremental backup

Each InnoDB page contains a LSN message, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

To achieve the first incremental backup, you can use the following command:

# innobackupex-user=root-password=xxxx-incremental / backup-incremental-basedir=BASEDIR

Where BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / backup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located.

Note: incremental backups can only be applied to InnoDB or XtraDB tables. For MyISAM tables, incremental backups are actually performed as full backups.

There are some differences between prepare incremental backups and collating full backups, especially the following:

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay, all backup data will be merged into the full backup.

(2) "rollback" uncommitted transactions based on all backups.

Therefore, the operation becomes:

# innobackupex-apply-log-redo-only BASE-DIR

Then execute:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Then comes the second increment:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

Where BASE-DIR refers to the directory where the full backup is located, INCREMENTAL-DIR-1 refers to the directory of the first incremental backup, INCREMENTAL-DIR-2 refers to the directory of the second incremental backup, and so on, that is, if there are multiple incremental backups, do the same each time.

Example of incremental backup and recovery:

1. Make a full backup first

# innobackupex-- user=root / backups/ generates a folder named for the current time under / backups/. As shown below:

2. Perform some operations such as modifying the data table.

[the modification operation of the data table is omitted]

3. Perform incremental backup

# innobackupex-- incremental / backups/-- incremental-basedir=/backups/2015-11-08 / 19-24-05

Description:

Sometimes when you look at the xtrabackup_checkpoints in the file of the incremental backup, you find that the value has not changed, maybe the LSN change is too small. After performing a large number of data table modifications and other operations, you will find that its LSN will change, and sometimes it is normal to remain the same.

Perform full backup and incremental backup with additional scripts: full backup: innobackupex-- user=root-- password=123456 / backups/ incremental backup: innobackupex-- user=root-- password=123456-- incremental / backups/-- incremental-basedir=/backups/$ (ls-l / backups | awk'{print $NF}'| tail-1) # add the above two to the cron scheduled task, and the backup path is under the / backups/ directory.

4. Merge backup files

# innobackupex-user=root-password=123456-apply-log-only-redo-only / backups/2015-11-08 / 19-57-53 /

# innobackupex-- user=root-- password=123456-- apply-log-only-- redo-only / backups/2015-11-08 / 19-57-53 /-- incremental-dir=/backups/2015-11-08 / 19-59-31 /

# cat / backups/2015-11-08 / 19-57-53/xtrabackup_checkpoints # verify whether the merger is successful

5. Perform the operation of restoring data to the database

# innobackupex-- user=root-- password=123456-- copy-back / backups/2015-11-08 / 19-57-53 / [Note that the path of basedir should be entered here]

# ls-lh / data/mysql/ to check whether files are restored

# chown-R mysql.mysql / data/mysql/ modify file permissions

6. Start MySQL to check whether the data has been recovered

# / etc/init.d/mysqld start

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.