MySQL-8.0.20

版本: 8.0.20 操作: Centos 7 Linux

未介绍针对数据库的详细操作，如有需求请前往

第一章 MySQL的介绍及安装

1、介绍

1.1 数据库管理系统(DBMS)
RDBMS : Oracle SQLServer DB2 MySQL
NoSQL : Redis MongoDB ES
NewSQL: PolarDB OceanBase TDB TiDB

1.2 排名
db-engines.com/ranking

1.3 MySQL 介绍

1.3.1 如何获取软件
www.mysql.com

1.3.2 企业版本选择.6
5.7
8.0

GA 6-12月版本,大约20个版本左右,可以测试使用了. 从边缘业务开始,做好前期的测试

1.3.3 分支
Oracle MySQL
Percona
MariaDB
RDS
TDB

2、MySQL的安装配置

2.1 上传并解压软件
[root@db01 local]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.ta

**2.2 软连接 **

[root@db01 local]# mv mysql-8.0.20-linux-glibc2.12-x86_64 mysq

**2.3 环境变量 **

[root@db01 bin]# vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

生效配置

source /etc/profile

[root@db01 bin]# mysql -V
mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

**2.4 建用户 **

[root@db01 bin]# useradd mysql

2.5 创建所需目录
[root@db01 ~]# mkdir -p /data/3306/data
[root@db01 ~]# chown -R mysql.mysql /data

2.6 数据初始化
[root@db01 ~]# mv /etc/my.cnf /tmp
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

2.7 配置文件

[root@db01 ~]# vim /etc/my.cnf

[mysql]
socket=/tmp/mysql.sock
[mysqld]
server_id=10
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock

**2.8 准备启动脚本 **
[root@db01 ~]# cd /usr/local/mysql/support-files/
[root@db01 support-files]# \cp mysql.server /etc/init.d/mysqld
[root@db01 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
. SUCCESS!

第二章 体系结构及基础管理

1、C/S

1.1 Unix Socket
1.2 网络socket(TCP/IP)
1.3 连接方法
自带的客户端程序: mysql mysqldump
开发工具: navicat sqlyog workbench
程序连接: pymysql

2、体系结构

2.1 server
2.1.1 连接层
协议
验证
线程

2.1.2 SQL层
语法,语义,权限检查
解析,生成解析树
优化,生成执行计划
执行,得出执行结果.

2.2 engine
从磁盘中段 , 区(簇,64个PAGE,默认1M) ,页(16KB)

3、用户管理

3.1 定义
oldguo@'白名单'

白名单:
%
10.0.0.%
10.0.0.5%
10.0.0.1
10.0.0.0/255.255.254.0
localhost

**3.2 用户管理 **
注意:

1. 8.0之后,必须先建立用户再授权,不能通过grant创建用户和修改密码.
 2. 8.0之后,加密插件从mysql_native_password 改为了 caching_sha2_password 

(1) 查询
mysql> select user,host,authentication_string ,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+

(2) 创建用户
mysql> create user oldguo@'10.0.0.%' identified with mysql_native_password by '123';
mysql> create user oldboy@'10.0.0.%' identified by '123';

(3) 修改

mysql> alter user root@'localhost' identified with mysql_native_password by '123';
mysql> alter user oldguo@'10.0.0.%' account lock;
mysql> alter user oldguo@'10.0.0.%' account unlock;

(4) 删除
drop user oldguo@'10.0.0.%';

4、权限管理

**4.1 权限的定义 **
mysql> show privileges;

8.0 加入了新特性role . approle
all
Grant option

**4.2 授权和回收 **
grant 权限 on 权限范围 to 用户

权限范围:
.
app.*
app.t1
mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on . to root@'10.0.0.%' with grant option;

revoke
mysql> revoke drop on . from root@'10.0.0.%';
mysql> show grants for root@'10.0.0.%';

4.3 授权表
例如: users db tables_priv

**4.4 本地管理员密码忘记 **

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[root@db01 ~]# mysql
mysql> flush privileges ;
mysql> alter user root@'localhost' identified with mysql_native_password by '123';
[root@db01 ~]# /etc/init.d/mysqld restart

5、数据库的启动和关闭

5.1 mysqld 程序启动
/usr/local/mysql/bin/mysqld &
直接启动数据库服务,并且打印所有日志到屏幕上.
一般是在需要启动调试时使用.

5.2 mysqld_safe 程序启动
mysqld_safe ----> mysqld
一般是在需要启动调试时使用.例如,可以在启动时,临时加参数,--skip-grant-tables --skip-networking

**5.3 sys-v ---> /etc/init.d/ **

[root@db01 ~]# service mysqld stop
[root@db01 ~]# service mysqld strt
[root@db01 ~]# service mysqld restart

**5.4 systemd **
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# systemctl restart mysqld

**5.5 关闭 **
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

6、配置文件

**6.1 默认读取位置 **
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

6.2 改变默认配置文件位置
mysqld
mysqld_safe
--defaults-file=/opt/aa.txt

6.3 配置文件结构

[标签] ----> 作用: 表示下面的配置影响了什么程序
配置

类型:
客户端标签: 影响本地登录 ---> [mysql] [mysqldump]
服务端标签: 影响数据库启动 ---> [mysqld] [mysqld_safe]

[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id=10
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock

7、连接管理

**7.1 本地客户端工具连接 **
(1) Socket文件方式
前提: 必须提前创建白名单为localhost用户,并授权.例如:oldguo@'localhost'
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock

(2) tcpip方式 :
前提: 必须提前创建白名单为远程登录用户,并授权.例如:oldboy@'10.0.0.%'
[root@db01 ~]# mysql -uoldboy -p123 -h10.0.0.51 -P3306

**7.2 开发工具 **

第三章节 SQL 规范

1. 去掉不必要的括号
   如： ((a AND b) AND c OR (((a AND b) AND (c AND d))))
   修改成 (a AND b AND c) OR (a AND b AND c AND d)

2. 去掉重叠条件
   如： (a<b AND b=c) AND a=5
   修改成 b>5 AND b=c AND a=5
   如： (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
   修改成 B=5 OR B=6

3. 避免使用not in、not exists 、<>、like %%

4. 多表连接，小表驱动大表

5. 减少临时表应用，优化order by 、group by、uninon、distinct、join等

6. 减少语句查询范围，精确查询条件

7. 多条件，符合联合索引最左原则

8. 查询条件减少使用函数、拼接字符等条件、条件隐式转换

9. union all 替代 union

10.减少having子句使用

11.如非必须不使用 for update语句

12.update和delete，开启安全更新参数
mysql> set global sql_safe_updates=1;

13.减少inset ... select语句应用
如果是归档表,pt-archiver

14.使用load 替代insert录入大数据

导入大量数据时，可以禁用索引、增大缓冲区(innodb_buffer_pool_size)、增大redo(innodb_log_file_size)文件和 redo buffer()、关闭autocommit、RC级别可以提高效率.

16.优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
limit 5000000,10

17. DDL执行前要审核
    8.0之前: 如果紧急,pt-osc,gh-ost

18. 多表连接语句执行前要看执行计划

字段定义规范

1. 每个表建议在30个字段以内。
2. 需要存储emoji字符的，则选择utf8mb4字符集。
3. 机密数据，加密后存储。
4. 整型数据，默认加上UNSIGNED。
5. 存储IPV4地址建议用INT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。
6. 如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。
7. 选择尽可能小的数据类型，用于节省磁盘和内存空间。
8. 存储浮点数，可以放大倍数存储。 3.14
9. 每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。
10. 每个列使用not null，或增加默认值。
11. 表名和业务有关,不使用大写字母
12. 列名要有意义,表名_列名
13. 表名不要数字开头,不要使用内置字符.
14. 每个列要有注释.

第四章 索引及执行计划

1、索引类型

Btree 索引
Hash 索引
FULLTEXT

2、Btree算法

b-tree、b+tree、b*tree：请看博主关于mysql的应用详解链接

3、MySQL 如何应用BTREE

3.1 聚簇(区)索引

IOT组织表: 索引组织表.聚簇索引组织表.数据行是按照聚簇索引顺序组织存储的.

前提:
1. 如果有主键,主键就是聚簇索引
2. 没有主键,选择第一个不为空的唯一键的列
3. 都没有,生成隐藏列

3.2 辅助索引

**3.3 关于索引自由化能力 **

AHI : 自适应Hash索引
索引的索引. latch 闩锁.
自动评估内存索引页访问频率,将热点内存页生成hash列表,快速锁定内存地址目的.

Change buffer(Insert buffer)
主要影响辅助索引的修改.
对于辅助索引的更新,不会立即进行,会将更新缓冲到change buffer区域中.

ICP 索引下推
(a,b,c)

MRR

3.4 索引优化

1. 非唯一索引按照“i_字段名称_字段名称[_字段名]”进行命名。

2. 唯一索引按照“u_字段名称_字段名称[_字段名]”进行命名。

3. 索引名称使用小写。

4. 索引中的字段数不超过5个。

5. 唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。

6. 没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。

7. 唯一键不和主键重复。

8. 索引选择度高的列作为联合索引最左条件

9. ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。 where a order by b

10. 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。查询性能问题无法解决的，应从产品设计上进行重构。

11. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

12. UPDATE、DELETE语句需要根据WHERE条件添加索引。

13. 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。

14. 下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。
    CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    url VARCHAR(255) NOT NULL DEFAULT 0,
    url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
    index idx_url(url_crc32));

15. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

16. 合理利用覆盖索引，减少回表。

17. 减少冗余索引和使用率较低的索引

第四章 索引及执行计划-下

1、作用

优化器选择完的执行计划.可以通过分析执行计划,评估查询语句的执行效率.

2、获取执行计划

mysql> desc select *from city ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3、分析执行计划

table
type
possible_keys
key
key_len
rows
Extra

4、type

ALL 全表扫描
index 全索引扫描

range 索引范围扫描

< >= <= like or in

**说明: **
in or 有些情境下可以改写为union all ,主要看一下重复值多少,如果太多不建议调整.

select * from city where countrycode in ('CHN','USA');
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='world'
--query="select * from city where countrycode in ('CHN','USA')" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='world'
--query="select * from city where countrycode='CHN' union all select * from city where countrycode='USA'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

ref 辅助索引等值查询

eq_ref 多表连接中的被驱动表的关联条件是主键或唯一键

const(system) 主键或唯一键等值查询

5、key_len

判断联合索引覆盖长度. 索引最大预估字节长度.
a,b,c

a keylen 63
b keylen 21
c keylen 15

             not null    无not null 

tinyint 1 1 1+1
int 4 4 4+1

utf8 not null 无 not null

char(10) 310 310+1

varchar(10) 310+2 310+2+1

utf8mb4 not null 无 not null

char(10) 410 410+1

varchar(10) 410+2 410+2+1

6、联合索引应用原则:

i_a_b_c(a,b,c)

1. 最左原则:
   建索引,最左列唯一值多的
   查询条件必须包含最左列

2. 完全覆盖
   where a= and b= and c=
   where a= and c= and b=
   where a and b order c

3. 部分覆盖
   where a= and c=
   where a= and c=
   where a= and b < > and c=

4. 不覆盖
   where b= and c=

7、Extra

using filesort 额外排序 : order by group by distinct union
using temp 使用了临时表 :

第五章节 存储引擎

1、介绍

mysql> show engines;
FEDERATED
MEMORY
InnoDB
PERFORMANCE_SCHEMA
MyISAM
MRG_MYISAM
BLACKHOLE
CSV
ARCHIVE

2、InnoDB 核心特性

​ 事务
​ 行锁
​ MVCC
​ 多缓冲区
​ 外键
​ 热备
​ 自动故障恢复(Crash Recovery)
​ DWB(double write buffer)

3、事务(Transaction "交易")

3.1 ACID特性
原子性 :
一致性 :
隔离性 :
持久性 :

**3.2 生命周期管理 **
(1) 标准事务控制语句
begin;/start transaction;
commit;
rollback;

(2) 自动提交机制
没有使用显示启动事务时
begin;/start transaction;
对每一个DML语句,都会自动提交

(3) 隐式提交

**3.3 事务的隔离级别 **
RU : 脏读 不可重复读 幻读
RC : 不可重复读 幻读
RR : 可以配合GAP Next lock 解决幻读
SE :

4、ACID 如何保证的?

4.1 redo 日志
WAL : 日志先行.

redo log file
redo log buffer

主要功能是实现ACID中的D的特性,同时还实现了AC.

**4.2 undo 日志 **

存储数据行修改的前镜像.
主要功能实现ACID中的A特性.CI的特性有一定影响

**4.3 I特性 **
隔离级别 : 读
锁 : 写
MVCC : 利用多个版本readview ---> undo

4.4 C 特性
CR : crash recovery ---> redo undo
DWB: double write buffer

第六章节: 日志管理

1、general_log

mysql> show variables like '%general_log%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/db01.log |

**1.1 作用: **
记录所有操作日志.

**1.2 配置 **
mysql> set global general_log=0;

2、log_error

mysql>
mysql> show variables like '%log_error%';

log_error | ./db01.err
log_error_verbosity | 2

2.1 作用
记录数据运行期间,状态 警告 报错

2.2 配置
mysql> set global log_error_verbosity=3;

**2.3 查看 **

Z 0 [ERROR] [MY-000067] [Server] unknown variable 'defualts-file=/opt/aa.txt'.
2020-10-09T03:35:59.905745Z 0 [ERROR] [MY-010119] [Server] Aborting

3、慢日志slow_log

**3.1 作用: **
记录数据库中运行较慢的语句

3.2 配置
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log_file;
+-------------------------------+
| @@slow_query_log_file |
+-------------------------------+
| /data/3306/data/db01-slow.log |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.01 sec)

mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> set glo
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

**3.3 分析 **
[root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log

4、binlog 二进制日志

4.1 作用
记录MySQL发生过的修改类的操作日志.以二进制事件的方式记录到日志文件中.
可以用来做数据恢复,可以用来做主从复制.

**4.2 配置 **
8.0版本默认开启,在数据目录下存储.

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

mysql> select @@log_bin_basename;
+------------------------+
| @@log_bin_basename |
+------------------------+
| /data/3306/data/binlog |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |

**二进制日志格式? **
row : 记录航变化,记录精准,日志量比较大.

update t1 set name='a' where id<1000; 记录999行变化日志.

statement : SQL 数据操作时,如果有函数操作,例如随机数函数.
update t1 set name='a' where id<1000; 记录SQL语句本身.

miexd: 混合模式.

4.3 管理操作

mysql> show binary logs;
mysql> show master status ;
mysql> show binlog events in 'binlog.000014';
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000014 | 4 | Format_desc | 10 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000014 | 125 | Previous_gtids | 10 | 156 | |
| binlog.000014 | 156 | Anonymous_Gtid | 10 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 233 | Query | 10 | 347 | create database oldguo /* xid=62898 */ |
+---------------+-----+----------------+-----------+-------------+---------------------------------------

[root@db01 data]# mysqlbinlog --start-position=233 --stop-position=347 /data/3306/data/binlog.000014 >/tmp/bin.sql

mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;
mysql> flush logs;

mysql> select @@binlog_expire_logs_seconds;
mysql> PURGE BINARY LOGS TO 'binlog.000010';
mysql> reset master;

4.4 binlog2sql 闪回DML操作

yum install python3
pip3 install -r requirements.txt
pip3 show pymysql
pip3 install --upgrade PyMySQL

解析日志事件SQL

a. 单独过滤某张表的binlog

[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d binlog -t t1 --start-file='binlog.000001'
INSERT INTO test1.t1(id) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid
INSERT INTO test1.t1(id) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid
INSERT INTO test1.t1(id) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid
INSERT INTO test1.t1(id) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid

b. 单独过滤某些类型的binlog

[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete --start-file='mysql-bin.000003'
DELETE FROM test1.t1 WHERE id=3 LIMIT 1; #start 5172 end 5416 time 2020-09-18 09:17:48 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=update --start-file='mysql-bin.000003'
UPDATE test1.t1 SET id=10 WHERE id=1 LIMIT 1; #start 4882 end 5141 time 2020-09-18 09:17:35 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=insert --start-file='mysql-bin.000003'
INSERT INTO test1.t1(id) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid
INSERT INTO test1.t1(id) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid
INSERT INTO test1.t1(id) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid
INSERT INTO test1.t1(id) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid
[root@db01 binlog2sql-master]#

c. 生成指定事件回滚语句
应用场景： 3000万数据，误删10行数据，怎么恢复？

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql

第七章 备份恢复

1、mysqldump

1.1 介绍
逻辑备份工具.备份的是SQL语句(Create database create table insert into)
比较适合于: 数据量小,大表比较少情景下使用.

1.2 使用

(1) 连接参数
-u -p -h -P -S ...
(2) -A 全备参数
mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/tmp/full.sql

(3) -B 单库\多库备份
mysqldump -uroot -p123 -S /tmp/mysql.sock -B oldguo test >/tmp/db.sql

(4) 单表多表备份

mysqldump -uroot -p123 -S /tmp/mysql.sock world city country >/tmp/tb.sql

(5) --master-data=2

1. 自动记录备份时binlog name pos 
2. FTWRL(flush table with read lock) 

(6) --single-transaction
基于一致性快照备份InnoDB的数据行.

(7) -R -E --triggers 特殊对象备份

source /tmp/full.sql

2、Percona Xtrabackup (PXB)

2.1 介绍
物理备份工具.拷贝表空间数据文件. 在备份同时,产生的新的数据变化通过redo的方式备份走.
恢复时,需要先将备份进行prepare(CR--->redo undo),然后进行恢复.
自带增量备份功能.

2.2 安装
8.0.18以后版本,需要使用 pxb 8.0.13 版本.

yum install percona-xtrabackup*.rpm

注意：
对于MySQL 8.0.20版本，需要使用PXB 8.0.12+以上版本，MysQL：8.0.11 ~ 8.0.19  使用PXB 8.0正式版本。

MySQL 8.0 之前（5.6，5.7 ）的版本： PXB 2.4

2.3 全量备份

1.全量备份
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --target-dir=/data/backup/full


2.数据恢复：

2.0 搞破坏 

[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/3306/data/*
[root@db01 ~]# \rm -rf /data/3306/logs/*
[root@db01 ~]# \rm -rf /data/3306/binlog/*


2.1 准备：
xtrabackup   --prepare --target-dir=/data/backup/full

说明： 模拟CR过程，将redo前滚，undo回滚，让备份数据是一致状态


2.2 拷回数据：
xtrabackup  --copy-back --target-dir=/data/backup/full

2.3 修改权限并启动数据库
[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

2.4 增量备份

**介绍 **

增量备份，是基于上一次备份LSN变化过的数据页进行备份，在备份同时产生的新变更，会将redo备份。
第一次增量是依赖于全备的。将来的恢复也要合并到全备中，再进行统一恢复。

增量备份演练

全量备份的目录为： mkdir  -p /data/backup/full
增量备份的目录为： mkdir  -p /data/backup/inc

1.备份操作：
1.1.全量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/data/backup/full

mysql> create database pxb;
mysql> use pxb
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;


增量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc  --incremental-basedir=/data/backup/full



# 模拟损坏  
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*


恢复操作：
2.1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
2.2 准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full  --incremental-dir=/data/backup/inc


全备份准备：
# xtrabackup --prepare --target-dir=/data/backup/full


拷回数据：
xtrabackup    --copy-back --target-dir=/data/backup/full

修改数据目录的权限和属性：
chown -R mysql:mysql /data/*



3. clone plugin

3、Clone Plugin介绍

8.0.17版本出现的功能.

本地克隆：
启动克隆操作的MySQL服务器实例中的数据，克隆到同服务器或同节点上的一个目录里

远程克隆：
默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。(可选)

原理

PAGE COPY

这里有两个动作
开启redo archiving功能，从当前点开始存储新增的redo log，这样从当前点开始所有的增量修改都不会丢失。同时上一步在page track的page被发送到目标端。确保当前点之前所做的变更一定发送到目标端。
关于redo archiving，实际上这是官方早就存在的功能，主要用于官方的企业级备份工具，但这里clone利用了该特性来维持增量修改产生的redo。
在开始前会做一次checkpoint， 开启一个后台线程log_archiver_thread()来做日志归档。当有新的写入时(notify_about_advanced_write_lsn)也会通知他去archive。当arch_log_sys处于活跃状态时，他会控制日志写入以避免未归档的日志被覆盖(log_writer_wait_on_archiver), 注意如果log_writer等待时间过长的话， archive任务会被中断掉.

Redo Copy

停止Redo Archiving", 所有归档的日志被发送到目标端，这些日志包含了从page copy阶段开始到现在的所有日志，另外可能还需要记下当前的复制点，例如最后一个事务提交时的binlog位点或者gtid信息，在系统页中可以找到。

Done

目标端重启实例，通过crash recovery将redo log应用上去。

限制

*官方文档列出的一些限制：
The clone plugin is subject to these limitations:

• DDL, is not permitted during a cloning operation. This limitation should be considered when selecting data sources. A workaround is to use dedicated donor instances, which can accommodate DDL operations being blocked while data is cloned. Concurrent DML is permitted.
• An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version. For example, you cannot clone between MySQL 5.7 and MySQL 8.0. The clone plugin is only supported in MySQL 8.0.17 and higher.
• Only a single MySQL instance can be cloned at a time. Cloning multiple MySQL instances in a single cloning operation is not supported.
• The X Protocol port specified byis not supported for remote cloning operations
• The clone plugin does not support cloning of MySQL server configurations.
• The clone plugin does not support cloning of binary logs.
• The clone plugin only clones data stored in InnoDB. Other storage engine data is not cloned.
• Connecting to the donor MySQL server instance through MySQL Router is not supported.
• Local cloning operations do not support cloning of general tablespaces that were created with an absolute path. A cloned tablespace file with the same path as the source tablespace file would cause a conflict.

4、应用

4.1 本地
4.1.1 加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

4.1.2 创建克隆专用用户
CREATE USER clone_user@'%' IDENTIFIED by 'password';
GRANT BACKUP_ADMIN ON . TO 'clone_user';

BACKUP_ADMIN是MySQL8.0 才有的备份锁的权限

4.1.3 本地克隆
[root@db01 3306]# mkdir -p /data/test/
[root@db01 3306]# chown -R mysql.mysql /data/

mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

观测状态

db01 [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE | STATE | END_TIME |
+-----------+-------------+----------------------------+
| DROP DATA | Completed | 2020-04-20 21:13:19.264003 |
| FILE COPY | Completed | 2020-04-20 21:13:20.025444 |
| PAGE COPY | Completed | 2020-04-20 21:13:20.028552 |
| REDO COPY | Completed | 2020-04-20 21:13:20.030042 |
| FILE SYNC | Completed | 2020-04-20 21:13:20.439444 |
| RESTART | Not Started | NULL |
| RECOVERY | Not Started | NULL |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)

日志观测：
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

4.1.4 启动新实例
[root@db01 clonedir]# mysqld_safe --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

4.2 远程clone

**4.2.1 作用 **
a. 远程的快速热迁移。
b. 快速创建复制关系

4.2.2 环境准备
a. 克隆1台虚拟机db02
b. 捐赠者： db01 接受者： db02
c. 接收者(db02)，搭建空的数据库实例。
pkill mysqld
\rm -rf /data/3306/data/*
\rm -rf /data/3306/binlog/*
\rm -rf /data/3306/logs/*
mysqld --initialize-insecure --user=mysql --basedir=/user/local/mysql --datadir=/data/3306/data
/etc/init.d/mysqld restart

4.2.3 加载各个节点插件

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

4.2.4 创建远程clone用户

捐赠者授权（db01）

create user test_jz@'%' identified by '123';
grant backup_admin on . to test_jz@'%';

接受者授权(db02)

create user test_js@'%' identified by '123';
grant clone_admin on . to test_js@'%';

4.2.5 远程clone(db02)

开始克隆

mysql -e "SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';"
mysql -utest_js -p123 -h10.0.0.52 -P3306 -e "CLONE INSTANCE FROM test_jz@'10.0.0.51':3306 IDENTIFIED BY '123';"