1.1 MySQL主从复制
1.1.1 主从复制架构和原理
1.1.1.1 服务性能扩展方式
-
Scale Up，向上扩展，垂直扩展（单台服务器使用最好的配置）
-
Scale Out，向外扩展，横向扩展（多台服务器扩展使用）
1.1.1.2 MySQL的扩展
-
读写分离
-
复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
1.1.1.3 复制的功用
-
数据分布
-
负载均衡读
-
备份
-
高可用和故障切换
-
MySQL升级测试
1.1.1.4 复制架构
-
一主一从复制架构
-
一主多从复制架构
1.1.1.5 主从复制原理
主从复制相关线程
主节点：
-
必须开启二进制日志功能；
-
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
-
建议开启二进制日志，一旦主服务器宕机，从服务器接管；二是备份数据库可以在从服务器备份，减少主服务器压力；
-
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
-
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
-
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
-
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
-
mariadb-relay-bin.00000#: 中继日志，保存从主节点复制过来的二进制日志，本质就是二进制日志
1.1.1.6 主从复制特点
-
异步复制
-
主从数据不一致比较常见
1.1.1.7 各种复制架构
-
一Master/一Slave
-
一主多从
-
从服务器还可以再有从服务器
-
Master/Master（其实还是一主一从，当主down掉，从直接接手不用配置）
-
一从多主：适用于多个不同数据库
-
环状复制
1.1.2 实现主从复制配置
参考官网 https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
1.1.2.1 主节点配置
(1) 启用二进制日志
[mysqld]
log_bin #开启二进制日志
?
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]
server-id=#
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
?
(3) 创建有复制权限的用户账号和密码
GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘HOST‘ IDENTIFIED BY ‘centos‘;
?
(4) 查看从二进制日志的文件和位置开始进行复制
SHOW MASTER LOG;
说明：server-id的取值范围
server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
?
select @@server-id #默认值都是1，需要设置成网络内全局唯一的ID号
1.1.2.2 从节点配置
(1) 启动中继日志
[mysqld]
server_id=# #为当前节点设置一个全局唯一的ID号
log-bin #开启二进制日志
read_only=ON #设置数据库只读，只针对普通用户，对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
?
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
vim /data/all.sql #主节点导出的数据库备份文件，在CHANGE MASTER TO行添加
CHANGE MASTER TO MASTER_HOST=‘masterhost‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘replpass‘,
MASTER_LOG_FILE=‘mariadb-bin.xxxxxx‘,
MASTER_LOG_POS=#;
?
START SLAVE [IO_THREAD|SQL_THREAD]; #不跟选项，默认启用两个线程
SHOW SLAVE STATUS;
1.1.2.3 主服务器非新建
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
-
通过备份恢复数据至从服务器
-
复制起始位置为备份时，二进制日志文件及其POS
范例1：主服务器非新建，一主一从
#1)主节点配置（100.8），主节点已有数据
[root@c8-master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8 #节点唯一ID号
log-bin
[root@c8-master ~]# systemctl restart mariadb
[root@c8-master ~]# mysql
mysql <hellodb.sql
MariaDB [hellodb]> insert teachers (name,age,gender)values(‘mage‘,30,‘M‘);
Query OK, 1 row affected (0.001 sec)
?
MariaDB [hellodb]> update students set classid=3 where stuid=24;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 ?Changed: 1 ?Warnings: 0
?
#记录下来二进制日志的名字和位置大小
[root@c8-master ~]# mysql -e ‘show master logs‘
+--------------------+-----------+
| Log_name ? ? ? ? ? | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? ? ? 795 |
| mariadb-bin.000002 | ? ? ? 640 |
| mariadb-bin.000003 | ? ? ? 389 |
+--------------------+-----------+
?
#2）在主服务器完全备份
[root@c8-master ~]# mkdir /backup
[root@c8-master ~]# mysqldump -A -F --single-transaction --master-data=1 >/backup/all_`date +%F`.sql
[root@c8-master ~]# ll /backup
total 476
-rw-r--r-- 1 root root 485809 Jun 26 15:42 all_2021-06-26.sql
?
#授权账户，参与复制
grant replication slave on *.* to repluser@‘192.168.8.%‘ identified by ‘magedu‘;
?
#3）复制备份文件到从节点
[root@c8-master ~]# scp /backup/all_2021-06-26.sql 192.168.100.18:/data/ #/data目录要存在
?
?
#4）将完全备份还原到新的从节点
[root@slave01 ~]# dnf -y install mariadb-server
[root@slave01 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only #不允许从节点写入数据，只读，先可不用
log-bin #先可不用
[root@slave01 ~]# systemctl restart mariadb
?
#5）配置从节点，从完全备份的位置之后开始复制
[root@c8-slave01 ~]# vi /data/all_2021-06-26.sql
CHANGE MASTER TO
MASTER_HOST=‘192.168.100.8‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘magedu‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000003‘, MASTER_LOG_POS=389;
?
#从节点还原
[root@c8-slave01 ~]# mysql </data/all_2021-06-26.sql
#注：还原时，最好先把二进制日志关闭，不然会生成很多日志，set sql_log_bin=0;等恢复后，再打开二进制日志选项。
[root@slave01 ~]#mysql
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State:
? ? ? ? ? ? ? ? ? Master_Host: 192.168.100.8 #都是change master的内容
? ? ? ? ? ? ? ? ? Master_User: repluser
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 389
? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000001
? ? ? ? ? ? ? ? Relay_Log_Pos: 4
? ? ? ? Relay_Master_Log_File: mariadb-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: No #IO和SQL线程还未启动，未同步
? ? ? ? ? ? Slave_SQL_Running: No
? ? ? ? Seconds_Behind_Master: NULL #复制的延迟时间，现在还没有开始同步，0表示已经同步完成
[root@c8-slave01 ~]# cat /var/lib/mysql//master.info #主节点信息
33
mariadb-bin.000003
389
192.168.100.8
repluser
magedu
3306
60
0
...省略...
[root@c8-slave01 ~]# cat /var/lib/mysql/relay-log.info #二进制日志对应关系
5
./mariadb-relay-bin.000001
4
mariadb-bin.000003
389
0
?
MariaDB [(none)]> start slave; #启动线程
Query OK, 0 rows affected (0.005 sec)
?
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.100.8
? ? ? ? ? ? ? ? ? Master_User: repluser
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 389
? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 557
? ? ? ? Relay_Master_Log_File: mariadb-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: Yes #线程已启动
? ? ? ? ? ? Slave_SQL_Running: Yes
?
MariaDB [(none)]> show processlist; #从节点的线程
+----+-------------+-----------+------+-----------+------+-------------------------------
| Id | User ? | Host ? ? ?| db ? | Command ? | Time | State ? ?|Info ? ? ? ? | Progress |
+----+-------------+-----------+------+-----------+------+-------------------------------
| 12 | system user | | NULL | Slave_IO|64| Waiting for master to send event|NULL|0.000 |
| 13 | system user | | NULL| Slave_SQL|64| Slave has read all relay log; waiting for the slave I/O thread to update it | NULL| 0.000 |
+----+-------------+-----------+------+-----------+------+-------------------------------
8 rows in set (0.000 sec)
?
MariaDB [(none)]> show processlist; #主节点线程
+----+-------------+----------------------+------+-------------+------+------------------| Id | User ? ? ? ?| Host ? ? |db ? | Command ? ? | Time | State| Info ?| Progress |
+----+-------------+----------------------+------+-------------+------+------------------
| 15 | repluser ? ?| 192.168.100.18:35872 | NULL | Binlog Dump | ?189 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL ? ? ? ? ? ? | ? ?0.000 |
+----+-------------+----------------------+------+-------------+------+------------------
7 rows in set (0.000 sec)
?
#查看是否有复制账户repluser，证明已经同步，因为是先备份的数据库，后授权的用户
MariaDB [(none)]> select user,host from mysql.user;
+----------+---------------+
| user ? ? | host ? ? ? ? ?|
+----------+---------------+
| root ? ? | 127.0.0.1 ? ? |
| repluser | 192.168.100.% |
| root ? ? | ::1 ? ? ? ? ? |
| root ? ? | c8-dns ? ? ? ?|
| root ? ? | localhost ? ? |
+----------+---------------+
5 rows in set (0.002 sec)
?
?
#同步数据，主节点任何数据库的更改，都同步到从节点
#主节点创建数据库db1
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.001 sec)
?
#从节点可以立即同步
MariaDB [(none)]> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| db1 ? ? ? ? ? ? ? ?|
| hellodb ? ? ? ? ? ?|
| information_schema |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)
?
drop databases db1; #主节点删除，从节点也同时删除，所以主从复制不能代替备份
show databases;
?
#主节点增加10w条数据
MariaDB [hellodb]> call pro_testlog;
Query OK, 99999 rows affected (1 min 29.840 sec)
MariaDB [hellodb]> show master logs;
+--------------------+-----------+
| Log_name ? ? ? ? ? | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? ? ? 795 |
| mariadb-bin.000002 | ? ? ? 640 |
| mariadb-bin.000003 | ?27378748 |
+--------------------+-----------+
3 rows in set (0.000 sec)
?
#从节点上可以看到同步延时以及传输的数据量等信息
MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.100.8
? ? ? ? ? ? ? ? ? Master_User: repluser
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 27378748
? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 26576096
? ? ? ? Relay_Master_Log_File: mariadb-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
? ? ? ? ? Exec_Master_Log_Pos: 26575928
? ? ? ? ? ? ? Relay_Log_Space: 27379227
? ? ? ? Seconds_Behind_Master: 13 #延时
? ?Slave_Transactional_Groups: 97070 #传输的数量
1 row in set (0.000 sec)
MariaDB [hellodb]> show slave status\G; #同步结束后
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.100.8
? ? ? ? ? ? ? ? ? Master_User: repluser
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 27378748
? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 27378916
? ? ? ? Relay_Master_Log_File: mariadb-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
? ? ? ? ? Exec_Master_Log_Pos: 27378748
? ? ? ? ? ? ? Relay_Log_Space: 27379227
? ? ? ? Seconds_Behind_Master: 0
? ?Slave_Transactional_Groups: 99999
1 row in set (0.000 sec)
?
优化选项
?
#建议优化主和从节点服务器的性能(有二进制日志的都需要优化)
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2; #主节点
MariaDB [hellodb]> set global sync_binlog=0;
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2; #从节点
MariaDB [hellodb]> set global sync_binlog=0;
?
MariaDB [hellodb]> show variables like ‘sync_binlog‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog ? | 0 ? ? |
+---------------+-------+
1 row in set (0.001 sec)
?
?
#开启优化后，只需28s
MariaDB [hellodb]> call pro_testlog;
[Query OK, 99999 rows affected (28.345 sec)
#开启优化并启动事务后，只需3s
MariaDB [hellodb]> begin;call pro_testlog;commit;
Query OK, 99999 rows affected (3.251 sec)
?
范例2：再增加一个从节点
1)先在主节点上做完全备份
[root@c8-master ~]# mysqldump -A -F --single-transaction --master-data=1 >/backup/all_`date +%F_%T`.log
[root@c8-master ~]# ll /backup
total 13920
-rw-r--r-- 1 root root 13764092 Jun 26 17:03 all_2021-06-26_17:03:05.log
-rw-r--r-- 1 root root ? 485809 Jun 26 15:42 all_2021-06-26.sql
?
2）复制到新的从节点上
#rsync -av /backup/all.sql 192.168.100.28:/data
[root@c8-master ~]# scp /backup/all_2021-06-26_17\:03\:05.log 192.168.100.28:/data/
[root@c8-master ~]# mysql -e ‘show master logs‘
+--------------------+-----------+
| Log_name ? ? ? ? ? | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? ? ? 795 |
| mariadb-bin.000002 | ? ? ? 640 |
| mariadb-bin.000003 | 114989291 |
| mariadb-bin.000004 | ? ? ? 389 |
+--------------------+-----------+
?
?
3)安装10.3
[root@c8-slave02 ~]# yum install -y mariadb-server
?
#更改mariadb-server.cnf配置文件
[root@c8-slave02 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
server-id=28
read-only
log-bin
4）修改/data/all.sql的内容
[root@c8-slave02 ~]# vi /data/all_2021-06-26_17\:03\:05.log
change master to
master_host=‘192.168.100.8‘,
master_user=‘repluser‘,
master_password=‘magedu‘,
master_port=‘3306‘,
master_log_file=‘mariadb-bin.000004‘,master_log_pos=389;
5)还原到第二从节点（28）
[root@c8-slave02 ~]# mysql </data/all_2021-06-26_17\:03\:05.log
?
6)登录，然后启用线程，同步
[root@c8-slave02 ~]# mysql hellodb
MariaDB [hellodb]> start slave;
?
7)主节点添加记录，查看从节点的状态
insert teachers (name,age,gender)values(‘mage‘,30,‘M‘);
#从两个节点查看,都有这条新添加的记录
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name ? ? ? ? ?| Age | Gender |
+-----+---------------+-----+--------+
| ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|
| ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|
| ? 3 | Miejue Shitai | ?77 | F ? ? ?|
| ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|
| ? 6 | mage ? ? ? ? ?| ?30 | M ? ? ?|
| ? 7 | wang ? ? ? ? ?| ?33 | M ? ? ?|
+-----+---------------+-----+--------+
6 rows in set (0.001 sec)
1.1.3 主主复制
主主复制：两个节点，都可以更新数据，并且互为主从，前端的代理服务器分配哪个为主，哪个为从，主节点宕机后，从节点直接接管。
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id，为避免生成id冲突，可以使用奇偶id配置
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
主主复制的配置步骤：
-
各节点使用一个惟一server_id
-
都启动binary log和relay log
-
创建拥有复制权限的用户账号
-
定义自动增长id字段的数值范围各为奇偶
-
均把对方指定为主节点，并启动复制线程
范例：实现两个节点的主主复制模型
#在第一个master节点上实现2 xtrabackup实现全量增量binlog恢复库
[root@master1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
[root@master1 ~]# systemctl start mariadb
[root@master1 ~]# mysql
MariaDB [(none)]>show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? ? 28251 |
| mariadb-bin.000002 | ? ? ? 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@‘192.168.100.%‘
identified by ‘magedu‘;
?
#在第二个master节点上实现
[rootmaster2 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
[root@master2 ~]# systemctl start mariadb
[root@master2 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.100.8‘,
-> MASTER_USER=‘repluser‘,
-> MASTER_PASSWORD=‘magedu‘,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘mariadb-bin.000002‘,
-> MASTER_LOG_POS=344;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show master logs; #查看二进制位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? 28251 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.001 sec)
?
#在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.100.18‘,
-> MASTER_USER=‘repluser‘,
-> MASTER_PASSWORD=‘magedu‘,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘mariadb-bin.000002‘,
-> MASTER_LOG_POS=344;
MariaDB [(none)]> start slave;
MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));
#两个节点分别插入数据
#在第一个节点上执行
MariaDB [db1]> create database db1;
MariaDB [db1]> insert t1 (name) values(‘user1‘);
#在第二个节点上执行
MariaDB [db1]> insert t1 (name) values(‘user2‘);
#两个节点同时插入数据
MariaDB [db1]> insert t1 (name) values(‘userX‘);
MariaDB [db1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.001 sec)
?
#两个节点同时创建数据库，发生复制冲突
MariaDB [db1]> create database db2;
?
2.1 xtrabackup工具介绍
Xtrabackup备份工具
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html 下载: https://www.percona.com/downloads/
2.2 xtrabackup 特点
-
备份还原过程快速、可靠
-
备份过程不会打断正在执行的事务
-
能够基于压缩等功能节约磁盘空间和流量
-
自动实现备份检验
-
开源，免费
2.3 xtrabackup安装
yum install percona-xtrabackup 在EPEL源中
最新版本下载安装： https://www.percona.com/downloads/XtraBackup/LATEST/
2.4 xtrabackup用法
xtrabackup工具备份和还原，需要三步实现
-
备份：对数据库做完全或增量备份
-
预准备： 还原前，先对备份的数据，整理至一个临时目录，即回滚未完成的事务
-
还原：将整理好的数据，复制回数据库目录中
xtrabackup 选项参考： https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
备份：
innobackupex [option] BACKUP-ROOT-DIR
选项说明：
--user #该选项表示备份账号
--password #该选项表示备份的密码
--host #该选项表示备份数据库的地址
--databases #该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
#如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。
#如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file #该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental #该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir #该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-dir #该选项表示还原时增量备份的目录
--include=name #指定表名，格式：databasename.tablename
?
Prepare预准备：
innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log #一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory #和--apply-log选项一起使用，当prepare备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
--export #表示开启可导出单独的表之后再导入其他Mysql中
--redo-only #此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
还原：
innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back #做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back #这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同时保留数据文件和Backup副本
--force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或--moveback选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败
还原注意事项：
-
datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-back选项不会覆盖
-
在restore之前，必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
-
由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户， 执行chown -R mysql:mysql /data/mysql，以上需要在用户调用innobackupex之前完成
2.5 实战案例：利用xtrabackup实现完全备份及还原
注意：目前percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS 8上的mariadb-10.3版本
案例：利用xtrabackup完全，增量备份及还原
本案例基于CentOS 8 的 MySQL5.7 实现
?
1 备份过程
1）完全备份
[root@c8-mysql5.7 scripts]# mkdir /backup/
[root@c8-mysql5.7 scripts]# xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
#查看备份目录
[root@c8-mysql5.7 scripts]# du /data/mysql/ -sh
147M ? ?/data/mysql/
[root@c8-mysql5.7 scripts]# du /backup/base/ -sh
39M ? ? /backup/base/
[root@c8-mysql5.7 scripts]# tree -d /backup
/backup
└── base
? ?├── hellodb
? ?├── mysql
? ?├── performance_schema
? ?└── sys
5 directories
?
2）第一次修改数据
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e "insert hellodb.teachers (name,age,gender)values(‘mage‘,30,‘M‘)"
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e "insert hellodb.teachers (name,age,gender)values(‘wang‘,33,‘M‘)"
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e "select * from hellodb.teachers"
+-----+---------------+-----+--------+
| TID | Name ? ? ? ? ?| Age | Gender |
+-----+---------------+-----+--------+
| ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|
| ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|
| ? 3 | Miejue Shitai | ?77 | F ? ? ?|
| ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|
| ? 5 | mage ? ? ? ? ?| ?30 | M ? ? ?|
| ? 6 | wang ? ? ? ? ?| ?33 | M ? ? ?|
+-----+---------------+-----+--------+
[root@c8-mysql5.7 scripts]#
?
3）第一次增量备份，基于完全备份
[root@c8-mysql5.7 scripts]# xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@c8-mysql5.7 scripts]# ll /backup
total 0
drwxr-x--- 6 root root 238 Jun 23 23:16 base
drwxr-x--- 6 root root 264 Jun 23 23:30 inc1
[root@c8-mysql5.7 scripts]# du -sh /backup/*
39M ? ? /backup/base
3.5M ? ?/backup/inc1
?
4）第二次修改数据
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e "insert hellodb.teachers (name,age,gender)values(‘zhang‘,35,‘M‘)"
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e "insert hellodb.teachers (name,age,gender)values(‘guiyuan‘,25,‘M‘)"
?
5）第二次增量，基于第一次增量
[root@c8-mysql5.7 scripts]# xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
?
[root@c8-mysql5.7 scripts]# du -sh /backup/*
39M ? ? /backup/base
3.5M ? ?/backup/inc1
3.4M ? ?/backup/inc2
[root@c8-mysql5.7 scripts]# ll /backup
total 0
drwxr-x--- 6 root root 238 Jun 23 23:16 base
drwxr-x--- 6 root root 264 Jun 23 23:30 inc1
drwxr-x--- 6 root root 264 Jun 23 23:35 inc2
?
2还原过程
#破坏数据
[root@c8-mysql5.7 scripts]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@c8-mysql5.7 scripts]# rm /data/mysql/* -rf
[root@c8-mysql5.7 scripts]# service mysqld start
Starting MySQL.Logging to ‘/data/mysql/mysql.log‘.
................ ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).
?
?
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@c8-mysql5.7 scripts]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
?
2）合并第1次增量备份到完全备份
[root@c8-mysql5.7 scripts]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
?
3）合并第2次增量备份到完全备份，最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2
[root@c8-mysql5.7 scripts]# du -sh /backup/*
155M ? ?/backup/base
12M ? ? /backup/inc1
3.4M ? ?/backup/inc2
?
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@c8-mysql5.7 scripts]# ss -nlt #无3306端口号
[root@c8-mysql5.7 scripts]# ps aux|grep mysql
root ? ? ? 1242 ?0.0 ?0.1 135908 ?3612 pts/6 ? ?T ? ?02:08 ? 0:00 mysql -uroot -px xxxx
root ? ? 118772 ?0.0 ?0.0 112716 ? 960 pts/6 ? ?S+ ? 23:42 ? 0:00 grep --color=auto mysql
[root@c8-mysql5.7 scripts]# ll /data/mysql/
total 0
?
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
[root@c8-mysql5.7 scripts]# ll /data/mysql/
total 122924
drwxr-x--- 2 root root ? ? ?310 Jun 23 23:43 hellodb
-rw-r----- 1 root root ? ? 1259 Jun 23 23:43 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jun 23 23:43 ibdata1
-rw-r----- 1 root root 50331648 Jun 23 23:43 ib_logfile0
-rw-r----- 1 root root 50331648 Jun 23 23:43 ib_logfile1
-rw-r----- 1 root root 12582912 Jun 23 23:43 ibtmp1
drwxr-x--- 2 root root ? ? 4096 Jun 23 23:43 mysql
drwxr-x--- 2 root root ? ? 8192 Jun 23 23:43 performance_schema
drwxr-x--- 2 root root ? ? 8192 Jun 23 23:43 sys
-rw-r----- 1 root root ? ? ? 26 Jun 23 23:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root ? ? ?527 Jun 23 23:43 xtrabackup_info
-rw-r----- 1 root root ? ? ? ?1 Jun 23 23:43 xtrabackup_master_key_id
?
5）还原属性
[root@c8-mysql5.7 scripts]# chown -R mysql.mysql /data/mysql
?
6）启动服务
[root@c8-mysql5.7 scripts]# service mysqld start
Starting MySQL.Logging to ‘/data/mysql/mysql.log‘.
....... SUCCESS!
[root@c8-mysql5.7 scripts]# mysql -uroot -pmagedu -e ‘select * from hellodb.teachers‘
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------------+-----+--------+
| TID | Name ? ? ? ? ?| Age | Gender |
+-----+---------------+-----+--------+
| ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|
| ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|
| ? 3 | Miejue Shitai | ?77 | F ? ? ?|
| ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|
| ? 5 | mage ? ? ? ? ?| ?30 | M ? ? ?|
| ? 6 | wang ? ? ? ? ?| ?33 | M ? ? ?|
+-----+---------------+-----+--------+
?
?
3 Mycat安装3.1 利用Mycat实现MySQL的读写分离
系统环境：
cat /etc/centos-release
CentOS Linux release 8.0.1905 (Core)
服务器共三台
c8-client 192.168.100.9
c8-mycat 192.168.100.8 #内存建议2G以上
c8-master 192.168.100.18
c8-slave 192.168.100.28
关闭SELinux和防火墙
systemctl stop firewalld
setenforce 0
#时间同步 #尤其是加密等必须时间同步
3.1.1 创建 MySQL 主从数据库
yum -y install mariadb-server
1、修改master和slave上的配置文件
#c8-master上的my.cnf
[root@c8-master ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
[root@c8-master ~]# systemctl restart mariadb
#c8-slave上的my.cnf
[root@c8-slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
log-bin
read-only
[root@c8-slave ~]# systemctl restart mariadb
2、Master上创建复制用户
[root@c8-master ~]# mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name ? ? ? ? ? | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | ? ? ? 330 |
+--------------------+-----------+
1 row in set (0.001 sec)
?
MariaDB [(none)]> grant replication slave on *.* to repluser@‘192.168.100.%‘ identified by ‘magedu‘;
Query OK, 0 rows affected (0.001 sec)
?
3、Slave上执行
[root@c8-slave ~]# mysql
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> CHANGE MASTER TO
? ?-> MASTER_HOST=‘192.168.100.18‘,
? ?-> MASTER_USER=‘repluser‘,
? ?-> MASTER_PASSWORD=‘magedu‘,
? ?-> MASTER_PORT=3306,
? ?-> MASTER_LOG_FILE=‘mariadb-bin.000001‘,
? ?-> MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> set sql_log_bin=1;
?
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.100.18
? ? ? ? ? ? ? ? ? Master_User: repluser
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mariadb-bin.000001
? ? ? ? ? Read_Master_Log_Pos: 535
? ? ? ? ? ? ? ?Relay_Log_File: mariadb-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 762
? ? ? ? Relay_Master_Log_File: mariadb-bin.000001
? ? ? ? ? ? ?Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: Yes
?
...省略...
3.1.2 在c8-mycat(100.8)上安装mycat并启动
#1)安装java和mariadb客户端
[root@c8-mycat ~]# yum install java mariadb
#确认安装成功
[root@c8-mycat ~]# java -version
openjdk version "1.8.0_292"
OpenJDK Runtime Environment (build 1.8.0_292-b10)
OpenJDK 64-Bit Server VM (build 25.292-b10, mixed mode)
?
#2）下载并安装mycat
[root@c8-mycat ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@c8-mycat ~]# mkdir /app
[root@c8-mycat ~]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app
[root@c8-mycat ~]# ll /app
total 0
drwxr-xr-x 7 root root 85 Jun 27 20:46 mycat
?
#3）配置环境变量
[root@c8-mycat ~]# echo ‘PATH=/app/mycat/bin:$PATH‘ > /etc/profile.d/mycat.sh
[root@c8-mycat ~]# source /etc/profile.d/mycat.sh
[root@c8-mycat ~]# echo $PATH
/app/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
?
#查看端口
[root@c8-mycat ~]# ss -nlt
State ? ? ? ? Recv-Q ? ? ? Send-Q ? ? ? ? ? Local Address:Port ? ? ? Peer Address:Port
LISTEN ? ? ? ? ? ? ? ?0 ? ? ? 128 ? ? ? ? ? ? ? ? 0.0.0.0:22 ? ? ? ? ? ? ?0.0.0.0:*
LISTEN ? ? ? ? ? ? ? ?0 ? ? ? 128 ? ? ? ? ? ? ? ? ? ?[::]:22 ? ? ? ? ? ? ? ? [::]:*
?
[root@c8-mycat ~]# ll /app/mycat/logs
total 0
?
#启动mycat
[root@c8-mycat ~]# mycat start
Starting Mycat-server...
[root@c8-mycat ~]# ss -ntlp
State ? ? ? ? Recv-Q ? ? ? ? Send-Q ? ? ? Local Address:Port ? ? Peer Address:Port
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 128 ? ? ? ? ? ? 0.0.0.0:22 ? ? ? ? ? ? ? ? ? ? ? ? ? ? 0.0.0.0:* ? ? ? ? ? ? ?users:(("sshd",pid=966,fd=4))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 1 ? ? ? ? ? ? 127.0.0.1:32000 ? ? ? ? ? ? ? ? ? ? ? ? ?0.0.0.0:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=4))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 50 ? ? ? ? ? ? ? ? ? ?*:37167 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=66))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 128 ? ? ? ? ? ? ? ?[::]:22 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?[::]:* ? ? ? ? ? ? ?users:(("sshd",pid=966,fd=6))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 50 ? ? ? ? ? ? ? ? ? ?*:45535 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=68))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 50 ? ? ? ? ? ? ? ? ? ?*:1984 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=67))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 100 ? ? ? ? ? ? ? ? ? *:8066 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=91))
LISTEN ? ? ? ? ?0 ? ? ? ? ? ? ? 100 ? ? ? ? ? ? ? ? ? *:9066 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? *:* ? ? ? ? ? ? ?users:(("java",pid=12499,fd=87))
[root@c8-mycat ~]# ll /app/mycat/logs
total 56
-rw-r--r-- 1 root root 41969 Jun 27 20:53 mycat.log
-rw-r--r-- 1 root root ? ? 6 Jun 27 20:52 mycat.pid
-rw-r--r-- 1 root root ? ?96 Jun 27 20:52 switch.log
-rw-r--r-- 1 root root ? 488 Jun 27 20:52 wrapper.log
#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@c8-mycat ~]# cat /app/mycat/logs/wrapper.log #启动时的日志
STATUS | wrapper ?| 2021/06/27 20:52:32 | --> Wrapper Started as Daemon
STATUS | wrapper ?| 2021/06/27 20:52:32 | Launching a JVM...
INFO ? | jvm 1 ? ?| 2021/06/27 20:52:33 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO ? | jvm 1 ? ?| 2021/06/27 20:52:33 | ? Copyright 1999-2006 Tanuki Software, Inc. ?All Rights Reserved.
INFO ? | jvm 1 ? ?| 2021/06/27 20:52:33 |
INFO ? | jvm 1 ? ?| 2021/06/27 20:52:35 | MyCAT Server startup successfully. see logs in logs/mycat.log
?
?
#用默认密码123456来连接mycat
[root@c8-client ~]# mysql -uroot -p123456 -h 192.168.100.8 -P8066
Welcome to the MariaDB monitor. ?Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB ? |
+----------+
1 row in set (0.004 sec)
?
MySQL [(none)]> use TESTDB;
Database changed
MySQL [TESTDB]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address ? ? ? ? ?|
| travelrecord ? ? |
+------------------+
2 rows in set (0.001 sec)
?
MySQL [TESTDB]> select * from travelrecord;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0 #还未连接到后台真正的数据库服务器上
?
3.1.3 在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@centos8 ~]#vim /app/mycat/conf/server.xml
...省略...
<user name="root"> #连接Mycat的用户名
<property name="password">magedu</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
</user>
</mycat:server>
3.1.4 修改schema.xml实现读写分离策略
[root@c8-mycat ~]#vim /app/mycat/conf/schema.xml #加*表示修改的地方
[root@c8-mycat ~]# cat /app/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
? ? ? ?<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
? ? ? ?<dataNode name="dn1" dataHost="localhost1" database="mycat" />
? ? ? ?<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" ?slaveThreshold="100">
? ? ? ?<heartbeat>select user()</heartbeat>
? ? ? ?<writeHost host="host1" url="192.168.100.18:3306" user="root" password="123456">
? ? ? ?<readHost host="host2" url="192.168.100.28:3306" user="root" password="123456" /></writeHost>
? ? ? ?</dataHost>
</mycat:schema>
?
?
#重新启动mycat
[root@c8-mycat ~]#mycat restart
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是192.168.100.18为主库，192.168.100.28为从库
注意：要保证192.168.100.18和192.168.100.28机器能使用root/123456权限成功登录mysql数据库。
同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
3.1.5 在后端主服务器创建用户并对mycat授权
#mysql> create database mycat; #先创建一个mycat名称的数据库
?
#下面使用hellodb数据库改变名称创建
[root@c8-master ~]# vi /hellodb_innodb.sql #修改hellodb数据库，改为mycat名称，也就是创建mycat名字的数据库
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mycat` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mycat`;
[root@c8-master ~]# mysql </hellodb_innodb.sql
[root@c8-master ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| hellodb ? ? ? ? ? ?|
| information_schema |
| mycat ? ? ? ? ? ? ?|
| mysql ? ? ? ? ? ? ?|
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)
?
MariaDB [(none)]> grant all on *.* to ‘root‘@‘192.168.100.%‘ identified by ‘123456‘ with grant option;
Query OK, 0 rows affected (0.001 sec)
?
MariaDB [(none)]> flush privileges;
?
?
3.1.6 在Mycat服务器上连接并测试
[root@c8-client ~]# mysql -uroot -pmagedu -h 192.168.100.8 -P8066
Welcome to the MariaDB monitor. ?Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
MySQL [(none)]> show databases; #虚拟的TESTDB数据库
+----------+
| DATABASE |
+----------+
| TESTDB ? |
+----------+
1 row in set (0.004 sec)
?
MySQL [(none)]> use TESTDB;
Database changed
MySQL [TESTDB]> show tables; #查看到mycat数据库内的表，实际就是主节点的表
+-----------------+
| Tables_in_mycat |
+-----------------+
| classes ? ? ? ? |
| coc ? ? ? ? ? ? |
| courses ? ? ? ? |
| scores ? ? ? ? ?|
| students ? ? ? ?|
| teachers ? ? ? ?|
| toc ? ? ? ? ? ? |
+-----------------+
7 rows in set (0.003 sec)
?
MySQL [TESTDB]> select @@server_id; #在28上读取
+-------------+
| @@server_id |
+-------------+
| ? ? ? ? ?28 |
+-------------+
1 row in set (0.002 sec)
?
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| c8-slave ?|
+------------+
1 row in set (0.008 sec)
?
?
MySQL [TESTDB]> insert teachers values(5,‘mage‘,30,‘M‘); #写入记录
Query OK, 1 row affected (0.058 sec)
MariaDB [mycat]> select * from teachers; #18主节点上查看记录
+-----+---------------+-----+--------+
| TID | Name ? ? ? ? ?| Age | Gender |
+-----+---------------+-----+--------+
| ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|
| ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|
| ? 3 | Miejue Shitai | ?77 | F ? ? ?|
| ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|
| ? 5 | mage ? ? ? ? ?| ?30 | M ? ? ?|
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
?
3.1.7 通过通用日志确认实现读写分离
在mysql中查看通用日志
show variables like ‘general_log‘; #查看日志是否开启
set global general_log=on; #开启日志功能
show variables like ‘general_log_file‘; #查看日志文件保存位置
set global general_log_file=‘tmp/general.log‘; #设置日志文件保存位置
?
[root@c8-client ~]# mysql -uroot -pmagedu -h 192.168.100.8 -P8066
MySQL [TESTDB]> set global general_log=on;
Query OK, 0 rows affected (0.006 sec)
?
MySQL [TESTDB]> show variables like ‘general_log‘; #这里看到的off没有更改，是因为读取的是从节点28上的记录信息，因为设置变量不会产生二进制日志，所以主节点的信息不会复制到从节点
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log ? | OFF ? |
+---------------+-------+
1 row in set (0.003 sec)
?
#主节点（18）上查看到日志已经开启
MariaDB [mycat]> show variables like ‘general_log‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log ? | ON ? ?|
+---------------+-------+
1 row in set (0.002 sec)
#在从节点28上也开启日志
MySQL [TESTDB]> set global general_log=on;
?
?
?
#开启日志后，会生成/var/lib/mysql/hostname.log文件，可以跟踪该文件查看，如在c8-client（9）上发生读取操作时，只发生在client28上，而master18上无反应
MySQL [TESTDB]> select * from teachers;
?
[root@c8-master ~]# tail -f /var/lib/mysql/c8-master.log
?
[root@c8-slave ~]# tail -f /var/lib/mysql/c8-slave.log
210627 22:12:18 ? ? 89 Query ? ?SET names utf8mb4;select * from teachers
?
?
?
?
3.1.8 停止从节点，MyCAT自动调度读请求至主节点
#从节点（28）停掉服务
[root@c8-slave ~]# systemctl stop mariadb
#客户端访问数据时，提示错误
[root@c8-client ~]# mysql -uroot -pmagedu -h192.168.100.8 -P8066
MySQL [TESTDB]> select * from teachers;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
#会连接到主机点（18）上，读取
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| c8-master ?|
+------------+
1 row in set (0.002 sec)
?
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| ? ? ? ? ?18 |
+-------------+
1 row in set (0.002 sec)
?
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name ? ? ? ? ?| Age | Gender |
+-----+---------------+-----+--------+
| ? 1 | Song Jiang ? ?| ?45 | M ? ? ?|
| ? 2 | Zhang Sanfeng | ?94 | M ? ? ?|
| ? 3 | Miejue Shitai | ?77 | F ? ? ?|
| ? 4 | Lin Chaoying ?| ?93 | F ? ? ?|
| ? 5 | mage ? ? ? ? ?| ?30 | M ? ? ?|
+-----+---------------+-----+--------+
5 rows in set (0.002 sec)
?
3.1.9 停止主节点，MyCAT不会自动调度读请求至从节点
[root@c8-master ~]# systemctl stop mariadb
?
#client客户端提示错误
MySQL [TESTDB]> insert teachers values(6,‘wang‘,33,‘M‘);
ERROR 1184 (HY000): java.net.ConnectException: Connection refused