mysql互为主备_MySQL双主互为主从配置

在使用双主互为主从的模式前提是互为主从的两个数据库，表的主键必须是自增的。

实验步骤是在主从复制已经配置好的两台数据库上配置。说下思想吧，其实很简单就是把原来的从库当主库，把原来的主库当从库。按照主从配置的方法从前把主库当从库，把从库当主库的配置一遍。

1.配置my.cnf

主库1配置my.cnf：

auto_increment_increment = 2

auto_increment_offset = 2

log-bin =/mysql_multi_case/3306/mysqld-bin

log-slave-updates = 1

主库2配置my.cnf：

auto_increment_increment = 2

auto_increment_offset = 1

log-bin =/mysql_multi_case/3307/mysqld-bin

log-slave-updates = 1

提示：以上参数都是配置mysqld下。

2.创建用户，给予replication slave 权限。

grant replication slave on *.* to 'rep_3306'@'127.0.0.1' identified by 'admin';　　　　　　　　####(3307上创建)

3.导出数据(原来的从库库数据)

mysqldump -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -A -B --master-data=1 -x --events > /work/bak/3307all.sql

4.导入数据(导入原来的主库)

mysql -uroot -padmin -S /mysql_muitl_case/3306/mysql.sock < /work/bak/3307all.sql

5.在主库1上执行change master to .. 语句

change master to

master_host='127.0.0.1',

master_port=3307

master_user='rep_3306',

master_password='admin';

提示：因为全备指定选项--master-data=1 所以我们在3306上执行change master to语句就不用指定master_log_file和master_log_pos的值。

mysql> change master to master_host='127.0.0.1',master_port=3307,master_user='rep_3306',master_password='admin';

Query OK, 0 rows affected (0.53 sec)

提示：我们可以去3306下的data目录下查看master-info文件

[root@qiuhom data]# cat /mysql_multi_case/3306/data/master.info

18

mysqld-bin.000007

1240

127.0.0.1

rep_3306

admin

3307

60

0

0

1800.000

0

提示：我们可以看出master_log_file是mysqld-bin.000007，pos是1240，说明--master-data=1 记录了我们binlog日志文件名和位置点。我们在开启同步看看slave status里是否一致。

6.开启同步和检查slave状态

mysql> start slave;

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: 127.0.0.1

Master_User: rep_3306

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysqld-bin.000007

Read_Master_Log_Pos: 1240

Relay_Log_File: relay-bin.000008

Relay_Log_Pos: 1387

Relay_Master_Log_File: mysqld-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1240

Relay_Log_Space: 1837

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

1 row in set (0.00 sec)

接下来我们再来看看两个库的线程情况

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show processlist;"

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1 | rep | 127.0.0.1:49248 | NULL | Binlog Dump | 1148 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

| 5 | system user | | NULL | Connect | 516 | Waiting for master to send event | NULL |

| 6 | system user | | NULL | Connect | 1029 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |

| 8 | root | localhost | NULL | Query | 0 | NULL | show processlist |

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

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show processlist;"

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1 | system user | | NULL | Connect | 1156 | Waiting for master to send event | NULL |

| 2 | system user | | NULL | Connect | 702 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |

| 5 | rep_3306 | 127.0.0.1:43773 | NULL | Binlog Dump | 524 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |

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

提示：可以看到3306和3307分别有3个线程，一个主io,一个从io,一个sql线程。和我们配置主从的线程是不是不一样了。这样两个互为主从的实例就配置好了。接下来我们测试下写入数据是什么情况。

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "create database qiuhom;use qiuhom;create table test(id int not null auto_increment primary key,name char(10) not null);"

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "show databases;use qiuhom;show tables;"

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| qiuhom |

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

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

| Tables_in_qiuhom |

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

| test |

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

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "use qiuhom;insert into test(name) values('aa'),('bb'),('cc');"

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "show databases;"

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| qiuhom |

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

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "use qiuhom;show tables;"

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

| Tables_in_qiuhom |

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

| test |

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

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "use qiuhom;insert into test(name) values('dd'),('ee'),('ff');"

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "use qiuhom;insert into test(name) values('gg'),('hh'),('ii');"

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3306/mysql.sock -e "select * from qiuhom.test;"

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

| id | name |

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

| 2 | aa |

| 4 | bb |

| 6 | cc |

| 7 | dd |

| 9 | ee |

| 11 | ff |

| 12 | gg |

| 14 | hh |

| 16 | ii |

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

[root@qiuhom data]# mysql -uroot -padmin -S /mysql_multi_case/3307/mysql.sock -e "select * from qiuhom.test;"

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

| id | name |

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

| 2 | aa |

| 4 | bb |

| 6 | cc |

| 7 | dd |

| 9 | ee |

| 11 | ff |

| 12 | gg |

| 14 | hh |

| 16 | ii |

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

提示：从上面的测试看我们发现我们俩个库都可以插入数据，况且插入数据有个规律，主库1插入的数据主键都是偶数，主库2插入的数据的主键都是奇数。这就是我们配置参数auto_increment_offset 有很大的关系，我们设置3306等于2所有它会从表里最后一个位置的主键开始偏移2开始计，因为我测试的表示空表所以3306开始插入的数据主键是从2开始的，然后两个主键相隔两个数，又计下一个主键。3307我们设置的=1 所3307插入的数据的主键就是从最后插入数据的主键位置偏移1开始计。所以从7开始计，它插入的数据主键也是相隔两数字，所以第二次插入数据的主键就是9以此类推。

我们查看3307里面的qiuhom库下的test表的数据和3306的一样，这样我们就实现了双主互为主从，不管在哪个库写另外一库就复制当从的角色。其实我们从字面意思也可以理解互为主从就是说一个数据库又是主库又是从库的意思。