1、mysql常见的存储引擎以及特点。
常见存储引擎有MyISAM、Memory、InnoDB、Archive、NDB，我这里介绍MyISAM和InnoDB
1.1 MyISAM 引擎
1.1.1 MyISAM 引擎特点
- 不支持事务
- 表级锁
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据引擎
1.1.2 MyISAM 引擎使用场景
- 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
1.1.3 MyISAM 引擎文件
- table_name.frm 表格式
- table_name.myd 数据文件
- table_name.myi 索引文件
1.2 InnoDB 引擎
1.2.1 InnoDB 引擎特点
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据引擎
1.2.2 InnoDB 引擎文件
- 所有InnoDB表的数据和索引放置在同一个表空间中
数据文件：ibdata1，ibdata2，存放在datadir定义的目录下
表格式定义：table_name.frm，存放在datadir定义的每个数据库对应的目录下
- 每个表单独使用一个表空间存储表的数据和索引
两类文件放在对应每个数据库独立目录中
数据文件（存储数据和索引）：table_name.idb
表格式定义：table_name.frm
启动独立表空间 innodb_file_per_table=ON（mariaDB 5.5以后是默认值）
2、MySQL各类日志。
mysql支持丰富的日志类型，如下：
- 事务日志
- 错误日志
- 通用日志
- 慢查询日志
- 二进制日志
- 中继日志
2.1 事务日志：transaction log
- redo log：实现WAL（Write Ahead Log），数据更新前先记录redo log
- undo log：保存与执行的操作相反的操作，用于实现rollback
2.1.1 InnoDB事务日志相关配置
show variables like '%innodb_log%';
innodb_log_file_size 50331648
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
2.1.2 事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
#建议设置为1，双一标准之一
1 这是默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
高并发业务行业最佳实践，是使用第三种（=2）
1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据
说明：
- 设置为1，同时sync_binlog = 1 表示最高级别的容错
- innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6 后废弃）
2.2 错误日志
- mysql启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
2.2.1 错误日志文件路径
show global variables like 'log_error';
范例：
mysql> show global variables like 'log_error';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| log_error | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.14 sec)
2.3 通用日志
记录了对数据库的通用操作，包括：错误的SQL语句
2.3.1 通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
范例：启用通用日志并记录至文件中
## 默认没有开启通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
## 启动
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
## 默认通用日志存放在文件中
mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
## 通用日志存放的文件路径
mysql> select @@general_log_file;
+-----------------------------+
| @@general_log_file |
+-----------------------------+
| /var/lib/mysql/centos-8.log |
+-----------------------------+
1 row in set (0.00 sec)
2.4 慢查询日志
记录执行查询时长超出指定时长的操作
2.4.1 慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除
2.4.2 慢查询分析工具mysqldumpslow
[root@centos-8 ~]$mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
## 范例
[root@centos-8 ~]$mysqldumpslow -s -c -t 2 /var/lib/mysql/centos-8.log
2.5 二进制日志（备份）
功能：通过“重放”日志文件中的事件来生成数据副本 注意：建议二进制日之前和数据文件分开存放
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖与存储引擎类型
2.5.1 二进制日志记录的三种格式
- 基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
- 基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
- 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上 ）
2.5.2 格式配置
# mysql 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
2.5.3 二进制日志文件的构成
有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表
2.5.4 二进制日志相关的服务器变量
sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除
2.5.5 二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
show {binary|master} logs
查看使用中的二进制日志文件
show master status
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
范例
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3
2.5.6 mysqlbinlog：二进制的客户端命令工具，支持离线查看二进制日志
命令格式
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv
范例
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
2.5.6 二进制日志事件的格式
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
2.5.7 清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
范例
PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
2.5.8 删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
2.5.9 切换日志
flush logs;
3、主从复制及主主复制的实现
3.1 主从复制原理
一次完整主从复制原理描述：
1.change master to 时，主库的信息IP地址、端口、用户、密码、二进制日志、位置等ip port user password binlog position写入到master.info进行记录
2.start slave 时，从库会启动IO线程和SQL线程
3.IO_T，读取master.info信息，获取主库信息连接主库
4.主库会生成一个准备binlog DUMP线程，来响应从库
5.IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6.DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7.IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，再把TCP缓存里的日志回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
11.relay-log会有自动清理已经应用过的relaylog的功能。
细节： 1.主库一旦有新的日志生成，会通过dump_T线程发个新信号给IO线程，IO线程再请求
3.2 新建主从复制
#主节点
[root@centos-8 ~]$dnf install mysql-server -y
[root@centos-8 ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
[root@centos-8 ~]$systemctl restart mysqld
[root@centos-8 ~]$mysql
#查看二进制文件和位置
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| centos-8-bin.000001 | 156 | No |
+---------------------+-----------+-----------+
1 row in set (0.00 sec)
#创建复制用户
mysql> create user 'repluser'@'10.0.0.%';
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%';
#从节点
[root@centos-8 ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
[root@centos-8 ~]$systemctl restart mysqld
[root@centos-8 ~]$mysql
mysql> help change master to;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.128',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos-8-bin.000001',
-> MASTER_LOG_POS=156;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos-8-bin.000001
Read_Master_Log_Pos: 620
Relay_Log_File: centos-8-relay-bin.000002
Relay_Log_Pos: 791
Relay_Master_Log_File: centos-8-bin.000001
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: 620
Relay_Log_Space: 1003
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: 128
Master_UUID: bc5112ad-e764-11eb-9e02-000c291234f5
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
3.3 主服务器非新建时，主服务器运行一段时间后，新增从节点服务器
#主服务器
[root@centos-8 backup]$mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql
[root@centos-8 backup]$scp /backup/fullbackup_2021-07-18_09\:36\:28.sql 10.0.0.130:/data/
#从服务器
[root@centos-8 ~]$dnf install mysql-server -y
[root@centos-8 ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=130
read-only
[root@centos-8 ~]$systemctl restart mysqld
[root@centos-8 ~]$grep '^CHANGE MASTER' /data/fullbackup_2021-07-18_09\:36\:28.sql
[root@centos-8 ~]$mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.128',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos-8-bin.000002',
-> MASTER_LOG_POS=156;
#导入备份数据库
[root@centos-8 ~]$mysql </data/fullbackup_2021-07-18_09\:36\:28.sql
[root@centos-8 ~]$mysql
mysql> start slave;
#查看从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos-8-bin.000002
Read_Master_Log_Pos: 332
Relay_Log_File: centos-8-relay-bin.000002
Relay_Log_Pos: 503
Relay_Master_Log_File: centos-8-bin.000002
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: 332
Relay_Log_Space: 715
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: 128
Master_UUID: bc5112ad-e764-11eb-9e02-000c291234f5
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
3.4 主主复制
主主复制：两个节点，都可以更新数据，并且互为主从 容易产生问题：数据不一致；因此慎用 考虑要点：自动增长id 配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤： (1) 各节点使用一个惟一server_id (2) 都启动binary log和relay log (3) 创建拥有复制权限的用户账号 (4) 定义自动增长id字段的数值范围各为奇偶 (5) 均把对方指定为主节点，并启动复制线程 范例：实现两个节点的主主复制模型
#在第一个master节点上实现
[root@centos-8 ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=133
log-bin
auto_increment_offset=1
auto_increment_increment=2
[root@centos-8 ~]$systemctl restart mysqld
[root@centos-8 ~]$mysql
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| centos-8-bin.000001 | 179 | No |
| centos-8-bin.000002 | 156 | No |
+---------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> create user 'repluser'@'10.0.0.%' identified by 'magedu';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
#在第二个master节点上实现
[root@centos-8 ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=134
log-bin
auto_increment_offset=2
auto_increment_increment=2
[root@centos-8 ~]$systemctl restart mysqld
[root@centos-8 ~]$mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.133',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos-8-bin.000002',
-> MASTER_LOG_POS=156;
mysql> start slave;
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| centos-8-bin.000001 | 179 | No |
| centos-8-bin.000002 | 705 | No |
+---------------------+-----------+-----------+
2 rows in set (0.00 sec)
#在第一个master节点上实现
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.134',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos-8-bin.000002',
-> MASTER_LOG_POS=705;
mysql> start slave
#两个节点分别插入数据
#在第一个节点上执行
mysql> create database db1;
#在第二个节点上执行
mysql> create database db2;
4、xtrabackup实现全量+增量+binlog恢复库
xtrabackup工具备份和还原，需要三步实现 1.备份：对数据库做完全或增量备份 2.预准备：还原前，先对备份的数据，整理至一个临时目录 3.还原：将整理好的数据，复制回数据库目录中
4.1 备份
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
4.2 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，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
4.3 还原
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之前完成
4.4 实战案例
第一步：备份过程（10.0.0.129）
#安装xtrabackup
[root@centos-8 ~]$yum install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm -y
#完全备份
[root@centos-8 ~]$mkdir /backup
#第一次修改数据
mysql> create database a;
#第一次增量备份
[root@centos-8 ~]$xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#第二次修改数据
mysql> create database b;
#第二次增量备份
[root@centos-8 ~]$xtrabackup -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#拷贝到目标主机
[root@centos-8 ~]$scp -r /backup/* 10.0.0.128:/backup/
第二步：还原过程（10.0.0.128）
[root@centos-8 ~]$yum install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm -y
[root@centos-8 ~]$xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#合并第一次增量备份到完全备份
[root@centos-8 ~]$xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#合并第最后一次增量到最后一次减一的次数合并，不需要加--apply-log-only
[root@centos-8 ~]$xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
#复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos-8 ~]$xtrabackup --copy-back --target-dir=/backup/base
#还原属性
[root@centos-8 ~]$chown -R mysql:mysql /var/lib/mysql
#启动服务
[root@centos-8 ~]$systemctl start mysqld
#再次切到主服务器（10.0.0.129）
[root@centos-8 ~]$mysql
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1066605 | No |
| binlog.000002 | 376 | No |
| binlog.000003 | 376 | No |
| binlog.000004 | 156 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
[root@centos-8 ~]$mysqlbinlog --start-position=156 /var/lib/mysql/binlog.000004 >/backup/cre.sql
[root@centos-8 ~]$scp /backup/cre.sql 10.0.0.128:/backup/
#切到还原主机（10.0.0.128）
[root@centos-8 ~]$mysql
mysql> source /backup/cre.sql
5、MyCAT中间件服务实现读写分离
5.1 MyCAT关键特性
支持SQL92标准
遵守MySQL 原生协议，跨语言，跨平台，跨数据库的通用中间件代理
基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群
支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
基于Nio实现，有效管理线程，高并发问题
支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页
支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join
支持通过全局表，ER关系的分片策略，实现了高效的多表join查询
支持多租户方案
支持分布式事务（弱xa）
支持全局序列号，解决分布式下的主键生成问题
分片规则丰富，插件化开发，易于扩展
强大的web，命令行监控
支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉
支持密码加密
支持服务降级
支持IP白名单
支持SQL黑名单、sql注入***拦截
支持分表（1.6）
集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）
MyCAT官网：http://www.mycat.org.cn/
5.2 MyCAT中间件可以简单概括
* 一个彻底开源的，面向企业应用开发的大数据库集群
* 支持事务、ACID、可以替代MySQL的加强版数据库
* 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
* 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Serve
* 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
* 一个新颖的数据库中间件产品
5.3 MyCAT的高可用性
在生产环境中, Mycat节点最好使用双节点, 即双机热备环境, 防止Mycat这一层出现单点故障. 可以使用的高可用集群方式有:
- Keepalived+Mycat+Mysql
- Keepalived+LVS+Mycat+Mysql
- Keepalived+Haproxy+Mycat+Mysql
5.4 MyCAT 安装
MyCAT是java开发，基于java环境运行 下载安装JDK
[root@mycat ~]#yum install java -y
#确认安装
[root@mycat ~]#java -version
openjdk version "1.8.0_265"
OpenJDK Runtime Environment (build 1.8.0_265-b01)
OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)
下载安装MyCAT
[root@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@mycat ~]#mkdir /apps
[root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
[root@mycat ~]#ls /apps/mycat/
bin catlet conf lib logs version.txt
mycat安装目录结构：
bin mycat命令，启动、重启、停止等
catlet catlet为Mycat的一个扩展功能
conf Mycat 配置信息
lib Mycat引用的jar包，Mycat是java开发的
logs 日志文件，包括Mycat启动的日志和运行的日志
version.txt mycat版本说明
logs目录：
wrapper.log mycat启动日志
mycat.log mycat详细工作日志
mycat的配置文件都在conf目录里面，我这里只介绍几个比较常用的文件：
server.xml Mycat软件本身相关的配置文件，设置账号、参数等
schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制
rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
启动及连接
#配置环境变量
[root@mycat ~]#vim /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
[root@mycat ~]#source /etc/profile.d/mycat.sh
#启动
[root@mycat ~]#mycat start
#查看日志
[root@mycat ~]#tail /app/mycat/logs/wrapper.log
看到下列一行，表示成功
INFO | jvm 1 | 2021/07/19 22:02:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
#连接mycat：
[root@mycat ~]#mysql -uroot -p123456 -h 127.0.0.1 -P8066
5.5 MyCAT主要配置文件说明
server.xml
存放MyCAT本身相关的配置文件，比如：连接MyCAT的用户、密码、数据库名称等 server.xml文件中配置的参数解释说明：
user 用户配置节点
name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名
password 客户端登录MyCAT的密码
schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2
privileges 配置用户针对表的增删改查的权限
readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false
- server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
- 逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！
- 这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！
schema.xml
此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。 schema.xml文件中配置的参数解释说明：
schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库
writeHost和readHost 标签
这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去
注意：Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost
5.6 案例：利用MyCAT实现MySQL读写分离
环境准备
#所有主机环境
[root@master ~]$cat /etc/centos-release
CentOS Linux release 8.3.2011
#服务器四台，只三台做配置
client 10.0.0.200
mycat-master 10.0.0.128 #内存建议2G以上，JAVA程序很吃内存
mysql-master 10.0.0.129 #MySQL 8.0 或者 Mariadb 10.3.7
mysql-slave 10.0.0.130 #MySQL 8.0 或者 Mariadb 10.3.7
#关闭SELinux和防火墙
systemctl disable --now firewalld
setenforce 0
时间同步
第一步，创建主从数据库
[root@master ~]$yum install mysql-server -y
或者
[root@master ~]$yum install mariadb-server -y
#修改master和slave上的配置文件
#master上的my.cnf
[root@master ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=129
log-bin
#slave上的my.cnf
[root@slave ~]$vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=130
#主从都重启
[root@master ~]$systemctl restart mysqld
[root@slave ~]$systemctl restart mysqld
#master上创建复制用户
[root@master ~]$mysql
mysql> create user repluser@'10.0.0.%' ;
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' ;
mysql> flush privileges;
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| centos-8-bin.000002 | 848 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#slave上执行
[root@slave ~]$mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.129',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='centos-8-bin.000003 ',
-> MASTER_LOG_POS=156;
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.129
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos-8-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: centos-8-relay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: centos-8-bin.000003
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: 156
Relay_Log_Space: 539
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: 129
Master_UUID: efa83c34-e764-11eb-87dd-000c29020682
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
第二步，在mysql代理服务器10.0.0.128安装mycat并启动
[root@mycat ~]# yum install java -y
#确认是否安装jdk
[root@mycat ~]# java -version
openjdk version "1.8.0_265"
OpenJDK Runtime Environment (build 1.8.0_265-b01)
OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)
#下载安装
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]# mkdir /apps
[root@mycat ~]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
#配置环境变量
[root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.sh
[root@mycat ~]# source /etc/profile.d/mycat.sh
#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@mycat ~]# mycat start
Starting Mycat-server...
#打开了多个端口，其中8066端口用于连接MyCAT
[root@mycat ~]# ss -lnt
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 100 127.0.0.1:25 0.0.0.0:*
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 *:37821 *:*
LISTEN 0 50 *:33983 *:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 100 *:8066 *:*
LISTEN 0 100 *:9066 *:*
#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@mycat ~]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2021/07/19 22:02:32 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/07/19 22:02:33 | Launching a JVM...
INFO | jvm 1 | 2021/07/19 22:02:34 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/07/19 22:02:34 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/07/19 22:02:34 |
INFO | jvm 1 | 2021/07/19 22:02:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
#用默认密码123456来连接mycat
[root@client ~]#mysql -uroot -p123456 -h10.0.0.126 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.001 sec)
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
第三步，在MyCAT上修改server.xml文件配置MyCAT的连接信息
[root@mycat ~]# vim /apps/mycat/conf/server.xml
#修改下图两处
#保存退出，重启
[root@mycat ~]# mycat restart
第四步，修改schema.xml实现读写分离策略
[root@mycat ~]# vim /apps/mycat/conf/schema.xml
#重启mycat
[root@mycat ~]# mycat restart
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为从库 注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
第五步，在后端主库服务器创建用户并对mycat授权
[root@master ~]$mysql
mysql> create database mycat;
mysql> create user 'root'@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to 'root'@'10.0.0.%' ;
第六步，连接测试mycat
[root@client conf]#mysql -uroot -pjiangmin -h10.0.0.126 -P3306
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.001 sec)
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> select @@server_id;
