Xtrabackup do MySQL master-slave synchronization

First, background:

The line of a main library pressure is larger, so add a slave library, but can not restart or stop the normal operation of the main library, unable to lock the library lock table to affect the normal operation of the business. So here's the idea of Xtrabackup.

Second, Xtrabackup Introduction: The Advantages of Xtrabackup: 1 、备份完成快速、可靠 2 、备份期间不间断的事务处理 3 、节省磁盘空间和网络带宽 4 、自动备份验证 5 、提高正常运行时间由于更快的恢复时间 备份INNODB引擎的数据库不会锁库锁表，但是备份MyISAM的表还是会锁的，还支持增量备份。
官方地址： https://www.percona.com/Iii. Environmental Descriptionsystem version: CentOS Linux release 7.0.1406 (Core)kernel version: 3.10.0-123.9.3.el7.x86_64? mysql version: 5.6.26xtrabackup Version:percona-xtrabackup-2.2.9-5067.el7.x86_64.rpm 四、安装XtraBackup 安装在主库上recommended that you use the RPM method of installation, many documents on the Web are compiled and installed, compile and install time-consuming effort, I am the master from the beginning to use the compilation installation, found that the server resources are exhausted at compile time can not ssh, resulting in the server can not process the request. So finally chose the rpm way to install, this way installs very quickly.

# Yum Install perl-time-hires # Yum Install perl-dbd-mysql-y #   Yum Install perl-digest-md5-y#  wget https://www.percona.com/downloads/XtraBackup/ xtrabackup-2.2.9/binary/redhat/7/x86_64/percona-xtrabackup-2.2.9-5067.el7.x86_64.rpm#  rpm- IVH percona-xtrabackup-2.2.9-5067.el7.x86_64.rpm

V. Main Library master-slave configuration

1. Main Library Configuration/etc/my.cnf

Add Server-id and bin-log#  vim/etc/my.cnfserver-id =log-bin = Mysql-bin  

2. Main Library Configuration Authorized account

mysql> grant replication Slave on * * to [email protected]'192.168.199.183' 123 ' ; MySQL

3. Manually set the main library Server-id and Log-bin parameters

Global server_id=128

Because these two parameters are dynamic parameters, you can take effect without restarting the main library if your main library already has these two parameters set, ignore this step

Vi. master-slave configuration from the library

From the library installation steps slightly

1. Configuring/ETC/MY.CNF from the Library

Add Server-id and bin-log#  vim/etc/my.cnfserver-id = 129#  If you need to make a bin-log backup from the library, you can add the following parameters Log-bin = mysql-bin   log-slave-updates = 1   expire- logs-days=10

Vii. backing up the main library data

1. Create a backup directory on the main library machine

# mkdir-p/backup/full_data

2. Perform a full library backup

2.1. Backup

# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123    --socket=/tmp/mysql.sock    /data /backup/full_dataPS: After the backup is completed, a point-in-time directory is generated in the/backup/full_data/directory, which is 2016-07-14_05-19-52. You can also add a--no-timestamp parameter does not produce this directory, directly back up to the/backup/full_data/directory

2.2. Replay Redo Log

# below Preparing,undo Undo uncommitted transactions, replay redo log # Innobackupex  --defaults-file=/etc/my.cnf  --user=root    --password=123  --apply-log   -- Socket=/tmp/mysql.sock   /data/backup/full_data/2016-07-14_05-19-52

ps:1, ls-l /data/backup/full_data/2016-07-14_05-19-52/view, you find that xtrabackup will put your database datadir all the data The files are copied and there are 5 new xtrabackup_ files, and we care about xtrabackup_binlog_info this file because it records the location of the Binlog from the library to the main library and the Pos point.

2, if only want to back up a library, add "--include=navy" to specify the name of the library, "--databases=navy" does not.

Viii. recovering data from a library

1. Stop from the library

# /etc/init.d/mysqld Stop

2. Copy the main library backup files to the slave library machine

# scp-ap/data/backup/full_data/2016-07-14_05-19-52   [Email protected]:/data/mysql/

3. Modify the Restore file permissions

# chown-r mysql:mysql/data/mysql/  

4. Start the database

# /etc/init.d/mysqld Start

5. Confirm that the data is restored successfully

Log in from the library, execute show databases;

6. Confirm the location point of the synchronization

This information can be viewed from the library or from the main library, and we are viewing it from the library.

#mysql-bin.000016        4542174

Nine, start the master-slave synchronization

1. Configure slave-master configuration from the library

#登录从库并执行

MySQL > Change MASTER   to master_host='192.168.199.182', master_user=' Rep ',  master_password='123', master_log_file='  mysql-bin.000016', master_log_pos=4542174;

2. Start syncing

mysql> start slave;

3. View sync Status

Mysql> Show Slave Status\g

Xtrabackup do MySQL master-slave synchronization