主数据库配置
[mysqld]
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
skip_slave_start=1
log_bin=master-bin.log
log-slave-updates=1
binlog_format=row
从数据库配置
[mysqld]
server_id=17
gtid_mode=on
enforce_gtid_consistency=on
skip_slave_start=1
log_bin=slave-bin.log
log-slave-updates=1
binlog_format=row
主数据库建立复制用户
mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'repl123';#创建用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限 mysql>flush privileges; #刷新权限
查看主库的位置和bin-log文件名
mysql> show master status;
+-------------------------+--------------+--------------------+------------------------------------------+---------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+--------------+------------------- +------------------------------------------+---------------------------------------------------------------+
| master-bin.000002 | 4033066 | | mysql,test,information_schema | c70af242-f78b-11e8-bff8-0050569e0666:1-753 |
+-------------------------+--------- ----+--------------------+------------------------------------------+---------------------------------------------------------------+
从库操作
CHANGE MASTER TO MASTER_HOST='123.57.44.85',MASTER_USER='slave',MASTER_PASSWORD='repl123',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;
启动从库
start slave；
查看slave状态
show slave status;
查看master状态
mysql> show slave hosts;
+-------------+-------+--------+--------------+--------------------------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-------------+-------+--------+--------------+--------------------------------------------------------+
| 17 | | 3306 | 1 | 330032df-f78b-11e8-b0ba-0033578eaa90 |
+-------------+-------+--------+--------------+--------------------------------------------------------+
跳过一个事务
和传统基于位置的主从不同，如果从库报错我们需要获得从库执行的最后一个事务，方法有如下：
- show slave status G 中的 Executed_Gtid_Set。
- show global variables like '%gtid%'; 中的 gtid_executed 。
- show master status;中的Executed_Gtid_Set。
然后构建一个空事务如下：
stop slave ;set gtid_next='4a6f2a67-5d87-11e6-a6bd-000c29a879a3:34';begin;commit;set gtid_next='automatic';start slave ;
如果是多个如下：
stop slave ;set gtid_next='89dfa8a4-cb13-11e6-b504-000c29a879a3:3';begin;commit;set gtid_next='89dfa8a4-cb13-11e6-b504-000c29a879a3:4';begin;commit;set gtid_next='automatic';start slave ;