xtrabackup全量备份+GTID主从同步

主库操作：

执行全库备份

innobackupex --user=xtra --password=xtra /data/xtra_backup

从库操作：

执行恢复

innobackupex --apply-log /mybackup/2017-10-20_14-24-45/

innobackupex --copy-back /mybackup/2017-10-20_14-24-45/

(xtrabackup会根据 my.cnf中的配置找到datadir所在)

修改数据权限：

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

(xtrabackup恢复后目录所属用户为当前运行用户，所以需要改回来)

启动数据库：

service mysql start

(启动mysql时建议提前在配置文件中开启skip-networking read-only=ON防止从数据库误操作)

(为了使用xtrabackup更高级的功能，应当加入innodb_file_per_table=ON开启此项)

设置GTID起始位置：

cat xtrabackup_info //从中获得GTID_PURGED所需字段

binlog_pos = filename 'master-bin.007531', position '751818381', GTID of the last change '5cc28f3c-6a8d-11e4-beff-00163e5563d9:1-708436155'

mysql > reset master;

mytsql > SET @@GLOBAL.GTID_PURGED='5cc28f3c-6a8d-11e4-beff-00163e5563d9:1-708436155';

(如果不reset master 清空则会报错，GTID_PURGED can only be set when gtid_executed is empty)

mysql > show global variables like '%GTID%';

mysql > change master to master_host='********',master_user='********',master_password='********',master_auto_position=1;

mysql > start slave;

mysql > show slave status;

*************************** 1. row ***************************

Slave_IO_State: System lock

Master_Host: ********

Master_User:********

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.007532

Read_Master_Log_Pos: 533541773

Relay_Log_File: hjkj-mysql-relay-bin.000009

Relay_Log_Pos: 19288569

Relay_Master_Log_File: master-bin.007532

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: fireway.%,finance.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 422059295

Relay_Log_Space: 264988879

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: 418946

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: 100

Master_UUID: 5cc28f3c-6a8d-11e4-beff-00163e5563d9

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Reading event from the relay log

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:708436156-708662807

Executed_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:1-708604480

Auto_Position: 1

1 row in set (0.02 sec)