1. MySQL 8.0 的日志管理

1.1 general_log

1.1.1 作用

1 2	一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供调试使用。

1.1.2查询及配置

show variables like 'general_log';       -- 查看日志是否开启
show variables like 'general_log_file';  -- 看看日志文件保存位置
show variables like 'log_output';        -- 看看日志输出类型 table或file
set global general_log=on;               -- 开启日志功能
set global general_log_file='/data/logs/general.log'; --设置日志文件保存位置
set global log_output='table';      --设置输出类型为 table
set global log_output='file';       --设置输出类型为 file
set global log_output='table,file';     --设置多位置保存

1.1.3 使用

1	检测mysqldump备份过程。

1.2 binlog *

1.2.1 作用

1
2
3

(1)备份恢复必须依赖二进制日志
(2)复制环境必须依赖二进制日志
8.0之后，默认自动开启。

1.2.2 配置

#注意：MySQL默认是没有开启二进制日志的。
基础参数查看:
开关:
[(none)]>select @@log_bin;                          

日志路径及名字：
[(none)]>select @@log_bin_basename;         

服务ID号:
[(none)]>select @@server_id;

二进制日志格式:
[(none)]>select @@binlog_format;

二进制日志只记录修改类的操作日志。对于DML（i u d ） 有以下格式
S : statement SQL ，日志量少，但可能不准确.
R ： ROW 记录行变化，日志量比较大，很准确.
M : Mixed 略.

###双一标准之二:
select @@innodb_flush_log_at_trx_commit=1/2
select @@sync_binlog=1

SBR 、 RBR 、MBR binlog_format的区别？
略
binlog 记录了些什么内容？       
略

1.2.3 使用

日志内容查看

db01 [(none)]>show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
| mysql-bin.000001 | 179       | No        |
| mysql-bin.000002 | 723901    | No        |
| mysql-bin.000003 | 21097721  | No        |
| mysql-bin.000004 | 34583     | No        |
| mysql-bin.000005 | 1011      | No        |
| mysql-bin.000006 | 243       | No        |
| mysql-bin.000007 | 243       | No        |
| mysql-bin.000008 | 2662      | No        |
+------------------+-----------+-----------+
8 rows in set (0.00 sec)

db01 [(none)]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File              | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 | 2662      |             |                   | 483e9795-ad5b-11ea-86e8-000c298e182d:1-85 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
Master [binlog]>show binlog events in 'mysql-bin.000003';

Log_name：           binlog文件名
Pos：                开始的position *****
Event_type：         事件类型
Format_desc：        格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：          mysql服务号标识
End_log_pos：        事件的结束位置号 *****
Info：               事件内容*****


#补充: 如何快速的定位drop操作的pos号 
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
[root@db01 binlog]# mysql -e "show binlog events in'  mysql-bin.000004'" |  grep drop

binlog文件内容详细查看

mysqlbinlog   /data/mysql/mysql-bin.000006
mysqlbinlog --base64-output=decode-rows -vvv  /data/binlog/mysql-bin.000003
mysqlbinlog  -d binlog /data/binlog/mysql-bin.000003

[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004

基于Position号进行日志截取

#核心就是找截取的起点和终点
--start-position=321
--stop-position=513

mysqlbinlog --start-position=219 --stop-position=1347/data/binlog/mysql-bin.000003 >/tmp/bin.sql

#面试案例:
1. 备份策略每天全备,有全量的二进制日志
2. 业务中一共10个库,其中一个被误drop了
3. 需要在其他9个库正常工作过程中进行数据恢复

彩蛋：截取日志痛点

###思考一下：如果生产中会有什么痛点？
1. 需要的日志在多个文件中，怎么截取？
方法1： 分段截取
方法2:  时间戳截取
方法3： gtid

2. binlog属于全局日志，日志中有其他库的操作，怎么排除掉？
mysqlbinlog -d oldboy mysql-bin.000008 > /tmp/bin.sql1.2.4 gtid特性

3. binlog中100w个事件，怎么快速找到drop database的位置点？
mysql> pager grep "DROP"
[root@db01 ~]# mysql -e "show binlog events in 'mysqlbin.000014'" |less
[root@db01 ~]# mysql -e "show binlog events in 'mysqlbin.000014'" |grep

4. 比如删除的库，建库是在2年前操作的。这种情况怎么办？
每天全备，binlog完好的。
可以使用全备+binlog方式实现恢复数据故障之前。

5. 恢复中只想要某张表的binlog，怎么办？
binlog2sql

1.2.4 gtid特性

介绍

5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7+ 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = server_uuid ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:1-29
重要参数介绍：
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
systemctl restart mysqld

应用

#具备GTID后,截取查看某些事务日志:
--include-gtids
--exclude-gtids
--skip-gtidscase:
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-
a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004


开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
就想恢复?怎么办?
--skip-gtids
mysqlbinlog --include-gtids='3ca79ab5-3e4d-11e9-a709-
000c293b577e:4' /data/binlog/mysql-bin.000004
/data/binlog/mysql-bin.000004
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

case:

db01 [(none)]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB              |Binlog_Ignore_DB | Executed_Gtid_Set
|
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 196      |             |
| 483e9795-ad5b-11ea-86e8-000c298e182d:1-86 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
db01 [(none)]>create database gtdb;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>use gtdb;
Database changed
db01 [gtdb]>create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)
db01 [gtdb]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set
|+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 570        |               |                | 483e9795-ad5b-11ea-86e8-000c298e182d:1-88 |
  +------------------+----------+--------------+-----------------+-------------------------------------------+
1 row in set (0.00 sec)
db01 [gtdb]>begin;
Query OK, 0 rows affected (0.00 sec)
db01 [gtdb]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
db01 [gtdb]>insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
db01 [gtdb]>insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)
db01 [gtdb]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [gtdb]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position  | Binlog_Do_DB | Binlog_Ignore_DB            | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 1019      |              |                | 483e9795-ad5b-11ea-86e8-000c298e182d:1-89   |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)db01 [gtdb]>begin;
Query OK, 0 rows affected (0.00 sec)
db01 [gtdb]>insert into t1 values(11);
Query OK, 1 row affected (0.00 sec)
db01 [gtdb]>insert into t1 values(12);
Query OK, 1 row affected (0.00 sec)
db01 [gtdb]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [gtdb]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 1380 |   |              | 483e9795-ad5b-11ea-86e8-000c298e182d:1-90 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
db01 [gtdb]>drop database gtdb;
Query OK, 1 row affected (0.01 sec)
db01 [(none)]>
db01 [(none)]>
db01 [(none)]>
db01 [(none)]>show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set                                                                          |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 1561 |    |             | 483e9795-ad5b-11ea-86e8-000c298e182d:1-91                                    |
| mysql-bin.000009 | 1380 |    |             | 483e9795-ad5b-11ea-86e8-000c298e182d:1-90                                        |
+------------------+----------+--------------+------------------+-------------------------------------------+
  
  
[root@db01 binlog]# mysqlbinlog --skip-gtids --include-gtids='483e9795-ad5b-11ea-86e8-000c298e182d:87-90' mysql-bin.000009 >/data/bin1.sql

注意： 只要显示的设定了gtid，不管是传统方式截取日志还是gtid截取，都需
要加--skip-gtids做恢复。


假设： 我要截取 1-20号gtid,跳过 14 ，18号gtid
mysqlbinlog --skip-gtids --include-gtids='483e9795-ad5b-11ea-86e8-000c298e182d:1-20'--exclude-gtids='483e9795-ad5b-11ea-86e8-000c298e182d:14','483e9795-ad5b-11ea-86e8-000c298e182d:18' mysql-bin.000009 >/data/bin1.sql

1.2.5 二进制日志其他操作

自动清理日志

show variables like '%expire%';
expire_logs_days 0
#自动清理时间,是要按照全备周期+1
set global expire_logs_days=8;

#永久生效:
my.cnf
expire_logs_days=15;

#企业建议,至少保留两个全备周期+1的binlog
8.0 变化：
binlog_expire_logs_seconds=2592000

手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2. 把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志

日志滚动

flush logs;
重启mysql也会自动滚动一个新的
日志文件达到1G大小(max_binlog_size)
| max_binlog_size | 1073741824
备份时,加入参数也可以自动滚动

1.2.6 binlog2sql应用

# 功能
1. 友好的展示或管理binlog
2. 快速DML闪回（通过日志翻转方式）。

# 安装配置binlog2sql
[root@db01 ] git clone https://github.com/danfengcao/binlog2sql.git &&
[root@db01 ] cd binlog2sql
[root@db01 ] yum install python3
[root@db01 ] pip3 install -r requirements.txt (Pymysql 0.9.3)
[root@db01 ]
db03 [(none)]>create user root@'10.0.0.%' identified with
mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)
db03 [(none)]>grant all on *.* to root@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

# 模拟数据修改
db01 [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)
db01 [(none)]>create database test;
Query OK, 1 row affected (0.01 sec)
db01 [(none)]>use test;
Database changed
db01 [test]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
db01 [test]>insert into t1 values(1),(2),(3),(4),(5),(6),
(7),(8);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
db01 [test]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [test]>update t1 set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
db01 [test]>delete from t1 where id=3;
Query OK, 1 row affected (0.00 sec)
db01 [test]>commit;
Query OK, 0 rows affected (0.01 sec)
db01 [test]>^DBye

#解析日志事件SQL
[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.100 -P3306 -uroot -p123 -d test -t t1 --start-file='binlog.000005'
USE b'test';
create database test;
USE b'test';
create table t1 (id int);
INSERT INTO `test`.`t1`(`id`) VALUES (1); #start 649 end
822 time 2020-06-13 19:58:40
INSERT INTO `test`.`t1`(`id`) VALUES (2); #start 649 end
822 time 2020-06-13 19:58:401.3 slowlog
INSERT INTO `test`.`t1`(`id`) VALUES (3); #start 649 end
822 time 2020-06-13 19:58:40
UPDATE `test`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1;
#start 932 end 1110 time 2020-06-13 19:58:56
DELETE FROM `test`.`t1` WHERE `id`=3 LIMIT 1; #start 932
end 1198 time 2020-06-13 19:59:05


# 只解析delete类型操作
[root@db01 binlog2sql]# python3 binlog2sql.py -h
10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --startfile='mysql-bin.000003' --sql-type=delete

#生成指定事件回滚语句
[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.3 slowlog

1.3.1 作用

1	记录慢SQL语句的日志,定位低效SQL语句的工具日志

1.3.2 配置

#开关:
slow_query_log=1

#文件位置及名字
slow_query_log_file=/data/mysql/slow.log

#设定慢查询时间:
long_query_time=0.1

#没走索引的语句也记录:
log_queries_not_using_indexes

vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld

1.3.3 分析及使用

1	mysqldumpslow -s c -t 10 /data/mysql/slow.log

1.3.4 pt-query-digest(pqd)应用

安装及介绍：

# 安装工具：
[root@master ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm
#命令语法：
pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。

--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。

--output 分析结果输出类型，值可以是report(标准分析报告)、

--slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd[hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

结果说明：

Overall：        总共有多少条查询
Time range： 查询执行的时间范围
unique：         唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：          总计 min：最小 max：最大 avg：平均
95%：                把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
median：         中位数，把所有值从小到大排列，位置位于中间那个数

Response: 总的响应时间。
time: 该查询在本次分析中总的时间占比。
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。
R/Call: 平均每次执行的响应时间。
Item : 查询对象

每部分详细统计结果
1号查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、
95%等各项目的统计。
Databases: 库名
Users: 各个用户执行的次数（占比）
Query_time distribution : 查询时间分布, 长短体现区间占比，本例中
查询集中在10ms。
Tables: 查询中涉及到的表
Explain: 示例

###每部分详细统计结果
1号查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、
95%等各项目的统计。
Databases: 库名
Users: 各个用户执行的次数（占比）
Query_time distribution : 查询时间分布, 长短体现区间占比，本例中
查询集中在1s+。
Tables: 查询中涉及到的表
Explain: 执行计划

命令行应用实例：

用法示例
1.直接分析慢查询文件:
pt-query-digest slow.log > slow_report.log

2.分析最近12小时内的查询：
pt-query-digest --since=12h slow.log > slow_report2.log

3.分析指定时间范围内的查询：
pt-query-digest slow.log --since '2019-01-07 09:30:00' --
until '2019-01-07 10:00:00'> > slow_report3.log

4.分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~m/^select/i' slow.log> slow_report4.log

5.针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~m/^root/i' slow.log> slow_report5.log
6.查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log>slow_report6.log
7.把结果保存到query_review表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log
8.把结果保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history --create-review-table slow.log_0001
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history --create-review-table slow.log_0002
9.通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt >slow_report9.log
10.分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql pt-query-digest --type=binlog mysql-bin000093.sql >slow_report10.log
11.分析general log
pt-query-digest --type=genlog localhost.log >slow_report11.log


###慢日志图形化展示项目：
gui
anemometer
lepus

2.MySQL 8.0的备份、恢复、迁移

2.1备份恢复中的职责

设计备份策略
日常备份巡检
定期恢复演练(测试库)
故障恢复
升级迁移

2.2 MySQL8.0备份工具介绍

###逻辑：
mysqldump
load data/mysqlimport
mydumper

###物理：
Percona Xtrabackup 8.0.12+
Enterprise Backup
8017+ Clone Plugin

2.3逻辑备份工具使用-mysqldump ✨

mysqldump备份原理 MySQL mysqldump备份恢复及原理 - VicLW - 博客园

1.调用FWRL（flush tables with read lock）,全局禁止读写

2.开启快照读，获取此期间的快照（仅仅对innodb起作用）

3.备份非innodb表数据（*.frm,*.myi,*.myd等）

4.非innodb表备份完毕之后，释放FTWRL

5.逐一备份innodb表数据

6.备份完成

客户端通用参数

-u -p -S -h -P
本地备份:
mysqldump -uroot -p -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p -h 10.0.0.51 -P3306

备份专用参数

-A 全备参数
-B db1 db2 db3      备份多个单库备份单个或多个表
-R                            备份存储过程及函数
--triggers              备份触发器
-E                          备份事件
-F                            在备份开始时,刷新一个新binlog日志
--master-data=2         以注释的形式,保存备份开始时间点的binlog的状态信息

###功能：
（1） 在备份时，会自动记录，二进制日志文件名和位置号
（2） 自动锁表（FTWRL）
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现 快照备份。
                        --single-transaction  innodb 存储引擎开启热备(快照备份)功能
案例：
1. 5.6 100+G 有MyISAM表，做大批量DML，mysqldump备份数据库 出现hang住。
2. 5.6 3000w表做DDL，改数据类型，备份期间，锁严重。 master-data可以自动加锁
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（2）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

--set-gtid-purged=auto  auto , (on)off

使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

--max-allowed-packet=#
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

--max-allowed-packet=#
The maximum packet length to send to or receive from server

例子：
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date+%F).sql.gz

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump备份的恢复方式    ###在生产中恢复要谨慎，恢复会删除重复的表
set sql_log_bin=0;       #恢复的时候不记录日志
source /backup/full_2018-06-28.sql
set sql_log_bin=1;

###注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以备份成功，但恢复时间通常需要5-10倍时间，数据量级很大很大的时候（PB、EB）
3、 mysqldump是覆盖形式恢复的方法。

#ps： 一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.

###逻辑备份的优势:
1、可读性强
2、压缩比很高

master-data=2 : 记录备份开始binlog pos gtid

2.4逻辑备份工具使用-mydumper&myloader

2.4.0 介绍

https://blog.csdn.net/Hehuyi_In/article/details/105354786

MySQL自身的mysqldump工具支持单线程工作，依次一个个导出多个表，没有一
个并行的机，这就使得它无法迅速的备份数据。
mydumper作为一个实用工具，能够良好支持多线程工作，可以并行的多线程的
从表中读入数据并同时写到不同的文件里，这使得它在处理速度方面快于传统的
mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需
要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主
从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper
能更好的完成备份任务。

mydumper特性
多线程备份
因为是多线程逻辑备份，备份后会生成多个备份文件
备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞

DML语句
保证备份数据的一致性
支持文件压缩
支持导出binlog
支持多线程恢复
支持以守护进程模式工作，定时快照和连续二进制日志
支持将备份文件切块

2.4.1 工作原理

# mydumper主要流程概括
1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性
2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3、N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事务
4、dump non-InnoDB tables, 首先导出非事务引擎的表
5、主线程 UNLOCK TABLES 非 事务引擎备份完后，释放全局只读锁
6、dump InnoDB tables, 基于 事务导出InnoDB表
7、事务结束

# 备份所生成的文件
目录中包含一个metadata文件
记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，
如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置
每个表有两个备份文件：
database.table-schema.sql 表结构文件
database.table.sql 表数据文件

如果对表文件分片，将生成多个备份数据文件，可以指定行数或指定大小分片

2.4.2 参数介绍

# mydumper
-B, --database 要备份的数据库，不指定则备份所有库
-T, --tables-list 需要备份的表，名字用逗号隔开
-o, --outputdir 备份文件输出的目录
-s, --statement-size 生成的insert语句的字节数，默认
1000000
-r, --rows 将表按行分块时，指定的块行数，指定这
个选项会关闭 --chunk-filesize
-F, --chunk-filesize 将表按大小分块时，指定的块大小，单位
是 MB
-c, --compress 压缩输出文件-e, --build-empty-files 如果表数据是空，还是产生一个空文件
（默认无数据则只有表结构文件）
-x, --regex 是同正则表达式匹配 'db.table'
-i, --ignore-engines 忽略的存储引擎，用都厚分割
-m, --no-schemas 不备份表结构
-k, --no-locks 不使用临时共享只读锁，使用这个选项会
造成数据不一致
--less-locking 减少对InnoDB表的锁施加时间（这种模
式的机制下文详解）
-l, --long-query-guard 设定阻塞备份的长查询超时时间，单位是
秒，默认是60秒（超时后默认mydumper将会退出）
--kill-long-queries 杀掉长查询 (不退出)
-b, --binlogs 导出binlog
-D, --daemon 启用守护进程模式，守护进程模式以某个
间隔不间断对数据库进行备份
-I, --snapshot-interval dump快照间隔时间，默认60s，需要在
daemon模式下
-L, --logfile 使用的日志文件名(mydumper所产生的日
志), 默认使用标准输出
--tz-utc 跨时区是使用的选项，不解释了
--skip-tz-utc 同上
--use-savepoints 使用savepoints来减少采集metadata
所造成的锁时间，需要 SUPER 权限
--success-on-1146 Not increment error count and
Warning instead of Critical in case of table doesn't exist
-h, --host 连接的主机名
-u, --user 备份所使用的用户
-p, --pass 密码
-P, --port 端口
-S, --socket 使用socket通信时的socket文件
-t, --threads 开启的备份线程数，默认是4
-C, --compress-protocol 压缩与mysql通信的数据
-V, --version 显示版本号
-v, --verbose 输出信息模式, 0 = silent, 1 =
errors, 2 = warnings, 3= info, 默认为 2



# myloader
-d, --directory 备份文件的文件夹
-q, --queries-per-transaction 每次事物执行的查询数量，默认
是1000
-o, --overwrite-tables 如果要恢复的表存在，则先drop
掉该表，使用该参数，需要备份时候要备份表结构
-B, --database 需要还原的数据库
-e, --enable-binlog 启用还原数据的二进制日志
-h, --host 主机
-u, --user 还原的用户
-p, --pass 密码
-P, --port 端口
-S, --socket socket文件
-t, --threads 还原所使用的线程数，默认是4
-C, --compress-protocol 压缩协议
-V, --version 显示版本
-v, --verbose 输出模式, 0 = silent, 1 =
errors, 2 = warnings,3 = info, 默认为2

#下载地址：
https://github.com/maxbube/mydumper/releases/

#安装：
[root@db01 app]# yum install -y mydumper-0.9.5-2.el7.x86_64.rpm

2.4.3 使用

#备份全库：
mydumper -u root -p '123' -o /data/backup/
# 备份test数据库：
mydumper -u root -p '123' -B test -o /data/backup/
#备份多张表(tableA,tableB)：
mydumper -u root -p '123' -B test -T tableA,tableB -o
/data/backup/
#备份tableA表的数据，不备份表结构
mydumper -u root -p '123' -B test -m -o /data/backup/
#备份tableA表的数据，并进行压缩
mydumper -u root -p '123' -B test -c -o /data/backup/



#还原test库：
myloader -u root -p '123' -B test -d /data/backup/
#还原tableA表
myloader -u root -p '123' -B test -o tableA -d  /data/backup/
Ps: 适合有很多的小表备份。

2.5逻辑导入导出-load data

2.5.0 功能

1
2
3

1. 大数据量的录入
2. 异构迁移
    数据源： MySQL 、异构平台导出的、造数工具生成文本类的文件。

2.5.1 造数

use oldguo
show tables;
+---------------------+
| Tables_in_oldguo    |
+---------------------+
| test                |
| test2             |
+---------------------+
create table test3(id int unsigned not null primary key
auto_increment,test varchar(100),test2 varchar(100));
insert into test3(test,test2) values('a
string','100.20'),('a string containing a ,
comma','102.20'),('a string containing a "
quote','102.20'),\
('a string containing a ", quote and comma','102.20');
                                 
select * from test3;
+----+------------------------------------------+--------+
| id |                                      test | test2 |
+----+------------------------------------------+--------+
| 2 | a string                                 | 100.20  |
| 4 | a string containing a , comma            | 102.20  |
| 6 | a string containing a " quote            | 102.20  |
| 8 | a string containing a ", quote and comma | 102.20  |
+----+------------------------------------------+--------+
4 rows in set (0.00 sec)

2.5.2 语法

help load data;
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE
'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
.....

load data语句加载的数据源可以是mysqldump导出的纯文本数据文件，也可
以是使用SELECT … INTO OUTFILE '/path/xx.txt';
语句生成的单表纯文本数据文件，或者其他的方式生成的txt（只要生成的纯文本数据列按指定分隔符分割的纯文本数据文件即可）
从上面的帮助信息可以看到整个load data语句的语法结构，其中load datainfile 'file.txt' into table tb_name; 是最基本的使用语句结构，
其余的都为可选子句

2.5.3必选子句或关键字

# load data语句简单示例
如果文本文件中的数据字段与表结构中的字段定义顺序相同，则直接使用如下语
句载入即可
select * from test3;
+----+------------------------------------------+--------+
| id | test                                     |  test2 |
+----+------------------------------------------+--------+
| 2 | a string                                  | 100.20 |
| 4 | a string containing a , comma             | 102.20 |
| 6 | a string containing a " quote             | 102.20 |
| 8 | a string containing a ", quote and comma  | 102.20 |
+----+------------------------------------------+--------+
select * from test3 into outfile "/tmp/test3.txt";
truncate test3;
load data infile '/tmp/t3.txt' into table test3;
select * from test3;     #这里可以看到，数据到处导入正常
+----+------------------------------------------+--------+
| id |                                     test | test2  |
+----+------------------------------------------+--------+
| 2 | a string                                  | 100.20 |
| 4 | a string containing a , comma             | 102.20 |
| 6 | a string containing a " quote             | 102.20 |
| 8 | a string containing a ", quote and comma  | 102.20 |
+----+------------------------------------------+--------+

2.5.4 设置字段顺序

如果文本文件中的数据字段与表结构中的字段定义顺序不同，则使用如下语句指
定载入表中的字段顺序
# 导出文本，导出文本时不使用select *，而是使用具体的字段，把顺序稍微
调整一下
system rm -f /tmp/t3.txt;
desc test3; # 留意表的字段定义顺序，这里是id, test, test2
+-------+------------------+------+-----+---------+----------------+
| Field | Type              | Null | Key | Default | Extra|
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL     |auto_increment |
| test  | varchar(100)     | YES  |     | NULL     |               |
| test2 | varchar(100)     | YES  |     | NULL     |               |
+-------+------------------+------+-----+---------+----------------+
select * from test3; # 留个表中各个字段的值大概是什么内容
+----+------------------------------------------+--------+
| id |                                      test | test2 |
+----+------------------------------------------+--------+
| 2 | a string                                  | 100.20 |
| 4 | a string containing a , comma             | 102.20 |
| 6 | a string containing a " quote             | 102.20 |
| 8 | a string containing a ", quote and comma  | 102.20 |
+----+------------------------------------------+--------+
select id,test2,test from test3 into outfile
"/tmp/test3.txt";
system cat /tmp/test3.txt; #这里可以看到文本文件中的test字段值
放到最后去了
2 100.20 a string
4 102.20 a string containing a , comma
6 102.20 a string containing a " quote
8 102.20 a string containing a ", quote and comma

# 现在，truncate掉表test3，执行load data载入数据
truncate test3;
load data infile '/tmp/test3.txt' into table
test3(id,test2,test);
select * from test3; #可以看到，使用(id,test2,test)子句指定
了与文本文件中数据的字段一致的顺序，导入表表中之后数据的顺序是正确的
+----+------------------------------------------+--------+
| id | test                                     | test2  |
+----+------------------------------------------+--------+
| 2 | a string                                  | 100.20 |
| 4 | a string containing a , comma             | 102.20 |
| 6 | a string containing a " quote             | 102.20 |
| 8 | a string containing a ", quote and comma  | 102.20 |
+----+------------------------------------------+--------+

2.5.5 LOCAL关键字

说明： 如果要载入的文本文件不在mysql server数据库本身的本地磁盘，客户端也不是从mysql server本机登录的，则需要使用local关键字，指定mysql server从client host本地加载该文件，需要mysql server端使用
     local_infile=true(或者设置为1，不设置时默认为1)启动，以及客户端连接mysql server时也使用local_infile=true(或者设置为1，不指定时默认为1)连接才能使用，server和client必须都开启这个参数才能使用local关键字，任意一个关闭都不能使用.

# 登录到数据库，重新导出表数据到文本，并发送到10.0.0.52服务器
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt";
system scp /tmp/test3.txt 10.0.0.52:/tmp/test3.txt
# 登录到10.0.0.52服务器，远程连接10.0.0.51数据库
mysql -uroot -p123 h10.0.0.51
mysql> use oldguo
mysql> system ls -lh /tmp/test3.txt;
-rw-r--r-- 1 root root 146 May 3 11:11 /tmp/test3.txt
mysql> system cat /tmp/test3.txt;
2 a string 100.20
4 a string containing a , comma 102.20
6 a string containing a " quote 102.20


8 a string containing a ", quote and comma 102.20
mysql> show variables like '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON     |
+---------------+-------+
mysql> set global local_infile=OFF; #关闭server端的local_infile参数
mysql> truncate test3;
mysql> load data local infile '/tmp/test3.txt' into table test3; #执行导入数据时报错了
ERROR 1148 (42000): The used command is not allowed with
this MySQL version
mysql> set global local_infile=ON; #重新打开server端的local_infile参数
mysql> load data local infile '/tmp/test3.txt' into table test3; #导入成功
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test3; #查看数据，可以看到数据已成功导入表
+----+------------------------------------------+--------+
| id | test                                      | test2 |
+----+------------------------------------------+--------+
| 2 | a string                                   | 100.20 |
| 4 | a string containing a , comma              | 102.20 |
| 6 | a string containing a " quote              | 102.20 |
| 8 | a string containing a ", quote and comma   | 102.20 |
+----+------------------------------------------+--------+
4 rows in set (0.00 sec)
# 对于客户端连接server时使用local_infile=0参数，在执行导入数据时也
会报相同的错误：
mysql -uroot -p123 -h10.0.0.51 --local-infile=0

2.5.6 REPLACE与IGNORE关键字

1
2
3

REPLACE和IGNORE关键字控制对唯一键值冲突行的处理： 如果指定了REPLACE关键字，则输入行将覆盖现有行。
换句话说，与主键或唯一 索引冲突的数据行将被执行覆盖写入，如果同时使用了local关键字，则与没有 使用local关键字行为相同 如果指定了IGNORE关键字，则与唯一键值冲突的数据行将被丢弃，
如果同时使用 了local关键字，则与没有使用local关键字行为相同

2.5.7 fields关键字应用

字段(列)分隔符应用

默认是\t，使用子句 fields terminated by 'string' 指定，其中
string代表指定的字段分隔符
select * from test3 into outfile "/tmp/test3.txt" FIELDS
TERMINATED BY ',';
system cat /tmp/test3.txt
2,a string,100.20
4,a string containing a \, comma,102.20
6,a string containing a " quote,102.20
8,a string containing a "\, quote and comma,102.20

应用：
db03 [oldguo]>load data infile '/tmp/passwd' into table
passwd FIELDS TERMINATED BY ':';

字段引用符

如果加optionally选项则只用在char、varchar和text等字符型字段上，数
值类型会忽略使用引用符，如果不指定该子句，则默认不使用引用符，使用子句
fields [optionally] enclosed by 'char'指定，其中char代表指定的
字段引用符
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt" FIELDS
ENCLOSED BY '"';
system cat /tmp/test3.txt
"2" "a string" "100.20"
"4" "a string containing a , comma" "102.20"
"6" "a string containing a \" quote" "102.20"
"8" "a string containing a \", quote and comma" "102.20"
"10" "\\t" "102.20"
# 指定字段引用符为"，使用optionally关键字，可以看到id列的字段引用符
去掉了
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt" FIELDS
optionally ENCLOSED BY '"';
system cat /tmp/test3.txt
2 "a string" "100.20"
4 "a string containing a , comma" "102.20"
6 "a string containing a \" quote" "102.20"
8 "a string containing a \", quote and comma" "102.20"
10 "\\t" "102.20"
db03 [oldguo]>select * from t5 into outfile '/tmp/t7.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
db03 [oldguo]>select * from t5 into outfile '/tmp/t8.txt'
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"';

转义字符

默认为\，使用子句fields escaped by 'char' 指定，其中char代表指定
的转义字符
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt" fields
escaped by '.';
system cat /tmp/test3.txt # 可以看到数据中指定的转义符.号被转义
了，而数据\t没有被转义
2 a string 100..20
4 a string containing a , comma 102..20
6 a string containing a " quote 102..20
8 a string containing a ", quote and comma 102..20
10 \t 102..20
truncate test3; #清空表
load data infile "/tmp/test3.txt" into table test3 fields
escaped by '.'; #导入数据时指定转义符为.号
select * from test3; #校验数据，可以看到导入数据正常
+----+------------------------------------------+--------+
| id | 										test | test2 |
+----+------------------------------------------+--------+
| 2 | a string 									| 100.20 |
| 4 | a string containing a , comma 			| 102.20 |
| 6 | a string containing a " quote 			| 102.20 |
| 8 | a string containing a ", quote and comma  | 102.20 |
| 10 | \t 										| 102.20 |
+----+------------------------------------------+--------+

2.5.8 LINES 关键字应用

行前缀字符串

# load data语句如下
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt" LINES
STARTING BY 'xxx';
system cat /tmp/test3.txt #可以看到每行数据前面多了个行前缀字符
串xxx
xxx2 a string 100.20
xxx4 a string containing a , comma 102.20
xxx6 a string containing a " quote 102.20
xxx8 a string containing a ", quote and comma 102.20
xxx10 \\t 102.20
# 现在，到shell命令行去修改一下，增加两行
system cat /tmp/test3.txt # 最后要加载的纯文本数据内容如下
xxx2 a string 100.20
xxx4 a string containing a , comma 102.20
xxx6 a string containing a " quote 102.20
xxx8 a string containing a ", quote and comma 102.20
xxx10 \\t 102.20
12 \\t 102.20
dfadsfasxxx14 \\t 102.20
truncate test3; #清空表
Query OK, 0 rows affected (0.01 sec)
load data infile "/tmp/test3.txt" into table test3 LINES
STARTING BY 'xxx'; #导入数据，指定行前缀字符为xxx
select * from test3; #校验表数据，可以看到没有xxx行前缀的行被忽
略了，而包含xxx的最后一行，从xxx开始截断，xxx字符本身及其之前的内容被
忽略，\
xxx之后的内容被解析为行数据导入了
+----+------------------------------------------+--------+
| id |										 test | test2 |
+----+------------------------------------------+--------+
| 2 | a string									 | 100.20 |
| 4 | a string containing a , comma				 | 102.20 |
| 6 | a string containing a " quote 			 | 102.20 |
| 8 | a string containing a ", quote and comma   | 102.20 |
| 10 | \t										 | 102.20 |
| 14 | \t 										 | 102.20 |
+----+------------------------------------------+--------+

行结束符

linux下默认为\n，使用子句lines terminated by 'string' 指定，其
中string代表指定的换行符
# 指定换行符为\r\n导出数据
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt" lines
terminated by '\r\n';
# 现在，把数据重新导入表，从下面的结果中可以看到，导入表中的数据正确
truncate test3;
load data infile "/tmp/test3.txt" into table test3 lines
terminated by '\r\n';
select * from test3;
+----+------------------------------------------+--------+
| id |										 test | test2 |
+----+------------------------------------------+--------+
| 2 | a string									 | 100.20 |
| 4 | a string containing a , comma				 | 102.20 |
| 6 | a string containing a " quote 			 | 102.20 |
| 8 | a string containing a ", quote and comma   | 102.20 |
| 10 | \t										 | 102.20 |
| 14 | \t 										 | 102.20 |
+----+------------------------------------------+--------+

2.5.9FIELDS和LINES注意事项

MySQL中反斜杠是SQL语句中特殊字符的转义字符，因此在sql语句中碰到
特殊字符时，您必须指定一个或者两个反斜杠来为特殊字符转义（如在mysql中
或者一些其他程序中，\n代表换行符，\t代表制表符，\代表转义符，那么需要
使用\t来转义制表符，\n来转义换行符，\来转义转义符本身，这样才能正确写
入数据库或者生成导出的数据文本,使用FIELDS ESCAPED BY子句指定转义符.
特殊字符列表如下
\0 ASCII NUL (X'00') 字符
\b 退格字符
\n 换行符
\r 回车符
\t 制表符
\Z ASCII 26 (Control+Z)
\N NULL值，如果转义符值为空，则会直接导出null字符串作为数据，这在导
入时将把null作为数据导入
例如： 数据中包含了ENCLOSED BY '"'子句指定字段引用符号，则与字段引
用符号相同数据字符也会被自动添加一个反斜杠进行转义（如果转义符指定为
空，则可能会导致数据在导入时无法正确解析）。
例如：
select * from test3 into outfile "/tmp/test3.txt" FIELDS
OPTIONALLY enclosed BY '"';
system cat /tmp/test3.txt;
2 "a string" "100.20"
4 "a string containing a , comma" "102.20"
6 "a string containing a \" quote" "102.20"
8 "a string containing a \", quote and comma" "102.20" #
可以看到与字段引用符相同的符号数据被转义了

2.5.10 IGNORE number {LINES | ROWS}子句

忽略输入文件中的前number行数据，使用子句ignore number lines指定忽
略文本的前number行，在某些情况下生成的文本（如：mysql -e "select
…." > xx.txt中）带有字段名称，在导入时会把这一行字段名称也当作数据，
所以需要忽略掉这行字段名称
system cat /tmp/test3.txt
id test test2 test3
2 a string 100.20 null
4 a string containing a , comma 102.20 NULL
6 a string containing a " quote 102.20 NULL
8 a string containing a ", quote and comma 102.20
NULL
10 \\t 102.20 NULL
14 \\t 102.20 NULL
truncate test4;
load data infile "/tmp/test3.txt" into table test4 ignore
1 lines; #载入文本时指定ignore 1 lines子句忽略文本中的前1行数据
select * from test4; #查询表test4中的数据，从下面的结果中可以看
到数据正确
+----+------------------------------------------+--------+-------+
| id | 										 test | test2 |test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20									   |null |
| 4 | a string containing a , comma 			  | 102.20 |NULL |
| 6 | a string containing a " quote 			  | 102.20 |NULL |
| 8 | a string containing a ", quote and comma    | 102.20 |NULL |
| 10 |\t									      | 102.20 |NULL |
| 14 | \t 										  | 102.20 |NULL |
+----+------------------------------------------+--------+-------+

2.5.11 SET col_name = expr,…子句

将列做一定的数值转换后再加载，使用子句set col_name = expr,.. 指
定，要注意：col_name必须为表中真实的列名，expr可以是任意的表达式或者
子查询，只要返回的数据结果值能对应上表中的字段数据定义类型即可，注意，
非set语句生成的列名，必须使用括号括起来，否则报语法错误。
# 如果系统将id列的文本数据加上10以后再加载到表的test3列中，可以如下
操作：
system rm -f /tmp/test3.txt;
select * from test3 into outfile "/tmp/test3.txt";
system cat /tmp/test3.txt
2 a string 100.20 null
4 a string containing a , comma 102.20 \N
6 a string containing a " quote 102.20 \N
8 a string containing a ", quote and comma 102.20 \N
10 \\t 102.20 \N
14 \\t 102.20 \N
create table test4 like test3;
alter table test4 add column test3 varchar(10);
truncate test4;
load data infile "/tmp/test3.txt" into table test4
(id,test,test2) set test3=id+10 ;
ERROR 1262 (01000): Row 1 was truncated; it contained more
data than there were input columns
select * from test4; #严格模式下因为文本中多了一个字段被截断了，
所以拒绝导入,修改sql_mode后导入。
load data local infile "/tmp/test3.txt" into table test4
(id,test,test2) set test3=id+10 ;
#可以使用local关键字强制进行截断最后一个字段的null值列进行导入
注意，如果不使用local关键字，那就需要修改sql_mode才能导入
select * from test4;
+----+------------------------------------------+--------+-------+
| id | test | test2 |test3 |
+----+------------------------------------------+--------+-------+
| 2 | a string | 100.20 |12 |
| 4 | a string containing a , comma | 102.20 |14 |
| 6 | a string containing a " quote | 102.20 |16 |
| 8 | a string containing a ", quote and comma | 102.20 |18 |
| 10 | \t | 102.20 |20 |
| 14 | \t | 102.20 |24 |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)
# 或者使用txt文件中的某些列进行计算后生成新的列插入，这里演示两个字段
进行相加后导入另外一个字段中：
load data local infile "/tmp/test3.txt" into table test4
(id,test,test2) set test3=id+test2 ; # 注意，如果不使用local
关键字，那就需要修改sql_mode才能导入
select * from test4;
+----+------------------------------------------+--------+-------+
| id | 		test																	 | test2 |test3  |
+----+------------------------------------------+--------+-------+
| 2 | a string																 	| 100.20 |102.2 |
| 4 | a string containing a , comma 				  	| 102.20 |106.2 |
| 6 | a string containing a " quote 					  | 102.20 |108.2 |
| 8 | a string containing a ", quote and comma  | 102.20 |110.2 |
| 10 | \t 																			| 102.20 |112.2 |
| 14 | \t 																			| 102.20 |116.2 |
+----+------------------------------------------+--------+-------+
6 rows in set (0.00 sec)

2.5.12使用mysqldump批量导出

mysqldump -u username -p'xxx' -T target_dir db_name

tb_name [option];
其中option参数是以下几种可选参数：
	--fields-terminated-by 'string' 字段分隔符
	--fields-enclosed-by 'char' 字段引用符
	--fields-optionally-enclosed-by 'char' 字段引用符，只在char,varchar,text等字段类型上生效
	--fields-escaped-by 'char' 转义字符
	--lines-terminated-by 'string' 记录结束符，即换行符

mkdir /data/backup/
chown mysql.mysql /data/backup -R
mysqldump -uroot -p123 -h 10.0.0.53 --single-transaction --master-data=2 --triggers --routines --events--fields-terminated-by ',' --fields-enclosed-by '"' world -T /data/backup/

2.5.13 使用mysqlimport批量导入

mysqlimport -uroot -p 'xxx' [--local] db_name
order_tab.txt [iption]
# 参数：
--fields-terminated-by=name 指定字段分隔符
--fields-enclosed-by=name 指定字段引用符
--fields-optionally-enclosed-by=name 指定字段引用符，但只在
char、varchar、text字段上使用引用符
--fields-escaped-by=name 指定转义字符
--lines-terminated-by=name 指定行记录结束符（换行符）
--ignore-liens=number 忽略前几行
--low-priority 碰到有其他线程update操作操作的表与导入操作表相同
时，延迟执行导入操作
-i, --ignore 如果碰到唯一键冲突就忽略冲突行导入
-r, --replace 如果碰到唯一键冲突就覆盖冲突行导入
-L, --local 从客户端主机加载数据文本文件
-C, --compress 在C/S模型之间使用压缩传输数据
-c, --columns=name 指定需要导入哪些列，与load data语句中一样需
要指定表定义中真实的列名，有多个列名时使用逗号分隔
--default-character-set=name 设置使用该选项指定的字符集来解析文
本文件中的内容
-h, --host 指定导入server的主机IP
-p, --password[=name] 指定导入server的用户密码
-P, --port=# 指定导入server的监听端口
--use-threads=# 指定多少个线程并发执行load data语句（实测单表时
指定多线程时要比单线程要快，由于数据量小，测试出来的差别并不大，官方并
没有说明是基于什么级别的并发，\
只写了一句：Load files in parallel using N threads，推测可能是
基于类似mydumper的并发，但是多表导入时指定多线程就明显比单线程要快很
多）
-u, --user=name 指定导入server的用户名
-d, --delete 指定导入操作之前先把表清空（实测重复导入时加了这个选项
之后可以正常执行，，通过解析binlog发现，发现binlog中记录的第二次和第
一次导入的语句完全相同是，\
第二次导入时如果发现表中有冲突数据，就先执行的不带where条件的
delete，所有表先delete掉，然后再执行load data语句导入数据，另外，
当与replace一起使用时，忽略replace选项）


# mysqlimport用法演示示例
## 单表
mysqlimport -uroot -p123 -h10.0.0.51 world
/data/backup/city.txt
## 多表
mysqlimport -uroot -p123 -h10.0.0.51 --replace world
/data/backup/*.txt
## 多表导入时可以使用参数--use-threads指定多个线程
mysqlimport -uroot -p123 -h10.0.0.51 --replace --use-threads=8 world /data/backup/*.txt

2.6物理备份工具使用-PerconaXtrabackup ✨

2.6.1 安装

yum install percona-xtrabackup
对于8.0.20版本，需要使用PXB 8.0.12+以上版本。
PXB 8.0 只能备份 MySQL 8.0 ，不能备份低版本
低版本MySQL 使用PXB2.4版本

2.6.2 全量备份

1.全量备份
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --
user=root --password=123 --port=3306 --backup --target-dir=/data/backup/full
或者：使用参数--datadir替换掉参数--defaults-file.
# xtrabackup --host=10.0.0.51 --user=root --password=123 -
-port=3306 --datadir=/data/crm/ --backup --target-dir=/data/backup/

2.数据恢复：
2.1 准备：
xtrabackup --host=10.0.0.51 --user=root --password=123 --
port=3306 --prepare --target-dir=/data/backup/
2.2 拷回数据：
xtrabackup --host=10.0.0.51 --user=root --password=123 --
port=3306 --datadir=/data/bak --copy-back --targetdir=/data/backup/
2.3 修改目录属性启动数据库：
chown -R mysql.mysql /data/bak
chmod -R 755 /data/bak
2.4 启动数据库实例:
2.5 若有主从的问题可以查看备份目录下的文件：
cat xtrabackup_binlog_pos_innodb

2.6.3 增量备份

全量备份的目录为： 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
1.2.增量备份：
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
2.恢复操作：
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
2.3 全备份准备：
# xtrabackup --prepare --target-dir=/data/backup
2.4 拷回数据：
xtrabackup --host=10.0.0.51 --user=root --password=123 --
port=3306 --datadir=/data/bak1 --copy-back --target-dir=/data/backup/full
2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/bak1
chmod -R 755 /data/bak1

2.7企业级备份工具MEB介绍及使用

# 下载地址
https://edelivery.oracle.com/
#全备及恢复
mysqlbackup --backup_dir=/data/backup backup
mysqlbackup --backup-dir=/data/backup/ apply-log
mysqlbackup --backup-dir=/data/backup/ copy-back
# 单文件
mysqlbackup --backup-image=/data/backup/img/bak.img --
backup-dir=/data/backup/tmp backup-to-image
#增量:
mysqlbackup --incremental=optimistic --incrementalbase=history:last_backup --backup-dir=/data/back/incr1 --
backup-image=incremental_image1.bi backup-to-image
mysqlbackup --incremental=optimistic --incrementalbase=dir:/back/full1 --backup-dir=/back/incr1 --backupimage=incremental_image1.bi backup-to-image
mysqlbackup --incremental=optimistic --incrementalbase=dir:/back/incr1 --backup-dir=/back/incr2 --backupimage=incremental_image2.bi backup-to-image
# 恢复：
方法一：
使用copy-back-and-apply-log恢复全量
使用copy-back-and-apply-log恢复增量
方法二：
前滚全量
mysqlbackup --backup-dir=/full-backup/ apply-log
前滚增量
mysqlbackup --incremental-backup-dir=/incr-backup
–backup-dir=/full-backup apply-incremental-backup

2.8 MySQL 8.0 (8.0.17+) Clone-plugin

1 . Clone Plugin介绍
本地克隆：
启动克隆操作的MySQL服务器实例中的数据，克隆到同服务器或同节点上的一个
目录里
远程克隆：
默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并
将其替换为捐赠者(donor)的克隆数据。您也可以将数据克隆到接受者的其他目
录，以避免删除现有数据。(可选)

2. 原理
# 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应用上去。

3. 限制
官方文档列出的一些限制：
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/3307 --port=3333 --
socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &
4.2 远程clone
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.1 创建远程clone用户
# 捐赠者授权
create user test@'%' identified by '123';
grant backup_admin on *.* to test@'%';
# 接受者授权
create user test@'%' identified by '123';
grant clone_admin on *.* to test@'%';
4.2.2 远程clone
# 开始克隆
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
mysql -utest -p123 -h10.0.0.53 -P3306
CLONE INSTANCE FROM test@'10.0.0.51':3306 IDENTIFIED BY
'123';

2.9 MySQL升级及迁移实战

1. MySQL升级和降级
1.1 方式
1.1.1 INPLACE 就地
在一台服务器上，原版本升级到新版本。
风险较大。
**** 建议 ： 不管哪种方式升级，都应该先做备份。方便失败回退。****
1.1.2 Mergeing 迁移 ----> 建议
备份迁移
主从迁移 （主库（低）---> 从库（高））
1.2 升级注意事项
Upgrade is only supported between General Availability
(GA) releases.
Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to
the latest release is recommended before upgrading to the
next version. For example, upgrade to the latest MySQL 5.6
release before upgrading to MySQL 5.7.
Upgrade that skips versions is not supported. For example,
upgrading directly from MySQL 5.5 to 5.7 is not supported.
Upgrade within a release series is supported. For example,
upgrading from MySQL 5.7.x to 5.7.y is supported. Skipping
a release is also supported. For example, upgrading from
MySQL 5.7.x to 5.7.z is supported.
a. 支持GA版本之间升级
b. 5.6--> 5.7 ,先将5.6升级至最新版，再升级到5.7
c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--
->5.7 最新
d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
e. 降低停机时间（停业务的时间）
1.3 INPLACE 升级过程原理
0. 备份原数据库数据
a. 安装新版本软件
b. 关闭原数据库（挂维护页）
c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--
skip-networking)
d. 升级 ： 只是升级系统表。升级时间和数据量无关的。(8.0 和 5.7 区
别)
e. 正常重启数据库。
f. 验证各项功能是否正常。
g. 业务恢复。
1.4 5.6.46 ----> 5.7.28 Inplace 升级演练
a. 安装 新版本软件
b. 停原库
# 1. 快速关库功能关闭(优雅关闭)
mysql> set global innodb_fast_shutdown=0 ;
Query OK, 0 rows affected (0.00 sec)
[root@db01 app]# /data/app/mysql56/bin/mysqladmin -S
/tmp/mysql56.scok shutdown
c. 使用高版本软件挂低版本数据启动
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/mysql56/data
socket=/tmp/mysql56.scok
port=3356
[root@db01 data]# /data/app/mysql/bin/mysqld_safe --
defaults-file=/etc/my.cnf --skip-grant-tables --skipnetworking &
d. 升级 （升级到8.0可以省略）
[root@db01 data]# /data/app/mysql/bin/mysql_upgrade -S
/tmp/mysql56.scok --force
e. 重启数据库到正常状态
[root@db01 data]# mysqladmin -S /tmp/mysql3316.sock
shutdown
f: 正常启动数据库
[root@db01 app]# /data/app/mysql/bin/mysqld_safe &
# 连接查看[root@db01 app]# /data/app/mysql56/bin/mysql -S
/tmp/mysql56.scok
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.28 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.28 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

# 测试应用
1、各项功能验证
2、SQL_MODE: 日期、group by
临时：关闭相应 SQL_mode
建议：让应用满足 SQL_mode
1.5 5.7.28 ----> 8.0.20 Inplace 升级演练
彩蛋： 8.0的新功能
mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e
"util.checkForServerUpgrade()"
例如： 5.7.28 升级至 8.0.20 版本
1. 下载 8.0.20 版本的 mysql-shell，并安装 。
[root@db01 app]# yum install -y mysql-shell-8.0.18-
1.el7.x86_64.rpm
2. 创建用户
mysql> grant all on *.* to root@'10.0.0.%' identified by
'123';
3. 预 检查
mysqlsh root:123@10.0.0.51:3306 -e
"util.checkForServerUpgrade()"
# 开始升级
a. 安装 8.0.20 软件
b. 优雅关闭5728
[root@db01 app]# /data/app/mysql/bin/mysql -S
/tmp/mysql56.scok
mysql> set global innodb_fast_shutdown=0 ;
mysql> shutdown;
c. 使用高版本软件挂低版本数据启动
[mysqld]
user=mysql
basedir=/data/app/mysql8
datadir=/data/mysql56/data
socket=/tmp/mysql56.scok
port=3356
d.高版本软件挂低版本数据启动
[root@db01 data]# /data/app/mysql8/bin/mysqld_safe --
defaults-file=/etc/my.cnf --skip-grant-tables --skipnetworking &
e. 正常启动数据库
[root@db01 data]# /data/app/mysql8/bin/mysqladmin -S
/tmp/mysql56.sock shutdown
f: 正常启动数据库
[root@db01 app]# /data/app/mysql8/bin/mysqld_safe &
1.6 通过mysqldump迁移升级方式：5.7.28到8.0.20
源端： 10.0.0.51 : 3306 ,MySQL 5.7.28 ,binlog开启。
目标端： 10.0.0.53 : 3306 ,MySQL 8.0.20 , binlog开启
预装mysql5728版本软件。
周日凌晨：2:00-3:00
a. 周六全备（凌晨）
[root@db03 ~]# /data/app/mysql/bin/mysqldump -uroot -p123
-h10.0.0.51 -P3356 -A --master-data=2 >/data/full.sql
b. 初始化一个5728新实例
[root@db03 ~]# mv /etc/my.cnf /etc/my.cnf.bak
[root@db03 ~]# mkdir -p /data/3357/data[root@db03 data]# /data/app/mysql/bin/mysqld --initializeinsecure --user=mysql --basedir=/data/app/mysql --
datadir=/data/3357/data
c. 配置文件
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3357/data
socket=/tmp/mysql57.scok
port=3357
server_id=57
log_bin=mysql-bin
d. 启动新环境
[root@db03 bin]# /data/app/mysql/bin/mysqld_safe &
e. 恢复周六数据
f. 拉取源端日志
/data/app/mysql/bin/mysqlbinlog -R --host=10.0.0.51 --
port=3306 --user=root --password=123 --raw --stop-never
mysql-bin.000003 &
g.依次恢复需要的日志
原端： flush logs
目标端：
[root@db03 data]# /data/app/mysql/bin/mysqlbinlog
/data/binlog/mysql-bin.000004 |/data/app/mysql/bin/mysql -
S /tmp/mysql57.sock
断开远端数据库所有连接，将所有剩余binlog，恢复。
h. 目标端inplace升级到8.0
略。
i. 测试应用
j. 应用端割接，重启业务。
1.7 降级
官方解释：
https://dev.mysql.com/doc/refman/5.7/en/downgradepaths.htmlDowngrade from MySQL 5.7 to 5.6 is supported using the
logical downgrade method.
https://dev.mysql.com/doc/refman/5.7/en/downgrade-binarypackage.html#downgrade-procedure-inplace
In-place downgrade is supported for downgrades between GA
releases within the same release series. 5.7.y ---> 5.7.x
## 5.7.28 ---》 5.7.10 inplace downgrade ok


###原环境：
5.7.28 二进制版本 + /data/3306/data
1. 安装 5.7.10 （低） 二进制版本
/data/app/mysql5710
2. 针对5728版本（高）进行处理工作
https://dev.mysql.com/doc/refman/5.7/en/downgrading-toprevious-series.html
set
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,N
O_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,N
O_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77)
COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77)
COLLATE utf8_bin NOT NULL DEFAULT '';
3. 优雅的关闭5.7.28（高）。
set global innodb_fast_shutdown=0 ;
mysqladmin shutdown
4. 删除ib_logfile*
[root@db01 mysql5710]# rm -rf /data/3306/data/ib_logfile*5. 替换配置文件、环境变量 （替换成低版本）
[root@db01 mysql5710]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
#basedir=/data/app/mysql56
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
~
[root@db01 mysql5710]# vim /etc/profile
export PATH=/data/app/mysql5710/bin:$PATH
[root@db01 mysql5710]# source /etc/profile
[root@db01 mysql5710]# mysql -V
mysql Ver 14.14 Distrib 5.7.10, for linux-glibc2.5
(x86_64) using EditLine wrapper
6. 启动数据库
[root@db01 mysql5710]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
7. 执行upgrade
[root@db01 mysql5710]# mysql_upgrade --force
## 5.7.28 ---》 5.6.46 logical downgrade ok
0. 恢复5.7.28 环境
[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*
# 恢复配置文件
[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
#basedir=/data/app/mysql56
datadir=/data/3306/datasocket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
# 恢复环境变量
export PATH=/data/app/mysql/bin:$PATH
[root@db01 data]# source /etc/profile
[root@db01 data]# mysql -V
mysql Ver 14.14 Distrib 5.7.28, for linux-glibc2.12
(x86_64) using EditLine wrapper
# 初始化数据
mysqld --initialize-insecure --user=mysql --
basedir=/data/app/mysql --datadir=/data/3306/data
# 启动数据库
[root@db01 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
SUCCESS!
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28 |
+-----------+
1 row in set (0.00 sec)


1. 安装5.6.46二进制版本软件
/data/app/mysql56
2. 处理5.7.28高版本数据
set
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,N
O_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,N
O_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77)
COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77)
COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT
NULL default '';
ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT
NULL default '';
ALTER TABLE mysql.user MODIFY User char(16) NOT NULL
default '';
ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default
'';
ALTER TABLE mysql.procs_priv MODIFY User char(16) binary
DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD Password char(41) character set
latin1
collate latin1_bin NOT NULL default '' AFTER user;
UPDATE mysql.user SET password = authentication_string
WHERE
LENGTH(authentication_string) = 41 AND plugin =
'mysql_native_password';
UPDATE mysql.user SET authentication_string = '' WHERE
LENGTH(authentication_string) = 41 AND plugin =
'mysql_native_password';
ALTER TABLE mysql.help_category ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_keyword ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_relation ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_topic ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_name ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition_type
ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.plugin ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.servers ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE
utf8_bin
DEFAULT 'mysql_native_password';
DROP DATABASE sys;

3. 逻辑备份5.7.28数据
[root@db01 ~]# mysqldump -A >/tmp/full.sql

4. 初始化一套5.6.46的空环境
[root@db01 ~]# vim /etc/profile
export PATH=/data/app/mysql56/bin:$PATH

[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V
mysql Ver 14.14 Distrib 5.6.46, for linux-glibc2.12
(x86_64) using EditLine wrapper

[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: overwrite ‘/etc/my.cnf.bak’? y
[root@db01 data]# rm -rf /data/3316/data/*
[root@db01 data]#
/data/app/mysql56/scripts/mysql_install_db --user=mysql -
-basedir=/data/app/mysql56 --datadir=/data/3316/data
[root@db01 data]# systemctl start mysqld3316

5. 恢复备份数据到5.6.46中
[root@db01 data]# mysql -S /tmp/mysql3316.sock
mysql> source /tmp/full.sql

2.10 MySQL备份策略定制、备份巡检思路

1. 备份策略：
	a. 备份工具
	逻辑：
	mysqldump ： 数据量较小
	mydumper ： 数据量小，表个数比较多，每个表都不大
	replication ： 延时从库
	mysqlbinlog ： -R --raws --stop-never
	navicat
	binlog2sql
物理：
	PXB
	MEB

存储镜像
	cp冷备

	b. 时间点
		凌晨
	c. 策略
		每天全备+binlog
		每周全备+inc+binlog
		每月全备+inc
2. 恢复策略
	大量数据损坏： 全备+增量+binlog
	部分： 从全备+增量提取备份+部分binlog ，延时从库
3. 定期备份检查和恢复演练
	a. 逻辑备份 ： 检查备份文件 头部 、 抽查备份文件中核心的业务表存在性、数据量。
	b. 物理备份 ： 备份目录下的日志。备份的大小。
	c. 恢复演练 ： 按季度。

1. MySQL 8.0 的日志管理

1.1 general_log

1.1.1 作用

1.1.2查询及配置

1.1.3 使用

1.2 binlog *

1.2.1 作用

1.2.2 配置

1.2.3 使用

日志内容查看

binlog文件内容详细查看

基于Position号进行日志截取

彩蛋： 截取日志痛点

1.2.4 gtid特性

介绍

应用

case:

1.2.5 二进制日志其他操作

自动清理日志

手工清理

日志滚动

1.2.6 binlog2sql应用

1.3 slowlog

1.3.1 作用

1.3.2 配置

1.3.3 分析及使用

1.3.4 pt-query-digest(pqd)应用

安装及介绍：

结果说明：

命令行应用实例：

2.MySQL 8.0的备份、恢复、迁移

2.1备份恢复中的职责

2.2 MySQL8.0备份工具介绍

2.3逻辑备份工具使用-mysqldump ✨

客户端通用参数

备份专用参数

2.4逻辑备份工具使用-mydumper&myloader

2.4.0 介绍

2.4.1 工作原理

2.4.2 参数介绍

2.4.3 使用

2.5逻辑导入导出-load data

2.5.0 功能

2.5.1 造数

2.5.2 语法

2.5.3必选子句或关键字

2.5.4 设置字段顺序

2.5.5 LOCAL关键字

2.5.6 REPLACE与IGNORE关键字

2.5.7 fields关键字应用

字段(列)分隔符应用

字段引用符

转义字符

2.5.8 LINES 关键字应用

行前缀字符串

行结束符

2.5.9FIELDS和LINES注意事项

2.5.10 IGNORE number {LINES | ROWS}子句

2.5.11 SET col_name = expr,…子句

2.5.12使用mysqldump批量导出

2.5.13 使用mysqlimport批量导入

2.6物理备份工具使用-PerconaXtrabackup ✨

2.6.1 安装

2.6.2 全量备份

2.6.3 增量备份

2.7企业级备份工具MEB介绍及使用

2.8 MySQL 8.0 (8.0.17+) Clone-plugin

2.9 MySQL升级及迁移实战

2.10 MySQL备份策略定制、备份巡检思路

彩蛋：截取日志痛点