MySQL主从复制(GTID)

1. 查询GTID是否开启

show variables like ‘%gtid%’;

2. 配置文件/etc/my.cnf 中开启gtid

[mysqld]
server-id=1
gtid_mode=ON # 开启gtid

3. 在主节点创建用户

create user ‘slave’@‘%’ identified by ‘123456’;

4. 授权

grant replication slave on . to ‘slave’@‘%’;

5. 查看权限

show grants for ‘slave’@‘%’;

6. 刷新权限

flush privileges;

7. 在从节点执行，建立主从关系

change master to master_host=‘192.168.10.103’,master_port=3306,master_user=‘slave’,master_password=‘123456’,master_auto_position=1,get_master_public_key=1;
start slave;

8. 查看是否成功建立主从关系,Slave_IO_Running:和Slave_SQL_Running都是Yes即可

show slave status \G;

可能用到的命令慎用

1. 查询MySQL用户

select user,host,plugin from mysql.user;

2. 修改密码

update user set authentication_string=“123456” where user=“copy”;

3. 将密码设置为空

update user set authentication_string=‘’ where user=‘copy’;

4. 选择插件修改密码

alter user ‘copy’@‘%’ identified with mysql_native_password by ‘123456’;

5. 重置salve

reset slave;