MySQL之日志 +备份+恢复

一、数据备份的重要性

1. 在生产环境中，数据的安全性至关重要
2. 任何数据的丢失都可能产生严重的后果
3. 备份的主要目的是灾难恢复
4. 造成数据丢失的原因

① 人为操作错误
② 运算错误
③ 磁盘故障
④ 灾难（如火灾，地震）和盗窃

二、MySQL日志

2.1日志保存位置

1. 常用的日志文件在（/etc/my.cnf中[mysqld] 客户端配置中修改）
2. 默认在 /usr/local/mysql/data/中

2.2 日志类型与作用

1. redo 重做日志：达到事务一致性（每次重启会重做）
   作用:确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redolog执行重做,达到事务一致性

2. undo回滚日志

作用:保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交。隔离级别就是通过mvcc+undo实现

3. errorlog错误日志
   用于记录Mysql 启动、停止、或运行时产生的错误信息，默认开启

可通过已下字段进行更新：

log-error=/usr/local/mysql/data/mysql_error.log

2.3 指定日志的保存位置和文件名

1. slow auery log 慢查询日志

作用:记录执行时间过长的sql，时间阈值可以配置，只记录执行成功

另一个作用：在于提醒优化
用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认时关闭的

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
##指定文件路径和名称
long_query_time=5
#设置执行超过5秒的语句会被记录，缺省时为10秒

2. bin log二进制日志
   作用:用于主从复制，实现主从同步
   记录的内容是：数据库中执行的sql语句

开启方式：

log-bin=mysql-bin
或者
log_bin=mysql-bin

3. relay log中继志
   作用:用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放

4. general log普通日志
   作用:记录数据库的操作明细，默认关闭，开启后会降低数据库性能

5. 通用查询日志
   用来记录MySQL的所有连接和语句，默认是关闭的

general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

6. 重启mysql访问

[root@localhost ~]# systemctl restart mysqld.service

三、查看日志状态

3.1 查看通用查询日志是否开启

mysql -u root -p   //登录数据库
show variables like 'general%'   //查看通用查询日志是否开启

3.2 查看二进制文件是否开启

show varoiables like 'log_bin%';  //查看二进制是否开启

3.3 查看慢查询日志

show variables like '%slow%';  // 查看慢查询日志是否开启

3.4 查看慢查询时间设置

mysql> show variables like '%slow%';

3.5 在数据库中设置开启慢查询的方法

set global show_query_log=ON

四、mysql 完全备份与恢复

4.1 数据备份的重要性

1. 备份的目的时灾难恢复
2. 在生产环境中，数据的安全性至关重要
3. 任何数据的丢失都可能产生严重的后果

4.2 造成数据丢失的原因

程序错误、人为操作错误、运算错误、磁盘故障、灾难

4.3 数据库备份的分类

从物理与逻辑的角度分类

(1)物理备份

对数据库操作系统的物理文件如（数据文件、日志文件）等的备份
物理备份的方法：

  * 冷备份：关闭数据库的时候进行。
  + 热备份：数据库处于运行状态，依赖于数据库的日志文件。
  + 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

（2)逻辑备份

1. 对数据库逻辑组件(表数据库对象）的备份

(3)数据库的备份分类

1. 完全备份
   每次对数据进行完整的备份，对整个数据库、数据库结构和文件结构的备份，是差异备份与增量备份的基础数据存在大量的重复，并且会占用大量的磁盘空间，备份时间长

2. 差异备份
   备份那些自从上次完全备份之后修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大，恢复数据时，只需恢复上次的完全备份与最近一次的差异备份

3. 增量备份
   只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
   以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量下，占用空间下，备份速度快。但恢复时，需要上一次的完整备份开始到最后一次增量备份的所有增量依次恢复如如中间某次的备份数据损坏，将导致数据的丢失

4.4 常见的备份方法

1. 物理冷备份
   需要在数据库处于关闭状态下，保证数据库的完整性
   优点： 速度快，恢复时也是最为简单的
   直接打包数据库文件夹/usr/local/mysql/data)来实现备份

2. 专用备份工具Mysqldump或Mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做备份
mysqldump 是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句
mysqldump 更为通用，可备份各种表
mysqlhotcopy 仅适用于某些存储引擎

3. 启用二进制日志进行增量备份mysql
   支持增量备份，进行增量备份时必须启用二进制日志，二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复进行增量备份，需要刷新二进制日志

4. 第三方工具备份
   免费的Mysql热备份软件 Percona XtraBackup
   支持在线热备份 Innodb 和XtraDB ,也可支持Mysql表备份，
   不过myISAM 表的备份要在表锁得情况下进行

五.Mysql 完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

5.1 物理冷备份与恢复

systemctl stop mysqld
yum -y install xz					//xz是一个压缩工具
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar Jxvf /opt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data
systemctl start mysqld

5.2 mysqldump备份与恢复

1. 完全备份一个或多个完整的库（包括其中所有的表）

//导出的备份文件就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
例：
mysqldump -u root -p --databases school > /opt/school.sql
mysqldump -u root -p --databases mysql school > /opt/mysql-school.sql

2. 完全备份mysql服务器中的所有库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

mysqldump -u root -p --all-databases > /备份路径/备份文件名.sql

例：
mysqldump -u root -p --all-databases > /opt/all.sql

3. 完全备份指定库中的部分表

mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“ -d ”选项，说明只保存数据库的表结构
#不使用“ -d ”选项，说明表数据也进行备份
例：
mysqldump -u root -p -d school test1 > /opt/school_test1.sq1

4. 查看备份文件

cat /opt/备份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"
例：
cat /opt/school_class.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

5. 恢复表的操作
   ① 恢复表时同样可以使用source或者mysql命令
   ② source恢复表的操作与恢复库的操作相同
   登录到MySQL数据库
   执行source备份sql脚本的路径
   
   ③ 当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

mysql -u 用户名-p [密码] < 表备份脚本的路径
mysql -u root -p mysql < /backup/mysql-user.sql

6. 在生产环境中，可以使用Shell脚本自动实现定时备份（时间频率需要确认）
   使用mysql命令示例：

[root@localhost ~]# mysqldump -u root -p123456  --databases test > /opt/test.sql #备份
[root@localhost ~]# mysql -uroot -p123456 -e 'drop database test;'               #删除
[root@localhost ~]# mysql -uroot -p123456 < /opt/test.sql                        #恢复

PS:mysqldump 严格来说属于温备份，会需要对表进行写入的锁定

在全量备份与恢复实验中，假设现有yunwei库，yunwei库中有一个test1表，需要注意的一点为：

① 当备份时加 --databases ,表示针对于yunwei库

1. 备份命令

mysqldump -uroot -p123123 --databases yunwei > /opt/yunwei_01.sql 

2. 恢复命令过程为：

mysql -uroot -p123123 
drop database yunwei;
exit
mysql -uroot -p123123  < /opt/yunwei_01.sql

② 当备份时不加 --databases，表示针对yunwei库下的所有表

1. 备份命令

[root@localhost bak]# mysql -uroot -p123456 < /opt/bak/mysql_yunwei02.sql 

2. 删除

[root@localhost bak]# mysql -uroot -p123456 -e 'drop database yunwei;'

3. 恢复过程：

[root@localhost bak]# mysql -uroot -p123456  -e 'create database yunwei;'  #需先创建表（也可以创建其他库，表直接导入其他库即可，这样就可以把多张表移动到另外一个库里面）
[root@localhost bak]# mysql -uroot -p123456 yunwei < /opt/bak/mysql_yunwei02.sql  #恢复
[root@localhost bak]# mysql -uroot -p123456 -e 'show tables from yunwei;'  #参看yunwei库中的表

六、mysql 完全恢复

6.1 恢复数据库

#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'drop database school;'
mysql -u root -p -e 'show databases;'

mysql -u root -p < /opt/school.sql
mysql -u root -p -e 'show databases;'

6.2 恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

mysql -u root -p -e 'drop table school.test2;'
mysql -u root -p -e 'show tables from school;'

mysql -u root -p school < /opt/school_test2.sql
mysql -u root -p -e 'show tables from school;'

七、mysql 增量备份

7.1 Mysql 增量备份

1. 开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

2. 二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld

cd /usr/local/mysql/data/
 ls

ls -l /usr/local/mysql/data/mysql-bin.*

7.2 可每周对数据库或表进行完全备份

mysqldump -u root -p school test1 > /opt/school_test1_$(date +%F).sql
mysqldump -u root -p school > /opt/school_$(date +%F).sql

 
 crontab -e
0 2 * * 1 mysqldump -u root -p school test1 > /opt/school_test1_$(date +%F).sql

0 2 * * 1 mysqldump -u root -p school > /opt/school_$(date +%F).sql

7.3 可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

7.4 插入新数据，以模拟数据的增加或变更

mysql -u root -p
use school;
show tables;

insert into test1 values(3,'王五',40),(4,'李六',50);

7.5 再次生成新的二进制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

7.6 查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

八、增量恢复

8.1 Mysql 增量恢复

1. 一般恢复
   ① 模拟丢失更改的数据的恢复步骤

mysql -u root -p123456
use school;
delete from test1 where id=3;
delete from test1 where id=4;

select * from test1;

 mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
mysql -u root -p -e 'use school;select * from test1';

8.2 模拟丢失所有数据的恢复步骤

mysql -u root -p123456

use school;
show tables;
drop table test1;
show tables;

 mysql -u root -p school < /opt/school_test1_2021-04-15.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

8.3 断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

# at 4
#210415 23:26:24 server id 1  end_log_pos 123 CRC32 0x56b1b3a0 	Start: binlog v 4, server v 5.7.20-log created 210415 23:26:24
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210415 23:26:24 server id 1  end_log_pos 154 CRC32 0xa2773434 	Previous-GTIDs
# [empty]
# at 154
#210415 23:32:12 server id 1  end_log_pos 219 CRC32 0xa641e810 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210415 23:32:12 server id 1  end_log_pos 366 CRC32 0xf6614805 	Query	thread_id=73	exec_time=0	error_code=0
use `school`/*!*/;
SET TIMESTAMP=1618500732/*!*/;
SET @@session.pseudo_thread_id=73/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test6(
id int(5) not null,
name varchar(40),
age int(5))
/*!*/;
# at 366
#210415 23:33:57 server id 1  end_log_pos 431 CRC32 0xdf1b202e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 431
#210415 23:33:57 server id 1  end_log_pos 514 CRC32 0xd2405743 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500837/*!*/;
BEGIN
/*!*/;
# at 514
#210415 23:33:57 server id 1  end_log_pos 679 CRC32 0xf6c239c2 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500837/*!*/;
insert into test6 values(1,'张三',10),(2,'李四',20),(3,'王五',30),(4,'李六',40)
/*!*/;
# at 679
#210415 23:33:57 server id 1  end_log_pos 710 CRC32 0xd3f77820 	Xid = 848
COMMIT/*!*/;
# at 710
#210415 23:36:03 server id 1  end_log_pos 775 CRC32 0x49540414 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 775
#210415 23:36:03 server id 1  end_log_pos 858 CRC32 0xf5e69fc1 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500963/*!*/;
BEGIN
/*!*/;
# at 858
#210415 23:36:03 server id 1  end_log_pos 964 CRC32 0x95f76436 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500963/*!*/;
delete from test6 where id=1
/*!*/;
# at 964
#210415 23:36:03 server id 1  end_log_pos 995 CRC32 0xc0f11b56 	Xid = 850
COMMIT/*!*/;
# at 995
#210415 23:36:41 server id 1  end_log_pos 1060 CRC32 0x8e8f14df 	Anonymous_GTID	last_committed=3 sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1060
#210415 23:36:41 server id 1  end_log_pos 1143 CRC32 0x6b1fc9ea 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618501001/*!*/;
BEGIN
/*!*/;
# at 1143
#210415 23:36:41 server id 1  end_log_pos 1260 CRC32 0xe432fdeb 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618501001/*!*/;
insert into test6 values(5,'赵七',50)
/*!*/;
# at 1260
#210415 23:36:41 server id 1  end_log_pos 1291 CRC32 0x0faff734 	Xid = 851
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqladmin -u root -p flush-logs
cd /usr/local/mysql/data

mysql -u root -p123456


mysqladmin -u root -p flush-logs

cp /usr/local/mysql/data/mysql-bin.000009 /opt
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000009

8.4 基于位置恢复

1. 仅恢复到操作 ID 为“858”之前的数据，即不恢复“858”的数据

mysqlbinlog --no-defaults --stop-position='858' /opt/mysql-bin.000009 | mysql -uroot -p

mysql -u root -p123456 -e 'use school;select * from test6;'

仅恢复“1143”的数据，跳过“858”的数据恢复

 mysqlbinlog --no-defaults --start-position='1143' /opt/mysql-bin.000009 | mysql -uroot -p

mysql -u root -p123456 -e 'use school;select * from test6;'

2. 基于时间点恢复
   仅恢复到 2021-04-15 23:36:03’之前的数据，即不恢复“本条”的数据

mysqlbinlog --no-defaults --stop-datetime='2021-04-15 23:36:03' /opt/mysql-bin.000009 |mysql -uroot -p

mysql -u root -p -e 'use school;select * from test6;'