说明:
环境
mysql-master:172.16.200.43
mysql-slave:172.16.200.44
系统:CentOS7
版本:MySQL5.6.35
主从环境的搭建和5.5没有什么区别,唯一需要注意的是:开启GTID需要启用这三个参数:
- #GTID
- gtid_mode = on
- enforce_gtid_consistency = 1
- log_slave_updates = 1
gtid-mode用来设置是否开启GTID功能,如果要开启GTID功能,需要同时开启log-bin和log_slave_updates功能,另外还需要开启enforce_gtid_consistency功能。gtid_mode参数可以设置为on、off、upgrade_step_1、upgrade_step_2四种值,其中upgrade_step_1和upgrade_step_2是给将来mysql可能的新功能预留的,对当前的myql没有任何意义。同时,mysql建议在mysql_upgrade的时候,关闭gtid_mode功能和enforce_gtid_consistency功能,因为Mysql在upgrade期间可能会操作非事务的MyISAM存储引擎表,会引起报错。
- MySQL[(none)] > grant replication slave on * . * to 'slave'@'172.16.200.44'identified by '000000';
- MySQL[(none)] > flush privileges;
- MySQL [(none)]> stop slave;
- MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.200.43', MASTER_USER='slave', MASTER_PASSWORD='000000', MASTER_AUTO_POSITION=1;
- MySQL [(none)]> start slave;
- systemctl stop firewalld.service #停止firewall
- systemctl disable firewalld.service #禁止firewall开机启动
- CREATE TABLE `info_area` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID',
- `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
- `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID',
- `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID',
- `level` int(11) NOT NULL DEFAULT '0' COMMENT '类别,1、省份 2、市 3、区 4、县',
- PRIMARY KEY (`id`),
- UNIQUE KEY `UNQ_RID` (`rel_id`) USING BTREE,
- KEY `IDX_PID` (`rel_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=55185 DEFAULT CHARSET=utf8 COMMENT='地区表';
- INSERT INTO`test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES('10000', '北京', '10000', '10000', '1');
- INSERT INTO`test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES('10002', '三环以内', '100001000110002', '10001', '3');
- INSERT INTO`test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES('10003', '三环到四环之间', '100001000110003', '10001', '3');
- delete from info_area where id = 10001;
- INSERT INTO`test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES('10001', '朝阳区', '1000010001', '10000', '2');
- INSERT INTO`test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES('10001', '朝阳区', '1000010001', '10000', '2');
由于主从复制,44也会同步该条数据,但是44上面刚刚已经插入过id=10001,所以此时报错
- MySQL [(none)]> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 172.16.200.43
- Master_User: slave
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000007
- Read_Master_Log_Pos: 83009452
- Relay_Log_File: mysql-relay-bin.000002
- Relay_Log_Pos: 83009382
- Relay_Master_Log_File: mysql-bin.000007
- Slave_IO_Running: Yes
- Slave_SQL_Running: No
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 1062
- Last_Error: Could not execute Write_rows event on table test.info_area; Duplicate entry '10001' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 83009421
- Retrieved_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14
- Executed_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-13,
- 22fd263e-71d1-11e7-be5e-000c29fffc35:1
- Auto_Position: 1
- mysql> stop slave;
- mysql> set GTID_NEXT='0f869100-71d1-11e7-be5e-000c29f2e72e:14';
- mysql> begin;
- mysql> commit;
- mysql> set GTID_NEXT='AUTOMATIC';
- mysql> start slave;
- 注:
- 传统方式
- mysql> stop slave;
- mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
- mysql> start slave;
- MySQL [(none)]> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 172.16.200.43
- Master_User: slave
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000007
- Read_Master_Log_Pos: 83009452
- Relay_Log_File: mysql-relay-bin.000003
- Relay_Log_Pos: 448
- Relay_Master_Log_File: mysql-bin.000007
- 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: 83009452
- Relay_Log_Space: 83010163
- 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: 1
- Master_UUID: 0f869100-71d1-11e7-be5e-000c29f2e72e
- Master_Info_File: /data/mysql/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has 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: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14
- Executed_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14,
- 22fd263e-71d1-11e7-be5e-000c29fffc35:1
- Auto_Position: 1
- 1 row in set (0.00 sec)
来源: http://www.linuxidc.com/Linux/2017-09/146669.htm