得此良药,病可尽除
1、首先mysql数据结构必须相同,并且互相都能通过ip访问,MYSQL 版本相同
2、授权
A:grant replication slave,file on *.* to 'conn1'@'192.9.1.1' identified
by '123456';
flush privileges;
B:grant replication slave,file on *.* to 'conn2'@'192.9.1.2' identified
by '123456'with grant option;
flush privileges;
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
A、B、C 停止mysql服务
3、修改配置文件
==========================A、B同时修改=========================================
user = mysql
log-bin=mysql-bin
#(A=1,B=2,C=3,...)
server-id = 1
#(要同步的数据库)
binlog-do-db=sys
#不同步的数据库
#binlog-ignore-db-start
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=test
#binlog-ignore-db-end
#同步的数据库
#replicate-db-start
replicate-do-db=sys
#replicate-db-end
#同步的表
#replicate-table-start
replicate-do-table=sys.sex
#replicate-table-end
#不同步的表
#-------------ignore-table-start
replicate-ignore-table=sys.age
replicate-ignore-table=sys.pwd
#-------------ignore-table-end
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
#(自增模式,A=1,B=2, C=3,... )
auto_increment_offset=1
4、备份数据
5、查看主机日志名称和存储位置
A:
show master status;
记录 master_log_file和master_log_pos
B:
show master status;
记录 master_log_file和master_log_pos
6、停止slave服务
A:slave stop;
B:slave stop;
7、授权(用到第五步记录的信息)
A:
change master to
master_host='192.9.1.2',
master_user='conn2',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=106;
B:
change master to
master_host='192.9.1.2',
master_user='conn2',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=106;
8、启动slave
A: slave start;
B: slave start;
9、重启A B mysql服务
10、查看配置信息
A: show slave status;
B: show slave status;
当显示waitting for master event 为成功
出现 error connect to...为未成功