MySQL集群搭建-主备搭建

本文最后更新于 1288 天前，其中的信息可能已经有所发展或是发生改变。

单点数据库永远不应该出现在生产环境，我们已经目睹过太多由于单点、备份缺失造成的损失，所以，搭建高可用 MySQL 集群是非常有必要的。

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

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

要说缺点，有以下几点:

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

因为主备搭建数据库是非常有必要的.

1.环境准备

1.1 启动数据库

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

IP	系统	端口	MySQL版本	节点
10.143.87.21	红帽7.4	3306	5.7.34	Master
10.143.87.22	红帽7.4	3306	5.7.34	Salve

关键配置:

Master:

# my.cnf
# 客户端设置，即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 21330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

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


#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/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:

# my.cnf
# 客户端设置，即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 22330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

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

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/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 ####
read_only=1

配置解析:

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 是正在使用的数据库，而新的需求是要在线搭建salve备库。我们现在模拟往 Master 节点插入一些测试数据

[root@wydsj-gpapp03 init.d]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

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

mysql> create database mytest;
Query OK, 1 row affected (5.03 sec)

mysql> use mytest;
Database changed
mysql> 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.00 sec)

mysql> 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

mysql> 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)

mysql>

2.在线搭建主从

现在我们的环境如下

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

2.1 创建同步用户

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

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

或者：

grant replication slave on *.* TO 'repl'@'%' identified by 'repl';

2.2 备份数据(可省略)

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

/usr/local/mysql/bin/mysqldump -S /data4/mysql_db/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql -pyourpasswd

参数解析:

-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 机器上

scp mysql_backup_full.sql mysql@wydsj-gpapp04:~

2.3 数据恢复

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

/usr/local/mysql/bin/mysql -pyourpasswd -S /data4/mysql_db/mysql.sock < mysql_backup_full.sql

导入成功后，查看数据是否基本一致…

2.4 开启同步

下面配置文件中的MASTER_LOG_FILE 和 MASTER_LOG_POS

是在 Master 执行 show master status 得到的二进制位置信息。

现在，我们执行同步命令(在slave数据库中)：

-- 重置复制
reset slave;

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

-- 开启同步
start slave

---实际执行结果如下:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

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

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看同步状态:

show slave status \G;

执行结果：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.143.87.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             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: information_schema.%,performance_schema.%,sys.%
                   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: 21330601
                  Master_UUID: ff0f9dbc-1c7f-11ec-8cfb-ac1f6bcf601f
             Master_Info_File: /data4/mysql_db/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 节点插入新数据

mysql> insert into test1 values(0, 'glj', 25);
Query OK, 1 row affected (0.01 sec)

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

---salve

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