Xtrabackup Online Backup Main library, build slave, use Gtid mode

mysql:5.6.29
xtrabackup:2.2.10
master:192.168.166.129
slave:192.168.166.131
MySQL Data Catalog:/data/mysql
MySQL backup directory:/data/dbbak/full #确保有足够的磁盘空间

Installing Xtrabackup software on master and slave machines
1. Installation dependent
Yum-y Install Libaio perl-time-hires perl-dbd-mysql Perl-io-socket-ssl rsync.x86_64

2, Installation Xtrabackup
RPM-IVH percona-xtrabackup-2.2.10-1.el6.x86_64.rpm

Operation on Master Machine
1. Create a backup account in the database
mysql> CREATE USER ' bkpuser ' @ ' localhost ' identified by ' S3cret ';
Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION client,process on *. * to ' bkpuser ' @ ' localhost ';
mysql> FLUSH privileges;

2. Back up the main library
Cd/data/dbbak
Innobackupex--user=bkpuser--password=s3cret--parallel=4/data/dbbak/full--no-timestamp--defaults-file=/etc/ My.cnf
Tar czvf full.tar.gz/data/dbbak/full--remove-files
SCP full.tar.gz [Email protected]:/data/dbbak/

Operation in Slave machine
1. Stop MySQL, delete or rename MySQL data directory
/etc/init.d/mysql.server stop
Rm-rf/data/mysql

2. Application log and database restore
cd/data/dbbak/
Innobackupex--apply-log/data/dbbak/full #应用日志
Innobackupex--copy-back/data/dbbak/full #数据库还原

Chown Mysql:mysql/data/mysql-r #修改数据目录的宿主权限
/etc/init.d/mysql.server Start #启动mysql

3. Set the Gtid that have been executed
[Email protected]_166_131 dbbak]# cat/data/mysql/xtrabackup_info |grep binlog_pos
Binlog_pos = GTID of the last change '132028ab-abc5-11e6-b2f0-000c29a60c3d:1-45675'

#查看slave已执行的gtid是否为空, is empty and needs to be checked. Backup restore is incorrect. If it is correct, reset MASTER needs to be performed, or Gtid cannot be set.
Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000001
position:151
binlog_do_db:
binlog_ignore_db:
Executed_gtid_set:
1 row in Set (0.00 sec)

ERROR:
No query specified
mysql> SET @MYSQLDUMP_TEMP_LOG_BIN = @ @SESSION. Sql_log_bin;
Mysql> SET @ @SESSION. sql_log_bin= 0;
Mysql> SET @ @GLOBAL. Gtid_purged= '132028ab-abc5-11e6-b2f0-000c29a60c3d:1-45675‘;
Mysql> SET @ @SESSION. Sql_log_bin = @MYSQLDUMP_TEMP_LOG_BIN;
mysql> start slave;
Mysql> Show slave status;
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.166.129
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000011
read_master_log_pos:550555
relay_log_file:relay-bin.000004
relay_log_pos:862
relay_master_log_file:mysql-bin.000011
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:550555
relay_log_space:1771
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:202181
Master_uuid:132028ab-abc5-11e6-b2f0-000c29a60c3d
Master_Info_File:mysql.slave_master_info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:132028ab-abc5-11e6-b2f0-000c29a60c3d:45676-45680
executed_gtid_set:132028ab-abc5-11e6-b2f0-000c29a60c3d:1-45680
Auto_position:1
1 row in Set (0.00 sec)

Xtrabackup Online Backup Main library, build slave, use Gtid mode