MySQL数据库备份

Gin—L

已于 2022-08-01 10:45:37 修改

阅读量150

点赞数

文章标签：数据库 mysql java

于 2022-07-31 16:58:28 首次发布

本文链接：https://blog.youkuaiyun.com/m0_72898391/article/details/126087585

版权

本文介绍了MySQL数据库的备份方案，包括全量备份、增量备份和差异备份。详细讲解了使用mysqldump工具进行备份及数据恢复的操作，并探讨了差异备份与恢复的流程，涉及开启二进制日志、模拟数据恢复等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql数据库备份与恢复

1 数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

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

1.1mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
 
 
 
    
//备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use Gin;
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_Gin    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> exit

[root@Gin ~]# ls
anaconda-ks.cfg  wget-1.14-18.el7_6.1.x86_64.rpm
[root@Gin ~]# mysqldump -uroot -p --all-databases >> all-20220731.sql 
Enter password: 
[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  wget-1.14-18.el7_6.1.x86_64.rpm



//单独备份

//备份Gin库的tb_course表和tb_students_info表
[root@Gin ~]# mysqldump -uroot Gin tb_course tb_students_info > table-20220731.sql
Enter password: 
[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  table-20220731.sql




//备份Gin库
[root@Gin ~]# mysqldump -uroot -p --databases Gin > database-20220731.sql
Enter password: 
[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  database-20220731.sql  table-20220731.sql



//模拟误删Gin数据库
mysql> drop database Gin;
Query OK, 2 rows affected (0.03 sec)

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

1.2mysql数据恢复

//恢复Gin数据库
[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  Gin-20220731.sql  table-20220731.sql
[root@Gin ~]# mysql -uroot -p < Gin-20220731.sql 
Enter password: 
[root@Gin ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+






//恢复Gin数据库的tb_course表和tb_students_info表
mysql> show databses;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use Gin;
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> source table-20220731.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...
mysql> show tables;
+------------------+
| Tables_in_Gin    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)






//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database Gin;
Query OK, 2 rows affected (0.01 sec)

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

mysql> exit

//恢复整个数据库
[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  Gin-20220731.sql  table-20220731.sql
[root@Gin ~]#  mysql -uroot -p < Gin-20220731.sql 
Enter password: 
[root@Gin ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2 差异备份与恢复

2.1 mysql差异备份

开启MySQL服务器的二进制日志功能

[root@Gin ~]# vim /etc/my.cnf 
[root@Gin ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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

对数据库进行完全备份

[root@Gin ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> show tables from Gin;
+------------------+
| Tables_in_Gin    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from Gin.tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | GO          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

mysql> select * from Gin.tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   22 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)


//完全备份
[root@Gin ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20220731.sql
Enter password: 
[root@Gin ~]# ll
total 872
-rw-r--r--. 1 root root 877711 Jul 31 15:49 all-20220731.sql
-rw-------. 1 root Gin    1428 Jul  5 09:50 anaconda-ks.cfg
-rw-r--r--. 1 root root   3097 Jul 31 15:30 Gin-20220731.sql
-rw-r--r--. 1 root root   2961 Jul 31 15:29 table-20220731.sql



//增加新内容
[root@Gin ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use Gin;
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_Gin    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> desc tb_course;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   |     | NULL    |       |
| course_name | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into tb_course(id,course_name) values(6,'C'),(7,'Linux');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | GO          |
|  5 | C++         |
|  6 | C           |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

2.2. mysql差异备份恢复

模拟误删数据

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

mysql> drop database Gin;
Query OK, 2 rows affected (0.01 sec)

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

刷新创建新的二进制日志

[root@Gin ~]# ll /opt/data/
total 123020
-rw-r-----. 1 mysql mysql       56 Jul 27 19:17 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 client-key.pem
-rw-r-----. 1 mysql mysql    59844 Jul 31 15:50 Gin.err
-rw-r-----. 1 mysql mysql      362 Jul 31 15:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 31 16:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 31 16:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:17 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 31 15:58 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 27 19:17 mysql
-rw-r-----. 1 mysql mysql      582 Jul 31 16:05 mysql_bin.000004
-rw-r-----. 1 mysql mysql       19 Jul 31 15:49 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 31 15:44 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:17 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 sys

//刷新创建新的二进制日志
[root@Gin ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@Gin ~]# ll /opt/data/
total 123024
-rw-r-----. 1 mysql mysql       56 Jul 27 19:17 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 client-key.pem
-rw-r-----. 1 mysql mysql    59844 Jul 31 15:50 Gin.err
-rw-r-----. 1 mysql mysql      362 Jul 31 15:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 31 16:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 31 16:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:17 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 31 15:58 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 27 19:17 mysql
-rw-r-----. 1 mysql mysql      629 Jul 31 16:06 mysql_bin.000004
-rw-r-----. 1 mysql mysql      154 Jul 31 16:06 mysql_bin.000005
-rw-r-----. 1 mysql mysql       38 Jul 31 16:06 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 31 15:44 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:17 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 sys

恢复完全备份

[root@Gin ~]# ls
all-20220731.sql  anaconda-ks.cfg  Gin-20220731.sql  table-20220731.sql
[root@Gin ~]# mysql -uroot -p < all-20220731.sql 
Enter password: 
[root@Gin ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Gin                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@Gin ~]# mysql -uroot -p -e 'show tables from Gin;'
Enter password: 
+------------------+
| Tables_in_Gin    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
[root@Gin ~]# mysql -uroot -p -e 'select * from Gin.tb_course;'
Enter password: 
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | GO          |
|  5 | C++         |
+----+-------------+
[root@Gin ~]# mysql -uroot -p -e 'select * from Gin.tb_students_info;'
Enter password: 
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   22 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
+----+--------+------+------+--------+-----------+

恢复差异备份

[root@Gin ~]# ll /opt/data/
total 124044
-rw-r-----. 1 mysql mysql       56 Jul 27 19:17 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:17 client-key.pem
drwxr-x---. 2 mysql mysql      118 Jul 31 16:11 Gin
-rw-r-----. 1 mysql mysql    59945 Jul 31 16:10 Gin.err
-rw-r-----. 1 mysql mysql      362 Jul 31 15:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 31 16:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 31 16:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:17 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 31 15:58 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 31 16:11 mysql
-rw-r-----. 1 mysql mysql      629 Jul 31 16:06 mysql_bin.000004
-rw-r-----. 1 mysql mysql   857893 Jul 31 16:11 mysql_bin.000005
-rw-r-----. 1 mysql mysql       38 Jul 31 16:06 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 31 15:44 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:17 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:17 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:17 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:17 sys

//检查误删数据库的位置在什么地方
[root@Gin ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show binlog events in 'mysql_bin.000004';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000004 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000004 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000004 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000004 | 219 | Query          |         1 |         290 | BEGIN                                 |
| mysql_bin.000004 | 290 | Table_map      |         1 |         344 | table_id: 142 (Gin.tb_course)         |
| mysql_bin.000004 | 344 | Write_rows     |         1 |         397 | table_id: 142 flags: STMT_END_F       |
| mysql_bin.000004 | 397 | Xid            |         1 |         428 | COMMIT /* xid=505 */                  |
| mysql_bin.000004 | 428 | Anonymous_Gtid |         1 |         493 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000004 | 493 | Query          |         1 |         582 | drop database Gin                     |
| mysql_bin.000004 | 582 | Rotate         |         1 |         629 | mysql_bin.000005;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=629 /opt/data/mysql_bin.000004 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'select * from Gin.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | GO          |
|  5 | C++         |
|  6 | C           |
|  7 | Linux       |
+----+-------------+