Mysql主从复制记录笔记
一、主机的配置
[mysqld]
log-bin=mysql-bin
binlog-do-db=test2024
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
server-id=1
二、从机的配置
server-id=2
log-bin=mysql-bin
binlog-do-db=test2024
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
read-only = 1
三、确认server-id
show VARIABLES like 'server_id';
两个服务的server-id不允许相同,必须是唯一值
四、确认mysql-8.0.25-winx64\Data下的:auto.cnf里的ID,不是同一个id,也必须唯一
五、在主机上建账号
create user 'copyuser'@'%' identified with mysql_native_password by '123456';
grant replication slave on *.* to 'copyuser'@'%';
六、在主机上查询source_log_file和source_log_pos
show master status;
七、在从机上执行命令
start replica;
change replication source to source_host='192.168.0.109',source_port=3366, source_user='copyuser',source_password='123456',source_log_file='mysql-bin.000004',source_log_pos=7860;
stop replica;
show master status;
show VARIABLES like 'server_id';
create user 'copyuser'@'%' identified with mysql_native_password by '123456';
grant replication slave on *.* to 'copyuser'@'%';
show master status;
start replica;
change replication source to source_host='192.168.0.109',source_port=3366, source_user='copyuser',source_password='123456',source_log_file='mysql-bin.000004',source_log_pos=7369;
stop replica;