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;