先锁表
FLUSH TABLES WITH READ LOCK;
导出要同步的库,导入到另一台mysql中,必须保证两台数据库要同步的库数据一致。不一致的话失败的几率很大。。
UNLOCK TABLES
ps:这一步也可以直接停掉所有的tomcat,保证同步成功之前数据没有再操作即可。
假设两台服务器分别为:
主服务器:192.168.0.1
从服务器:192.168.0.2
建立mysql同步账号,密码为123456
主 mysql>grant replication slave,file on *.* to 'backup'@'192.168.0.2' identified by '123456'; mysql>flush privileges; 从 mysql>grant replication slave,file on *.* to 'backup'@'192.168.0.1' identified by '123456'; mysql>flush privileges;
检查同步账号是否能互相访问:
登录192.168.0.1,执行
mysql -h 192.168.0.2 -u backup -p
登录192.168.0.2,执行
mysql -h 192.168.0.1 -u backup -p
上面两步确保OK。有问题检查防火墙配置。
主my.ini中添加:
server-id = 1 log-bin=mysql-bin binlog-do-db = hro_d binlog-ignore-db = mysql replicate-do-db = hro_d auto_increment_offset = 1 auto_increment_increment = 2 replicate-ignore-db = mysql
从my.ini中添加:
server-id = 2 log-bin=mysql-bin binlog-do-db = hro_d binlog-ignore-db = mysql replicate-do-db = hro_d auto_increment_offset = 2 auto_increment_increment = 2 replicate-ignore-db = mysql
主执行:
mysql> change master to master_host='192.168.0.2',master_user='backup',master_password='123456',master_port=3306; mysql> start slave;
从执行:
mysql> change master to master_host='192.168.0.1',master_user='backup',master_password='123456',master_port=3306; mysql> start slave;
主从分别执行:
mysql> show master status;
#如果显示为空,重启mysql即可。重启后继续执行:show master status;
分别记住logfile和log_pos,类似'mysql-bin.000001',‘master_log_pos=120’这样的。
主
mysql> stop slave; mysql> change master to master_host='192.168.0.2',master_user='backup',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120; mysql> start slave; mysql> show slave status\G
#其中master_log_file是从机显示的logfile名,master_log_pos同理。
#Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 这两项都是YES的话,ok,同步成功
如果不是YES,查看mysql日志,解决问题。
从
mysql> stop slave; mysql> change master to master_host='192.168.0.1',master_user='backup',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120; mysql> start slave; mysql> show slave status\G
#其中master_log_file是主服务器显示的logfile名,master_log_pos同理。
测试,随便找一台mysql改点数据,看另一台mysql是否同步过去。