搭建mysql互为主主_mysql 互为主从(主主)配置

A.B两台mysql服务器

MySQL-master1：172.16.1.107 255.255.252.0 OS版本：CentOS 6.0X64位 mysql 5.1.54

MySQL-master2：172.16.0.106 255.255.252.0 OS版本：CentOS 5.5X32位 mysql 5.1.54

一、MySQL master-master配置

1、修改MySQL配置文件

两台MySQL均如要开启binlog日志功能，开启方法：在MySQL-master1  配置文件/etc/my.cnf

[MySQLd]段中加上

server-id = 1

binlog-do-db = test

binlog-ignore-db = mysql

replicate-do-db = test

replicate-ignore-db = mysql

master-host     =   172.16.1.107

master-user     =   backup

master-password =   123456

master-port     =  3306

master-connect-retry = 10

sync-binlog = 1

log-bin=mysql-bin

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=1   //指的是服务器频繁的刷新日志这个保证了在其中一台挂掉的话日志刷新到另外一台从而保证了数据的同步。

在MySQL-master2  配置文件/etc/my.cnf

[MySQLd]段中加上

server-id = 2

binlog-do-db = test         //需要同步的数据库

binlog-ignore-db = mysql   //不需要同步的数据库名，多个数据库中间用逗号(,)隔开；

replicate-do-db = test    //需要同步的数据库

replicate-ignore-db = mysql

master-host     =   172.16.0.106

master-user     =   backup

master-password =   123456

master-port     =  3306

master-connect-retry = 10 //断点重连时间

sync-binlog = 1

log-bin=mysql-bin       //记录二进制文件

log-slave-updates      //表示 如果一个MASTER 挂掉的话，另外一个马上接管。

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=2  //指的是服务器频繁的刷新日志这个保证了在其中一台挂掉的话日志刷新到另外一台从而保证了数据的同步。

将两个配置文件保存,分别重启mysql服务器

binlog-do-db=abc 需要同步的数据库,同步多个数据库重复设置选项 binlog-do-db=test   和 replicate-do-db=test

例如

binlog-do-db=test1

replicate-do-db=test1

binlog-do-db=test2

replicate-do-db=test2

2、在master1和master2上进行数据库操作

master1/master2: mysql>Slave stop;

master1/master2: mysql>Reset master;

master1: mysql>grant replication slave on *.* to backup@172.16.0.106 identified by '123456';

master2: mysql>grant replication slave on *.* to backup@172.16.1.107 identified by '123456';

master1/master2:mysql> show master status;

master1:记录file与Position的值

mysql> show master status

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      106 | test         | mysql            |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

master1:

mysql>change master to master_host='172.16.0.106',

mysql>master_user='backup',

mysql>master_password='123456',

mysql>master_port=3306,

mysql>master_log_file='mysql-bin.000002', //填写master2的值

mysql>master_log_pos=106;                 //填写master2的值

Query OK, 0 rows affected (0.17 sec)

mysql>slave start;

Query OK, 0 rows affected (0.00 sec)

master1:查看同步状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.0.106

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 106

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

master2:mysql> slave stop;

Query OK, 0 rows affected (0.01 sec)

master2:mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      106 | test         | mysql            |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

master2:mysql> change master to master_host='172.16.1.107',

-> master_user='backup',

-> master_password='123456',

-> master_port=3306,

-> master_log_file='mysql-bin.000002',  //填写master1的值

-> master_log_pos=106;                  //填写master1的值

Query OK, 0 rows affected (0.10 sec)

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.107

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 106

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

看到如下状态就表示同步成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3、测试数据库同步，在A的test库里增加表，在B的test库里删除表Use test；

master1:Create table abc (id int(10));

查看B中是否有abc这个表

master2: drop table abc

查看master1中是否已经删除abc这个表做到这里就完成了。