mysql数据主备要求_Mysql数据库主主互备

实验环境：

系统： 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的状态。