主:192.168.0.1
从:192.168.0.2
修改主节点配置my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=test-db
#binlog-ignore-db=sys
expire_logs_days=1
重启后查看主库状态:
show master status;
在主库上给从库同步的账号赋权:
grant FILE on *.* to 'repl'@'192.168.0.2' identified by 'repl@123';
grant replication slave on *.* to 'repl'@'192.168.0.2' identified by 'repl@123';
flush priveliges;
修改从节点配置my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2
binlog-do-db=test-db
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
配置从库同步主库信息:
stop slave;
change master to master_host='192.168.0.1',master_user='repl',master_password='repl@123',master_log_file='mysql-bin.000001',master_log_pos=8285449;
start slave;
重启后查看从库状态:
show slave status;
当Slave_IO_Running和Slave_SQL_Running都为Yes,才说明主从复制成功。
Slave_IO_Running:连接到主库,并读取主库日志到本地,生成本地日志文件。
Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。
问题及解决办法:
1.跳过错误
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;