MySql集群搭建实战

Image

主从同步复制原理

在开始之前，我们先来了解主从同步复制原理。

复制分成三步：

  1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

  2. slave将master的binary log events拷贝到它的中继日志(relay log)；

  3. slave重做中继日志中的事件，将改变反映它自己的数据。

Image

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。

MySQL5.6以前的版本复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。 MySQL5.6版本参数slave-parallel-workers=1 表示启用多线程功能。

MySQL5.6开始，增加了一个新特性，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

双主架构方案思路是

1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；

2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；

3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）；

4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;

5.建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；

这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；

但是也有几个不足的地方:

1.masterB可能会一直处于空闲状态（可以用它当从库，负责部分查询）；

2.主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据，这样可能会造成一定程度的同步延时；

MySQL主主同步方案

Docker搭建多主多从

创建工作目录

1
2
3
4
5
6
7
8
9
10
11
12
mkdir /usr/local/mysql_multi
cd /usr/local/mysql_multi

mkdir master1 master2 slave1 slave2
cd /usr/local/mysql_multi/master1
mkdir data conf logs
cd /usr/local/mysql_multi/master2
mkdir data conf logs
cd /usr/local/mysql_multi/slave1
mkdir data conf logs
cd /usr/local/mysql_multi/slave2
mkdir data conf logs

创建容器

1
docker pull mysql:8.0.22

master1

1
2
3
4
5
docker run -p 4306:3306 --name master1 \
-v /usr/local/mysql_multi/master1/conf:/etc/mysql/conf.d \
-v /usr/local/mysql_multi/master1/logs:/var/log/mysql \
-v /usr/local/mysql_multi/master1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 -itd mysql:8.0.22　

master2

1
2
3
4
5
docker run -p 4307:3306 --name master2 \
-v /usr/local/mysql_multi/master2/conf:/etc/mysql/conf.d \
-v /usr/local/mysql_multi/master2/logs:/var/log/mysql \
-v /usr/local/mysql_multi/master2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 -itd mysql:8.0.22

slave1

1
2
3
4
5
docker run -p 5306:3306 --name slave1 \
-v /usr/local/mysql_multi/slave1/conf:/etc/mysql/conf.d \
-v /usr/local/mysql_multi/slave1/logs:/var/log/mysql \
-v /usr/local/mysql_multi/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 -itd mysql:8.0.22

slave2

1
2
3
4
5
docker run -p 5307:3306 --name slave2 \
-v /usr/local/mysql_multi/slave2/conf:/etc/mysql/conf.d \
-v /usr/local/mysql_multi/slave2/logs:/var/log/mysql \
-v /usr/local/mysql_multi/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 -itd mysql:8.0.22

命令参数注解：

参数 说明
--name 容器名称
-p 映射容器端口号和宿主机端口号
-v 挂载宿主机目录和docker容器中的目录，前面是宿主机目录，后面是容器内部目录
-d 后台运行容器
-e 环境参数，MYSQL_ROOT_PASSWORD设置root用户的密码
-itd 指定数据库版本

查看容器是否创建成功

1
docker ps

配置(双主双从)

master1

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
vi /usr/local/mysql_multi/master1/conf/my.cnf

[mysqld]
# master01主服务器01唯一ID
server-id=4306

# 启用二进制日志
log-bin=mysql-bin

#从库的中继日志，主库日志写到中继日志，中继日志再重做到从库
# relay-log=myslql-relay-bin

# binlog保留时间7天
expire_logs_days=7

# binlog 文件的大小
max_binlog_size=1G

#设置logbin格式。取值：STATEMENT （默认），ROW，MIXED
binlog_format=ROW

# 设置不要赋值的数据
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 设置需要复制的数据(可选)
# 如果配置了此项,就是只复制那个数据库, 如果不指定就是所有
# binlog-do-db=需要复制的主数据库1

# 设置login格式
binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

# 该从库是否写入二进制日志。如果需要成为多主则可启用。只读可以不需要
log-slave-updates=1

#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2

# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

master2

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
vi /usr/local/mysql_multi/master2/conf/my.cnf

[mysqld]
# master01主服务器01唯一ID
server-id=4307

# 启用二进制日志
log-bin=mysql-bin

#从库的中继日志，主库日志写到中继日志，中继日志再重做到从库
# relay-log=myslql-relay-bin

# binlog保留时间7天
expire_logs_days=7

# binlog 文件的大小
max_binlog_size=1G

#设置logbin格式。取值：STATEMENT （默认），ROW，MIXED
binlog_format=ROW

# 设置不要赋值的数据
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 设置需要复制的数据(可选)
# 如果配置了此项,就是只复制那个数据库, 如果不指定就是所有
# binlog-do-db=需要复制的主数据库1

# 设置login格式
binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

# 该从库是否写入二进制日志。如果需要成为多主则可启用。只读可以不需要
log-slave-updates=1

#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2

# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

slave1

1
2
3
4
5
6
7
vi /usr/local/mysql_multi/slave1/conf/my.cnf

[mysqld]
# 从服务唯一ID
server-id=5306
# 启用中继日志
relay-log=mysql-relay

slave2

1
2
3
4
5
6
7
vi /usr/local/mysql_multi/slave2/conf/my.cnf

[mysqld]
# 从服务唯一ID
server-id=5307
# 启用中继日志
relay-log=mysql-relay

配置完成后重启容器

1
docker restart $(docker ps -q)

创建数据库相关账户并授权

1
2
3
4
5
6
7
8
9
#进入容器 master1是容器名称 
docker exec -it master1 bash
#进入mysql窗口
mysql -uroot -p123456
create user 'master1'@'%' identified with mysql_native_password by 'a123456';
#一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.245.139，加强安全。
grant replication slave,replication client on *.* TO 'master1'@'%'; 
#刷新生效
flush privileges;

同样的方式配置master2

从机复制主机

获取master1信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
docker exec -it master1 bash
mysql -uroot -p123456
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 867
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

# 查看容器IP
docker inspect master1 |grep IPAddress #master1
"SecondaryIPAddresses": null,
"IPAddress": "172.17.0.2",
"IPAddress": "172.17.0.2",

slave1复制master1

1
2
3
4
5
6
7
8
9
10
docker exec -it slave1 bash
mysql -uroot -p123456

mysql> change master to master_host='172.17.0.3',
    ->        master_user='master2',
    ->        master_password='a123456',
    ->        master_log_file='mysql-bin.000001',
    ->        master_log_pos=867;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启用同步进程

1
start slave;

查看同步从库状态

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: master1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 867
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 867
              Relay_Log_Space: 529
              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: 4306
                  Master_UUID: 75a0b5cc-202d-11ec-8644-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

slave2复制maste2,操作流程同上

两主机互相复制

master1复制master2

1
2
3
4
5
6
7
8
9
10
docker exec -it master1 bash
mysql -uroot -p123456

mysql> change master to master_host='172.17.0.3',
    ->        master_user='master2',
    ->        master_password='a123456',
    ->        master_log_file='mysql-bin.000001',
    ->        master_log_pos=867;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启用同步进行：

1
start slave;

查看状态：

1
mysql> show slave status\G;

测试以上集群是否成功

在master1上创建一个库

1
CREATE DATABASE test_db;

创建一个表

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `user`(
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `age` INT(3) NOT NULL,
     PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

1
2
INSERT INTO user (name, age) VALUES ("zhaoming", 18);
INSERT INTO user (name, age) VALUES ("张三", 20);

在master2和slave1中可以看到数据已经同步过来了