MySQL备份与恢复

1.数据库备份的分类

1.1 数据备份的重要性

 

1.2 数据库备份的分类

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

 备份策略

 

 1.3 常见的备份方法

 

2.MySQL完全备份与恢复

2.1 MySQL完全备份

• 是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基
• 优点：备份与恢复操作简单方便
• 缺点：数据存在大量的重复；占用大量的备份空间；备份与恢复时间长
  础

 2.2 数据库完全备份分类

物理冷备份与恢复

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

mysqldump备份与恢复

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL脚本
• 使用命令mysql导入备份的数据

 

2.3 MySQL物理冷备份及恢复

先关闭数据库，然后打包备份相关数据库文件；

systemctl stop mysqld
cd /usr/local/mysql
tar zcf /opt/mysql_backup_$(date +%Y%m%d) data/
cd /opt/backup/
tar tf mysql_data-2023-06-19.tar.gz     #查看tar压缩包里的数据文件
tar xf mysql_data-2023-06-19.tar.gz    #解压缩tar压缩包里的数据文件

 恢复数据库

systemctl start mysqld
mysql -u root -pabc123
mysql> show databases;
mysql> drop database lll01;
mysql> show databases;

tar xvf /opt/mysql_backup_20240623
systemctl restart mysqld

mv /usr/local/mysql/data /usr/local/mysql/data.bak
mv /opt/data/ /usr/local/mysql/
systemctl start mysqld
mysql -uroot -pabc123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lll01              |
| lll03              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

迁移备份数据库到20.0.0.128 

###远端已备份数据文件主机（IP:20.0.0.128）
cd  /opt
scp mysql_backup_20240619 20.0.0.128:/opt
 
###需要恢复数据库文件主机（IP:20.0.0.128）
systemctl stop mysqld
cd /opt
tar xf mysql_backup_20240619 
mv /usr/local/mysql/data /usr/local/mysql/data.bak    #将原有的数据库文件移走
mv /opt/data/ /usr/local/mysql/
systemctl restart mysqld   #重新启动数据库服务
mysql -uroot -pabc123    #此数据库密码为之前备份数据库的密码

 

2.4 mysqldump进行逻辑备份

2.4.1 mysqldump备份数据库

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

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

 

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

 2.4.2 mysqldump备份数据表

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

3. 恢复数据库

3.1 使用source命令恢复数据库

mysql -u root -pabc123; 登录数据库
source /opt/backup/tan1.sql  #使用soure 指定原先库备份的路劲
show  databases;   #查看库

 

3.2 使用mysql命令恢复数据库

3.2.1 重定向方式恢复数据库

 

 3.2.2 管道符方式恢复数据库

 3.3 恢复表操作

 

4.MySQL增量备份与恢复

4.1 二进制日志文件

vim /etc/my.cnf
------------在“server-id”行下面添加mysql数据库的日志信息---------
server-id = 1
---------------------------------------------------------------
#错误日志
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
 
systemctl restart mysqld
mysql -uroot -pabc123

 

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

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

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

 

 

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

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

测试

 systemctl restart mysqld重启

###转换格式，查看指定序列号的二进制数据日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003
--no-defaults：不使用默认格式进行查看
--base64-output=decode-rows：使用base64密码格式进行转换，-rows：按行进行输出
-v：显示详细输出过程

 

4.2 实例：完全备份+增量备份恢复数据库

完全备份+增量备份

 

 

 

 

 完全恢复+增量恢复

4.3 断点恢复