1-日志分类
2-备份&还原&恢复
- 2.1-备份(backup)&还原(restore)
- 2.3-恢复(recovery)

1-日志分类

4-日志 - 图1

undo redo后面讲 relaylog到主从的时候讲

1.1-general query log

查询日志是记录mysql的所有日志的文件，如果要记录意味着数据会很大，所以默认是开启的。

查看查询日志的变量相关：

show variables like 'general_log%';

因为默认是不会开启全部的查询日志的，所以初次去看的时候这个文件是不存在的

开启查询日志：

set global general_log =1;
show variables like 'general_log%';

随便执行两条sql：

select * from `order` where id =1;
update `order` set order_id = "xxxx" where order_id = "xxxx" ;

查看日志：

cat /var/lib/mysql/ecs-342416-0001.log

select update都会进入

1.2-error log

show variables like 'log_error%'

错误日志默认是开启的

之前我们就出现过错误日志，我们直接去看看内容：

cat /var/log/mysql/error.log

不看不知道，居然有人在扫描我的mysql，所以生产环境一定要设置内网访问做好权限关卡啊！！

1.3-slow query log

慢查询在索引那一小节就讲到过。

show variables like 'slow%';

update order set order_id = “xxxx” where order_id = “xxxx”; 刚刚我们测试查询日志的sql也进入了，因为我们此时的慢查询sql的时间为0.01s，这条sql没有走索引，进行全表扫描，所以耗时较长。

1.4-binnary log

1.4.1-binlog文件使用

binnary log就是大家平时说的binlog，二进制日志了。

show variables like 'log_bin%';//查看日志开启状态和日志位置
show variables like 'server_id%';//

server_id 表示当前数据库实例的id，只有同时保持log_bin=ON和server_id>1 binlog才会生效。是因为在主从复制的时候需要标记数据的来源，所以server_id是要是一个大于0的数，且在整个数据库集群中server_id应该唯一，不然数据同步的时候会出现各种问题。

我这里是mysql8.0 默认就是开启的binlog

在mysql的数据目录下已经有了很多和二进制文件：

查看binlog内容：

 cat /var/lib/mysql/binlog.000031

全是乱码因为binlog为二进制需要专门的命令查看

 mysqlbinlog /var/lib/mysql/binlog.000031

或者重定向到文件中也可以

mysqlbinlog /var/lib/mysql/binlog.000030 > binlog-sql-plain-text.sql
wc  binlog-sql-plain-text.sql

一百多万行下载下来留个底吧免得以后看：scp root@xx.xx.xx.xx:/root/binlog-sql-plain-text.sql ./

binlog-sql-plain-text.sql

1.4.2-管理binlog文件

列出所有二进制文件

show binary logs;

当前正在使用的二进制文件：

show master status;

当前正在使用的是000031这个

切换开启一个新的二进制文件：

flush logs;

在某个特殊的节点我们可能需要手动的操作来完成日志节点标识除了手动执行flush logs会重新生成一个日志文件外，还有mysql重启和单个日志文件大于max_binlog_size也会重新开启一个日志文件。

查看日志文件中的事件：

show binlog events in "binlog.000006";

从Pos(postion)标记位为447的开始往后看日志事件：

show binlog events in "binlog.000006" from 447;

删除binlog日志：

reset master

这里我就不演示了后面我还要做备份还原的

2-备份&还原&恢复

2.1-备份(backup)&还原(restore)

4-日志 - 图20

2.1.1-物理备份&还原

2.1.1.1-cp命令

cp -a /var/lib/mysql /backup

物理备份一般是建议停服，因为在你copy的过程中如果你插入了一条数据，但是刚好这个数据文件copy完成，那么你刚插入的数据就会丢失。
网上都说是停服备份我以为是客观原因只能停服，结果我尝试了在执行这条命令的时候写入和读取操作都是可以进行的。

假设我们删除了数据库中的db为“taobao”的数据库相关信息：

rm -rf /var/lib/mysql/taobao
service restart mysql

再去访问的时候表无法访问

数据库下面“taobao”文件夹已经不在

我们把刚才备份对应的“taobao”文件copy到mysql下面

cp -a /backup/taobao /var/lib/mysql
service mysql restart

数据又回来了

当然你可以删除/var/lib/mysql下面的任何一个文件，然后恢复，甚至全部删除都行。

2.1.2.3-xtrabackup

官方文档：https://docs.percona.com/percona-xtrabackup/8.0/installation/apt_repo.html
percona公司产品，他们的 mysq集群解决方案pcx也很出名。
目前xtrabackup只支持innodb引擎。

 wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/debian/focal/x86_64/percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb
 apt-get install libdbd-mysql-per
 dpkg -i percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb

官方给的那个apt-install的方案的源有点问题我选择直接下载.deb包安装

 xtrabackup --user=root --password=root123456... --parallel=2 --backup  --target-dir=/backup/20220707 --no-timestamp

目前还不支持mysql8.0，尬住了。。。。。。。

2.1.2-逻辑备份&还原

2.1.2.1-mysqldump

mysqldump -u root -p --databases taobao > taobao.sql;

当然还可以全部数据库备份备份指定表或者根据where条件备份某个表的某些数据

备份成功后我删除taobao这个数据库：。

现在我们进行数据库备份还原

mysql -u root -p taobao < taobao.sql

也可以进入mysql终端 source /backup/taobao.sql效果是一样的

数据库正在慢慢恢复，因为order表有1千万条数据，所以再等待order表恢复才能继续往下走：

过了15分钟恢复正常

order中的一千万条数据也回来了

mysqldump 在上面的备份命令上默认锁表，只允许select操作不允许其他操作。

mysqldump -u root -p taobao order > order.sql;

此时向数据库insert操作插入数据，一直在等待，说明mysqldump是锁表的

但是调用api接口访问order表是可以正常访问的（get查询接口），说明mysqldump允许select操作

所以官方也提供了许多其他参数来供你选择，一般来讲最佳方案是：

mysqldump -u root -p --databases taobao 
--flush-logs  
--master-data=2  
--single-transaction  
--routines  
--triggers> taobao.sql;

这样还是会锁表，只是—master-data=2 —single-transaction两个参数保证了备份时候数据的一致性

—flush-logs 开始备份前先手动刷新二进制文件，做个标识
—master-data=2 记录当前备份操作备份到哪个二进制文件的哪个postion点，方便后续还原
—single-transaction 把当前的备份命令当作事务去执行，不影响其他的crud操作
—routines 备份存储过程
—triggers 备份触发器
需要注意的是这样备份还是会锁表，只不过是保证了数据的一致性。

show binary logs;

现在的binlog节点是编号为000047

mysqldump -u root -p --databases taobao --flush-logs  --master-data=2  --single-transaction  --routine  --triggers> taobao.sql;

表示我备份到了编号为0000048 postion为157的这个位置，大于157之后的就没有备份了

看看在157之后进行了什么操作：

show binlog events in "binlog.000048" from 157;

INSERT INTO order(order_id,user_id)VALUES (‘32424234234’,32131); 我在备份完成后手动插入了一条数据，二进制文件可以看出对table进行了写入操作

查看具体操作：

mysqlbinlog /var/lib/mysql/binlog.000048 -v

这对应数据库字段的编号的值，6 7是时间戳因为我数据库字段是时间字段。

也就是说我们可以基于这个备份恢复到编号为000048的position=157的这个位置的，实现增量备份和数据恢复操作，后面会讲。

2.1.2.2-mysqlpump

mysqlpump是一个支持多线程的备份工具，其他的命令我这先不介绍，介绍多线程备份的命令

#全备份大概30s
mysqldump -u root -p --all-databases > taobao.sql
#全备份大概18s
mysqlpump -u root -p --default-parallelism=10  --parallel-schemas=4    - --all-databases> taobao.sql;

如果数据库中的db够多，差距应该会更大。

2.1.3-全量备份

全量备份就是前面的备份全部数据库内容，不管是cp命令还是mysqldump —all-database 或者xtrabackup都可以做到，就是前面已经实现过的。这里不在过多讲解，只是把命令贴出来：

cp -a /var/lib/mysql /backup
cp -a backup /backup /var/lib/mysql

mysqldump -u root -p --all-databases --flush-logs  --master-data=2  --single-transaction  --routines  --triggers> taobao.sql;

mysqlpump -u root -p --default-parallelism=10  --parallel-schemas=4    - --all-databases> taobao.sql;

还不支持8.0改天搞个5.7的补上这里。。。

2.1.4-增量备份

2.1.4.1-基于mysqldump的增量备份

假设我们2022-07-07这天有史以来对数据库进行了第一次的备份：

mysqldump -u root -p --databases taobao --flush-logs  --master-data=2  --single-transaction  --routines  --triggers> taobao.sql;

目前的备份的数据库在binglog.000050 position=157的地方

随着业务的进行，产生了很多的数据：

flush logs;
INSERT INTO `order`(order_id,user_id)VALUES ('00000000000000000000',32131);
INSERT INTO `order`(order_id,user_id)VALUES ('111111111111111111',32131);

后面我们只需要保存binglog.000050（包括）之后的的binlog文件就行了

cp /var/lib/mysql/binlog.000050  /backup/
cp /var/lib/mysql/binlog.000051 /backup/

增量备份就到此结束了

2.1.4.1-基于xtrabackup的增量备份

目前xtrabackup还不支持mysql8.0这个地方后面要搞个mysql5.7的补

2.3-恢复(recovery)

2.3.1-数据磁损坏（一锤子把磁盘锤坏）

要想在磁盘损坏的情况下恢复数据，前提就是:

备份的文件和数据库文件(var/lib/mysql)不在一起，我们假设分开磁盘放。
binlog日志文件和数据库文件也要单独分开，我们假设分开磁盘放。

现在这一行是注释掉的，所以binlog相关的都是直接放在数据目录下：/var/lib/mysql

我们修改二进制文件位置，并且重启服务

service mysql restart

重新更换二进制文件目录，原来数据的二进制文件在原来的地方，这里是重新生成的。也就是说如果你应该从项目开始就这么做不要半途做，不然你都不晓得备份还原的节点是旧的二进制还是新的。

我们假设从这一刻开始是我们第一次使用数据库，我先创建一个新的db插入1百万条数据

create database xiongyao charset=utf8mb4; //utf8mb4编码只是表情包是真正的utf8
CREATE TABLE `order` (
    `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT ( 32 ) DEFAULT 0 COMMENT '下单用户id',
    `order_id` VARCHAR ( 64 ) DEFAULT NULL COMMENT '订单号',
    `status` TINYINT ( 1 ) DEFAULT 1 COMMENT '该笔交易的状态0进行中；1支付完成；2支付失败；3取消支付；',
    `pay_type` TINYINT ( 1 ) DEFAULT 1 COMMENT '1支付宝2微信3银行卡',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

import os
import sys
import random
import pymysql.cursors
import uuid
def handler():
    mysql_connect = pymysql.connect(
        host="",
        user="root",
        port=3306,
        password="",
        database="xiongyao",
        cursorclass=pymysql.cursors.DictCursor,
    )
    with mysql_connect.cursor() as mysql_cursor:
        # 每次插入10000条
        values = "('c02f8f06b6bc468892328ab97c212562',32120,1,2)"
        for i in range(10000):
            order_id = str(uuid.uuid4()).replace("-", "")
            user_id = random.randint(1, 100000)
            # 从数仓数据库查询数据
            values += f",('{order_id}',{user_id},1,2)"
        sql_string = f"INSERT INTO `order`(order_id,user_id,`status`,pay_type) VALUES {values};"
        # print(sql_string)
        mysql_cursor.execute(sql_string, args=())
    mysql_connect.commit()
    mysql_connect.close()
if __name__ == "__main__":
    print(f"开始时间：{datetime.datetime.now()}")
    from multiprocessing import Pool
    workers = os.cpu_count() * 2 + 1
    p = Pool(workers)
    # 100个任务，每个任务同时插入1w条数据=1百万条数据
    for i in range(0, 100):
        p.apply_async(handler, args=())
    p.close()
    p.join()
    #开始时间：2022-07-07 23:36:44.011341
    #结束时间：2022-07-07 23:37:36.939952
    print(f"结束时间：{datetime.datetime.now()}")

假设我们数据库服务上线了，第一天晚上进行了正常的全备份：

#物理全备份
cp -a /var/lib/mysql /backup/mysql-backup-physics
#逻辑全备份
mysqldump -u root -p --all-databases --flush-logs  --master-data=2  --single-transaction  --routine  --triggers > all-database.sql;

第二天白天产生100万条的业务数据：

用上面的python再生成100万条数据

第二天晚上进行的是增量备份：
先查看上次的mysqldump逻辑备份的时候备份到哪个binglog节点了

所以我们要保存000003和它之后的binlog文件

cp  /var/log/mysql/mysql-bin.000003 /backup

第三天白天上午业务发生了50万条数据：

用上面的python再生成50万条数据

记住此时一同250万条数据

突然此时物理磁盘被炸弹炸坏了。。。。。

rm -rf /var/lib/mysql

/var/lib下面没有mysql这个目录了

再也连不上去了

但是实际情况是我删除了数据目录但是还是能上去的，过一会才不能上去。

开始恢复操作。。。。。

cp -a /backup/mysql-backup-physics /var/lib
cd /var/lib
mv mysql-backup-physics mysql

重启mysql

 service mysql restart

现在数据库是100万条数据，是对的，因为我们上次无力备份的时候就是一百万数据。
下面我们需要进行增量备份的还原，先把00003之后的二进制文件全部拉出来

/var/log/mysql/mysql-bin.000004 /backup
/var/log/mysql/mysql-bin.000005 /backup

开始还原000003 000004 000005的文件内容

mysqlbinlog --start-position=157  mysql-bin.000003 >  mysql-bin-000003-diff.sql

mysqlbinlog  mysql-bin.000004 >  mysql-bin-000004.sql
mysqlbinlog  mysql-bin.000005 >  mysql-bin-000005.sql

恢复：

mysql -uroot -p < mysql-bin-000003-diff.sql
mysql -uroot -p < mysql-bin-000004.sql
mysql -uroot -p < mysql-bin-000005.sql

恢复完成

2.3.2-程序员删库跑路(drop database)

drop database xiongyao;

开始恢复操作。。。

cp /var/log/mysql/mysql-bin.000001 /backup
cp /var/log/mysql/mysql-bin.000002 /backup

我们之前是进行的全备份到000003节点，所以我们缺少两个文件，要补齐。

mysqlbinlog  --database=xiongyao mysql-bin.000001 >  xiongyao-000001.sql
mysqlbinlog  --database=xiongyao mysql-bin.000002 >  xiongyao-000002.sql
mysqlbinlog  --database=xiongyao mysql-bin.000003 >  xiongyao-000003.sql
mysqlbinlog  --database=xiongyao mysql-bin.000004 >  xiongyao-000004.sql
mysqlbinlog  --database=xiongyao mysql-bin.000005 >  xiongyao-000005.sql

还原

mysql -uroot -p < xiongyao-000001.sql
mysql -uroot -p < xiongyao-000002.sql
mysql -uroot -p < xiongyao-000003.sql
mysql -uroot -p < xiongyao-000004.sql
mysql -uroot -p < xiongyao-000005.sql

过程中还遇到了max_allowed_packet过小导致sql执行失败的问题，我是修改配置文件重启服务后直成功的

2.3.3-程序员误操作

在实际的过程中误操作delete和update没有加条件导致操作失误的问题很常见，我自己也犯过错

 select * from `order` where user_id=1681 ;

操作前一共23条数据

+---------+---------+----------------------------------+--------+----------+---------------------+---------------------+
| id      | user_id | order_id                         | status | pay_type | created_at          | updated_at          |
+---------+---------+----------------------------------+--------+----------+---------------------+---------------------+
|  920095 |    1681 | f5866774acea4bf9a7ecc8f8b7e1a11e |      1 |        2 | 2022-07-07 23:40:48 | 2022-07-07 23:40:48 |
| 1019183 |    1681 | 600f6278dae448768847d74f516fa822 |      1 |        2 | 2022-07-07 23:40:52 | 2022-07-07 23:40:52 |
| 1031722 |    1681 | 4d2939d6546349a68366909cf417dd7f |      1 |        2 | 2022-07-07 23:40:53 | 2022-07-07 23:40:53 |
| 1219841 |    1681 | 63a06f3777d746c9b1fc1fc2da0d4b02 |      1 |        2 | 2022-07-07 23:41:02 | 2022-07-07 23:41:02 |
| 1244914 |    1681 | ed346cd9ae064fd68eacdb354552ed69 |      1 |        2 | 2022-07-07 23:41:06 | 2022-07-07 23:41:06 |
| 1271526 |    1681 | 2150ff9debe14e518eeeb3f58d3530bf |      1 |        2 | 2022-07-07 23:41:06 | 2022-07-07 23:41:06 |
| 1396173 |    1681 | a1c53ca3ac91416baf21e95405abaa2f |      1 |        2 | 2022-07-07 23:41:14 | 2022-07-07 23:41:14 |
| 1485125 |    1681 | 7212c2c12da746e984c9a75cb966b940 |      1 |        2 | 2022-07-07 23:41:19 | 2022-07-07 23:41:19 |
| 1571910 |    1681 | a2173eaa135548a2be919921f9b02435 |      1 |        2 | 2022-07-07 23:41:24 | 2022-07-07 23:41:24 |
| 1787781 |    1681 | 27c577c4d3784d9b8689f6900b736c14 |      1 |        2 | 2022-07-07 23:41:34 | 2022-07-07 23:41:34 |
| 1789536 |    1681 | b2f86a0e837a47dc828c5c443f52adda |      1 |        2 | 2022-07-07 23:41:34 | 2022-07-07 23:41:34 |
| 2141209 |    1681 | 226d3b1ef3cf49b5bfb9d7126b63174b |      1 |        2 | 2022-07-07 23:51:03 | 2022-07-07 23:51:03 |
| 2348930 |    1681 | 753d6a6f27c446dd9fd4cb9ea6d2cb5e |      1 |        2 | 2022-07-07 23:51:11 | 2022-07-07 23:51:11 |
| 2533803 |    1681 | 57e14299d42e4c10aa10e870f3d152e8 |      1 |        2 | 2022-07-07 23:51:23 | 2022-07-07 23:51:23 |
| 2550198 |    1681 | 4887287156494cc7967e614f060e8323 |      1 |        2 | 2022-07-07 23:51:24 | 2022-07-07 23:51:24 |
| 2569084 |    1681 | b724008b97204b408f9d4217cdd42b9a |      1 |        2 | 2022-07-07 23:51:24 | 2022-07-07 23:51:24 |
| 2571130 |    1681 | 24f5c935ffc74eee8c9691786ea0b8cf |      1 |        2 | 2022-07-07 23:51:24 | 2022-07-07 23:51:24 |
| 2870748 |    1681 | 3c86abe555e3474eb52665c9503904e3 |      1 |        2 | 2022-07-07 23:51:37 | 2022-07-07 23:51:37 |
| 3018927 |    1681 | b0b873d4a46a4cfeb12bad08de42ec25 |      1 |        2 | 2022-07-07 23:58:29 | 2022-07-07 23:58:29 |
| 3105966 |    1681 | 1f6337a231bf47269b29eb115078705e |      1 |        2 | 2022-07-07 23:58:34 | 2022-07-07 23:58:34 |
| 3232810 |    1681 | ffac16b7b84a44a4985b74bedc9281f2 |      1 |        2 | 2022-07-07 23:58:39 | 2022-07-07 23:58:39 |
| 3293738 |    1681 | ad3062050d9b49ed9d12d5b2bb86f1a6 |      1 |        2 | 2022-07-07 23:58:42 | 2022-07-07 23:58:42 |
| 3394882 |    1681 | e338b53362f64714bdd5830f51624b32 |      1 |        2 | 2022-07-07 23:58:45 | 2022-07-07 23:58:45 |
+---------+---------+----------------------------------+--------+----------+---------------------+---------------------+

误操作:

update `order` set order_id = "000000000001" where user_id = 1681;

本来我只是想update user_id=1681和order_id=e338b53362f64714bdd5830f51624b32的数据的，但是我少了一个order_id=e338b53362f64714bdd5830f51624b32条件

开始恢复

show master status;

mysqlbinlog  --start-datetime='2022-07-08 01:00:00' --stop-datetime='2022-07-08 02:00:00'  --database=xiongyao /var/log/mysql/mysql-bin.000010 -v

order_update.sql

找到原始sql怎么还原都可以。