mysql备份恢复(mysqldump备份 mysqlbinlog恢复)

一、备份的分类

1. 物理备份

   ​ 物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)

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

2. 逻辑备份

   逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的备份

   从数据库的备份策略角度，备份可分为

   • 完全备份：每次对数据进行完整的备份
   • 差异备份：差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全备份和最后一次差异备份进行恢复。
   • 增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。

二、常见的备份方法

1. 物理冷备
   • 备份时数据库处于关闭状态，直接打包数据库文件
   • 备份速度快，恢复时也是最简单的
   • 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
2. 专用备份工具mydump或mysqlhotcopy
   • mysqldump常用的逻辑备份工具
   • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
3. 启用二进制日志进行增量备份
   • 进行增量备份，需要刷新_二进制日志
4. 第三方工具备份
   • 免费的MySQl热备份软件Percona XtraBackup

三、mysql完全备份

1. 定义

   完全备份是对整个数据库的备份、数据库结构和文件结构的备份
   完全备份保存的是备份完成时刻的数据库
   完全备份是增量备份的基础

2. 优点

   安全性高
   备份与恢复操作简单方便

3. 缺点

   数据存在大量的重复
   占用大量的备份空间，空间利用率低
   备份与恢复时间长

四、数据库完全备份分类

4.1 物理备份/冷备份-tar (备份与恢复)

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

• 准备好数据库(当然可以自己准备数据库，主要演示对数据的增删改)

进入mysql 创建kings数据库
create database kings;

use kings;

创建tanks表
tanks | CREATE TABLE `tanks` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `skills` varchar(100) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 

插入数据
insert into tanks(name,skills,price) values('猪八戒','像一个肉球一样弹',12888);

select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

• 先退出mysql，关闭数据库服务，再打包备份

tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

cd /backup/

ll
总用量 760
-rw-r--r-- 1 root root 776114 8月   7 01:21 mysql_all-2023-08-07.tar.gz

• 将原来的数据移走到备份文件夹中，解压刚才备份的tar包到/restore目录下，再移动到mysql服务的文件夹中

mkdir /bak

将现有mysql的data 移走
mv /usr/local/mysql/data/ /bak

mkdir /restore
 
将备份文件解压到restore
tar -zxf /backup/mysql_all-2023-08-07.tar.gz -C /restore/

将解压的备份文件data 移动到mysql中
mv restore/usr/local/mysql/data/ /usr/local/mysql

• 重启mysql服务，登录mysql，查看数据是否恢复

mysql>use kings;

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

4.2 逻辑备份/热备份-mysqldump备份

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
mysqldump命令备份过程，实际上是把数据库、表，以SQL语句的形式，输出为文件的备份过程，这种方式称之为逻辑备份。
但是这种方式效率并不高，以SQL导出，在海量数据下，例如几十G的场景，备份、恢复的时间都会过长。
备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。

mysqldump命令用于备份MySQL数据库

语法:

mysqldump -u username -p password [option] database_name table_name > backup_file.sql

常用参数:

--all-databases：备份所有数据库。
--single-transaction：在备份期间使用单个事务，确保数据的一致性(不停机备份)。
--master-data：为1时在文件中生成change master语句，为2时也添加change master并注释。
--delete-master-logs：在master上备份后删除其二进制日志。
--flush-logs：切断日志，做一次日志轮转。
--skip-lock-tables：备份时跳过锁定表，适用于大型数据库。
--no-create-db：备份时不包含创建数据库语句。
--no-create-info：备份时不包含创建表的语句。
--no-data：备份时不包含数据。
--add-drop-database：备份时在创建数据库之前添加DROP DATABASE语句。
--add-drop-table：备份时在创建表之前添加DROP TABLE语句。
--result-file=file_name：将备份结果输出到指定文件中。
--compress：在备份过程中使用压缩。

常用命令:

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

mysqldump -u root -p --all-databases --single-transaction --master-data=2 --flush-logs >/backup/`date +%F-%H`mysql_all.sql ###备份所有库，添加change master并注释，并切断日志

对单个表备份

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kings              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -u root -p kings  > /backup/kings-$(date +%F).sql
Enter password: 
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
kings-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

对所有库进行备份

[root@localhost ~]# mysqldump -u root -p --all-databases > /backup/all-$(date +%F).sql
Enter password: 
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
all-2023-08-07.sql  kings-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

备份表

[root@localhost backup]# mysqldump -u root -p kings tanks > /backup/kings.tanks-$(date +%F).sql
[root@localhost backup]# ls
all-2023-08-07.sql  kings-2023-08-07.sql  kings.tanks-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

4.2.1使用source命令恢复数据库

source命令【作用于mysql模式下】

使用source恢复数据库

模拟删除表 恢复表

mysql> drop table tanks;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /backup/kings.tanks-2023-08-07.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

4.2.2使用mysql命令恢复数据库

mysql命令【作用于于linux模式下】

模拟删表 恢复

mysql> drop table tanks;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

kings为数据库名字 恢复导入表需要指定数据库！
[root@localhost ~]# mysql -uroot -p kings < /backup/kings.tanks-2023-08-07.sql 
Enter password: 

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

五、二进制日志备份、恢复

mysqlbinlog工具来读取和分析binlog文件

语法：

mysqlbinlog [options] logfile1 logfile2

常用参数:

--database=name，仅显示指定数据库的转储内容。
--short-form，使用简单格式。
--start-datetime=name，转储日志的起始时间。
--stop-datetime=name，转储日志的截止时间。
--start-position=#，转储日志的起始位置。
--stop-position=#，转储日志的截止位置。
--no-defaults：指定不加载或使用默认配置文件，以确保按照命令行参数指定的配置选项进行操作。
--base64-output=decode-rows：将读取的二进制数据解码为可读格式，即将Base64编码的内容解码为原始数据。
-v：输出详细信息，包括读取的日志事件和相关数据。

常用命令：

查看日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v 

恢复数据
mysqlbinlog --stop-position=xxx [mysql-bin.0000xx] | mysql -uroot -p
mysqlbinlog --start-position=xxx --stop-position=xxx [mysql-bin.0000xx] | mysql -uroot -p
mysqlbinlog --start-datetime=xxx --stop-datetime=xxx [mysql-bin.0000xx] | mysql -uroot -p

5.1基于position(位置点)备份

1.开启mysql服务器的二进制日志功能

[root@localhost ~]# tail -15 /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
port = 3306
pid-file = /usr/local/mysql/mysql.pid
user = mysql
skip-name-resolve
server-id = 1   // 设置服务器标识
log-bin = /usr/local/mysql/mysql-bin  //开启二进制功能

查看日志文件
[root@localhost ~]# ll /opt/data/
-rw-r-----. 1 mysql mysql      154 2月  22 16:37 mysql_bin.000001
-rw-r-----. 1 mysql mysql       19 2月  22 16:37 mysql_bin.index

2.对数据库进行完全备份

mysqldump -uroot -p --single-transation --master-data=2 --flush-logs --all-databases --delete-master-logs > /backup/all-`date +%F-%H`.sql

可以看到all-2023-08-07-16.sql已经创建
[root@localhost backup]# ls
all-2023-08-07-16.sql  kings-2023-08-07.sql        mysql_all-2023-08-07.tar.gz
all-2023-08-07.sql     kings.tanks-2023-08-07.sql 

3.在数据数据库中增加类容

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

mysql> insert into tanks(name,skills,price) values('钟馗','钩子英雄',12888);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tanks;                                              
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
|  3 | 钟馗      | 钩子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

4.模拟删库

mysql> drop database kings;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

5.刷新二进制文件

[root@localhost mysql]# mysqladmin -uroot -p flush-logs

[root@localhost mysql]# ls /usr/local/mysql/
mysql-bin.000002  mysql-bin.000003  ...

6.恢复完全备份
查看tanks表内容，可以发现备份之后修改的内容丢失

[root@localhost mysql]# mysql -uroot -p < /backup/all-2023-08-07-16.sql
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kings              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use kings;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

7.查看binlog 日志,发现414为删库前，所以恢复到414

mysql> show binlog events in 'mysql-bin.000002'\G

*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 233
 Event_type: Query
  Server_id: 1
End_log_pos: 383
       Info: use `kings`; insert into tanks(name,skills,price) values('钟馗','钩子英雄',12888)
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 383
 Event_type: Xid
  Server_id: 1
End_log_pos: 414
       Info: COMMIT /* xid=965 */
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 414
 Event_type: Query
  Server_id: 1
End_log_pos: 509
       Info: drop database kings
       
恢复到删库前
退出mysql 执行mysqlbinlog --stop-position=
[root@localhost ~]# mysqlbinlog --stop-position=414 /usr/local/mysql/mysql-bin.000003 | mysql -uroot -p
Enter password: 

进入mysql 查看tanks表
mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
|  3 | 钟馗      | 钩子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

同理也可以恢复到插入'id=3'语句之前

对二进制日志查询还可以用

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000023 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4                                                   |
| mysql-bin.000023 | 120 | Query       |         1 |         201 | BEGIN                                                                                   |
| mysql-bin.000023 | 201 | Intvar      |         1 |         233 | INSERT_ID=3                                                                             |
| mysql-bin.000023 | 233 | Query       |         1 |         383 | use `kings`; insert into tanks(name,skills,price) values('钟馗','钩子英雄',12888)       |
| mysql-bin.000023 | 383 | Xid         |         1 |         414 | COMMIT /* xid=965 */                                                                    |
| mysql-bin.000023 | 414 | Query       |         1 |         509 | drop database kings                                                                     |
| mysql-bin.000023 | 509 | Rotate      |         1 |         556 | mysql-bin.000024;pos=4                                                                  |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

5.2基于datetime(时间点)备份

1.删库 （之前添加的id=3 数据 这里不进行修改表了）

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
|  3 | 钟馗      | 钩子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.01 sec)

Database changed
mysql> drop database kings;
Query OK, 1 row affected (0.00 sec)

2.恢复完全备份 （因为之前有备份过就不需要进行mysqldump备份了，直接使用备份文件完全恢复就行）

mysql -uroot -p  < /backup/all-2023-08-07-16.sql

3.查看表 此时已经恢复到了完全备份的数据状态

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.01 sec)

4.使用mysqlbinlog查询时间点

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002

找到有关的信息
# at 233
#230807 17:01:37 server id 1  end_log_pos 233 CRC32 0xf9822b3a  Intvar
SET INSERT_ID=3/*!*/;
#230807 17:01:37 server id 1  end_log_pos 383 CRC32 0xc6524022  Query   thread_id=9     exec_time=0    error_code=0
use `kings`/*!*/;
SET TIMESTAMP=1691398897/*!*/;
insert into tanks(name,skills,price) values('钟馗','钩子英雄',12888)
/*!*/;
# at 383
#230807 17:01:37 server id 1  end_log_pos 414 CRC32 0x090e26df  Xid = 965
COMMIT/*!*/;
# at 414
#230807 17:02:35 server id 1  end_log_pos 509 CRC32 0xd7c5718b  Query   thread_id=9     exec_time=0    error_code=0
SET TIMESTAMP=1691398955/*!*/;
drop database kings
/*!*/;

5.使用mysqlbinlog时间点恢复

[root@localhost 3306]#  mysqlbinlog --no-defaults --start-datetime='2023-08-07 17:01:37' --stop-datetime='2023-08-07 17:02:35' /usr/local/mysql/mysql-bin.000023 | mysql -uroot -p 

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 猪八戒    | 像一个肉球一样弹         | 12888 |
|  3 | 钟馗      | 钩子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• Mysql时间常用函数
• 【Leetcode1949. 坚定的友谊】使用MySQL在无向图中寻找{"CompleteTripartite", {1, 1, 3}}这个pattern

备份 mysqlbinlog mysqldump mysql备份mysqlbinlog mysqldump mysql 备份mysqldump逻辑mysql 备份mysqldump mysql 备份mysqldump命令 event备份mysqldump权限 mysqlbinlog数据库 数据mysql 备份mysqldump环境 数据库 备份mysqldump环境 数据 数据备份 备份mysqldump命令 备份mysqldump命令 数据库