mysql数据库的备份与恢复

最新推荐文章于 2023-06-18 14:51:47 发布

逐流·

最新推荐文章于 2023-06-18 14:51:47 发布

阅读量242

点赞数

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

本文链接：https://blog.csdn.net/weixin_46115601/article/details/119915936

版权

linux 专栏收录该内容

71 篇文章 2 订阅

订阅专栏

文章目录

数据库备份方案及特点
mysql备份工具mysqldump
示例
差异备份
- MySQL差异备份

数据库备份方案及特点

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝数据恢复快备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推没有重复的备份数据，备份时间短，恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

mysql备份工具mysqldump

语法

mysqldump [OPTIONS] database_name [tables_name ...]            #备份数据库中的一个或多个表
mysqldump [OPTIONS] --all-databases [OPTIONS]                  #备份所有的数据库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]     #备份其中一个或多个数据库,使用空格分隔

常用选项

-uUSERNAME      #指定数据库用户名
-hHOST          #指定服务器主机，请使用ip地址
-pPASSWORD      #指定数据库用户的密码
-P#             #指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
--all-databases,-A  #备份所有数据库
--databases,-B  #要备份的数据库，可以同时备份多个，使用空格分隔

示例

全量备份

备份所有数据库

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

mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -p -hlocalhost --all-databases > all_databases_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  Desktop    Downloads  original-ks.cfg  Public      Templates
anaconda-ks.cfg           Documents  Music      Pictures         sqlkey.txt  Videos

备份库中的表

备份test1库中的table1table2表

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| table2          |
+-----------------+
2 rows in set (0.00 sec)
[root@localhost ~]#  mysqldump -uroot -p -hlocalhost test1 table1 table2 > tables_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  Desktop    Downloads  original-ks.cfg  Public      tables_backup.sql  Videos
anaconda-ks.cfg           Documents  Music      Pictures         sqlkey.txt  Templates

备份单个库

[root@localhost ~]#  mysqldump -uroot -p -hlocalhost --databases test1  > test1_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  Documents  original-ks.cfg  sqlkey.txt         test1_backup.sql
anaconda-ks.cfg           Downloads  Pictures         tables_backup.sql  Videos
Desktop                   Music      Public           Templates
备份多个库用空格隔开即可

模拟数据恢复

[root@localhost ~]# mysql -uroot -p -e 'drop database test1'
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
模拟误删数据库test1

恢复数据

[root@localhost ~]# mysql -uroot -p -hlocalhost  < test1_backup.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+

想要恢复什么数据就用什么备份包来释放表和库同理

差异备份

MySQL差异备份

修改MySQL配置文件（以往文章二进制安装MySQL提到过）

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/opt/data/mysql.pid

server-id=1  # 设置服务器标识符；只能是数字
log-bin=mysql_bin # 开启二进制日志功能

修改配置文件后重启服务

[root@localhost ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

首先进行全量备份
原有数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| table2          |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from table1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 22   |   33 |
|  3 | 44   |   55 |
+----+------+------+
2 rows in set (0.00 sec)

开始全量备份

[root@localhost ~]#  mysqldump -uroot -p --all-databases > all-databases.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  Desktop    Music            Public             Templates
all-databases.sql         Documents  original-ks.cfg  sqlkey.txt         test1_backup.sql
anaconda-ks.cfg           Downloads  Pictures         tables_backup.sql  Videos

增加新内容（差异）

mysql> insert into table1 values(99,99,30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 22   |   33 |
|  3 | 44   |   55 |
| 99 | 99   |   30 |
+----+------+------+
3 rows in set (0.00 sec)

差异备份恢复

模拟误删数据

mysql> drop database test1;
Query OK, 2 rows affected (0.00 sec)

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

刷新创建新的二进制日志

[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:

恢复完全备份

[root@localhost ~]#  mysql -uroot -p  < all-databases.sql
[root@localhost ~]# mysql -uroot -p -e 'select * from test1.table1;'
Enter password: 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 22   |   33 |
|  3 | 44   |   55 |
+----+------+------+
#只有原始数据没有新增加数据

恢复差异备份

检查误删位置

[root@localhost ~]# ll /opt/data
总用量 123804
-rw-r-----. 1 mysql mysql       56 8月  25 00:58 auto.cnf
-rw-------. 1 mysql mysql     1676 8月  25 00:58 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  25 00:58 ca.pem
-rw-r--r--. 1 mysql mysql     1112 8月  25 00:58 client-cert.pem
-rw-------. 1 mysql mysql     1676 8月  25 00:58 client-key.pem
-rw-r-----. 1 mysql mysql      421 8月  25 03:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  25 04:15 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  25 04:15 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  25 00:58 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  25 03:41 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月  25 03:54 mysql
-rw-r-----. 1 mysql mysql      907 8月  25 03:53 mysql_bin.000001
-rw-r-----. 1 mysql mysql   854862 8月  25 03:55 mysql_bin.000002
-rw-r-----. 1 mysql mysql      629 8月  25 04:17 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 8月  25 04:17 mysql_bin.000004
-rw-r-----. 1 mysql mysql       76 8月  25 04:17 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 8月  25 03:04 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  25 00:58 performance_schema
-rw-------. 1 mysql mysql     1676 8月  25 00:58 private_key.pem
-rw-r--r--. 1 mysql mysql      452 8月  25 00:58 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 8月  25 00:58 server-cert.pem
-rw-------. 1 mysql mysql     1680 8月  25 00:58 server-key.pem
drwxr-x---. 2 mysql mysql     8192 8月  25 00:58 sys
drwxr-x---. 2 mysql mysql       20 8月  25 02:36 test2
看时间04：17为一组

从第一个开始看

mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |         1 |         292 | BEGIN                                 |
| mysql_bin.000003 | 292 | Table_map      |         1 |         346 | table_id: 149 (test1.table1)          |
| mysql_bin.000003 | 346 | Write_rows     |         1 |         391 | table_id: 149 flags: STMT_END_F       |
| mysql_bin.000003 | 391 | Xid            |         1 |         422 | COMMIT /* xid=887 */                  |
| mysql_bin.000003 | 422 | Anonymous_Gtid |         1 |         487 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 487 | Query          |         1 |         582 | drop database test1      ##删除数据库动作             |
| mysql_bin.000003 | 582 | Rotate         |         1 |         629 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)


#从上述列表可以看出，数据删除是在487开始，结束是在582，也就是说在487节点数据库未删除，到860数据库已经删除

使用 mysqlbinlog 恢复差异备份

[root@localhost ~]# mysqlbinlog --stop-position=487 /opt/data/mysql_bin.000003 | mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p -e 'select * from test1.table1;'
Enter password: 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 22   |   33 |
|  3 | 44   |   55 |
| 99 | 99   |   30 |
+----+------+------+

逐流·

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库的备份与恢复

文章目录数据库备份方案及特点mysql备份工具mysqldump示例全量备份备份所有数据库备份库中的表备份单个库模拟数据恢复差异备份MySQL差异备份数据库备份方案及特点备份方案特点全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝数据恢复快备份时间长增量备份增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行
复制链接

扫一扫