MySQL数据库主主复制并基于MyCAT实现高可用

简单介绍

数据库的高可用,首先需要多主多从的支持.那么多主之间怎么同步呢?
不妨先来看一下MySQL主从复制
看完主从复制,聪明的你是否已经想到怎么同步多主呢?
没错,那就是多主之间互为主从.
废话不多说,开始配置.

环境描述

  • 操作系统:CentOS 7.4 1708
  • 服务器:192.168.0.123
  • 服务器:192.168.0.124

MySQL主主复制

先将两个服务器锁表

1
2
mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;

修改两个服务器的数据库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
123服务器配置:
$ vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1 #服务器唯一ID,每台服务器需不同
auto-increment-increment = 2 #有几个服务器,这里就是几
auto-increment-offset = 1
#做主主备份的时候,因为每台数据库服务器都可能在同一个表中插入数据,如果表有一个自动增长的主键,那么就会在多服务器上出现主键冲突。
#解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是,假设需要将来可能需要10台服务器做备份,将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始,不超过auto-increment-increment。
binlog-ignore-db = mysql #跳过mysql库,避免主主复制的冲突
binlog-ignore-db = information_schema,performance_schema #同样不需要复制
relay_log = mysql-relay-bin #开启中继日志,复制线程先把远程的变化复制到中继日志中,再执行。
slave-skip-errors = all #跳过所有的sql错误
log-slave-updates = 1 #中继日志执行之后将变化写入自己的二进制文件


124服务器配置:
$ vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2 #服务器唯一ID,每台服务器需不同
auto-increment-increment = 2 #有几个服务器,这里就是几
auto-increment-offset = 2
#做主主备份的时候,因为每台数据库服务器都可能在同一个表中插入数据,如果表有一个自动增长的主键,那么就会在多服务器上出现主键冲突。
#解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是,假设需要将来可能需要10台服务器做备份,将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始,不超过auto-increment-increment。
binlog-ignore-db = mysql #跳过mysql库,避免主主复制的冲突
binlog-ignore-db = information_schema,performance_schema #同样不需要复制
relay_log = mysql-relay-bin #开启中继日志,复制线程先把远程的变化复制到中继日志中,再执行。
slave-skip-errors = all #跳过所有的sql错误
log-slave-updates = 1 #中继日志执行之后将变化写入自己的二进制文件

重启两个服务器MySQL

1
systemctl restart mysqld.service

建立账户

在123服务器上建立只能被124服务器帐户并授权slave

1
2
3
mysql> MySQL -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* to 'user'@'192.168.0.124' identified by 'password';
mysql> FLUSH PRIVILEGES;

在124服务器上建立只能被123服务器帐户并授权slave

1
2
3
mysql> MySQL -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* to 'user'@'192.168.0.123' identified by 'password';
mysql> FLUSH PRIVILEGES;

查看数据库bin-log状态

123服务器

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

124 服务器

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

File和Position已经要记下来,后面要用到。

指定同步位置

123服务器执行

1
2
3
4
5
6
mysql> change master to
-> master_host='192.168.0.124',
-> master_user='user',
-> master_password='password',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=120;

124服务器执行

1
2
3
4
5
6
mysql> change master to
-> master_host='192.168.0.123',
-> master_user='user',
-> master_password='password',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=120;

启动slave同步进程并查看状态

123服务器状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.169.0.124
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000292
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

124服务器状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.169.0.123
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000292
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。

验证同步

分别创建数据库

123服务器

1
msyql> create database testa;

124服务器

1
msyql> create database testb;

查看

123服务器

1
2
3
4
5
6
7
8
9
10
11
msyql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testa |
| testb |
+--------------------+
5 rows in set (0.01 sec)

124服务器

1
2
3
4
5
6
7
8
9
10
11
msyql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testa |
| testb |
+--------------------+
5 rows in set (0.01 sec)

可以看到两个服务器都有testatestb数据库

通过MyCAT中间件实现高可用+读写分离

MyCAT介绍和安装请参考,这里就不再多说。
之前的配置只能说实现了负载读写分离,这里主要说一下怎么配置MyCAT来实现高可用.
通过看MyCAT的开发文档做以下调整

配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 定义MyCat的逻辑库 -->
<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema>
<!-- 定义MyCat的数据节点 -->
<dataNode name="pcxNode" dataHost="dtHost" database="test" />
<!-- 定义数据主机dtHost,连接到MySQL读写分离集群 ,schema中的每一个dataHost中的host属性值必须唯一-->
<!-- dataHost实际上配置就是后台的数据库集群,一个datahost代表一个数据库集群 -->
<!-- balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡-->
<!-- writeType="0",所有写操作发送到配置的第一个writeHost,这里就是我们的hostmaster,第一个挂了切到还生存的第二个writeHost-->
<!-- switchType="2",基于 MySQL 主从同步的状态决定是否切换-->
<dataHost name="dtHost" maxCon="500" minCon="20" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<!--心跳检测 -->
<heartbeat>show slave status</heartbeat>

<!--配置后台数据库的IP地址和端口号,还有账号密码 -->
<writeHost host="hostM1" url="192.168.0.123:3306" user="proxy" password="msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv">
<!-- <readHost host="hostS2" url="192.168.0.124:3306" user="re_proxy" password="msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv"/> -->
</writeHost>
<writeHost host="hostM2" url="119.29.110.63:3306" user="proxy" password="msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv"/>
</dataHost>
</mycat:schema>

重启MyCAT

1
$ mycat restart

文章不错,你都不请我喝杯茶,就是说你呀!
0%
upyun