测试环境:
192.168.1.192
192.168.1.193
mysql5.2
配置192服务器:
/usr/local/mysql/bin/mysql -uroot -p
授权:
grant replication slave,file on *.* to 'xm1'@'192.168.1.193' identified by '100200';
刷新:
flush privileges;
配置193服务器:
/usr/local/mysql/bin/mysql -uroot -p
授权:
grant replication slave,file on *.* to 'xm2'@'192.168.1.192' identified by '100200';
刷新:
flush privileges;
停掉两台服务器的mysql服务:
/etc/init.d/mysqld stop
接下来配置两台服务器的my.cnf文件:(开启二进制日志)
vi /etc/my.cnf
192服务器:
user = mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
skip-name-resolve
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
193服务器:
user = mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
skip-name-resolve
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
同时启动mysql服务:
/etc/init.d/mysqld start
在192和193执行相同的操作:
/usr/local/mysql/bin/mysqld_safe &
之后进入mysql
/usr/local/mysql/bin/mysql -uroot -p
192服务器:
flush tables with read lock\G
show master status\G
193服务器:
flush tables with read lock;
show master status\G
备份数据。
表解锁:
UNLOCK TABLES;
停掉slave
slave stop;
在192.193两台服务器执行CHANGE MASTER TO命令。
192:
change master to master_host='192.168.1.193', master_user='xm2', master_password='100200',master_log_file='mysql-bin.000070',master_log_pos=106;
193:
change master to master_host='192.168.1.192', master_user='xm1', master_password='100200',master_log_file='mysql-bin.000067',master_log_pos=106;
192:
show processlist\G
193:
show processlist\G
192:
showdatabases;
use test;
show tables;
193:
showdatabases;
use test;
show tables;
之后就是测试的过程
分别向两个数据库创建
192:
create table table1;
切换到193:
show tables;
193:
create table table2;
切换到192:
show tables;
能彼此看见数据库,证明主主配置好了!