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

Tom cat.

已于 2024-07-02 15:49:01 修改

阅读量416

点赞数 6

文章标签：数据库 mysql

于 2024-06-26 14:52:36 首次发布

本文链接：https://blog.csdn.net/2401_83330816/article/details/139963035

版权

一、数据备份的重要性

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