master mysql
log-bin=mysql-bin #打开mysql二进制日志
server-id = 1 #设置mysql_id,主从不能相同
binlog-do-db=test #设置二进制日志记录的库
binlog-ignore-db=mysql ##设置二进制日志不记录的库
sync_binlog=1
slave mysql
log-bin=mysql-bin
server-id = 2
replicate-do-db=test #设置同步的库
replicate-ignore-db=mysql #设置不同步的库
log-slave-updates #同步后记录二进制日志
slave-skip-errors=all
sync_binlog=1
slave-net-timeout=60
分别重启主从mysqld服务,登录主mysql,在主上执行flush tables with read lock;后将test数据库的数据copy到从上,并记录下主上show master status\G的结果:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 1237
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
然后执行unlock tables
登录从mysql,在从上执行:
stop slave;
change master to master_user='root',
master_password='123456' ,
master_host='192.168.0.111',
master_log_file='mysql-bin.000010',
master_log_pos=580;
start slave;
show slave status\G;
如果出现下面的情况,说明主从同步已经成功!
Slave_IO_Running: Yes
Slave_SQL_Running: Yes