1. MySQL5.7实现主从复制
通过主从复制，主要解决数据备份以及高可用和故障切换以及恢复的问题。主从复制有几种形式：
- 1主1从 ：一台master节点以及一台slave节点，master节点执行数据写入，slave节点设置为只读。
- 1主多从 ：上述形式的扩展，通过设置多个从节点，可以更好的提升数据安全性。
- 级联复制（1主1中间1从） ：这种形式在主从服务器之间，增加了中间服务器作为中继。
后面用master节点表示主服务器，用slave节点表示从服务器。也可能会混合使用这种表述。
这里使用第一种架构，主从复制的架构图如下所示：
主从复制过程中，会涉及到一些线程。比如主节点上的 dump thread 以及从节点的 I/O thread, SQL thread 线程。各线程的作用如下：
- dump thread ：为从节点的 I/O thread 线程提供一个dump线程，用于向从节点发送二进制日志事件（binary log events）。
- I/O thread ：向master节点请求二进制日志事件，并保存在中继日志中。
- SQL thread ：从中继日志中读取日志事件，并在从节点上执行二进制日志中记录的操作，实现数据同步。
主从复制为异步复制，所以性能相对较好，但是带来的问题也很明显，就是存在master节点和slave节点上的数据库数据不一致的情况。此时就需要借助其他一些手段实现主从数据库之间的数据一致性检查（比如Percona提供的Percona Toolkit，就是一个很好的用于检查主从数据一致性的工具）。此处不展开介绍数据一致性检查的操作。
另外，由于是异步复制，所以主节点在给从节点发送数据的时候，并不关心从节点是否完成了数据接收，为了增加数据传输的可靠性，可以设置 半同步复制，此处不包含半同步复制。
在开始主从复制配置之前，需要注意一下场景，通常有两种实际场景：
- master节点和slave节点都是新部署的数据库库服务器：此时直接配置主从复制即可，因为不涉及到双方数据一致与否的问题。
- master节点的数据库中已经有一些数据：此时要配置主从复制，就需要先将master节点数据库全量备份之后，将备份文件在slave节点上恢复，然后再配置主从环境。
在接下来的环境中，会采用第二种方式进行主从复制的环境配置。即在原来有数据的数据库服务器作为master节点，另外一台服务器作为slave节点。服务器的角色规划如下表所示：
| Server Name | Server Role | Server IP | MySQL Version |
|---|---|---|---|
| c7u6s2 | master node | 192.168.122.21 | 5.7.34 |
| c7u6s3 | slave node | 192.168.122.22 | 5.7.34 |
1.1 基础环境
MySQL服务器以及客户端安装步骤省略。不再介绍，具体安装方法参见官方手册：Chapter 2 Installing and Upgrading MySQL。
我的环境中，master节点上已经安装了MySQL服务器并且其中包含了一些数据。具体如下所示：
[root@c7u6s2:~]# 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.
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.01 sec)
(root@localhost) [(none)]> show tables in mydb;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
(root@localhost) [(none)]> show tables in testdb;
+-------------------+
| Tables_in_testdb |
+-------------------+
| department |
| dept_trigger_time |
| employee |
| gradeinfo |
| information |
| operate |
| product |
| score |
| student |
| student2 |
| trigger_test |
+-------------------+
11 rows in set (0.00 sec)
(root@localhost) [(none)]>
slave节点上也已经安装了好了MySQL服务器和对应的客户端命令行工具。其中的数据库内容具体如下所示：
[root@c7u6s3:~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34 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.00 sec)
mysql>
上述即为基础的环境。
1.2. 配置主从复制
同增量备份一样，主从复制也依赖于二进制日志功能，每个复制服务器（slave node）都会接收到一份二进制日志文件。在增量备份中，通过二进制日志文件中记录的数据库和表的内容的修改，可以实现数据库的增量备份；同样的，在主从复制环境中，也可以通过二进制日志文件实现数据的同步。
接下来的配置操作中，在master节点上和slave节点上的操作是有些差别的，所以下面分别介绍这两个节点上的配置。
1.2.1. master节点上的配置
确认master节点开启了二进制日志功能，如果为开启，则需要开启二进制日志功能。同时确保服务器具有唯一的服务器ID（在my.cnf配置文件中通过字段 server-id 字段进行设置）。随后创建一个具有复制权限的用户，用于执行后续的主从复制操作。
由于此处的场景是master节点上已经存在了一些数据，所以此时就需要先在master节点上做全量备份，然后将备份结果分发到slave节点上，进行导入。
配置文件的内容如下所示：
[root@c7u6s2:~]# cat /etc/my.cnf | egrep -v '^$|^#'
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate_password_policy = LOW
validate_password_length = 4
character_set_server = utf8mb4
symbolic-links=0
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
log-bin=/var/log/mysql-bin-log/mysql-binlog-c7u6s2.0000001
server-id=1
general_log=on
general_log_file=/var/log/mysqld/mysql-ge.log
slow_query_log=1
long_query_time=2
log_slow_admin_statements=1
slow_query_log_file=/var/log/mysql-slow-query/mysql-slowquery-c7u6s2.log
[mysql]
safe-updates = 0
default_character_set = utf8mb4
prompt=(\\u@\\h) [\\d]>\\_
auto-rehash
[client]
default_character_set = utf8mb4
上述的 [mysqld] 部分指定了server-id为1，比给你且开启了二进制日志功能。默认情况下，server-id的值为0，表示不接受任何复制服务器的连接。设置为非0值，表示接收复制服务器的连接。另外，master节点上必须要开启二进制日志的功能，否则无法完成主从复制。上述的配置文件中，通过字段log-bin=/var/log/mysql-bin-log/mysql-binlog-c7u6s2.0000001指定了开启二进制日志功能的同时，指定了二进制日志文件的存储位置。
注意： 在InnoDB存储引擎中，还应该设置两个字段：
- innodb_flush_log_at_trx_commit=1
- sync_binlog=1
设置这两个选项，可以最大可能的保持复制的耐久性以及一致性。此外，还需要确保没有设置 skip_networking 这个系统变量。
至此，配置文件就修改完成了。
创建具有复制权限的用户
在shell终端中执行mysql -uroot -p命令连接到mysql服务器，然后执行用户创建以及授权的操作。具体如下所示：
(root@localhost) [(none)]> create user if not exists repluser@'192.168.122.%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant replication slave on *.* to repluser@'192.168.122.%';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]>
上述操作中创建了一个名为repluser@192.168.122.%的用户，给其所有数据库的replication slave权限。
查看二进制日志的坐标信息
执行mysql -uroot -p命令连接到mysql服务器，由于准备进行全量备份（数据库快照），所以此时应该避免出现未完成的事务提交操作，执行语句flush tables with read lock即可。然后执行show master status语句查看二进制日志的坐标信息。具体如下所示：
(root@localhost) [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-binlog-c7u6s2.000009
Position: 641
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
(root@localhost) [(none)]>
从上述输出中可以看出，当前的二进制日志文件名为mysql-binlog-c7u6s2.000009，同时记录的位置为641。
创建数据库快照（全量备份）
对于InnoDB存储引擎，采用mysqldump命令实现全部数据库的全量备份即可。具体如下所示：
[root@c7u6s2:~]# mysqldump -uroot -p -A -F --single-transaction --master-data=1 > master_all_db-`date +%F`.bak
Enter password:
[root@c7u6s2:~]# ls -lhF master_all_db-2021-08-09.bak
-rw-r--r-- 1 root root 881K 8月 9 00:10 master_all_db-2021-08-09.bak
[root@c7u6s2:~]#
上述用到的几个选项解释如下：
- -uroot ：表示以root身份连接数据库
- -p ：表示在命令行中交互式输入mysql数据库的root用户的密码
- -A ：表示备份所有数据库，是
--all-databases选项的简写- -F ：表示刷新二进制日志，是
--flush-logs选项的简写- –single-transaction ：表示将数据库设置为事务隔离模式，只对InnoDB存储引擎中的事务相关的表起作用，如果数据库中没有启用事务功能，可以省略这个选项（具体解释参见
man mysqldump）- –master-data=1 ：表示将
CHANGE MASTER TO3语句以SQL语句的形式写入到备份文件中，另外一个值是2，此时表示将上述语句以注释的形式写入到备份文件中
将上述的备份文件发送到slave节点
通过scp命令或者rsync命令将上述备份文件发送到slave节点上。具体如下所示：
[root@c7u6s2:~]# ls master_all_db-2021-08-09.bak
master_all_db-2021-08-09.bak
[root@c7u6s2:~]# rsync -av --progress -e 'ssh -p 22 -l root' master_all_db-2021-08-09.bak c7u6s3:~
root@c7u6s3's password:
sending incremental file list
master_all_db-2021-08-09.bak
901,847 100% 165.76MB/s 0:00:00 (xfr#1, to-chk=0/1)
sent 902,172 bytes received 35 bytes 360,882.80 bytes/sec
total size is 901,847 speedup is 1.00
[root@c7u6s2:~]#
至此，master节点上的操作就完成了。
1.2.2. slave节点上的配置
在slave节点上，设置配置文件，设置唯一的server-id，同时将slave节点的mysql服务设置为只读模式。接下来在还原master节点上备份的数据库之前，需要修改下备份文件，在其中加上CHANGE MASTER TO语句相关的选项，用于指向master节点。然后就可以开始还原数据库了。由于slave节点上的数据库被设置为只读模式，所以无需开启二进制日志功能（但是考虑到后期可能会需要将slave节点提升为master节点，所以建议在slave节点上开启二进制日志功能）。
设置slave节点的配置文件
在my.cnf配置文件的 [mysqld] 部分增加两个配置选项：server-id=22 以及 read-only 。具体如下所示：
[root@c7u6s3:~]# vim /etc/my.cnf
[root@c7u6s3:~]# cat /etc/my.cnf | egrep -v '^$|^#'
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=22
read-only=ON
log-bin
relay-log=relay-log
relay-log-index=relay-log.index
[root@c7u6s3:~]#
修改完配置文件之后，需要重启mysqld服务，才会生效。具体如下所示：
[root@c7u6s3:~]# systemctl daemon-reload
[root@c7u6s3:~]# systemctl restart mysqld
[root@c7u6s3:~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disable
d)
Active: active (running) since 一 2021-08-09 09:29:59 CST; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 8255 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p
id $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 8234 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 8258 (mysqld)
Tasks: 27
Memory: 194.9M
CGroup: /system.slice/mysqld.service
└─8258 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
8月 09 09:29:59 c7u6s3 systemd[1]: Starting MySQL Server...
8月 09 09:29:59 c7u6s3 systemd[1]: Started MySQL Server.
[root@c7u6s3:~]#
修改备份文件，在其中添加master节点的信息
为了实现主从复制，在slave节点上，就需要指定从二进制日志的哪个位置开始进行复制，而这个位置就是在master节点上执行mysqldump命令的时候，通过--master-data=1这个选项生成的，记录在备份文件中，具体如下所示：
[root@c7u6s3:~]# egrep '^CHANGE MASTER TO' master_all_db-2021-08-09.bak
CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog-c7u6s2.000010', MASTER_LOG_POS=154;
[root@c7u6s3:~]#
上述记录了二进制日志文件名为mysql-binlog-c7u6s2.000010，二进制日志坐标位置为154。目前这一行只有上述的二进制日志信息，为了实现主从复制，需要加上master节点的信息，具体如下所示：
[root@c7u6s3:~]# vim master_all_db-2021-08-09.bak
[root@c7u6s3:~]# cat -n master_all_db-2021-08-09.bak | head -n28 | tail -n7
22 CHANGE MASTER TO
23 MASTER_HOST='192.168.122.21',
24 MASTER_USER='repluser',
25 MASTER_PASSWORD='password',
26 MASTER_PORT=3306,
27 MASTER_LOG_FILE='mysql-binlog-c7u6s2.000010',
28 MASTER_LOG_POS=154;
[root@c7u6s3:~]#
上述为修改后的备份文件的内容，查看slave节点的数据库内容，具体如下所示：
[root@c7u6s3:~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34 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.00 sec)
mysql>
此时数据库中没有用户数据，接下来准备还原数据库，具体如下所示：
[root@c7u6s3:~]# mysql -uroot -p < master_all_db-2021-08-09.bak
Enter password:
[root@c7u6s3:~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34 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 |
| mydb |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql>
上述过程还原完成，数据库中多了两个库：mydb 以及 testdb 。查看这两个库中所包含的表，具体如下所示：
mysql> show tables in mydb;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
mysql> show tables in testdb;
+-------------------+
| Tables_in_testdb |
+-------------------+
| department |
| dept_trigger_time |
| employee |
| gradeinfo |
| information |
| operate |
| product |
| score |
| student |
| student2 |
| trigger_test |
+-------------------+
11 rows in set (0.00 sec)
上述输出表明，数据已经完成了还原。接下来查看slave节点的状态信息，具体如下所示：
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.122.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog-c7u6s2.000010
Read_Master_Log_Pos: 154
Relay_Log_File: c7u6s3-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-binlog-c7u6s2.000010
Slave_IO_Running: No
Slave_SQL_Running: No
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: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
mysql>
测试主从复制
在master节点的mydb.product表中插入一条记录，然后观察slave节点上是否有数据被同步过来。
在master节点上插入数据，具体如下所示：
-- 以下操作在master节点上执行
(root@localhost) [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
(root@localhost) [mydb]> select * from product;
+----+--------------------------+---------------+--------------+--------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+--------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
+----+--------------------------+---------------+--------------+--------------+
6 rows in set (0.00 sec)
(root@localhost) [mydb]> insert into product
-> (id, guitar_name, producer_name, guitar_price, dealer)
-> values
-> (null, 'Ibanez J.Custom RG8570Z', 'IBANEZ', 17500, '西木野乐器')
-> ;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [mydb]> select * from product;
+----+--------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
+----+--------------------------+---------------+--------------+-----------------+
7 rows in set (0.00 sec)
(root@localhost) [mydb]>
上述已经在master节点插入了一条记录，即id=8的这一行记录。
在slave节点查看数据是否完成了同步
起初，并没有自动完成数据复制，因为此时还没有在slave节点上启动I/O Thread以及SQL Thread，所以无法实现主从数据复制；在slave节点上执行start slave语句之后，slave节点上就启动了I/O Thread以及SQL Thread，也就自然可以进行主从数据复制了。具体如下所示：
-- 以下操作在slave节点上执行
mysql> select * from product;
+----+--------------------------+---------------+--------------+--------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+--------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
+----+--------------------------+---------------+--------------+--------------+
6 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from product;
+----+--------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
+----+--------------------------+---------------+--------------+-----------------+
7 rows in set (0.00 sec)
mysql>
从上述输出中可以看出，在slave节点上执行了start slave语句之后，就触发了主从复制，将master节点上在mydb.product表中添加的一条记录复制过来了。start slave语句会在slave节点上启动I/O Thread以及SQL Thread这两个线程，从而实现主从复制的数据同步。
上述启动了I/O Thread以及SQL Thread这两个线程之后，再次在master节点上添加一条记录，具体如下所示：
-- 下面的操作在master节点上执行
(root@localhost) [mydb]> insert into product
-> (id, guitar_name, producer_name, guitar_price, dealer)
-> values
-> (null, 'Fender Cobra Blue 011-8012-795', 'FENDER', 14480, '世界淘吉他')
-> ;
Query OK, 1 row affected (0.02 sec)
(root@localhost) [mydb]> select * from product;
+----+--------------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
| 9 | Fender Cobra Blue 011-8012-795 | FENDER | 14480 | 世界淘吉他 |
+----+--------------------------------+---------------+--------------+-----------------+
8 rows in set (0.00 sec)
(root@localhost) [mydb]>
master节点添加完数据之后，接下来查看slave节点上对应表的信息，应该会自动发生改变，具体如下所示：
mysql> select * from product;
+----+--------------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
| 9 | Fender Cobra Blue 011-8012-795 | FENDER | 14480 | 世界淘吉他 |
+----+--------------------------------+---------------+--------------+-----------------+
8 rows in set (0.01 sec)
mysql>
从上述输出可以看出，新添加的id=9这条记录已经被同步过来了。查看下slave节点上的进程列表信息，具体如下所示：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: mydb
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 609
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 352
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
mysql>
上述的输出显示，I/O Thread进程已经在等待master节点发送数据变更信息到slave节点的中继日志上了。并且SQL Thread也已经准备从中继日志中读取变更的数据记录语句了。
查看下slave节点状态信息，具体如下所示：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.122.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog-c7u6s2.000011
Read_Master_Log_Pos: 793
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 653
Relay_Master_Log_File: mysql-binlog-c7u6s2.000011
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: 793
Relay_Log_Space: 854
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: 1
Master_UUID: a1a0cb8d-e1f2-11eb-aef1-52540013d08a
Master_Info_File: /var/lib/mysql/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)
mysql>
上述输出中的Seconds_Behind_Master: 0也间接证明主从复制同步操作正确执行了。
至此，主从复制操作完成。
1.2.3. 中继日志问题debug
有的时候在启动slave节点的I/O Thread以及SQL Thread进程（执行start slave语句的时候），会出现如下错误：
-- 下面的操作在slave节点上执行
mysql> start slave
-> ;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
此时可以执行reset slave语句重置slave节点，然后查看master节点的二进制日志信息，并且根据其中的二进制日志信息重新在slave节点上执行change master to语句（这个过程也是master节点和slave节点刚刚安装好mysql服务，并且其中不包含任何数据的场景下，配置主从复制的执行方式）。
重置slave节点
-- 下面的操作在slave节点上执行
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
查看master节点上二进制日志的信息
-- 下面的操作在master节点上执行
(root@localhost) [mydb]> show master status\G
*************************** 1. row ***************************
File: mysql-binlog-c7u6s2.000011
Position: 470
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
(root@localhost) [mydb]>
重新在slave节点上执行change master to语句
-- 下面的操作在slave节点上执行
mysql> change master to
-> MASTER_HOST='192.168.122.21',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='password',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-binlog-c7u6s2.000011',
-> MASTER_LOG_POS=470
-> ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: mydb
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 15
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 15
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.01 sec)
此时，就重新在slave节点上配置好了主从复制，并且启动了相关的监听进程。
2. Percona XtraBackup实现全量和增量备份
Percona XtraBackup执行备份的时候，无法对单库实现全量备份之后再进行增量备份。只能在全部数据库执行全量备份之后，才能执行增量备份。
这部分也涉及到2个服务器，角色如下：
| Server Name | Server Role | Server IP | MySQL Version |
|---|---|---|---|
| c7u6s2 | backup node | 192.168.122.21 | 5.7.34 |
| c7u6s3 | restore node | 192.168.122.22 | 5.7.34 |
2.1. Percona XtraBackup工具简介以及安装
Percona XtraBackup目前的最新版为8.0版本，顾名思义，支持MySQL-8.x版本的数据库的备份，但是并不支持此前的5.7.x版本的备份。所以此处采用的是Percona XtraBackup 2.4版本进行MySQL-5.7的备份。关于Percona XtraBackup工具更详细介绍，参见Percona XtraBackup。
要安装Percona XtraBackup软件，可以通过镜像源借助yum命令安装，在此之前，需要配置好yum镜像仓库。清华大学的软件镜像源中提供了Percona XtraBackup的软件镜像，依据上述的链接地址，配置镜像仓库配置文件，具体如下所示：
[root@c7u6s2:~]# cat /etc/yum.repos.d/percona-xtrabackup-cluster.repo
[pxc-57]
name=Percona Xtrabackup Cluster
baseurl= https://mirrors.tuna.tsinghua.edu.cn/percona/pxc-57/yum/release/7/RPMS/x86_64/
enabled=1
gpgcheck=0
[root@c7u6s2:~]#
接下来准备安装，具体如下所示：
[root@c7u6s2:~]# yum list | egrep '^percona'
percona-xtrabackup-24.x86_64 2.4.23-1.el7 installed
percona-xtrabackup-24-debuginfo.x86_64 2.4.23-1.el7 installed
percona-xtrabackup-test-24.x86_64 2.4.23-1.el7 installed
percona-xtrabackup.x86_64 2.3.6-1.el7 epel
percona-xtrabackup-test.x86_64 2.3.6-1.el7 epel
[root@c7u6s2:~]#
[root@c7u6s2:~]# yum list | egrep '^percona.*24' | gawk '{print $1}'
percona-xtrabackup-24.x86_64
percona-xtrabackup-24-debuginfo.x86_64
percona-xtrabackup-test-24.x86_64
[root@c7u6s2:~]# yum list | egrep '^percona.*24' | gawk '{print $1}' | xargs -i yum install -y {}
[root@c7u6s2:~]# rpm -qa | egrep percona
percona-xtrabackup-24-debuginfo-2.4.23-1.el7.x86_64
percona-xtrabackup-test-24-2.4.23-1.el7.x86_64
percona-xtrabackup-24-2.4.23-1.el7.x86_64
[root@c7u6s2:~]#
[root@c7u6s2:~]# which xtrabackup
/usr/bin/xtrabackup
[root@c7u6s2:~]#
至此，c7u6s2这个节点上的Percona XtraBackup软件包就安装完成了。同样的操作，也在c7u6s3节点上执行一遍。
2.2. 全部数据库的全量备份
接下来在c7u6s2节点上执行全量备份，具体如下所示：
[root@c7u6s2:~]# mkdir -p /data/mysql_backup
[root@c7u6s2:~]# cd /data/mysql_backup
[root@c7u6s2:mysql_backup]# ls
[root@c7u6s2:mysql_backup]#
[root@c7u6s2:mysql_backup]# xtrabackup --backup --user=root --password --target-dir=`pwd`/base_full
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/log/mysql-bin-log/mysql-binlog-c7u6s2.0000001 --server-id=1
xtrabackup: recognized client arguments: --backup=1 --user=root --password --target-dir=/data/mysql_backup/base_full
Enter password:
210728 16:07:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210728 16:07:14 version_check Connected to MySQL server
210728 16:07:14 version_check Executing a version check against the server...
210728 16:07:14 version_check Done.
210728 16:07:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.34-log
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210728 16:07:14 >> log scanned up to (4531404)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
210728 16:07:14 [01] Copying ./ibdata1 to /data/mysql_backup/base_full/ibdata1
210728 16:07:14 [01] ...done
210728 16:07:14 [01] Copying ./mysql/plugin.ibd to /data/mysql_backup/base_full/mysql/plugin.ibd
...
210728 16:07:15 [01] Copying ./mydb/employee.frm to /data/mysql_backup/base_full/mydb/employee.frm
210728 16:07:15 [01] ...done
210728 16:07:15 Finished backing up non-InnoDB tables and files
210728 16:07:15 [00] Writing /data/mysql_backup/base_full/xtrabackup_binlog_info
210728 16:07:15 [00] ...done
210728 16:07:15 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '4531395'
xtrabackup: Stopping log copying thread.
.210728 16:07:15 >> log scanned up to (4531404)
210728 16:07:16 Executing UNLOCK TABLES
210728 16:07:16 All tables unlocked
210728 16:07:16 [00] Copying ib_buffer_pool to /data/mysql_backup/base_full/ib_buffer_pool
210728 16:07:16 [00] ...done
210728 16:07:16 Backup created in directory '/data/mysql_backup/base_full/'
MySQL binlog position: filename 'mysql-binlog-c7u6s2.000008', position '29389'
210728 16:07:16 [00] Writing /data/mysql_backup/base_full/backup-my.cnf
210728 16:07:16 [00] ...done
210728 16:07:16 [00] Writing /data/mysql_backup/base_full/xtrabackup_info
210728 16:07:16 [00] ...done
xtrabackup: Transaction log of lsn (4531395) to (4531404) was copied.
210728 16:07:16 completed OK!
[root@c7u6s2:mysql_backup]#
至此，所有数据库的全量备份操作完成。
2.3. 修改数据库，准备第一次增量备份
在上述的全量备份基础上，执行增量备份，所以需要先修改数据库。然后对修改的内容执行增量备份。
2.3.1. 修改数据库
具体如下所示：
(root@localhost) [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| score |
| student |
+----------------+
5 rows in set (0.00 sec)
(root@localhost) [mydb]> create table product_sel like product;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
(root@localhost) [mydb]> desc product;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| guitar_name | varchar(50) | YES | | NULL | |
| producer_name | varchar(20) | NO | | NULL | |
| guitar_price | int(11) | NO | | NULL | |
| dealer | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
(root@localhost) [mydb]> select * from product;
+----+--------------------------+---------------+--------------+--------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+--------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
+----+--------------------------+---------------+--------------+--------------+
6 rows in set (0.00 sec)
(root@localhost) [mydb]> insert into product_sel
-> select id, guitar_name, producer_name, guitar_price, dealer
-> from product
-> where guitar_name like 'Taylor%' or guitar_name like 'Fender%'
-> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
(root@localhost) [mydb]>
(root@localhost) [mydb]> select * from product_sel;
+----+--------------------------+---------------+--------------+-------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+-------------+
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
+----+--------------------------+---------------+--------------+-------------+
3 rows in set (0.00 sec)
(root@localhost) [mydb]>
2.3.2. 执行第一次增量备份
同执行全部数据库的全量备份操作一样，增量备份也是使用xtrabackup命令完成，具体如下所示：
[root@c7u6s2:mysql_backup]# ls
base_full
[root@c7u6s2:mysql_backup]# xtrabackup --backup --user=root --password --target-dir=`pwd`/incr1 --incremental-basedir=`pwd`/base_full
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/log/mysql-bin-log/mysql-binlog-c7u6s2.0000001 --server-id=1
xtrabackup: recognized client arguments: --backup=1 --user=root --password --target-dir=/data/mysql_backup/incr1 --incremental-basedir=/data/mysql_backup/base_full
Enter password:
210728 16:15:31 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210728 16:15:31 version_check Connected to MySQL server
210728 16:15:31 version_check Executing a version check against the server...
210728 16:15:31 version_check Done.
210728 16:15:31 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.34-log
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
incremental backup from 4531395 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210728 16:15:31 >> log scanned up to (4541558)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
xtrabackup: using the full scan for incremental backup
210728 16:15:31 [01] Copying ./ibdata1 to /data/mysql_backup/incr1/ibdata1.delta
210728 16:15:31 [01] ...done
210728 16:15:31 [01] Copying ./mysql/plugin.ibd to /data/mysql_backup/incr1/mysql/plugin.ibd.delta
210728 16:15:31 [01] ...done
...
210728 16:15:34 [01] Copying ./mydb/student.frm to /data/mysql_backup/incr1/mydb/student.frm
210728 16:15:34 [01] ...done
210728 16:15:34 [01] Copying ./mydb/department.frm to /data/mysql_backup/incr1/mydb/department.frm
210728 16:15:34 [01] ...done
210728 16:15:34 [01] Copying ./mydb/employee.frm to /data/mysql_backup/incr1/mydb/employee.frm
210728 16:15:34 [01] ...done
210728 16:15:34 [01] Copying ./mydb/product_sel.frm to /data/mysql_backup/incr1/mydb/product_sel.frm
210728 16:15:34 [01] ...done
210728 16:15:34 Finished backing up non-InnoDB tables and files
210728 16:15:34 [00] Writing /data/mysql_backup/incr1/xtrabackup_binlog_info
210728 16:15:34 [00] ...done
210728 16:15:34 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '4541549'
xtrabackup: Stopping log copying thread.
.210728 16:15:34 >> log scanned up to (4541558)
210728 16:15:34 Executing UNLOCK TABLES
210728 16:15:34 All tables unlocked
210728 16:15:34 [00] Copying ib_buffer_pool to /data/mysql_backup/incr1/ib_buffer_pool
210728 16:15:34 [00] ...done
210728 16:15:34 Backup created in directory '/data/mysql_backup/incr1/'
MySQL binlog position: filename 'mysql-binlog-c7u6s2.000008', position '29934'
210728 16:15:34 [00] Writing /data/mysql_backup/incr1/backup-my.cnf
210728 16:15:34 [00] ...done
210728 16:15:34 [00] Writing /data/mysql_backup/incr1/xtrabackup_info
210728 16:15:34 [00] ...done
xtrabackup: Transaction log of lsn (4541549) to (4541558) was copied.
210728 16:15:34 completed OK!
[root@c7u6s2:mysql_backup]#
[root@c7u6s2:mysql_backup]# ls -lhF
total 0
drwxr-x--- 7 root root 249 7月 28 16:44 base_full/
drwxr-x--- 7 root root 275 7月 28 16:15 incr1/
[root@c7u6s2:mysql_backup]#
2.3.3. 将备份的结果拷贝到c7u6s3
由于要在c7u6s3节点上执行恢复，所以需要将c7u6s2节点上生成的备份结果拷贝到c7u6s3节点上。具体如下所示：
[root@c7u6s2:mysql_backup]# rsync -av --progress -e 'ssh -l root -p 22' ./ c7u6s3:/data/mysql_backup/
root@c7u6s3's password:
sending incremental file list
./
base_full/
[root@c7u6s2:mysql_backup]#
登录c7u6s3节点，查看拷贝的结果，具体如下所示：
[root@c7u6s3:~]# cd /data/mysql_backup/
[root@c7u6s3:mysql_backup]# ls -lh
total 0
drwxr-x--- 7 root root 317 7月 28 16:39 base_full
drwxr-x--- 7 root root 275 7月 28 16:39 incr1
[root@c7u6s3:mysql_backup]#
上述已经将c7u6s2拷贝到了c7u6s3节点上。
2.3.4. 在c7u6s3节点上还原备份结果
在c7u6s3节点上安装好mysql客户端和服务器端软件包以及Percona XtraBackup软件包之后，在开始执行xtrabackup恢复备份结果之前，需要先停掉mysqld服务，并且保证 /var/lib/mysql（即mysql的数据库目录） 为空才可以。
具体如下所示：
[root@c7u6s3:mysql_backup]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2021-07-28 16:03:10 CST; 33min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 7232 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 7181 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 7235 (mysqld)
CGroup: /system.slice/mysqld.service
└─7235 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
7月 28 16:03:06 c7u6s3 systemd[1]: Starting MySQL Server...
7月 28 16:03:10 c7u6s3 systemd[1]: Started MySQL Server.
[root@c7u6s3:mysql_backup]# systemctl stop mysqld
[root@c7u6s3:mysql_backup]# rm -rf /var/lib/mysql/*
[root@c7u6s3:mysql_backup]# ls /var/lib/mysql/*
ls: cannot access /var/lib/mysql/*: No such file or directory
[root@c7u6s3:mysql_backup]#
[root@c7u6s3:mysql_backup]# ls
base_full incr1
接下来就可以准备还原备份的数据库了。还原的时候，需要先还原全量备份的结果，然后再还原增量备份的结果，如果还有第二次增量以及第三次增量备份结果，那么需要依次按照增量次序的数字从小到大依次还原。即全量备份 —> 增量备份1 —> 增量备份2 —> 增量备份3 —> … 这样的顺序来还原。
全量备份结果还原
具体如下所示：
[root@c7u6s3:mysql_backup]# xtrabackup --prepare --apply-log-only --target-dir=`pwd`/base_full
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/mysql_backup/base_full
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
xtrabackup: cd to /data/mysql_backup/base_full/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4531395)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 4531395
InnoDB: Doing recovery: scanned up to log sequence number 4531404 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 29200, file name mysql-binlog-c7u6s2.000008
InnoDB: xtrabackup: Last MySQL binlog file position 29200, file name mysql-binlog-c7u6s2.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4531413
InnoDB: Number of pools: 1
210728 16:37:37 completed OK!
[root@c7u6s3:mysql_backup]#
上述就完成了全量备份结果的还原操作。接下来就是还原第一次增量备份的结果。
第一次增量备份结果还原
具体如下所示：
[root@c7u6s3:mysql_backup]# xtrabackup --prepare --apply-log-only --target-dir=`pwd`/base_full --incremental-dir=`pwd`/incr1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/mysql_backup/base_full --incremental-dir=/data/mysql_backup/incr1
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
incremental backup from 4531395 is enabled.
xtrabackup: cd to /data/mysql_backup/base_full/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4541549)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /data/mysql_backup/incr1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 704 for mydb/department, old maximum was 0
xtrabackup: page size for /data/mysql_backup/incr1//ibdata1.delta is 16384 bytes space id is 0
Applying /data/mysql_backup/incr1//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/department.ibd.delta is 16384 bytes space id is 704
Applying /data/mysql_backup/incr1//mydb/department.ibd.delta to ./mydb/department.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/employee.ibd.delta is 16384 bytes space id is 705
Applying /data/mysql_backup/incr1//mydb/employee.ibd.delta to ./mydb/employee.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/product.ibd.delta is 16384 bytes space id is 722
Applying /data/mysql_backup/incr1//mydb/product.ibd.delta to ./mydb/product.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/product_sel.ibd.delta is 16384 bytes space id is 734
Applying /data/mysql_backup/incr1//mydb/product_sel.ibd.delta to ./mydb/product_sel.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/score.ibd.delta is 16384 bytes space id is 730
Applying /data/mysql_backup/incr1//mydb/score.ibd.delta to ./mydb/score.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mydb/student.ibd.delta is 16384 bytes space id is 729
Applying /data/mysql_backup/incr1//mydb/student.ibd.delta to ./mydb/student.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/engine_cost.ibd.delta is 16384 bytes space id is 20
Applying /data/mysql_backup/incr1//mysql/engine_cost.ibd.delta to ./mysql/engine_cost.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/time_zone.ibd.delta is 16384 bytes space id is 9
Applying /data/mysql_backup/incr1//mysql/time_zone.ibd.delta to ./mysql/time_zone.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/gtid_executed.ibd.delta is 16384 bytes space id is 18
Applying /data/mysql_backup/incr1//mysql/gtid_executed.ibd.delta to ./mysql/gtid_executed.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/help_category.ibd.delta is 16384 bytes space id is 5
Applying /data/mysql_backup/incr1//mysql/help_category.ibd.delta to ./mysql/help_category.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/servers.ibd.delta is 16384 bytes space id is 3
Applying /data/mysql_backup/incr1//mysql/servers.ibd.delta to ./mysql/servers.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/time_zone_name.ibd.delta is 16384 bytes space id is 8
Applying /data/mysql_backup/incr1//mysql/time_zone_name.ibd.delta to ./mysql/time_zone_name.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/help_keyword.ibd.delta is 16384 bytes space id is 7
Applying /data/mysql_backup/incr1//mysql/help_keyword.ibd.delta to ./mysql/help_keyword.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/slave_master_info.ibd.delta is 16384 bytes space id is 16
Applying /data/mysql_backup/incr1//mysql/slave_master_info.ibd.delta to ./mysql/slave_master_info.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/help_relation.ibd.delta is 16384 bytes space id is 6
Applying /data/mysql_backup/incr1//mysql/help_relation.ibd.delta to ./mysql/help_relation.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/help_topic.ibd.delta is 16384 bytes space id is 4
Applying /data/mysql_backup/incr1//mysql/help_topic.ibd.delta to ./mysql/help_topic.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/slave_relay_log_info.ibd.delta is 16384 bytes space id is 15
Applying /data/mysql_backup/incr1//mysql/slave_relay_log_info.ibd.delta to ./mysql/slave_relay_log_info.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/innodb_index_stats.ibd.delta is 16384 bytes space id is 14
Applying /data/mysql_backup/incr1//mysql/innodb_index_stats.ibd.delta to ./mysql/innodb_index_stats.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/slave_worker_info.ibd.delta is 16384 bytes space id is 17
Applying /data/mysql_backup/incr1//mysql/slave_worker_info.ibd.delta to ./mysql/slave_worker_info.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/innodb_table_stats.ibd.delta is 16384 bytes space id is 13
Applying /data/mysql_backup/incr1//mysql/innodb_table_stats.ibd.delta to ./mysql/innodb_table_stats.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/plugin.ibd.delta is 16384 bytes space id is 2
Applying /data/mysql_backup/incr1//mysql/plugin.ibd.delta to ./mysql/plugin.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/server_cost.ibd.delta is 16384 bytes space id is 19
Applying /data/mysql_backup/incr1//mysql/server_cost.ibd.delta to ./mysql/server_cost.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/time_zone_leap_second.ibd.delta is 16384 bytes space id is 12
Applying /data/mysql_backup/incr1//mysql/time_zone_leap_second.ibd.delta to ./mysql/time_zone_leap_second.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/time_zone_transition.ibd.delta is 16384 bytes space id is 10
Applying /data/mysql_backup/incr1//mysql/time_zone_transition.ibd.delta to ./mysql/time_zone_transition.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//mysql/time_zone_transition_type.ibd.delta is 16384 bytes space id is 11
Applying /data/mysql_backup/incr1//mysql/time_zone_transition_type.ibd.delta to ./mysql/time_zone_transition_type.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//sys/sys_config.ibd.delta is 16384 bytes space id is 21
Applying /data/mysql_backup/incr1//sys/sys_config.ibd.delta to ./sys/sys_config.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/department.ibd.delta is 16384 bytes space id is 566
Applying /data/mysql_backup/incr1//testdb/department.ibd.delta to ./testdb/department.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/dept_trigger_time.ibd.delta is 16384 bytes space id is 560
Applying /data/mysql_backup/incr1//testdb/dept_trigger_time.ibd.delta to ./testdb/dept_trigger_time.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/employee.ibd.delta is 16384 bytes space id is 567
Applying /data/mysql_backup/incr1//testdb/employee.ibd.delta to ./testdb/employee.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/information.ibd.delta is 16384 bytes space id is 544
Applying /data/mysql_backup/incr1//testdb/information.ibd.delta to ./testdb/information.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/operate.ibd.delta is 16384 bytes space id is 563
Applying /data/mysql_backup/incr1//testdb/operate.ibd.delta to ./testdb/operate.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/product.ibd.delta is 16384 bytes space id is 562
Applying /data/mysql_backup/incr1//testdb/product.ibd.delta to ./testdb/product.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/score.ibd.delta is 16384 bytes space id is 565
Applying /data/mysql_backup/incr1//testdb/score.ibd.delta to ./testdb/score.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/student.ibd.delta is 16384 bytes space id is 564
Applying /data/mysql_backup/incr1//testdb/student.ibd.delta to ./testdb/student.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/student2.ibd.delta is 16384 bytes space id is 569
Applying /data/mysql_backup/incr1//testdb/student2.ibd.delta to ./testdb/student2.ibd...
xtrabackup: page size for /data/mysql_backup/incr1//testdb/trigger_test.ibd.delta is 16384 bytes space id is 561
Applying /data/mysql_backup/incr1//testdb/trigger_test.ibd.delta to ./testdb/trigger_test.ibd...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /data/mysql_backup/incr1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 4541549
InnoDB: Doing recovery: scanned up to log sequence number 4541558 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 29934, file name mysql-binlog-c7u6s2.000008
InnoDB: xtrabackup: Last MySQL binlog file position 29934, file name mysql-binlog-c7u6s2.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4541567
InnoDB: Number of pools: 1
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/db.opt to ./mydb/db.opt
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/department.frm to ./mydb/department.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/employee.frm to ./mydb/employee.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/product.frm to ./mydb/product.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/product_sel.frm to ./mydb/product_sel.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/score.frm to ./mydb/score.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mydb/student.frm to ./mydb/student.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/slow_log.CSV to ./mysql/slow_log.CSV
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/columns_priv.MYD to ./mysql/columns_priv.MYD
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/slow_log.frm to ./mysql/slow_log.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/columns_priv.MYI to ./mysql/columns_priv.MYI
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/tables_priv.MYD to ./mysql/tables_priv.MYD
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/columns_priv.frm to ./mysql/columns_priv.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/db.MYD to ./mysql/db.MYD
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/db.MYI to ./mysql/db.MYI
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/db.frm to ./mysql/db.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/db.opt to ./mysql/db.opt
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/tables_priv.MYI to ./mysql/tables_priv.MYI
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/engine_cost.frm to ./mysql/engine_cost.frm
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/procs_priv.MYI to ./mysql/procs_priv.MYI
210728 16:39:06 [01] ...done
210728 16:39:06 [01] Copying /data/mysql_backup/incr1/mysql/tables_priv.frm to ./mysql/tables_priv.frm
210728 16:39:06 [01] ...done
...
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/gradeinfo.frm to ./testdb/gradeinfo.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/information.frm to ./testdb/information.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/operate.frm to ./testdb/operate.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/product.frm to ./testdb/product.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/score.frm to ./testdb/score.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/student.frm to ./testdb/student.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/student2.frm to ./testdb/student2.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [01] Copying /data/mysql_backup/incr1/testdb/trigger_test.frm to ./testdb/trigger_test.frm
210728 16:39:07 [01] ...done
210728 16:39:07 [00] Copying /data/mysql_backup/incr1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
210728 16:39:07 [00] ...done
210728 16:39:07 [00] Copying /data/mysql_backup/incr1//xtrabackup_info to ./xtrabackup_info
210728 16:39:07 [00] ...done
210728 16:39:07 completed OK!
[root@c7u6s3:mysql_backup]#
至此，全量备份以及第一次增量备份结果就全部还原完成了。
接下来就可以检查还原的结果了。在启动mysqld服务之前，需要先修改下数据库目录/var/lib/mysql/的所有者和所属组关系，否则无法启动mysqld服务。因为默认还原完成之后的所有者和所属组关系为root，导致mysqld进程无法读取该目录下的文件。在修改完成之后，就可以正常启动mysqld服务了。具体如下所示：
[root@c7u6s3:mysql_backup]# ls /var/lib/mysql
ib_buffer_pool performance_schema xtrabackup_info
ibdata1 sys xtrabackup_master_key_id
mydb testdb
mysql xtrabackup_binlog_pos_innodb
[root@c7u6s3:mysql_backup]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 三 2021-07-28 16:36:17 CST; 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 7232 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 7181 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 7235 (code=exited, status=0/SUCCESS)
7月 28 16:03:06 c7u6s3 systemd[1]: Starting MySQL Server...
7月 28 16:03:10 c7u6s3 systemd[1]: Started MySQL Server.
7月 28 16:36:15 c7u6s3 systemd[1]: Stopping MySQL Server...
7月 28 16:36:17 c7u6s3 systemd[1]: Stopped MySQL Server.
[root@c7u6s3:mysql_backup]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@c7u6s3:mysql_backup]#
[root@c7u6s3:mysql]# ls -lh
total 77M
-rw-r----- 1 root root 517 7月 28 16:40 ib_buffer_pool
-rw-r----- 1 root root 76M 7月 28 16:40 ibdata1
drwxr-x--- 2 root root 260 7月 28 16:40 mydb
drwxr-x--- 2 root root 4.0K 7月 28 16:40 mysql
drwxr-x--- 2 root root 8.0K 7月 28 16:40 performance_schema
drwxr-x--- 2 root root 8.0K 7月 28 16:40 sys
drwxr-x--- 2 root root 4.0K 7月 28 16:40 testdb
-rw-r----- 1 root root 33 7月 28 16:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 571 7月 28 16:40 xtrabackup_info
-rw-r----- 1 root root 1 7月 28 16:40 xtrabackup_master_key_id
[root@c7u6s3:mysql]# ls -lhd .
drwxr-x--x 7 mysql mysql 210 7月 28 16:44 .
[root@c7u6s3:mysql]# chown -R mysql.mysql .
[root@c7u6s3:mysql]# systemctl start mysqld
[root@c7u6s3:mysql]#
启动完成之后，连接到数据库，检查数据，具体如下所示：
[root@c7u6s3:mysql]# 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 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>
可以正常连接到数据库，接下来查看数据库中的相关库以及表是否存在，具体如下所示：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> use mydb;
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
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
mysql> select * from product_sel;
+----+--------------------------+---------------+--------------+-------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------+---------------+--------------+-------------+
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
+----+--------------------------+---------------+--------------+-------------+
3 rows in set (0.00 sec)
mysql>
从上述结果可以看出，相关的库和表都已经存在了。
上述还原的时候，需要注意如下2点：
- 在开始还原之前，需要确保数据库的数据目录为空，本例中/var/lib/mysql/这个目录需要为空；另外还需要将mysqld.service这个守护进程停掉
- 在还原之后，启动mysqld.service服务之前，需要确保数据库的数据目录中文件的所有者和所属组均为mysql，上述两点保证之后，就可以正常启动还原主机上的mysqld.service守护进程了
单纯使用Percona XtraBackup进行备份和还原的时候，这个操作无法实现删除数据的恢复，还需要借助二进制日志进行删除的数据、表和库的还原。关于Percona XtraBackup与binlog的结合，官方文档并没有很详细的介绍，参见Percona XtraBackup：Working with Binary Logs
3. ProxySQL实现读写分离
MySQL的读写分离，是建立在主从复制的基础上的。主从复制作为读写分离的先决条件，是需要先配置好的。本篇博客的第一部分已经介绍了2个节点的主从复制集群，后续如果要增加新的slave节点，实现1个master节点多个slave节点的架构，只需要按照上述的slave节点的添加方式，添加新的节点即可。
实现了主从复制，此时如果要提升数据库集群的工作效率，就需要对不同的操作请求做不同的处理。在生产环境中，通常查询操作比写入操作更频繁，所以为了提升查询效率，通常会采用多个slave节点响应查询服务；相对的，写入操作则并不那么频繁，所以通常一个master节点即可，如果业务对写入操作性能相应要求较高，也可以使用双主架构。通过这种将查询请求和修改请求分开到不同的数据库服务器上，实现性能的提升。
而支持上述架构的软件，一般被称为MySQL中间件，常用的中间件比如此处用到的ProxySQL以及MyCat。作为实现读写分离的MySQL数据库中间件软件，支持多种形式的读写分离（比如基于用户、基于数据库、以及基于正则表达式的语句规则等实现语句路由），同时也支持缓存查询结果、后端节点状态监控等功能。
在开始安装ProxySQL之前，先规划下服务器角色，具体如下表所示：
| Server Name | Server Role | Server IP | MySQL Version | ProxySQL Version |
|---|---|---|---|---|
| c7u6s2 | master node | 192.168.122.21 | 5.7.34(Server and Client) | 不需要安装ProxySQL |
| c7u6s3 | slave node | 192.168.122.22 | 5.7.34(Server and Client) | 不需要安装ProxySQL |
| c7u6s1 | proxy node | 192.168.122.20 | 5.7.34(Client) | ProxySQL-2.2.0 |
c7u6s2节点作为master节点，c7u6s3节点作为slave节点，这两个节点实现主从复制；在这两个节点之上，构建通过c7u6s1节点搭建ProxySQL代理服务，从而实现读写分离。即c7u6s1作为应用直接连接的服务器，应用或者客户端将SQL语句提交到c7u6s1节点上，通过ProxySQL解析之后，将查询请求和修改请求路由到不同的后端数据库服务器上（c7u6s2以及c7u6s3），从而实现读写分离。在这个过程中，应用或者客户端不会直接连接到后端数据库服务器上（c7u6s2以及c7u6s3）。
3.1. 安装并连接到ProxySQL
关于ProxySQL的安装，参见官方手册Download and Install ProxySQL，我采用的是直接下来对应版本的软件包到虚拟机，同时虚拟机配置基本的yum源（base源以及update源即可，此处的ISO是我用iso镜像配置的yum源）。安装过程具体如下所示：
[root@c7u6s1:~]# ls
anaconda-ks.cfg mezzanine
color_prompt mysql-5.7.34
dockerfile mysql-5.7.34-1.el7.x86_64.rpm-bundle.tar
harbor-offline-installer-v2.2.3.tgz original-ks.cfg
iptables-modified.rule proxysql-2.2.0-1-centos7.x86_64.rpm
iptables.rule tomcat-web-images.tar
logs
[root@c7u6s1:~]# yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Examining proxysql-2.2.0-1-centos7.x86_64.rpm: proxysql-2.2.0-1.x86_64
Marking proxysql-2.2.0-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.2.0-1 will be installed
--> Processing Dependency: gnutls for package: proxysql-2.2.0-1.x86_64
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirror.sjtu.edu.cn
* extras: mirrors.163.com
* updates: mirrors.163.com
--> Processing Dependency: libgnutls.so.28(GNUTLS_1_4)(64bit) for package: proxysql-2.2.0-1.x
86_64
--> Processing Dependency: libgnutls.so.28(GNUTLS_3_0_0)(64bit) for package: proxysql-2.2.0-1
.x86_64
Dependencies Resolved
=============================================================================================
Package Arch Version Repository Size
=============================================================================================
Installing:
proxysql x86_64 2.2.0-1 /proxysql-2.2.0-1-centos7.x86_64 57 M
Installing for dependencies:
gnutls x86_64 3.3.29-9.el7_6 base 680 k
nettle x86_64 2.7.1-9.el7_9 updates 328 k
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 ISO 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 ISO 57 k
perl-DBD-MySQL x86_64 4.023-6.el7 ISO 140 k
perl-DBI x86_64 1.627-4.el7 ISO 802 k
perl-IO-Compress noarch 2.061-2.el7 ISO 260 k
perl-Net-Daemon noarch 0.48-5.el7 ISO 51 k
perl-PlRPC noarch 0.2020-14.el7 ISO 36 k
trousers x86_64 0.3.14-2.el7 ISO 289 k
Transaction Summary
=============================================================================================
Install 1 Package (+10 Dependent packages)
Total size: 60 M
Total download size: 2.6 M
Installed size: 64 M
...
Installed:
proxysql.x86_64 0:2.2.0-1
Dependency Installed:
gnutls.x86_64 0:3.3.29-9.el7_6 nettle.x86_64 0:2.7.1-9.el7_9
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7
perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PlRPC.noarch 0:0.2020-14.el7 trousers.x86_64 0:0.3.14-2.el7
Complete!
[root@c7u6s1:~]#
安装完成之后，查看相关服务的状态，proxysql.service服务已经设置了开机自动启动，用于初始化ProxySQL的服务proxysql-initial.service并没有设置为开机自动启动，也确实无需设置为开机自动启动，只在需要的时候，执行systemctl start proxysql-initial即可了。
[root@c7u6s1:~]# systemctl list-unit-files --type service | egrep proxy
proxysql-initial.service disabled
proxysql.service enabled
[root@c7u6s1:~]#
由于需要连接到ProxySQL服务上，所以还需要在c7u6s1节点上安装MySQL-5.7.34的客户端软件。而该软件包是从MySQL官方站点下载的bundle版本的合集软件包，解压之从从中选择client软件包安装即可，安装过程具体如下所示：
[root@c7u6s1:~]# ls
anaconda-ks.cfg mezzanine
color_prompt mysql-5.7.34
dockerfile mysql-5.7.34-1.el7.x86_64.rpm-bundle.tar
harbor-offline-installer-v2.2.3.tgz original-ks.cfg
iptables-modified.rule proxysql-2.2.0-1-centos7.x86_64.rpm
iptables.rule tests
logs tomcat-web-images.tar
[root@c7u6s1:~]# cd mysql-5.7.34/
[root@c7u6s1:mysql-5.7.34]# ls
mysql-community-client-5.7.34-1.el7.x86_64.rpm
mysql-community-common-5.7.34-1.el7.x86_64.rpm
mysql-community-devel-5.7.34-1.el7.x86_64.rpm
mysql-community-embedded-5.7.34-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.34-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.34-1.el7.x86_64.rpm
mysql-community-libs-5.7.34-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.34-1.el7.x86_64.rpm
mysql-community-server-5.7.34-1.el7.x86_64.rpm
mysql-community-test-5.7.34-1.el7.x86_64.rpm
[root@c7u6s1:mysql-5.7.34]# yum localinstall -y mysql-community-client-5.7.34-1.el7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Examining mysql-community-client-5.7.34-1.el7.x86_64.rpm: mysql-community-client-5.7.34-1.el7.x86_64
mysql-community-client-5.7.34-1.el7.x86_64.rpm: does not update installed package.
Nothing to do
[root@c7u6s1:mysql-5.7.34]# cd
[root@c7u6s1:~]# mysql --version
mysql Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using EditLine wrapper
[root@c7u6s1:~]#
至此，MySQL客户端软件以及ProxySQL软件包都安装完成了。
ProxySQL提供了几个文件，具体如下所示：
[root@c7u6s1:mysql-5.7.34]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@c7u6s1:mysql-5.7.34]#
上述输出中，/etc/logrotate.d/proxysql用于实现日志论转；/etc/systemd/system/proxysql-initial.service以及/etc/systemd/system/proxysql.service是跟ProxySQL服务相关的服务脚本文件，前者用于初始化ProxySQL，后者用于启动、重启以及停止ProxySQL；此外，最后两个文件是跟Galera集群相关的脚本文件。
除此之外，其他是比较主要的文件，比如ProxySQL的主配置文件/etc/proxysql.cnf，用于启动ProxySQL的主程序文件/usr/bin/proxysql，服务脚本中也是调用的这个主程序文件，同时给其传递配置文件，从而实现服务管理。
主配置文件/etc/proxysql.cnf分为如下几个部分：
- datadir 以及 errorlog ：这两个配置项用于定义数据文件以及错误日志的存放路径；
- admin_variables ：其中包含了控制管理接口（默认为TCP协议的6032号端口）功能的全局变量；
- mysql_variables ：其中包含了控制服务接口（默认为TCP协议的6033号端口）功能的全局变量；
- mysql_servers ：其中记录了ProxySQL代理的后端MySQL数据库服务器的信息，比如后端服务器的IP地址、数据库服务的端口号、以及分组和并发连接数以及权重等信息，这些信息通常记录在/var/lib/proxysql/目录下，使用sqlite管理这些信息。所以通常不会用到配置文件的中的配置区域。
- mysql_users ：其中定义了所有的MySQL用户
- mysql_query_rules ：其中定义ProxySQL的查询规则，读写分离正是通过这些查询规则的定义来实现的。
除了上述的这些配置项之外，配置文件中还包含了其他一些配置项，通常需要修改的只是那两个全局变量相关的信息。
我的配置文件中修改后的内容如下所示：
[root@c7u6s1:mysql-5.7.34]# cat /etc/proxysql.cnf | egrep -v '^#|^$'
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.7.34"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
在上述配置文件中，admin_variables部分定义了管理接口的登录用户名和密码，均为admin，同时定义了管理服务的端口号为6032，监听连接到本机的所有IP地址。
mysql_variables部分定义了代理服务部分的用户名和密码，均为monitor，同时定义了默认的服务端口号为6033，监听在连接本机的任何IP地址上。其他一些配置项基本见名知义。
接下来，就可以启动proxysql服务了，具体如下所示：
[root@c7u6s1:~]# systemctl start proxysql
[root@c7u6s1:~]# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since 二 2021-08-10 01:10:53 CST; 3s ago
Process: 6347 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPT
S (code=exited, status=0/SUCCESS)
Main PID: 6349 (proxysql)
Tasks: 25
Memory: 23.8M
CGroup: /system.slice/proxysql.service
├─6349 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─6350 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
8月 10 01:10:53 c7u6s1 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
8月 10 01:10:53 c7u6s1 proxysql[6347]: 2021-08-10 01:10:53 [INFO] Using config file /e...cnf
8月 10 01:10:53 c7u6s1 proxysql[6347]: 2021-08-10 01:10:53 [INFO] Using OpenSSL versio...021
8月 10 01:10:53 c7u6s1 proxysql[6347]: 2021-08-10 01:10:53 [INFO] No SSL keys/certific...es.
8月 10 01:10:53 c7u6s1 systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
[root@c7u6s1:~]# ss -ntlp | egrep 603.
LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=6350,fd=40))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=6350,fd=37))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=6350,fd=36))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=6350,fd=35))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=6350,fd=32))
启动proxysql服务之后，自动打开6032管理服务端口以及6033代理服务端口。
接下来通过6032号端口连接到ProxySQL的管理服务，具体如下所示：
[root@c7u6s1:~]# mysql -uadmin -p -h localhost -P 6032 --prompt='Admin> '
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
上述mysql客户端命令默认使用本地的mysql服务的sock套接字连接ProxySQL服务，而代理服务器上并没有安装mysql服务器软件，所以自然也就无法连接。此时需要通过选项--protocol=TCP选项明确制定通过TCP协议连接。具体如下所示：
[root@c7u6s1:~]# mysql -uadmin -p -h localhost -P 6032 --prompt='Admin> ' --protocol=TCP
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
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.
Admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
Admin>
此时就可以正常连接到ProxySQL的管理服务上了。在安装好ProxySQL软件包之后，自动创建了几个数据库：
- main ：默认数据库，其中存放后端数据库服务器相关的表、用户验证相关的表、SQL语句路由规则等表；
- disk ：sqlite管理的上述配置的持久化存储的文件，在管理服务中所做的操作，通过
save xxx to disk即可实现配置的持久化保存； - stats ：是代理服务的统计信息，通过这些统计信息，可以查看读写分离的效果；
- monitor ：存储monitor模块收集的信息，比如后端数据库的健康检查信息、延迟信息等；
- stats_history ：统计信息历史，同样为sqlite管理的文本数据库文件。
注意 ：在管理服务中做的修改，需要执行语句
load xxx to runtime才能生效；同样，要将修改持久化保存，需要执行语句save xxx to disk，将修改信息写入到proxysql.db这个sqlite管理的数据库中，才能确保修改的信息得到持久化保存。
更多的关于全局变量的信息，参见官方文档ProxySQL Global Variables。
后面的配置操作主要在main这个库中执行，查看其中的几个主要的表中的内容，具体如下所示：
ProxySQL Admin> use main;
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
ProxySQL Admin> select * from mysql_servers;
Empty set (0.00 sec)
ProxySQL Admin> select * from mysql_replication_hostgroups;
Empty set (0.00 sec)
ProxySQL Admin> select * from mysql_query_rules;
Empty set (0.00 sec)
ProxySQL Admin>
另外，由于连接到管理服务之后，默认使用sqlite管理这些配置信息，所以其中并不支持MySQL中经常用于查看表结构的desc语句或者describe语句。所以，要查看表结构的时候，需要使用show create table语句完成。具体如下所示：
在proxysql中查看表结构的时候，是不能使用desc语句或者describe语句的。只能使用show create table语句。具体如下所示：
ProxySQL Admin> desc mysql_users;
ERROR 1045 (28000): ProxySQL Admin Error: near "desc": syntax error
ProxySQL Admin> describe mysql_users;
ERROR 1045 (28000): ProxySQL Admin Error: near "describe": syntax error
ProxySQL Admin> describe mysql_servers;
ERROR 1045 (28000): ProxySQL Admin Error: near "describe": syntax error
ProxySQL Admin> show create table mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port
<= 65535) NOT NULL DEFAULT 0,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'
)) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144
000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.00 sec)
3.2. 配置ProxySQL实现读写分离
连接到ProxySQL的管理服务具体如下所示：
[root@c7u6s1:~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "
[root@c7u6s1:~]# mysql -uadmin -p -P 6032 -h localhost --protocol=TCP
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
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.
(admin@localhost:6032) [(none)]>
由于主要的配置操作都是在main这个数据库中完成，查看其中的表，具体如下所示：
(admin@localhost:6032) [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
(admin@localhost:6032) [(none)]> use main;
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
(admin@localhost:6032) [main]> show tables;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
32 rows in set (0.00 sec)
上述即为main数据库中包含的表。
3.2.1. 向ProxySQL服务器中添加后端数据库服务器
接下来向ProxySQL管理服务中插入后端数据库服务器的信息，具体如下所示：
(admin@localhost:6032) [main]> show create table mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_po
rt <= 65535) NOT NULL DEFAULT 0,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HA
RD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126
144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.00 sec)
(admin@localhost:6032) [main]>
(admin@localhost:6032) [main]> insert into mysql_servers
-> (hostgroup_id, hostname, port, weight, comment)
-> values
-> (20, '192.168.122.22', 3306, 1, 'Read Group'),
-> (10, '192.168.122.21', 3306, 1, 'Write Group')
-> ;
Query OK, 2 rows affected (0.00 sec)
(admin@localhost:6032) [main]> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 20
hostname: 192.168.122.22
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Read Group
*************************** 2. row ***************************
hostgroup_id: 10
hostname: 192.168.122.21
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: Write Group
2 rows in set (0.00 sec)
(admin@localhost:6032) [main]>
两台后端数据库服务器以及成功被添加到ProxySQL代理服务器中，其中hostgroup_id=10的组为写入主机组（对应的主机须为master节点），后面对数据库的修改操作，会被路由到这个主机组上；hostgroup_id=20的组为读取主机组（对应的节点须为slave节点），对后端数据库的查询操作，且被后面指定的查询规则匹配的语句，都会被路由到这个主机组上执行。
要使上述配置生效，并且持久保存，还需要执行如下操作，具体如下所示：
(admin@localhost:6032) [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.02 sec)
(admin@localhost:6032) [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)
(admin@localhost:6032) [main]>
检查新添加的后端的健康状态，具体如下所示：
(admin@localhost:6032) [main]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
(admin@localhost:6032) [main]> select * from monitor.mysql_server_ping_log order by ti
me_start_us desc limit 3;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.122.22 | 3306 | 1628793125874832 | 955 | NULL |
| 192.168.122.21 | 3306 | 1628793125766894 | 1015 | NULL |
| 192.168.122.21 | 3306 | 1628793115923603 | 991 | NULL |
+----------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)
(admin@localhost:6032) [main]>
从上述输出可以看出，两个节点已经成功添加完成。
3.2.2. 准备代理服务的用户
在上面的配置文件中，设置了代理服务的用户为monitor，即可以连接到6033端口的用户，此时这个用户虽然在ProxySQL服务的配置文件中指定了，但是在后端数据库服务器上还不存在，所以此时需要在后端数据库服务器的master节点上创建这个用户(由于已经配置了主从复制，所以只需要在master节点上执行即可，master节点的数据库变更情况会自动复制到slave节点上)，并给这个用户授权，具体如下所示：
(root@localhost) [mydb]> create user if not exists monitor identified by 'monitor';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [mydb]> grant all privileges on *.* to monitor;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [mydb]>
上述操作就完成了monitor用户的创建，并且将所有数据库的所有权限都赋予给这个用户，实际在生产环境中，尽量给这个用户制定数据库的指定权限，而不要开放完全权限。
对于未被查询规则匹配的语句，需要给其指定默认的主机组，此时就需要在monitor用户这里进行指定了。具体如下所示：
(admin@localhost:6032) [main]> show create table mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT
'',
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
(admin@localhost:6032) [main]> insert into mysql_users
-> (username, password, default_hostgroup, transaction_persistent)
-> values
-> ('monitor', 'monitor', 20, 1)
-> ;
Query OK, 1 row affected (0.00 sec)
(admin@localhost:6032) [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@localhost:6032) [main]> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
(admin@localhost:6032) [main]>
(admin@localhost:6032) [main]> select * from mysql_users\G
*************************** 1. row ***************************
username: monitor
password: monitor
active: 1
use_ssl: 0
default_hostgroup: 20
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
1 row in set (0.00 sec)
(admin@localhost:6032) [main]>
上述就向main.mysql_users表中添加了monitor这个用户，并且为其指定了默认的主机组，即没有被查询规则匹配到的语句，会被路由到默认的主机组上，此处是路由到hostgoup_id=20的主机组上，即读取主机组。
在完成了上述的添加后端服务器以及代理服务的用户以及默认主机组之后，就可以向ProxySQL服务器中添加查询规则了。
3.2.3. 添加查询规则实现读写分离
接下来添加读写分离规则，将select语句路由到hostgroup_id=20的主机组上，将对数据库有更新等写入操作的SQL语句路由到hostgroup_id=10的主机组上。其他未被匹配到的规则北路有到默认的主机组中（在mysql_users表中的字段default_hostgroup中指定的组）。此处的默认主机组为读取组，即hostgroup_id=10的组。具体如下所示：
(admin@localhost:6032) [main]> show create table mysql_query_rules\G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT CHECK (flagOUT >= 0),
replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_p$
ttern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
cache_timeout INT CHECK(cache_timeout >= 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED CHECK (timeout >= 0),
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED CHECK (delay >=0),
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
gtid_from_hostgroup INT UNSIGNED,
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT
'',
comment VARCHAR)
1 row in set (0.00 sec)
(admin@localhost:6032) [main]>
(admin@localhost:6032) [main]> insert into mysql_query_rules
-> (rule_id, active, match_digest, destination_hostgroup, apply)
-> values
-> (10, 1, '.*update.*', 10, 1),
-> (20, 1, '^select.*', 20, 1),
-> (11, 1, '^create.*', 10, 1),
-> (12, 1, '.*set.*', 10, 1)
-> (13, 1, '^delete from.*', 10, 1),
-> (14, 1, '^drop.*', 10, 1)
-> ;
Query OK, 6 rows affected (0.01 sec)
(admin@localhost:6032) [main]> select rule_id, active, match_digest, destination_hostgroup, apply from mysql_query_rules;
+---------+--------+----------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------+-----------------------+-------+
| 10 | 1 | .*update.* | 10 | 1 |
| 11 | 1 | ^create.* | 10 | 1 |
| 12 | 1 | .*set.* | 10 | 1 |
| 13 | 1 | ^delete from.* | 10 | 1 |
| 14 | 1 | ^drop.* | 10 | 1 |
| 20 | 1 | ^select.* | 20 | 1 |
+---------+--------+----------------+-----------------------+-------+
6 rows in set (0.01 sec)
(admin@localhost:6032) [main]>
(admin@localhost:6032) [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.03 sec)
(admin@localhost:6032) [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@localhost:6032) [main]>
上述添加了4条查询规则，将create, update, set相关的操作都路由到写入主机组，及hostgroup_id=10的主机组（对应于master节点），将select相关的操作路由到读取主机组，及hostgroup_id=20的主机组。
3.2.4. 测试读写分离效果
使用monitor用户，从TCP协议的6033号端口连接到ProxySQL服务，访问代理服务，具体如下所示：
[root@c7u6s1:~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "
[root@c7u6s1:~]# mysql -umonitor -p -P 6033 --protocol=TCP -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL)
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.
(monitor@localhost:6033) [(none)]>
连接成功，此时查看数据库，应该是后端数据库服务器中的数据库以及表，而不是ProxySQL自身的库和表。具体如下所示：
(monitor@localhost:6033) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.01 sec)
(monitor@localhost:6033) [(none)]>
执行一些查询操作，具体如下所示：
(monitor@localhost:6033) [(none)]> show tables in mydb;
+----------------+
| Tables_in_mydb |
+----------------+
| department |
| employee |
| product |
| product_sel |
| score |
| student |
+----------------+
6 rows in set (0.00 sec)
(monitor@localhost:6033) [(none)]> show tables in testdb;
+-------------------+
| Tables_in_testdb |
+-------------------+
| department |
| dept_trigger_time |
| employee |
| gradeinfo |
| information |
| operate |
| product |
| score |
| student |
| student2 |
| trigger_test |
+-------------------+
11 rows in set (0.00 sec)
(monitor@localhost:6033) [(none)]>
上述执行了一些查询操作，接下来切换回ProxySQL的管理服务端口，查看统计信息，具体如下所示：
(admin@localhost:6032) [main]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
(admin@localhost:6032) [main]> show tables in stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
19 rows in set (0.00 sec)
(admin@localhost:6032) [main]>
(admin@localhost:6032) [main]> select * from stats.stats_mysql_query_digest limit 1\G
*************************** 1. row ***************************
hostgroup: 20
schemaname: information_schema
username: monitor
client_address:
digest: 0x78D2B3EFF97BA186
digest_text: show tables in mydb
count_star: 1
first_seen: 1628794593
last_seen: 1628794593
sum_time: 427
min_time: 427
max_time: 427
sum_rows_affected: 0
sum_rows_sent: 6
1 row in set (0.01 sec)
(admin@localhost:6032) [main]> select * from stats.stats_mysql_query_digest limit 2\G
*************************** 1. row ***************************
hostgroup: 20
schemaname: information_schema
username: monitor
client_address:
digest: 0x84F71D10FC89BC2E
digest_text: show tables in testdb
count_star: 1
first_seen: 1628794692
last_seen: 1628794692
sum_time: 524
min_time: 524
max_time: 524
sum_rows_affected: 0
sum_rows_sent: 11
上述输出显示，这两个show databases语句都被路由到了默认的hostgoup=20这个主机组上执行了。
接下来执行一些select语句，具体如下所示：
(monitor@localhost:6033) [(none)]> select * from testdb.department;
+------+-----------+--------------+---------+
| d_id | d_name | function | address |
+------+-----------+--------------+---------+
| 1001 | 人事部 | 人事管理 | 北京 |
| 1002 | 科研部 | 研发产品 | 北京 |
| 1003 | 生产部 | 生产产品 | 天津 |
| 1004 | 销售部 | 商品销售 | 上海 |
+------+-----------+--------------+---------+
4 rows in set (0.00 sec)
(monitor@localhost:6033) [(none)]>
切换回ProxySQL的管理服务接口，查询统计信息，具体如下所示：
(admin@localhost:6032) [main]> select * from stats.stats_mysql_query_digest limit 1\G
*************************** 1. row ***************************
hostgroup: 20
schemaname: information_schema
username: monitor
client_address:
digest: 0xEC76060623D97973
digest_text: select * from testdb.department
count_star: 1
first_seen: 1628794995
last_seen: 1628794995
sum_time: 3430
min_time: 3430
max_time: 3430
sum_rows_affected: 0
sum_rows_sent: 4
1 row in set (0.02 sec)
(admin@localhost:6032) [main]>
从上述输出可以见，查询操作北路有到了hostgoup=20的读取主机组上。
删除此前创建的名为write_read_split的数据库，具体如下所示：
(monitor@localhost:6033) [mydb]> drop database write_read_split;
Query OK, 0 rows affected (0.01 sec)
(monitor@localhost:6033) [mydb]>
切换回ProxySQL的管理服务接口，具体如下所示：
(admin@localhost:6032) [main]> select * from stats.stats_mysql_query_digest\G
*************************** 35. row ***************************
hostgroup: 10
schemaname: information_schema
username: monitor
client_address:
digest: 0xACFF75AB93F160B5
digest_text: drop database write_read_split
count_star: 1
first_seen: 1628760171
last_seen: 1628760171
sum_time: 5700
min_time: 5700
max_time: 5700
sum_rows_affected: 0
sum_rows_sent: 0
从上述输出可见，删除数据库的操作被路由到了hostgoup=10的主机组，即写入主机组。
接下来更新一下数据库中表的字段内容，具体如下所示：
(monitor@localhost:6033) [mydb]> select * from mydb.product;
+----+--------------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 9500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
| 9 | Fender Cobra Blue 011-8012-795 | FENDER | 14480 | 世界淘吉他 |
+----+--------------------------------+---------------+--------------+-----------------+
8 rows in set (0.00 sec)
(monitor@localhost:6033) [mydb]> update product set guitar_price=10500 where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(monitor@localhost:6033) [mydb]> select * from mydb.product;
+----+--------------------------------+---------------+--------------+-----------------+
| id | guitar_name | producer_name | guitar_price | dealer |
+----+--------------------------------+---------------+--------------+-----------------+
| 1 | YAMAHA A3R VN | YAMAHA | 6500 | KuYueTang |
| 2 | Ibanez RG2550Z | IBANEZ | 12800 | LingYueYueQi |
| 4 | Fender Japan Aerodyne II | FENDER | 8100 | NULL |
| 5 | Fender Japan Morden | FENDER | 10500 | NULL |
| 6 | PRS Custom SE24 JAKKO | PRS | 5330 | NULL |
| 7 | Taylor 314CE | TAYLOR | 14600 | WuYouMeiTao |
| 8 | Ibanez J.Custom RG8570Z | IBANEZ | 17500 | 西木野乐器 |
| 9 | Fender Cobra Blue 011-8012-795 | FENDER | 14480 | 世界淘吉他 |
+----+--------------------------------+---------------+--------------+-----------------+
8 rows in set (0.01 sec)
(monitor@localhost:6033) [mydb]>
切换回ProxySQL的管理服务接口，查询统计信息，具体如下所示：
(admin@localhost:6032) [main]> select * from stats.stats_mysql_query_digest limit 1\G
*************************** 1. row ***************************
hostgroup: 10
schemaname: mydb
username: monitor
client_address:
digest: 0xE1675EA780593064
digest_text: update product set guitar_price=? where id=?
count_star: 1
first_seen: 1628795724
last_seen: 1628795724
sum_time: 6284
min_time: 6284
max_time: 6284
sum_rows_affected: 1
sum_rows_sent: 0
1 row in set (0.01 sec)
(admin@localhost:6032) [main]>
从上述输出可以看出，这个修改表中字段的操作，也被路由到了hostgroup=10这个主机组中，即写入主机组。
至此，已经实现了基于ProxySQL的MySQL数据库服务器的读写分离操作。
4. References
[1]. 16.1 Configuring Replication
[2]. 16.3.9 Semisynchronous Replication
[3]. Setup MySQL Replication Cluster: Architecture, Use Cases and Tutorial
[4]. How to setup a replica for replication in 6 simple steps with Percona XtraBackup
[5]. proxysql实现mysql读写分离
[6]. How to set up ProxySQL Read/Write Split
[7]. ProxySQL Documentation
880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
