Mysql数据库的备份和恢复及日志管理

一、数据备份概述

1.1 备份的分类

完全备份：整个数据库完整地进行备份
增量备份：在完全备份的基础之上，对后续新增的内容进行备份

冷备份：关机备份，停止mysql服务，然后进行备份
热备份：开机备份，无需关闭mysql服务，然后进行备份

物理备份：对数据库系统的物理文件(数据文件、日志文件)进行备份
逻辑备份：只是对数据库的逻辑组件进行备份(表结构)，以sql语句的形式，把库、表机构、表数据进行备份保存 (直接在数据库系统中删除全部文件，逻辑备份无法恢复)

1.2 数据需要备份的原因

1、在生产环境中，数据的安全至关重要，任何数据的丢失都会产生非常严重的后果
2、数据为何丢失？
程序操作、运算错误、磁盘故障、不可预期的时间、人为操作

二、数据备份操作

2.1 物理冷备份

物理备份：一般采用完全备份，对整个数据库进行完整的打包备份
优点：操作简单
缺点：占用空间太大、备份和恢复的时间很长、需要暂停数据库服务避免有新的数据进入被覆盖，也可能导致恢复失败

创建两个新的库pup、pup1：
create database pup;
create database pup1;
 
pup、pup1创建相同表info1并插入相同数据：
 
create table if not exists info1 (
id int(4) primary key,
name char(5),
hobby char(5)
);

[root@mysql1 ~]# systemctl stop mysqld
[root@mysql1 ~]# cd /usr/local/mysql/
[root@mysql1 mysql]# yum -y install xz
[root@mysql1 mysql]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
[root@mysql1 mysql]# cd /opt
[root@mysql1 opt]# tar Jxvf mysql_all_2023-11-06.tar.xz 
[root@mysql1 opt]# cd /usr/local/mysql/
[root@mysql1 mysql]# rm -rf data
[root@mysql1 mysql]# cd /opt/usr/local/mysql/
[root@mysql1 mysql]# cp -a data/ /usr/local/mysql/
[root@mysql1 mysql]# systemctl restart mysqld.service 

2.2 主机间数据迁移

mysql2虚拟机：
[root@mysql2 mysql]# cd /usr/local/mysql/
[root@mysql2 mysql]# rm -rf data/
[root@mysql2 mysql]# scp -r root@20.0.0.81:/opt/usr/local/mysql/data /opt
[root@mysql2 mysql]# cd /opt
[root@mysql2 opt]# cp -a data/ /usr/local/mysql/
[root@mysql2 opt]# systemctl restart mysqld.service
[root@mysql2 opt]# cd /usr/local/mysql/data/
[root@mysql2 data]# chown -R mysql.mysql pup pup1

2.3 逻辑热备份

mysqldump：Mysql自带的备份文件的命令
特点：方便、简单，但是只能基于逻辑上的表结构和表数据恢复
物理删除之后再用逻辑恢复会报错，也可以作为数据迁移
占用大空间，比较物理备份相对来说占的空间要小得多

Mysql自带备份工具：mysqldump
 
备份一个库：mysqldump -u root -p --databases 库名 > /opt/备份名.sql (名称自定义，要以.sql结尾)
备份多个库：mysqldump -u root -p --databases 库名1 库名2 > /opt/备份名.sql
备份所有库：mysqldump -u root -p --all-databases > /opt/备份名.sql
(只是逻辑意义上)

备份恢复一个库 

[root@mysql1 ~]# systemctl restart mysqld.service 
[root@mysql1 ~]# netstat -antp | grep 3306
[root@mysql1 ~]# mysqldump -u root -p --databases pup > /opt/pup.sql
[root@mysql1 ~]# mysql -u root -p -e 'drop database pup;'
#-e：指定连接库，执行命令后即刻退出
#只能逻辑删除，物理删除/usr/local/mysql/data目录下的库文件无法恢复
[root@mysql1 ~]# mysql -u root -p < /opt/pup.sql 

 备份恢复多个库

[root@mysql1 ~]# systemctl restart mysqld.service 
[root@mysql1 ~]# netstat -antp | grep 3306
[root@mysql1 ~]# mysqldump -u root -p --databases pup pup1 > /opt/pup+pup1.sql
[root@mysql1 ~]# mysql -u root -p -e 'drop database pup;'
[root@mysql1 ~]# mysql -u root -p -e 'drop database pup1;'
[root@mysql1 ~]# mysql -u root -p < /opt/pup+pup1.sql 

备份恢复指定库指定表

备份表info1：
[root@mysql1 data]# mysqldump -u root -p pup info1 > /opt/pup_info1.sql
[root@mysql1 data]# mysql -u root -p -e 'drop table pup.info1;'

恢复：
[root@mysql1 data]# mysql -u root -p pup < /opt/pup_info1.sql

恢复时，不同的库也可以读取备份内容：
[root@mysql1 data]# mysql -u root -p pup1 < /opt/pup_info1.sql

备份恢复指定库多个表

 pup库新建表info2

将表info1、info2备份在一个文件中：
[root@mysql1 data]# mysqldump -u root -p pup info1 info2 > /opt/pup_info1-2.sql
[root@mysql1 data]# mysql -u root -p -e 'drop table pup.info1;' 
[root@mysql1 data]# mysql -u root -p -e 'drop table pup.info2;'

恢复：
[root@mysql1 opt]# mysql -u root -p pup < /opt/pup_info1-2.sql

主机间数据迁移

[root@mysql1 opt]# mysqldump -u root -p --all-databases > /opt/all_databases.sql
 
主机2创建库pup和pup2，pup和主机1中的库重名，以此进行对照

主机2使用主机1的所有库备份信息进行恢复：
[root@mysql2 ~]# scp root@20.0.0.81:/opt/all_databases.sql /opt
[root@mysql2 ~]# mysql -u root -p < /opt/all_databases.sql

结果：

 

结论：
当数据在主机间进行迁移时，有同名的库其数据不会被覆盖，没有的库及其数据会迁移进来

2.4 增量备份

mysqldump也支持增量备份
优点：没有重复数据，备份量小，时间短
缺点：备份恢复时锁表，必然会影响业务，超过10G耗时会比较长，导致服务不可用

Mysql二进制日志记录格式有三种：

• 1、STATEMENT：基于sql语句 记录修改的sql语句，高并发情况下，记录sql语句时候的顺序可能会出错，恢复数据时可能会导致丢失和误差，效率比较高
• 2、ROW：基于行 精准记录每一行的数据，准确率高，但是恢复效率低
• 3、MIXED：既可以根据sql语句也可以根据行 在正常情况下使用STATEMENT，一旦发生高并发，会智能自动切换至ROW模式

实操：

Mysql提供的二进制日志间接地实现增量备份
如何获取此二进制文件？
[root@mysql1 opt]# vim /etc/my.cnf
--插入--
log-bin=mysql-bin
binlog_format=MIXED
 
[root@mysql1 opt]# systemctl restart mysqld.service 

查看备份的二进制日志文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

刷新并创建新的断点 

mysqladmin -u root -p flush-logs

恢复数据

mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p

基于位置点恢复数据

基于位置点来进行恢复，三种方式：
​​​​​​​从某一个点开始，恢复到最后：mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
 
从开头恢复到某个位置：mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
 
从指定点恢复到指定结束点：mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

从起始点开始恢复

以插入数据4之前为起点，恢复4、5、6

mysqlbinlog --no-defaults --start-position='296' mysql-bin.000001 | mysql -u root -p

从开头恢复到指点结束点

删除4、5、6，以插入完数据6为终点，恢复4、5、6

mysqlbinlog --no-defaults --stop-position='1096' mysql-bin.000001 | mysql -u root -p

从指定起始点恢复到指定结束点

删除4、5、6，以插入数据5前为起点，插入完数据6为终点，恢复5、6

mysqlbinlog --no-defaults --start-position='610' --stop-position='1096' mysql-bin.000001 | mysql -u root -p

基于时间点恢复数据

从起始时间开始恢复

mysqlbinlog --no-defaults --start-datetime='时间点' 文件 | mysql -u root -p

从开头恢复到指定结束时间

mysqlbinlog --no-defaults --stop-datetime='时间点' 文件 | mysql -u root -p

从指定起始时间恢复到指定结束时间

mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 文件名 | mysql -u root -p

三、Mysql日志管理

3.1 记录错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
vim /etc/my.cnf
--添加--
log-error=/usr/local/mysql/data/mysql_error.log	

3.2 通用日志

用来记录MySQL的所有连接和语句，默认是关闭的
 vim /etc/my.cnf
--添加--
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
 
查看通用日志是否开启：
show variables like 'general%'

3.3 二进制日志(binlog)

用来记录所有当MySQL启动、停止或运行时发送的错误信息，默认是关闭的
 vim /etc/my.cnf
--添加--
log-bin=mysql-bin
 
查看二进制日志是否开启：
show variables like 'log_bin%';

3.4 慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
慢查询日志也可用来查询哪些搜索的字段超时，可以选择是否需要增加索引，加快查询速度。
vim /etc/my.cnf
--添加--
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5  #修改慢查询时间
 
查看慢查询是否开启：
show variables like '%slow%';
查看慢查询时间：
show variables like 'long_query_time';