【Linux】MySql数据库备份

目录

1、MySQL备份概述

1.1备份和冗余有什么区别?

1.2备份什么

1.3备份类型

1.31逻辑备份

1.32物理备份

1.33在线热备(冗余)

1.4备份工具

1.41社区版安装包中的备份工具:

1.42社区版安装包中的备份工具:

2、MySQL的逻辑备份

2.1mysqldump备份

2.11基本语法

2.12常用参数

2.13举例说明

2.2mysqldump+binlog增量备份

1、MySQL备份概述

1.1备份和冗余有什么区别?

• 备份:能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它

• 冗余:数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

1.2备份什么

• 数据库:一堆物理文件的集合;日志文件+数据文件+配置文件

• DB BINLOG my.cnf

1.3备份类型

1.31逻辑备份

• 备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL)。

• 适用于中小型数据库，效率相对较低。一般在数据库正常提供服务的前提下进行，如:mysqldump、mydumper、into outfile (表的导出导入）等。

1.32物理备份

• 直接复制数据库文件

• 适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

• 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份);如: tar、cp、xtrabackup(数据库可以正常提供服务) 、lvm snapshot、rsync等

1.33在线热备(冗余)

• MySQL的replication架构，如M-S |M-S-S |M-M-S等

• 实时在线备份

1.4备份工具

1.41社区版安装包中的备份工具:

1. mysqldump(逻辑备份，只能全量备份)

   1. 企业版和社区版都包含

   2. 本质上使用SQL语句描述数据库及数据并导出

   3. 在MYISAM引擎上锁表,Innodb引擎上锁行

   4. 数据量很大时不推荐使用

2. mysqlhotcopy (物理备份工具)

   1. 企业版和社区版都包含

   1. perl写的一个脚本，本质上是使用锁表语句后再拷贝数据

   2. 只支持MYISAM数据引擎

1.42社区版安装包中的备份工具:

1. mysqlbackup

   • 在线备份

   • 增量备份

   • 部分备份

   • 在某个特定时间的一致性状态的备份

2. 第三方备份工具:

   1. XtraBackup和innobackupex(物理备份)

      • Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。

      • Xtrabackup有两个主要的工具: xtrabackup、innobackupex

        • xtrabackup只能备份InnoDB和XtraDB两种数据表，不能备份myisam类型的表。

3. mydumper(逻辑备份)

   1. 多线程备份工具 https://launchpad.net/mydumper/mydumper-0.9.1.tar,gz 2015-11-06 (最后更新时间)

2、MySQL的逻辑备份

2.1mysqldump备份

• 本质:导出的是sql语句文件

• 优点:无论是什么存储引擎，都可以用mysqldump备成sql语句

• 缺点:速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份

• 提供三种级别的备份，表级，库级和全库级

2.11基本语法

#表级别备份
mysqldump [OPTIONS] database [tables]
#库级别备份
mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
#全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

2.12常用参数

常用参数:
--flush-logs, -F 开始备份前刷新日志
--flush-privileges 备份包含mysql数据库时刷新授权表
--lock-all-tables ，-x 服务可用性(针对所有库所有表)
--lock-tables，-l 备份前锁表（针对要备份的库)
--single-transaction 适用InnoDB引擎，保证一致性，服务可用性
--master-data=2
表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行;
--master-data=1
表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行;
--master-data参数其他说明:
1)恢复时会执行，默认是1
2)需要RELOAD privilege并必须打开二进制文件
3)这个选项会自动打开--lock-all-tables，关闭--lock-tables

2.13举例说明

表级备份:
# mysqldump -p db01 emp > /mysqlbak/emp.sql 备份单个表
# mysqldump -p db01 emp dept > /mysqlbak/dept_emp.sql备份多个表

表级恢复:
#mysql -p db01 </mysqlbak/emp.sql;
或者在mysql数据库内使用s ource命令来执行外部的sql文件
mysql>source /mysqlbak/dept_emp.sql;

库级备份:
#mysqldump -p --databases db01 >/mysqlbak/db01.sql 备份单个库
#mysqldump -p --databases db01 db02  >/mysqlbak/db01_02.sql 备份多个库

库级恢复:
# mysql -p </mysqlbak/db01.sql
mysql> source /mysqlbak/db01.sql

全库级备份:
mysqldump -p --all-databases --master-data --single-transaction > all.sql
或
mysqldump -p -A --master-data --single-transaction > all.sql
使用drop 命令删除mysql或其它数据库，模拟数据库被删除意外
全库级恢复
#mysql -p < /mysqlbak/all.sql 
模拟删除了mysql数据库：不重启mysqld服务与重启mysqld服务

2.2mysqldump+binlog增量备份

结合二进制日志增量备份核心思路:

1. 备份的目的为了数据出问题进行恢复

2. 二进制日志文件默认会记录下所有对数据库数据变化的操作(增、删、改)

3. 二进制文件同时还会记录具体sql执行时的环境、时间以及起始位置和结束位置(pos值)

4. 通过获取二进制文件里的SQL操作来进行数据的恢复

思路

1. 先做全量备份(mysqldump)

2. 对数据库做更改操作

3. 模拟故障进行测试验证

4. 备份二进制日志文件

5. 数据恢复

6. 测试验证
    

实战案例：恢复误删除的表

案例说明：每天2：30做完全备份，早上10：00误删除students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

#完全备份
[root@localhost mysqlbak]# mysqldump  -p --single-transaction --flush-logs --master-data=2 --all-databases >/mysqlbak/all_`date +%F_%T`.sql
Enter password: 
[root@localhost mysqlbak]# ll
总用量 1593168
-rw-r--r--. 1 root root 1631400589 12月  4 13:20 all_2022-12-04_13:19:44.sql

#完全备份后数据更新
mysql> insert into dept values(7,'测试部') ;
Query OK, 1 row affected (0.04 sec)

mysql> insert into dept values(8,'实施运维部');

#10：00误删除了一个重要的表
mysql> drop table emp;
Query OK, 0 rows affected (0.04 sec)

#后续其它表继续更新
mysql> use db02;
mysql> insert into emp values(7,'曹操',40,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into emp values(8,'刘备',45,1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from emp;
+----+-----------+------+---------+
| id | name      | age  | dept_id |
+----+-----------+------+---------+
|  1 | 张无忌    |   20 |       1 |
|  2 | 杨逍      |   33 |       1 |
|  3 | 赵敏      |   18 |       2 |
|  4 | 常遇春    |   43 |       2 |
|  5 | 小昭      |   19 |       3 |
|  6 | 韦一笑    |   48 |       3 |
|  7 | 曹操      |   40 |       1 |
|  8 | 刘备      |   45 |       1 |
+----+-----------+------+---------+
8 rows in set (0.01 sec)

#10：10发现表删除，进行还原
#停止数据库访问

#从完全备份中，找到二进制位置
[root@localhost mysqlbak]# grep '\-\- CHANGE MASTER TO' /mysqlbak/all_2022-12-04_13:19:44.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=154;

#备份从完全备份后的二进制日志
[root@localhost mysql]  mysqlbinlog --no-defaults --start-position=154 /var/lib/mysql/mysql_bin.000008 > /mysqlback/inc.sql

#找到误删除的语句，从备份中删除此语句
[root@localhost mysql] vim /data/inc.sql
#DROP TABLE `emp` /* generated by server */
#如果文件过大，可以使用sed实现
[root@localhost mysql] sed -i.bak '/^DROP TABLE/d' /data/inc.sql

#利用完全备份和修改过的二进制日志进行还原
[root@localhost mysql] mysql -uroot -p
mysql> set sql_log_bin=0;
mysql> source /mysqlback/all_2025-07-11_10:24:54.sql;
mysql> source /mysqlback/inc.sql;
mysql> set sql_log_bin=1;