实验环境:
系统: Centos6.4 64
masterA:192.168.1.223
masterB:192.168.1.224
要求:实现两服务器之间数据的同步
首先做的事安装mysql,并能正常登陆
[root@masterA ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
修改登陆密码,默认没有密码
[root@masterA ~]# mysqladmin -uroot password 123456
[root@masterA ~]# mysql -uroot -p123456
mysql>
[root@masterA ~]#cp /etc/my.cnf /etc/my.cnf.bak
[root@masterA ~]# vim /etc/my.cnf
server_id = 1 将这个修改为1,另外一台修改为2或是其他,之后重启
[root@masterA ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'replication';
Query OK, 0 rows affected (2.24 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.15 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 732 | | | d8c0e68c-0b35-11e5-96a0-000c29e6a569:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
(masterB上查看的 记下mysql-bin.000004、732这两个值)
mysql> change master to
-> master_host='192.168.1.224',
-> master_user='replication',
-> master_password='replication',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=732;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
至此,masterA配置完毕
[root@masterB ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'replication';
Query OK, 0 rows affected (2.24 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.15 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 582 | | | d8c0e68c-0b35-11e5-96a0-000c29e6a569:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
(在masterA上查看的,记下mysql-bin.000005、582这两个值)
mysql> change master to
-> master_host='192.168.1.223',
-> master_user='replication',
-> master_password='replication',
-> master_log_file='mysql-bin.000005',
-> master_log_pos=582;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.09 sec)
查看slave的状态,如果这两项都为YES状态,说明成功。(A和B上面都显示为YES)
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
所有配置基本完成,下面进行测试就OK了
在masterA上面创建数据会自己同步到masterB上,同理,在masterB上面创建一个数据也会自己同步到masterA上面,说明成功了。
如果出现IO和SOL不是YES状态,执行
stop slave;
reset slave;
start slave;
或是重新执行一遍一下这些命令
先stop slave
mysql> change master to
-> master_host='IP地址',
-> master_user='授权的用户',
-> master_password='用户密码',
-> master_log_file='log bin 的值',
-> master_log_pos=pos 的值;
然后
start slave
重新查看slave的状态。