（亲测）MySQL集群搭建-主备搭建

心已赠人 2023-01-17 09:21 212阅读 0赞

### 文章目录 ###

*   *  前言
     *  环境准备
     *  在线搭建主从

## 前言 ##

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

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

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

架构复杂，在部署、管理方面对技术人员有要求  
备节点拉取主节点日志时会对主节点服务器性能有一定影响  
如果配置了半同步复制，会对事务提交有一点影响  
总的说，集群是一定要搭建的，谁敢把自己的数据跑在一个随时会有风险的数据库上呢。接下来我会以几篇文章介绍怎么从简单地主备模式到高可用架构。本节主要介绍如何搭建 MySQL 主备，注重操作，不会有太多理论讲解。

## 环境准备 ##

**1.1 启动数据库**

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

<table> 
 <thead> 
  <tr> 
   <th>IP</th> 
   <th>系统</th> 
   <th>端口</th> 
   <th>MySQL版本</th> 
   <th>节点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.41.83</td> 
   <td>Centos6.8</td> 
   <td>3306</td> 
   <td>5.7.20</td> 
   <td>Master</td> 
  </tr> 
  <tr> 
   <td>192.168.41.72</td> 
   <td>Centos6.8</td> 
   <td>3306</td> 
   <td>5.7.20</td> 
   <td>Salve</td> 
  </tr> 
 </tbody> 
</table>

**关键配置:**

Master:

[client]
    port = 3306
    default-character-set=utf8mb4
    socket = /data/mysql_db/mysql_test/mysql.sock
    
    [mysqld]
    datadir = /data/mysql_db/mysql_test
    basedir = /usr/local/mysql57
    tmpdir = /tmp
    socket = /data/mysql_db/mysql_test/mysql.sock
    pid-file = /data/mysql_db/mysql_test/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_test/mysql-bin
    log_bin_index = /data/mysql_log/mysql_test/mysql-bin.index
    binlog_format = row
    relay_log_recovery=ON
    relay_log=/data/mysql_log/mysql_test/mysql-relay-bin
    relay_log_index=/data/mysql_log/mysql_test/mysql-relay-bin.index
    log_error = /data/mysql_log/mysql_test/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_test/mysql.sock
    
    [mysqld]
    datadir = /data/mysql_db/mysql_test
    basedir = /usr/local/mysql57
    tmpdir = /tmp
    socket = /data/mysql_db/mysql_test/mysql.sock
    pid-file = /data/mysql_db/mysql_test/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_test/mysql-bin
    log_bin_index = /data/mysql_log/mysql_test/mysql-bin.index
    binlog_format = row
    relay_log_recovery=ON
    relay_log=/data/mysql_log/mysql_test/mysql-relay-bin
    relay_log_index=/data/mysql_log/mysql_test/mysql-relay-bin.index
    log_error = /data/mysql_log/mysql_test/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 -S /data/mysql_db/mysql_test/mysql.sock

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)

## 在线搭建主从 ##

现在我们的环境如下

*  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_test/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_test/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` 得到的**二进制位置信息**。现在，我们执行同步命令

-- 同步配置
    CHANGE MASTER TO
    MASTER_HOST='192.168.41.83',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl',
    MASTER_LOG_FILE='mysql-bin.000002',
    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.000002',
        -> 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)

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

[MySQL]: https://blog.csdn.net/weixin_42740530/article/details/116231575