MySQL数据库备份与恢复

mysql数据库备份与恢复

1.数据库常用备份方案

数据库备份方案：

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

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

2.全量备份与恢复

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

//查询数据库与表和表的内容
[root@localhost ~]# mysql -uroot -pPassw0rd@_
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use hl;
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_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.01 sec)

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | sex    |
+----+----------+------+--------+
|  1 | liwei    |   22 | male   |
|  2 | zhaohang |   23 | male   |
|  3 | liuyi    |   21 | female |
|  4 | wangwu   |   24 | male   |
|  5 | sunrei   |   21 | female |
|  6 | zhaoyan  |   20 | female |
|  7 | tianqi   |   19 | male   |
|  8 | zhangsan |   20 | female |
+----+----------+------+--------+
8 rows in set (0.00 sec)

//备份单个数据库
[root@localhost ~]# mkdir /data
[root@localhost ~]# mysqldump -uroot -pPassw0rd@_ --databases hl > /data/hl-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /data
hl-20231208.sql

//备份hl数据库中的表
[root@localhost ~]# mysqldump -uroot -pPassw0rd@_ hl emp dept student > /data/e.d.s-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# 
[root@localhost ~]# ls /data
e.d.s-20231208.sql  hl-20231208.sql

//备份全部数据库
[root@localhost ~]# mysqldump -uroot -pPassw0rd@_ --all-databases > /data/all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /data
all-20231208.sql  e.d.s-20231208.sql  hl-20231208.sql

//模拟误删hl数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database hl;
Query OK, 3 rows affected (0.02 sec)

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

//恢复hl数据库
[root@localhost ~]# mysql -uroot -pPassw0rd@_ < /data/hl-20231208.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

mysql> drop database hl;
Query OK, 3 rows affected (0.02 sec)

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

//恢复hl数据库
[root@localhost ~]# mysql -uroot -pPassw0rd@_ < /data/all-20231208.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

模拟误删hl数据库中的表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hl;
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_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+
3 rows in set (0.00 sec)

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

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

mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

//恢复hl数据库中误删的表
方法一：
[root@localhost ~]# mysql -uroot -pPassw0rd@_ hl < /data/e.d.s-20231208.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'show tables from hl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| Tables_in_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+

方法二：
mysql> use hl;
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 e.d.s-20231208.sql;             //跟上绝对路径也行/data/e.d.s-20231208.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)

Query OK, 0 rows affected (0.00 sec)
......
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+

3.差异备份与恢复

开启MySQL服务器的二进制日志功能（mysql8.0.35默认是开启的就不用打开了）

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

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

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

//查询数据库、表、表中的内容
[root@localhost ~]# mysql -uroot -pPassw0rd@_ 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hl;
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_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> 
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | sex    |
+----+----------+------+--------+
|  1 | liwei    |   22 | male   |
|  2 | zhaohang |   23 | male   |
|  3 | liuyi    |   21 | female |
|  4 | wangwu   |   24 | male   |
|  5 | sunrei   |   21 | female |
|  6 | zhaoyan  |   20 | female |
|  7 | tianqi   |   19 | male   |
|  8 | zhangsan |   20 | female |
+----+----------+------+--------+
8 rows in set (0.00 sec)

//全量备份
[root@localhost ~]# ls /data
all-20231208.sql  e.d.s-20231208.sql  hl-20231208.sql
[root@localhost ~]# rm -rf /data/all-20231208.sql 
[root@localhost ~]# mysqldump -uroot -pPassw0rd@_ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > /data/all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
WARNING: --delete-master-logs is deprecated and will be removed in a future version. Use --delete-source-logs instead.
[root@localhost ~]# ls /data
all-20231208.sql  e.d.s-20231208.sql  hl-20231208.sql

[root@localhost ~]# mysql -uroot -pPassw0rd@_ 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hl;
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_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+
3 rows in set (0.01 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+



//向表中插入内容
mysql> insert dept(deptno,dname,loc) values(50,'manage','wuhan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
|     50 | manage     | wuhan    |
+--------+------------+----------+

//修改表中的数据
mysql> update dept set loc = 'zhejiang' where deptno = 40;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | zhejiang |
|     50 | manage     | wuhan    |
+--------+------------+----------+
5 rows in set (0.00 sec)

//模拟误删与恢复
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database hl;
Query OK, 3 rows affected (0.02 sec)

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

[root@localhost ~]# cd /opt/data
[root@localhost data]# ls
 auto.cnf             ib_buffer_pool              performance_schema
 binlog.000010        ibdata1                     private_key.pem
 binlog.index         ibtmp1                      public_key.pem
 ca-key.pem          '#innodb_redo'               server-cert.pem
 ca.pem              '#innodb_temp'               server-key.pem
 client-cert.pem      localhost.localdomain.err   sys
 client-key.pem       mysql                       undo_001
'#ib_16384_0.dblwr'   mysql.ibd                   undo_002
'#ib_16384_1.dblwr'   mysql.pid

[root@localhost data]# mysqladmin -uroot -pPassw0rd@_ flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls
 auto.cnf            '#ib_16384_1.dblwr'          mysql.pid
 binlog.000010        ib_buffer_pool              performance_schema
 binlog.000011        ibdata1                     private_key.pem
 binlog.index         ibtmp1                      public_key.pem
 ca-key.pem          '#innodb_redo'               server-cert.pem
 ca.pem              '#innodb_temp'               server-key.pem
 client-cert.pem      localhost.localdomain.err   sys
 client-key.pem       mysql                       undo_001
'#ib_16384_0.dblwr'   mysql.ibd                   undo_002

[root@localhost ~]# mysql -uroot -pPassw0rd@_ < /data/all-20231208.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hl                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'show tables from hl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| Tables_in_hl |
+--------------+
| dept         |
| emp          |
| student      |
+--------------+
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'select * from hl.dept;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+

//恢复差异备份（就是在备份之后到误删之间操作的数据）
[root@localhost ~]# ls /opt/data
 auto.cnf            '#ib_16384_1.dblwr'          performance_schema
 binlog.000010        ib_buffer_pool              private_key.pem
 binlog.000011        ibdata1                     public_key.pem
 binlog.index         ibtmp1                      server-cert.pem
 ca-key.pem          '#innodb_redo'               server-key.pem
 ca.pem              '#innodb_temp'               sys
 client-cert.pem      localhost.localdomain.err   undo_001
 client-key.pem       mysql                       undo_002
 hl                   mysql.ibd
'#ib_16384_0.dblwr'   mysql.pid

//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -pPassw0rd@_ 
mysql> show binlog events in 'binlog.000010';
+---------------+------+----------------+-----------+-------------+------------
--------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info       
                          |
+---------------+------+----------------+-----------+-------------+------------
--------------------------+
| binlog.000010 |    4 | Format_desc    |         1 |         126 | Server ver:
 8.0.35, Binlog ver: 4    |
| binlog.000010 |  126 | Previous_gtids |         1 |         157 |            
                          |
| binlog.000010 |  157 | Anonymous_Gtid |         1 |         236 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 |  236 | Query          |         1 |         309 | BEGIN      
                          |
| binlog.000010 |  309 | Table_map      |         1 |         368 | table_id: 3
08 (hl.dept)              |
| binlog.000010 |  368 | Write_rows     |         1 |         421 | table_id: 3
08 flags: STMT_END_F      |
| binlog.000010 |  421 | Xid            |         1 |         452 | COMMIT /* x
id=4077 */                |
| binlog.000010 |  452 | Anonymous_Gtid |         1 |         531 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 |  531 | Query          |         1 |         613 | BEGIN      
                          |
| binlog.000010 |  613 | Table_map      |         1 |         672 | table_id: 3
08 (hl.dept)              |
| binlog.000010 |  672 | Update_rows    |         1 |         747 | table_id: 3
08 flags: STMT_END_F      |
| binlog.000010 |  747 | Xid            |         1 |         778 | COMMIT /* x
id=4084 */                |
| binlog.000010 |  778 | Anonymous_Gtid |         1 |         857 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 |  857 | Query          |         1 |         939 | BEGIN      
                          |
| binlog.000010 |  939 | Table_map      |         1 |         998 | table_id: 3
08 (hl.dept)              |
| binlog.000010 |  998 | Update_rows    |         1 |        1073 | table_id: 3
08 flags: STMT_END_F      |
| binlog.000010 | 1073 | Xid            |         1 |        1104 | COMMIT /* x
id=4085 */                |
| binlog.000010 | 1104 | Anonymous_Gtid |         1 |        1183 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 | 1183 | Query          |         1 |        1265 | BEGIN      
                          |
| binlog.000010 | 1265 | Table_map      |         1 |        1324 | table_id: 3
08 (hl.dept)              |
| binlog.000010 | 1324 | Update_rows    |         1 |        1410 | table_id: 3
08 flags: STMT_END_F      |
| binlog.000010 | 1410 | Xid            |         1 |        1441 | COMMIT /* x
id=4087 */                |
| binlog.000010 | 1441 | Anonymous_Gtid |         1 |        1520 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 | 1520 | Query          |         1 |        1602 | BEGIN      
                          |
| binlog.000010 | 1602 | Table_map      |         1 |        1661 | table_id: 3
08 (hl.dept)              |
| binlog.000010 | 1661 | Update_rows    |         1 |        1747 | table_id: 3
08 flags: STMT_END_F      |
| binlog.000010 | 1747 | Xid            |         1 |        1778 | COMMIT /* x
id=4089 */                |
| binlog.000010 | 1778 | Anonymous_Gtid |         1 |        1855 | SET @@SESSI
ON.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 | 1855 | Query          |         1 |        1953 | drop databa
se hl /* xid=4094 */      |
| binlog.000010 | 1953 | Rotate         |         1 |        1997 | binlog.0000
11;pos=4                  |
+---------------+------+----------------+-----------+-------------+------------
--------------------------+
30 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=1855 /opt/data/binlog.000010  |mysql -uroot -pPassw0rd@_
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPassw0rd@_ -e 'select * from hl.dept'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | zhejiang |
|     50 | manage     | wuhan    |
+--------+------------+----------+