Mysql主从配置操作

1- 两个mysql服务器

主(master): 腾讯云mysql version: 5.7.18

从(slave): 本地虚拟机mysql version: 5.7.21

2-配置master

1. 创建用户:    
CREATE USER  '用户名'@'%'  IDENTIFIED BY '密码';

2. 赋予权限:   
GRANT REPLICATION SLAVE ON *.* TO  '用户名'@'%' identified by '密码';

3 导出所有数据(该命令在不用进入mysql)
mysqldump -h ip -P 端口 -uroot -p'密码'  --set-gtid-purged=OFF --all-databases >文件名.sql

3-配置slave

1.数据导入从库:
mysql -uroot -p‘密码’ < 文件名.sql

2.从库指向主库:
CHANGE MASTER TO MASTER_HOST = '主库IP', MASTER_USER = '同步用户', MASTER_PASSWORD = '主库密码', MASTER_PORT = 主库端口, MASTER_LOG_FILE='主库binlog', MASTER_LOG_POS=主库pos点;

注意: MASTER_LOG_FILE和MASTER_LOG_POS 的值在2-3导出的文件中查找,
或者: 执行完2-3命令, 在master数据库, mysql的help_topic表中查找

3: 开启从库的GTID(注意顺序):
		set global gtid_mode='OFF_PERMISSIVE';
		set global gtid_mode='ON_PERMISSIVE';
		set global enforce_gtid_consistency=ON;
		set global gtid_mode='ON'  

6、启动从库
start slave;

4-查看主从状态

master正常状态:

slave正常状态:

5-我在配置过程中出现的bug: Slave_IO_Running:NO, 查看slave的mysql错误日志

1: Slave I/O for channel ‘’: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. Error_code: 1593

对slave进行操作:
执行:
		show global variables like 'gtid_mode';
发现     
		gtid_mode = off
需要重新执行:
		stop slave;
		set global gtid_mode='OFF_PERMISSIVE';
		set global gtid_mode='ON_PERMISSIVE';
		set global gtid_mode='ON' ;
		start slave;

2:紧接着又出现: Slave I/O for channel ‘’: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’, Error_code: 1236

master上执行2个sql

   flush logs;
   show master status\G;

slave执行3个sql

stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=190; (这两个变量的值来源于master)
start slave;