Use XtraBackup to build MySQL master-slave synchronization without stopping the table and locking it

Xtrabackup is an open-source software developed by Percona to back up InnoDB data and support online Hot Backup (data read/write is not affected during Backup ). During backup, X

Xtrabackup is an open-source software developed by Percona to back up InnoDB data and support online Hot Backup (data read/write is not affected during Backup ). During backup, X

Xtrabackup is an open-source software developed by Percona to back up InnoDB data and support online Hot Backup (data read/write is not affected during Backup ). During backup, Xtrabackup records the binlog information of the Master in the xtrabackup_slave_info file. With this information, you can easily set up Master-slave replication.

XtraBackup has two tools: xtrabackup and innobackupex:

Xtrabackup can only back up InnoDB and XtraDB, but cannot back up MyISAM;

Innobackupex is modified by the Hot Backup script, and MyISAM and InnoDB can be backed up at the same time. However, the read lock is required to back up MyISAM.

Official Website:
Documentation:

Note: In this article, the server environment is CentOS. For other environments, modify and implement them on your own.

Modify the MySQL configuration file of the master database and slave Database

1. Master

Vim/etc/my. cnf

2. Slave:

Vim/etc/my. cnf

Server-id = 2 datadir =/var/lib/mysql install XtraBackup1 and add the source

Yum install

Check whether it is successfully added:

Yum list | grep percona

If the execution is correct, the output information is usually similar:

Percona-release.x86_640.0-1installed... Percona-. rhel5perconaPercona-. rhel5perconaPercona-. rhel5perconaPercona-. rhel5perapercona-. rhel5percona... xtrabackup. x86_641.2-22.rhel5percona2, install xtrabackup

Yum install percona-xtrabackup

Create backup

Innobackupex -- user = DBUSER -- password = DBUSERPASS/path/to/BACKUP-DIR/

If the execution is correct, the output information is usually similar:

Innobackupex: Backup created in directory '/path/to/BACKUP-DIR/2015-03-03_00-00-09' innobackupex: MySQL: 00: 53 innobackupex: completed OK!

During backup, innobackupex will call xtrabackup to back up InnoDB table data, and copy the table definition files (. frm files) and data files of MyISAM, MERGE, CSV, and ARCHIVE tables. At the same time, files related to the trigger and database configuration will be backed up. These files will be saved in a specified Backup directory named after the timestamp.

Prepare backup

Generally, after the backup is complete, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not yet synchronized to the data file. Therefore, the data files are still inconsistent. The main function of "Preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files, so that the data files are in a consistent state.

Innobackupex -- apply-log/path/to/BACKUP-DIR

If the execution is correct, the following lines of information are usually output:

Xtrabackup: starting: 01: 36 InnoDB: Starting shutdown... 09:01:40 innobackupex: completed OK!

During the "Preparation" process, innobackupex can also use the -- use-memory option to specify the memory size that can be used. The default value is usually 100 MB. If enough memory is available, you can allocate more memory to the prepare process to speed up its completion.

Restore backup

Copy data to the slave server:
Scp-r/path/to/BACKUP-DIR root @ slave_host:/data/

Restore backup data from the server:
Innobackupex -- copy-back/path/to/BACKUP-DIR

If the remaining space on the server is insufficient, you can use -- move-back to replace -- copy-back.

If the execution is correct, the last few lines of the output information are usually as follows:

Innobackupex: Starting to copy InnoDB log filesinnobackupex: in '/backup/comment 'innobackupex: back to original InnoDB log directory'/mydata/data' innobackupex: Finished copying back files .... 120407 09:36:10 innobackupex: completed OK! Start the slave database MySQL and set the master database information

After the data is restored to the data directory, make sure that the owner and group of all data files are correct users, such as mysql. Otherwise, before starting mysqld, you must modify the owner and group of the data file in advance. For example:

Chown-R mysql: mysql/mydata/data/

Start slave database:

/Etc/init. d/mysqld start

Set up master-slave accounts and permissions in the master database:

Grant replication slave on *. * TO 'slave '@ '192. 168.0.1 'identified BY 'slave ';

View the log file and position in the xtrabackup_binlog_info backup file.

MASTER_HOST =' ', MASTER_USER =' ', MASTER_PASSWORD =' ', MASTER_LOG_FILE =' ', MASTER_LOG_POS = ;

Enable master-slave synchronization:

Start slave;

View slave database status:

;

Ps: When innobackupex is used to back up data, two files in the backup data processed by-apply-log indicate the file name and location of the binlog corresponding to the backup data. But sometimes the locations of the two files may be different.
1 For pure InnoDB operations, the content of the above two files in the backup data is consistent
2 For mixed operations between InnoDB and non-transaction storage engines, the position shown in xtrabackup_binlog_info should be greater than the value shown in xtrabackup_pos_innodb. In this case, xtrabackup_binlog_info should prevail, while the content displayed in InnoDB recovery log is consistent with that shown in apply-log, which is only for InnoDB data.

Ps2: how to solve the permission problem when MySQL is started:

Error message:

: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.

Solution:
Chown-R mysql: mysql/home/data/mysql
Chcon-R-t mysqld_db_t/home/mysql

References:

1,
2,

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: