MySQL备份恢复详解

最新推荐文章于 2024-05-06 10:17:43 发布

哈密猿

最新推荐文章于 2024-05-06 10:17:43 发布

阅读量1k

点赞数 3

分类专栏： MySQL 文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/qq_51010919/article/details/131820991

版权

MySQL 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

文章目录

备份

备份

MySQL数据库备份主要分为以下几种类型:

热备份(Hot Backup)

特点:在数据库正常运行时进行备份,不影响数据库服务。
方法:主要通过InnoDB引擎的日志文件实现,如Percona Xtrabackup。
过程:先刷新数据缓存到磁盘,同时记录二进制日志位置。然后拷贝所有数据文件和日志文件。

温备份(Warm Backup)

特点:将数据库设置为只读,新的修改无法写入。
方法:用mysqldump导出数据库,但不断开客户端连接。
过程:设置数据库只读,然后用mysqldump导出,备份期间 clients仍连接数据库。

冷备份(Cold Backup)

特点:完全关闭数据库进行备份。
方法:关闭MySQL服务,直接复制数据文件。
过程:停止数据库,复制相关数据目录,再启动数据库。

异地备份(Remote Backup)

特点:将备份存储到远程其他地方。
方法:可以备份到远程服务器,也可以备份到云存储。
过程:一般通过网络把本地备份传输到远程服务器。

综上,各种备份方式在性能影响、数据完整性保证上都有不同。应根据实际需求选择合适的备份方法。

mysqldump

mysqldump是MySQL数据库的一个命令行工具，用于备份和导出数据库。它允许你将数据库的结构和数据以SQL语句的形式导出到文件中，以便后续恢复或迁移数据库。下面是对mysqldump命令的详细解析：

mysqldump命令的基本语法如下：

mysqldump [选项] [数据库] [表]

[选项]：用于指定各种选项和参数，控制导出的行为。
[数据库]：可选参数，指定要备份的数据库名称。如果不指定该参数，则默认备份所有数据库。
[表]：可选参数，指定要备份的特定表名称。如果不指定该参数，则默认备份指定数据库中的所有表。

下面是一些常用的选项和参数：

-h, --host=<主机名>：指定要连接的MySQL服务器的主机名。
-u, --user=<用户名>：指定用于连接MySQL服务器的用户名。
-p, --password=<密码>：指定连接MySQL服务器时使用的密码。如果密码为空，则系统将提示输入密码。
--databases：备份多个数据库，使用该选项后可以指定多个数据库名称。
--tables：备份多个表，使用该选项后可以指定多个表名称。
--ignore-table=<数据库名.表名>：指定要忽略备份的表，不包含在备份中。
-r, --result-file=<文件路径>：指定导出的SQL语句保存到的文件路径。
--no-data：只备份数据库结构，不包括数据。
--single-transaction：在导出数据时使用事务，确保一致性。

使用mysqldump命令可以执行以下操作：

完全备份数据库：mysqldump -h <主机名> -u <用户名> -p <密码> --all-databases > backup.sql
这将备份MySQL服务器上的所有数据库，并将备份文件保存为backup.sql。
```
[root@mysql backup]# mysqldump  -uroot -p123456 --databases > backup.sql
或
[root@mysql backup]# mysqldump  -uroot -p123456 --all-databases > backup.sql
```
备份指定数据库：mysqldump -h <主机名> -u <用户名> -p <密码> <数据库名> > backup.sql
这将备份指定的数据库，并将备份文件保存为backup.sql。
```
[root@mysql backup]# mysqldump  -uroot -p123456 ydh > ydh.sql
```
备份指定表：mysqldump -h <主机名> -u <用户名> -p <密码> <数据库名> <表名> > backup.sql
这将备份指定数据库中的特定表，并将备份文件保存为backup.sql。
```
[root@mysql backup]# mysqldump -uroot -p123456 TENNIS PLAYERS >tennis_player.sql 
```
仅备份数据库结构：mysqldump -h <主机名> -u <用户名> -p <密码> --no-data <数据库名> > structure.sql
这将备份指定数据库的结构，不包含数据，并将备份文件保存为structure.sql。

mysqldump命令非常灵活，提供了许多选项和参数来满足不同的备份需求。你可以根据自己的具体需求选择适当的选项和参数进行备份操作。

恢复

用二进制文件进行恢复

基于时间

备份方案：

每天的下午11:20点做全备，刚好到了下午11点25分的时候，数据库被删除了，如何将数据恢复到11点25分的状态？

1.开启二进制日志
root@(none) 11:13  scmysql>show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)
2.模拟产生数据
root@(none) 11:18  scmysql>
root@(none) 11:18  scmysql>create database wangshuai;
Query OK, 1 row affected (0.00 sec)

root@(none) 11:20  scmysql>use wangshuai
Database changed
root@wangshuai 11:20  scmysql>create table t1(id int primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.11 sec)

root@wangshuai 11:20  scmysql>insert into t1(id,name) values(1,'cali'),(2,'hepang'),(3,'zhangquanliang');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@wangshuai 11:21  scmysql>
3.产生一个新的二进制日志，为了方便我们后面查找，特意新建一个新的二进制日志

root@wangshuai 11:22  scmysql>flush logs;
Query OK, 0 rows affected (0.05 sec)

root@wangshuai 11:23  scmysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000003 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@wangshuai 11:23  scmysql>
4.做全备
[root@sc-mysql backup]# mkdir /backup
[root@sc-mysql backup]# mysqldump -uroot -p'Sanchuang1234#' --databases wangshuai >/backup/ws.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql backup]# 
[root@sc-mysql backup]# ls
all_db.sql  hunan_liangliang.sql  tennis_player.sql  ws.sql
[root@sc-mysql backup]# 

5.模拟全备后产生一些新的数据
root@wangshuai 11:23  scmysql>insert into t1(id,name) values(4,'cali2'),(5,'hepang2'),(6,'zhangquanliang2');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@wangshuai 11:27  scmysql>update t1 set name = 'fengdeyong' where id = 4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@wangshuai 11:28  scmysql>
root@wangshuai 11:28  scmysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000003 |      749 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

root@wangshuai 11:28  scmysql>
6.模拟删除数据库wangshuai
root@wangshuai 11:28  scmysql>drop database wangshuai;
Query OK, 1 row affected (0.04 sec)

root@(none) 11:29  scmysql>

进行恢复

1.先恢复全备
	[root@sc-mysql backup]# mysql -uroot -p'Sanchuang1234#' < /backup/ws.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql backup]#
root@wangshuai 11:30  scmysql>select * from t1;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | cali           |
|  2 | hepang         |
|  3 | zhangquanliang |
+----+----------------+
3 rows in set (0.00 sec)

root@wangshuai 11:31  scmysql>
2. 再根据二进制日志去恢复数据

[root@sc-mysql mysql]# mysqlbinlog -vv sc-mysql-bin.000003 |egrep  -i "drop database wangshuai" -C 100
查找删除数据库和


220818 11:27:37  开始的时间

220818 11:29:36  drop database前的时间

mysqlbinlog --start-datetime "2022-08-18 11:27:37"  --stop-datetime "2022-08-18 11:29:36"  sc-mysql-bin.000003 |mysql -uroot -p'Sanchuang1234#' 

[root@sc-mysql mysql]# mysqlbinlog --start-datetime "2022-08-18 11:27:37"  --stop-datetime "2022-08-18 11:29:36"  sc-mysql-bin.000003 |mysql -uroot -p'Sanchuang1234#' 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql mysql]# 


root@wangshuai 11:48  scmysql>select * from t1;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | cali            |
|  2 | hepang          |
|  3 | zhangquanliang  |
|  4 | fengdeyong      |
|  5 | hepang2         |
|  6 | zhangquanliang2 |
+----+-----------------+
6 rows in set (0.00 sec)

root@wangshuai 11:48  scmysql>

基于位置号

备份和还原操作：
1.产生一个全新的二进制文件
root@(none) 10:33  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000004 |    23054 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@(none) 10:34  sc-mysql>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@(none) 10:35  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000005 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

root@(none) 10:35  sc-mysql>
2.给数据库做全备
[root@sc-mysql mysql]# mysqldump -uroot -p'Sanchuang123#' --databases sanchuang >/backup/sanchuang.sql
3.让数据发生变化，进行insert 和删除操作等


root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(3,'苏文洋',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(4,'qinjiahui',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:40  sc-mysql>insert into emp(id,name,deptid) values(5,'chenran',20);
Query OK, 1 row affected (0.00 sec)

root@sanchuang 10:41  sc-mysql>show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| sc-mysql-bin.000005 |      998 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@sanchuang 10:41  sc-mysql>

4.模拟出现故障，删除数据库
root@sanchuang 10:41  sc-mysql>drop database sanchuang;

恢复

5.开始取恢复数据
	第一步恢复全备
	[root@sc-mysql backup]# mysql -uroot -p'Sanchuang123#' < sanchuang.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql backup]#
	第2步：查看二进制日志找到删除数据库之前的position 位置号
[root@sc-mysql mysql]# mysqlbinlog  -v sc-mysql-bin.000005|egrep -C 5 "drop database"
#210401 10:42:39 server id 1  end_log_pos 1063 CRC32 0x8623d686 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1063
#210401 10:42:39 server id 1  end_log_pos 1170 CRC32 0xf9eee347 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1617244959/*!*/;
drop database sanchuang
/*!*/;
# at 1170
#210401 10:44:13 server id 1  end_log_pos 1235 CRC32 0xa92ef3c4 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1235
[root@sc-mysql mysql]#

	第3步：使用二进制日志去恢复

[root@sc-mysql mysql]# mysqlbinlog   --start-position=154 --stop-position=1063  sc-mysql-bin.000005|mysql -uroot -p'Sanchuang123#'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@sc-mysql mysql]#

6.查看数据是否恢复
root@sanchuang 10:54  sc-mysql>select * from emp;
+----+-----------+--------+
| id | NAME      | deptid |
+----+-----------+--------+
|  1 | 张三      |     10 |
|  2 | 李四      |     10 |
|  3 | 苏文洋    |     20 |
|  4 | qinjiahui |     20 |
|  5 | chenran   |     20 |
| 19 | 吴佩      |     10 |
+----+-----------+--------+
6 rows in set (0.00 sec)

root@sanchuang 10:55  sc-mysql>

恢复指定数据库

[root@mysql backup]# mysql -uroot -p123456 < /backup/ydh.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1046 (3D000) at line 22: No database selected

错误信息指出：ERROR 1046 (3D000) at line 22: No database selected，表示没有选择数据库。这意味着在导入SQL文件之前没有指定要使用的数据库。

为了解决这个问题，你需要在执行导入操作之前选择要导入数据的数据库。可以按照以下步骤进行：

登录到MySQL服务器：mysql -u root -p，然后输入密码。
如果没有该数据库了，需要重新建一个
选择要导入数据的数据库：使用use命令选择数据库，例如：use <数据库名>。将<数据库名>替换为你要导入数据的实际数据库名称。
退出MySQL命令行界面：输入exit或者使用快捷键Ctrl+D退出。

接下来，你可以重新执行导入命令，确保在导入之前选择了正确的数据库：

mysql -u root -p123456 -D <数据库名> < /backup/ydh.sql

这样，命令将会执行成功，并将SQL文件中的内容导入到指定的数据库中。记得将<数据库名>替换为你要导入数据的实际数据库名称，并使用正确的用户名和密码。

请注意，为了提高安全性，建议不要在命令行中直接使用密码（-p选项），而是让MySQL提示输入密码。例如：

mysql -u root -p -D <数据库名> < /backup/ydh.sql

这样，你将会在执行命令后输入密码，避免密码直接暴露在命令行历史记录中。

延迟备份

关闭从服务器的slave，好进行修改

root@(none) 14:45 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)

开启延迟备份设置，延迟十秒

root@(none) 14:46 mysql>CHANGE MASTER TO MASTER_DELAY = 10;
Query OK, 0 rows affected (0.01 sec)

启动slave

root@(none) 14:48 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

查看是否配置成功

             Master_Server_Id: 1
                  Master_UUID: 951033bf-1bea-11ee-a85d-000c299e81dd
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 10
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read al

哈密猿

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL备份恢复详解

是MySQL数据库的一个命令行工具，用于备份和导出数据库。它允许你将数据库的结构和数据以SQL语句的形式导出到文件中，以便后续恢复或迁移数据库。每天的下午11:20点做全备，刚好到了下午11点25分的时候，数据库被删除了，如何将数据恢复到11点25分的状态？这样，命令将会执行成功，并将SQL文件中的内容导入到指定的数据库中。替换为你要导入数据的实际数据库名称，并使用正确的用户名和密码。这将备份MySQL服务器上的所有数据库，并将备份文件保存为。这将备份指定数据库的结构，不包含数据，并将备份文件保存为。
复制链接

扫一扫