MySQL 双主配置

环境说明

CentOS 6.8192.168.197.61C6-node15.6.36
CentOS 6.8192.168.197.62C6-node25.6.36

MySQL安装这里不做介绍,下面是其配置文件。这里测试使用的是没有数据的纯净数据库。

node1节点配置

配置文件

[mysqld]
datadir=/data/mysql
port=3306
socket=/tmp/mysql.sock
pid-file=/data/mysql/mysqld.pid
log_error=error.log
user=mysql
skip-name-resolve
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id=61
character_set_server=utf8
log-slave-updates=1
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
binlog_format=mixed
auto-increment-increment=2
auto-increment-offset=1 [mysql]
prompt=(\\u@\\h) [\\d]>\\_ [client]
user=root
password=

创建复制帐号:

(root@localhost) [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.197.62' IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.05 sec)

node2节点配置

配置文件

[mysqld]
datadir=/data/mysql
port=3306
socket=/tmp/mysql.sock
pid=/data/mysql/mysqld.pid
log_error=error.log
user=mysql
skip-name-resolve
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id=62
character_set_server=utf8
log-slave-updates=1
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
binlog_format=mixed
auto-increment-increment=2
auto-increment-offset=2 [mysql]
prompt=(\u@\h) [\d]>\_ [client]
user=root
password=

创建复制帐号

(root@localhost) [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.197.61' IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.00 sec)

node1为主node2连接

(root@C6-node1) [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 338 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) (root@C6-node2) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.197.61',MASTER_USER='mysync',MASTER_PASSWORD='redhat',MASTER_PORT=33066,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=338;
Query OK, 0 rows affected, 2 warnings (0.11 sec) (root@C6-node2) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

node2为主node1连接

(root@C6-node1) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.197.62',MASTER_USER='mysync',MASTER_PASSWORD='redhat',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=338;
Query OK, 0 rows affected, 2 warnings (0.36 sec) (root@C6-node1) [(none)]> start slave;
Query OK, 0 rows affected (0.05 sec)

配置说明

1.注意我在mysql命令行执行命令的主机,两个的执行顺序一定不要弄错。
2.在执行start slave;一定要执行show slave status\G命令,查看Slave_IO_Running: YesSlave_SQL_Running: Yes这两个参数是否为YES,如果不是请查看日志进行排错。
3.两台机器直接网络必须是通的(废话),有时安装时会忘记关闭iptables 或者iptables 没有配置好这两种情况会直接影响到双主模型的搭建的。
4.双主模式必须要开启log-slave-updates参数,具体原因参考
5.auto-increment-increment表示自增长字段每次递增的量,其默认值是1。它的值应设为整个结构中服务器的总数,本案例用到两台服务器,所以值设为2。
6.auto-increment-offset是用来设定数据库中自动增长的起点(即初始值),因为这两能服务器都设定了一次自动增长值2,所以它们的起点必须得不同,这样才能避免两台服务器数据同步时出现主键冲突。

验证双主模型

(root@C6-node1) [test]> CREATE TABLE Stu(Id INT AUTO_INCREMENT,Name char(30) NOT NULL,PRIMARY KEY(Id));
Query OK, 0 rows affected (0.02 sec) (root@C6-node1) [test]> INSERT INTO Stu(Name) VALUES('Yang guo');
Query OK, 1 row affected (0.00 sec) (root@C6-node1) [test]> INSERT INTO Stu(Name) VALUES('Xiao long nv');
Query OK, 1 row affected (0.00 sec) (root@C6-node1) [test]> INSERT INTO Stu(Name) VALUES('Ling hu chong');
Query OK, 1 row affected (0.01 sec) (root@C6-node1) [test]> select * from Stu;
+----+---------------+
| Id | Name |
+----+---------------+
| 1 | Yang guo |
| 3 | Xiao long nv |
| 5 | Ling hu chong |
+----+---------------+
3 rows in set (0.00 sec) (root@C6-node2) [test]> INSERT INTO Stu(Name) VALUES('Ren ying ying');
Query OK, 1 row affected (0.01 sec) (root@C6-node2) [test]> INSERT INTO Stu(Name) VALUES('Dong fang bu bai');
Query OK, 1 row affected (0.01 sec) (root@C6-node2) [test]> select * from Stu;
+----+------------------+
| Id | Name |
+----+------------------+
| 1 | Yang guo |
| 3 | Xiao long nv |
| 5 | Ling hu chong |
| 6 | Ren ying ying |
| 8 | Dong fang bu bai |
+----+------------------+
5 rows in set (0.00 sec)

上面验证配置时需要主机主机的切换和数据库所在位置。

基于GTID双主

不管是搭建MySQL主从还是双主模式默的认都是需要指定binlog文件的位置以及binlog 中pos的位置,这种方式的操作及其容易弄错,所以在MySQL的5.6版本之后新增了GTID的功能。

什么是GTID

GTID的概述

1、全局事物标识:global transaction identifieds。
2、GTID事物是全局唯一性的,且一个事务对应一个GTID。
3、一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致。
4、GTID用来代替classic的复制方法,不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。
5、MySQL-5.6.5开始支持的,MySQL-5.6.10后开始完善。
6、在传统的slave端,binlog是不用开启的,但是在GTID中,slave端的binlog是必须开启的,目的是记录执行过的GTID(强制)。

GTID的组成部分

前面是server_uuid:后面是一个序列号
例如:server_uuid:sequence number
7800a22c-95ae-11e4-983d-080027de205a:10
UUID:每个mysql实例的唯一ID,由于会传递到slave,所以也可以理解为源ID。
Sequence number:在每台MySQL服务器上都是从1开始自增长的序列,一个数值对应一个事务。

GTID比传统复制的优势:

1、更简单的实现failover,不用以前那样在需要找log_file和log_Pos。
2、更简单的搭建主从复制。
3、比传统复制更加安全。
4、GTID是连续没有空洞的,因此主从库出现数据冲突时,可以用添加空事物的方式进行跳过。

GTID的工作原理

1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描。

配置

在前面node1和node2中的my.cnf配置文件中添加如下配置:

gtid_mode=on
enforce_gtid_consistency=1
skip_slave_start=1

之后需要重启MySQL数据库。
注意:由于这里配置的是双主模型所以两个数据库的数据一定要保持一致!

node1配置

(root@C6-node1) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.197.62',MASTER_USER='mysync',MASTER_PASSWORD='redhat',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

(root@C6-node1) [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

node2配置

(root@C6-node2) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.197.61',MASTER_USER='mysync',MASTER_PASSWORD='redhat',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec) (root@C6-node2) [(none)]> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

验证数据

node1 操作

(root@C6-node1) [test]> insert into Stu(Name) values('Ou yang feng');
Query OK, 1 row affected (0.07 sec) (root@C6-node2) [(none)]> select * from test.Stu;
+----+------------------+
| Id | Name |
+----+------------------+
| 1 | Yang guo |
| 3 | Xiao long nv |
| 5 | Ling hu chong |
| 6 | Ren ying ying |
| 8 | Dong fang bu bai |
| 9 | Ou yang feng |
+----+------------------+
6 rows in set (0.00 sec)

node2 操作

(root@C6-node2) [(none)]> insert into test.Stu(Name) values('Zhou bo tong');
Query OK, 1 row affected (0.01 sec) (root@C6-node1) [test]> select * from Stu;
+----+------------------+
| Id | Name |
+----+------------------+
| 1 | Yang guo |
| 3 | Xiao long nv |
| 5 | Ling hu chong |
| 6 | Ren ying ying |
| 8 | Dong fang bu bai |
| 9 | Ou yang feng |
| 10 | Zhou bo tong |
+----+------------------+
7 rows in set (0.00 sec)

注意事项

1.如果若要配置双主是需要想将两个数据库数据保持一致之后在进行gtid的配置。
2.如果是主从,则可以先配置主,而从可以先从备份文件导入部分数据,然后在进行change 操作。

以下方式仅供参考:
如果给已经运行的GTID的master端添加一个新的slave:
方法一、适用于master也是新建不久的情况。
1、如果你的master所有的binlog还在。可以选择类似于上面的方法,安装slave,直接change master to到master端。
2、原理是直接获取master所有的GTID并执行。
3、优点:简单方便。
4、缺点:如果binlog太多,数据完全同步需要时间较长,并且master一开始就启用了GTUD。

方法二、适用于拥有较大数据的情况。(推荐)
1、通过master或者其他slave的备份搭建新的slave。(看第三部分)
2、原理:获取master的数据和这些数据对应的GTID范围,然后通过slave设置@@global.gtid_purged跳过备份包含的gtid。
3、优点:是可以避免第一种方法的不足。
4、缺点:相对来说有点复杂。

通过备份搭建新的slave:(方法二的扩展)
方法一、mysqldump的方式:
1、在备份的时候指定--master-data=2(来保存binlog的文件号和位置的命令)。
2、使用mysqldump的命令在dump文件里可以看到下面两个信息:
SET @@SESSION.SQL_LOG_BIN=0;
SET @@GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8';
3、将备份还原到slave后,使用change master to命令挂载master端。
注意:在mysql5.6.9以后的命令才支持这个功能。

方法二、percona Xtrabackup
1、Xtrabackup_binlog_info文件中,包含global.gtid_purged='XXXXXX:XXXX'的信息。
2、然后到slave去手工的 SET GLOBAL.GTID_PURGED='XXXXXX:XXXX'。
3、恢复备份,开启change master to 命令。

注意:如果系统运行了很久,无法找到GTID的变好了,可以通过上面的方式进行查找。

参考学习
扩展学习

05-11 10:50