MySQL集群搭建(1)-主备搭建

数据库在任何业务中都是最重要的环节之一，这就对数据库架构提出的较高的要求。单点数据库永远不应该出现在生产环境，我们已经目睹过太多由于单点、备份缺失造成的损失，所以，搭建高可用 MySQL 集群是非常有必要的。

搭建集群有以下几点好处:

1. 高可用性，在主节失效时自动切换，不需要技术人员紧急处理
2. 高吞吐，可以多个节点同时提供读取数据服务，降低主节点负载，实现高吞吐
3. 可扩展性强，支持在线扩容
4. 无影响备份，在备节点进行备份操作不会对业务产生影响

要说缺点，有以下几点:

1. 架构复杂，在部署、管理方面对技术人员有要求
2. 备节点拉取主节点日志时会对主节点服务器性能有一定影响
3. 如果配置了半同步复制，会对事务提交有一点影响

总的说，集群是一定要搭建的，谁敢把自己的数据跑在一个随时会有风险的数据库上呢。接下来我会以几篇文章介绍怎么从简单地主备模式到高可用架构。本节主要介绍如何搭建 MySQL 主备，注重操作，不会有太多理论讲解。

1 环境准备

1.1 启动数据库

在两台机器分别启动 MySQL 实例, MySQL 搭建方式可以参考 MySQL 安装(二进制版)

IP	系统	端口	MySQL版本	节点
192.168.41.83	Centos6.8	3306	5.7.20	Master
192.168.41.72	Centos6.8	3306	5.7.20	Salve

关键配置:

Master:

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Salve

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

配置解析

• datadir, basedir, tmpdir 分别为数据文件位置、数据库程序安装位置、临时文件位置
• server_id 实例id，注意，同一集群机器的 server_id 不能相同
• read_only 是否只读, 一般在备库设置
• log_bin, log_bin_index 二进制日志位置、二进制日志索引文件位置
• binlog_format 二进制日志格式，row 表示记录每条数据变化情况、statement 表示记录相关 sql 语句、mixed 表示两种混用，在搭建集群的时候建议使用 row 格式，如果是用 sql 语句来同步数据很容易出现数据不一致的情况
• relay_log_recovery slave 宕机后，假如中继日志损坏，则重新拉取日志，为了保证中继日志完整性，建议开启
• relay_log, relay_log_index 中继日志以及中继日志索引文件位置
• log_error 错误日志位置
• replicate_wild_ignore_table 同步时需要忽略的表，这里我们忽略了系统统计表，如果出现奇怪的同步失败情况，可以尝试开启
• plugin_dir 插件位置
• plugin_load 启动时需要加载的插件
• loose_rpl_semi_sync_master_enabled 是否开启无损半同步复制-主库（建议主备都开启,方便主备切换）
• loose_rpl_semi_sync_slave_enabled 是否开启无损半同步复制-备库（建议主备都开启,方便主备切换）

1.2 插入数据

我们假设 Master 是正在使用的数据库，现在要在线搭建备库，我们往 Master 节点插入一些测试数据

[mysql@mysql-test-83 ~]$ mydb-test_seg
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 29
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

db83-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

db83-3306>>create database mytest;
Query OK, 1 row affected (0.00 sec)

db83-3306>>use mytest;
Database changed
db83-3306>>create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default '',
    -> age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.01 sec)
db83-3306>>insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2 在线搭建主从

现在我们的环境如下

• 192.168.41.83:3306 Master 节点，正在使用
• 192.168.41.72:3306 新搭建数据库，要在上面做 192.168.41.83 的备库

2.1 创建同步用户

我们创建一个用户名为 repl 的用户，授予 REPLICATION SLAVE 权限专门用来同步

db83-3306>>CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (5.01 sec)

db83-3306>>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

db83-3306>>flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.2 备份数据

常用的备份数据的方式有 innobackupex 和 mysqldump，这里数据量少，我们用 mysqldump 进行全备

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysqldump -S /data/mysql_db/mysql_seg_3306/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

参数解析:

• -S 选择 socket 文件，本机连接数据库可以用这种方法，也可以指定 ip、端口进行连接
• -F 开始导出之前刷新日志
• --opt 如果有这个参数表示同时激活了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 参数
  • --quick 代表忽略缓冲输出，mysqldump 命令直接将数据导出到指定的SQL文件
  • --add-drop-table 就是在每个 CREATE TABEL 命令之前增加 DROP-TABLE IF EXISTS 语句，防止数据表重名
  • --add-locks 在INSERT数据之前和之后锁定和解锁对应的数据表
  • --extended-insert 表示可以多行插入
• -R 导出存储过程以及自定义函数, 如果有用到存储过程, 需要加这个参数
• --single-transaction (innodb)设置事务的隔离级别为可重复读，即 REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据, 如果全部表都为 InnoDB 就带上这个参数，保证数据一致性，备份时不会锁表。如果有 MyISAM 的表，需要锁表备份才能保证数据的一致性
• --lock-all-tables 备份过程加读锁, single-transaction 选项和 lock-all-tables 选项是二选一的
• --master-data=2 记录当前二进制日志位置, master_data取1和取2的区别，只是后者把 change master ... 命令注释起来了
• --default-character-set 选择编码, 这个选项非常重要， 编码选不对或者没有设置很容易造成乱码
• -A 代表备份所有的库

数据备份完毕后，把数据文件直接传输到 Slave 机器上

[mysql@mysql-test-83 ~]$ ll
total 772
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-83 ~]$ rsync -avzP mysql_backup_full.sql 192.168.41.72:/home/mysql/

2.3 数据恢复

在 Slave 机器上直接执行 sql 文件导入数据

[mysql@mysql-test-72 ~]$ ll mysql_backup_full.sql 
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-72 ~]$ /usr/local/mysql57/bin/mysql -S /data/mysql_db/mysql_seg_3306/mysql.sock < mysql_backup_full.sql

导入完毕，我们可以看到数据和 Master 的备份数据一致

db72-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

db72-3306>>use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db72-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

db72-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2.4 开启同步

回到备份文件，我们从头部找到 Master 备份时间点的二进制日志位置

[mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

MASTER_LOG_FILE 和 MASTER_LOG_POS 就是在 Master 执行 show master status 得到的二进制位置信息。现在，我们执行同步命令

-- 重置复制
-- reset slave;

-- 同步配置
CHANGE MASTER TO
MASTER_HOST='192.168.41.83',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;

-- 开启同步
start slave

实际执行结果如下

db72-3306>>CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.83',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

db72-3306>>start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

db72-3306>>show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.83
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 154
              Relay_Log_Space: 527
              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: 833306
                  Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
             Master_Info_File: /data/mysql_db/mysql_seg_3306/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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

我们可以从 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 这三个参数可以判断出同步状态是否正常

• Slave_IO_Running 取 Master 日志的线程, Yes 为正在运行
• Slave_SQL_Running 从日志恢复数据的线程, Yes 为正在运行
• Seconds_Behind_Master 当前数据库相对于主库的数据延迟， 这个值是根据二进制日志的时间戳计算得到的(秒)

从输出结果可以看到我们的同步是正常的，下面我们来测试一下

2.5 同步测试

在 Master 节点插入新数据

db83-3306>>insert into test1 values(0, 'chengqm', 24);
Query OK, 1 row affected (0.00 sec)

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

备节点检查数据同步状态

db72-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

可以看到数据已经同步到备节点，本次主备搭建完成