【MySQL】数据库——备份与恢复，日志管理1

一、数据备份的重要性

1.备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因：

程序错误
人为,操作错误
运算错误
磁盘故障
灾难（如火灾、地震）和盗窃

2.数据库备份的分类

物理备份:直接对数据库的数据文件或者日志文件进行备份.

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

逻辑备份:对数据库的库或表对象进行备份.

即以sql语句的形式，把库、表结构、表数据保存下来

数据库的备份策略：

完全备份（全量备份）：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

二、MySQL的完全备份与恢复

物理冷备份（完全备份）与恢复 tar压缩打包

systemctl stop mysqld
mkdir /opt/backup    #将数据库备份文件保存到/opt/backup目录下
[root@localhost backup]# cd /usr/local/mysql/
[root@localhost mysql]# tar zcf /opt/backup/mysql_data-$(date +%F).tar.gz data/
[root@localhost mysql]# cd /opt/backup/
[root@localhost backup]# ls
mysql_data-2024-06-26.tar.gz
[root@localhost backup]# scp mysql_data-2024-06-26.tar.gz 192.168.100.18:~/#发送给备份数据文件的主机的根目录下~/

再在数据库备份主机（192.168.100.18）操作恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# ls[root@localhost ~]# tar xf mysql_data-2024-06-26.tar.gz 
[root@localhost ~]# ls[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ls[root@localhost mysql]# mv data/ data_old
[root@localhost mysql]# mv ~/data/ data
[root@localhost mysql]# ls[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# mysql -uroot -pabcd1234

mysqldump进行逻辑备份

mysqldump备份数据库

备份指定数据库中的文件：
mysqldump -u用户名 -p密码数据库名 > 指定路径的绝对路径/数据库名.sql

备份指定数据库和文件：
mysqldump -u用户名 -p密码 --databases 数据库名 > 指定路径的绝对路径/数据库名.sql

备份多个库及文件
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > 指定路径的绝对路径/数据库名.sql

备份所有数据库文件：
mysqldump -u用户名 -p密码 --all-databases > 指定路径的绝对路径/数据库名.sql

逻辑热备份（完全备份）与恢复

mysqldump实现逻辑备份
mysqldump -u用户名 -p密码 --databases 库1 [库2 ...] > /存储路径/xxx.sql
##备份库以及库中所有表的表数据及表结构mysqldump -u用户名 -p密码 --all-databases  > /存储路径/xxx.sql
##备份所有的库以及其表数据mysqldump -u用户名 -p密码 库名 > /存储路径/xxx.sql
##只备份指定库中的所有表数据，不包含库对象mysqldump -u用户名 -p密码 库名 表1 [表2 ] > /存储路径/xxx.sql
##备份指定库中的单个或多个表的数据和表结构，不包含库对象mysqldump -u用户名 -p密码 -d 库名 表1 [表2 ] > /存储路径/xxx.sql
##只备份指定库中的单个或多个表的结构，不包含库对象，也不包含表的数据

恢复

方法一：
先登录到数据库，然后使用source +/存储路径/xxx.sql 进行恢复（如果sql文件是只备份了表数据的文件，那么需要先创建库，并use切换库后才能执行该命令）方法二：
使用sql语句来进行恢复
mysql -u用户名 -p密码 < /存储路径/xxx.sql
cat /存储路径/xxx.sql | mysql -u用户名 -p密码 
##恢复库
mysql -u用户名 -p密码  指定库名 < /存储路径/xxx.sql
cat /存储路径/xxx.sql | mysql -u用户名 -p密码 指定库名 
##恢复库中的表，且需要库是存在的

三、MySQL的增量备份与恢复

增量备份是采用的物理二进制日志文件分割来完成增量备份的
启用相关日志需要编辑 /etc/my.cnf 文件内容，末行添加以下内容

vim /etc/my.cnf#开错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#开通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#开二进制日志
log_bin=mysql_bin
#开启慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2

重启mysql并查看日志

[root@zx1 backup]# systemctl restart mysqld
[root@zx1 backup]# cd /usr/local/mysql/data/
[root@zx1 data]# ls
auto.cnf         ib_logfile0       mysql_general.log     sys
ca-key.pem       ib_logfile1       mysql_slow_query.log  zx
ca.pem           ibtmp1            performance_schema    zx1
client-cert.pem  mysql             private_key.pem       zx2
client-key.pem   mysql_bin.000001  public_key.pem
ib_buffer_pool   mysql_bin.index   server-cert.pem
ibdata1          mysql_error.log   server-key.pem
[root@zx1 data]# vim /etc/my.cnf
[root@zx1 data]#

刷新生成二进制日志文件的两种方式：

（1）重启数据库服务：systemctl restart mysqld;

（2）flush-logs命令刷新：mysqladmin -uroot -pabc123 flush-logs；

二进制日志(binlog)有3种不同的记录格式：

STATEMENT(基于SQL语句)：默认格式是STATEMENT。该方式记录语句快，占用内存空间少。但高并发情况下会导致记录日志顺序紊乱，造成恢复数据时发生偏差。
ROW(基于行)：基于数据内容行进行记录，不仅记录执行的命令语句，还会记录命令影响的相关数据行。
MIXED(混合模式)：高并发情况下ROW方式进行记录，一般情况下采用STATEMENT方式进行记录。

1、手动增量备份

手动增量备份的方式有三种：
第一种：手动刷新
mysqladmin -uroot -pabc123 flush-logs第二种：重启mysqld服务
systemctl restart mysqld第三种：二进制日志文件的大小达到1G的时候会进行自动分割
与max_binlog_size大小相关
默认为1073741824字节等于1G

2、增量备份恢复

mysqlbinlog --no-defaults 指定binlog |mysql -u用户名 -p密码

[root@ll data]# cd /root/back-everyday/
[root@zll back-everyday]# ls
mysql_bin.20240624  mysql_bin.20240625  mysql_bin.20240626
[root@ll back-everyday]# mysqlbinlog --no-defaults mysql_bin.20240625 | mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ll back-everyday]# mysql -u root -pabc123 -e 'select * from zx.zx;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | scj  | 男   |   20 |
|  2 | zx   | 男   |   21 |
|  3 | tc   | 男   |   20 |
|  4 | ctt  | 女   |   20 |
|  5 | jlh  | 男   |   20 |
+----+------+------+------+
[root@ll back-everyday]# mysqlbinlog --no-defaults mysql_bin.20240626 | mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ll back-everyday]# mysql -u root -pabc123 -e 'select * from zx.zx;'              mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  1 | scj  | 男   |   20 |
|  2 | zx   | 男   |   21 |
|  3 | tc   | 男   |   20 |
|  4 | ctt  | 女   |   20 |
|  5 | jlh  | 男   |   20 |
|  6 | zyr  | 男   |   20 |
|  7 | tj   | 男   |   20 |
+----+------+------+------+

4、增量备份断点恢复

第一种：基于位置恢复

基于位置点恢复
mysqlbinlog --no-defaults --start-position="" --stop-position="" 指定binlog |mysql -u用户名 -p密码

第二种：基于时间点恢复

基于时间点恢复
mysqlbinlog --no-defaults --start-datetime="" --stop-datetime="" 指定binlog |mysql -u用户名 -p密码

如果需要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前
如果需要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start

总结

备份方法
物理备份：直接对数据库的物理文件（数据文件、日志文件等）进行备份
逻辑备份：对数据库的库和表对象以SQL语言的形式导出进行备份备份策略
完全备份：每次备份都备份完整的库或者表数据
差异备份：只备份上一次完全备份后的更新数据
增量备份：每次备份只备份上一次完全备份或增量备份后的更新数据数据库上云迁移     冷迁移     物理冷备      先关闭数据库，再打包备份+恢复热迁移     阿里云 DTS完全备份
物理冷备：先关闭数据库，使用tar命令打包备份数据库的数据目录和文件  mysql/data/mysqldump逻辑热备
mysqldump -uXXX -pXXX  --databases 库1 [库2 ....] > XXX.sql       备份一个或多个库及库中所有的表数据
mysqldump -uXXX -pXXX  --all-databases > XXX.sql                  备份所有的库
mysqldump -uXXX -pXXX  库名 > XXX.sql                             备份指定库中的所有的表数据，不包含库对象
mysqldump -uXXX -pXXX  库名 表1 [表2 ....] > XXX.sql              备份指定库中的一个或多个表数据，不包含库对象xtrabackup物理热备完全恢复
先登录到数据库，再执行 source XXX.sql （如果sql文件是只备份了表数据的文件，需要先创建库并 use 切换库后再执行）mysql -uXXX -pXXX < XXX.sql         cat XXX.sql | mysql -uXXX -pXXX                          恢复库
mysql -uXXX -pXXX 库名 < XXX.sql    cat XXX.sql | mysql -uXXX -pXXX 库名                     恢复表增量备份
通过刷新二进制日志间接实现增量备份
mysqladmin -uXXX -pXXX flush-logs查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v <BINLOG>使用二进制日志增量恢复
mysqlbinlog --no-defaults <BINLOG> | mysql -uXXX -pXXX断点恢复
基于位置点恢复
mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  <BINLOG> | mysql -uXXX -pXXX基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-position='YYYY-mm-dd HH:MM:SS'  <BINLOG> | mysql -uXXX -pXXX如果需要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前
如果需要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start