Mysql数据库的备份与恢复

最新推荐文章于 2024-03-26 08:42:43 发布

烟雨时遇

最新推荐文章于 2024-03-26 08:42:43 发布

阅读量367

点赞数 8

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_65309423/article/details/136717008

版权

mysql数据库备份与恢复

数据库常用备份方案

数据库备份方案：

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

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

mysql备份工具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           |
+--------------------+
| cjy                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use cjy;
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_cjy |
+---------------+
| a_table       |
| b_table       |
| student       |
| test1         |
| test2         |
| test3         |
| test5         |
| test6         |
+---------------+
8 rows in set (0.01 sec)

[root@cjy ~]# ls
anaconda-ks.cfg
[root@cjy ~]# mkdir /data
[root@cjy ~]# ls /data/
[root@cjy ~]# date '+%Y%m%d'
20231208
[root@cjy ~]# 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@cjy ~]# ls /data/
all-20231208.sql
[root@cjy ~]# ll /data/
total 1264
-rw-r--r-- 1 root root 1292773 Dec  8 15:42 all-20231208.sql
[root@cjy ~]# du -sh /data/all-20231208.sql
1.3M	/data/all-20231208.sql



备份cjy库的test1表和test2表
[root@cjy ~]# mysqldump -uroot -pPassw0rd@_ cjy test1 test2 > /data/tb-$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cjy ~]# ls /data/
all-20231208.sql  tb-20231208.sql





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




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

mysql> drop database cjy;
Query OK, 8 rows affected (0.03 sec)

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

mysql数据恢复

恢复cjy数据库
[root@cjy ~]# mysql -uroot -pPassw0rd@_ < /data/cjy-20231208.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cjy ~]# 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 34
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           |
+--------------------+
| cjy                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


恢复cjy数据库的test1表和test2表
mysql> use cjy;
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_cjy |
+---------------+
| a_table       |
| b_table       |
| student       |
| test1         |
| test2         |
| test3         |
| test5         |
| test6         |
+---------------+
8 rows in set (0.00 sec)

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

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

mysql> show tables;
+---------------+
| Tables_in_cjy |
+---------------+
| a_table       |
| b_table       |
| student       |
| test3         |
| test5         |
| test6         |
+---------------+


mysql> source /data/tb-20231208.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 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)

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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 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, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

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)

Query OK, 0 rows affected (0.01 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, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

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)

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_cjy |
+---------------+
| a_table       |
| b_table       |
| student       |
| test1         |
| test2         |
| test3         |
| test5         |
| test6         |
+---------------+
8 rows in set (0.00 sec)


模拟删除整个数据库
[root@cjy ~]# 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 34
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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

恢复整个数据库
[root@cjy ~]# ls /data/
all-20231208.sql  cjy-20231208.sql  tb-20231208.sql
[root@cjy ~]# mysql -uroot -pPassw0rd@_ < /data/all-20231208.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cjy ~]# 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 34
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           |
+--------------------+
| cjy                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能（9.0以上版本的一般都是开启的）

[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@cjy data]# ls
all-20231208.sql  cjy-20231208.sql  tb-20231208.sql
[root@cjy data]# mysqldump -uroot -pPassw0rd@_ --single-transaction --flush-logs --source-data=2 --all-databases --delete-master-logs > all-2023121014.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --delete-master-logs is deprecated and will be removed in a future version. Use --delete-source-logs instead.
[root@cjy data]# ls
all-20231208.sql  all-2023121014.sql  cjy-20231208.sql  tb-20231208.sql

在数据库里面进行增加和修改
[root@cjy data]# mysql -uroot -p'Passw0rd@_'
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 38
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> use cjy;
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> select * from test5;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | heli    | 13   |
|    2 | wuyi    | 14   |
|    3 | jiangxi | 15   |
+------+---------+------+
3 rows in set (0.00 sec)

mysql> insert test5(id,name,age) values (4,'xuxiao',16);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test5;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | heli    | 13   |
|    2 | wuyi    | 14   |
|    3 | jiangxi | 15   |
|    4 | xuxiao  | 16   |
+------+---------+------+
4 rows in set (0.00 sec)

mysql> update test5 set age = 12 where name = 'heli';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test5;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | heli    | 12   |
|    2 | wuyi    | 14   |
|    3 | jiangxi | 15   |
|    4 | xuxiao  | 16   |
+------+---------+------+
4 rows in set (0.00 sec)

mysql差异备份恢复

模拟误删数据

mysql> drop database cjy;
Query OK, 8 rows affected (0.03 sec)

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


由上可以看到cjy这个数据库已被删除

刷新创建新的二进制日志

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

刷新创建新的二进制日志
[root@cjy data]# mysqladmin -uroot -pPassw0rd@_ flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@cjy data]# ls /opt/data/
 auto.cnf        client-cert.pem     '#innodb_redo'        public_key.pem
 binlog.000005   client-key.pem      '#innodb_temp'        server-cert.pem
 binlog.000006  '#ib_16384_0.dblwr'   mysql                server-key.pem
 binlog.index   '#ib_16384_1.dblwr'   mysql.ibd            sys
 ca-key.pem      ib_buffer_pool       mysql.pid            undo_001
 ca.pem          ibdata1              performance_schema   undo_002
 cjy.err         ibtmp1               private_key.pem

恢复完全备份

[root@cjy data]# mysql -uroot -pPassw0rd@_ < all-2023121014.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cjy data]# 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 41
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           |
+--------------------+
| cjy                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use cjy;
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_cjy |
+---------------+
| a_table       |
| b_table       |
| student       |
| test1         |
| test2         |
| test3         |
| test5         |
| test6         |
+---------------+
8 rows in set (0.00 sec)

mysql> select * from test5;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | heli    | 13   |
|    2 | wuyi    | 14   |
|    3 | jiangxi | 15   |
+------+---------+------+
3 rows in set (0.00 sec)

恢复差异备份

检查误删数据库的位置在什么地方
mysql> show binlog events in 'binlog.000005';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000005 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.35, Binlog ver: 4    |
| binlog.000005 | 126 | Previous_gtids |         1 |         157 |                                      |
| binlog.000005 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 236 | Query          |         1 |         310 | BEGIN                                |
| binlog.000005 | 310 | Table_map      |         1 |         371 | table_id: 332 (cjy.test5)            |
| binlog.000005 | 371 | Write_rows     |         1 |         423 | table_id: 332 flags: STMT_END_F      |
| binlog.000005 | 423 | Xid            |         1 |         454 | COMMIT /* xid=3363 */                |
| binlog.000005 | 454 | Anonymous_Gtid |         1 |         533 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 533 | Query          |         1 |         616 | BEGIN                                |
| binlog.000005 | 616 | Table_map      |         1 |         677 | table_id: 332 (cjy.test5)            |
| binlog.000005 | 677 | Update_rows    |         1 |         743 | table_id: 332 flags: STMT_END_F      |
| binlog.000005 | 743 | Xid            |         1 |         774 | COMMIT /* xid=3365 */                |
| binlog.000005 | 774 | Anonymous_Gtid |         1 |         851 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 851 | Query          |         1 |         952 | drop database cjy /* xid=3368 */     |
| binlog.000005 | 952 | Rotate         |         1 |         996 | binlog.000006;pos=4                  |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
15 rows in set (0.00 sec)


使用mysqlbinlog恢复差异备份
[root@cjy data]# mysqlbinlog --stop-position 851 /opt/data/binlog.000005 | mysql -uroot -pPassw0rd@_
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cjy data]# 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 43
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> use cjy;
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> select * from test5;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
|    1 | heli    | 12   |
|    2 | wuyi    | 14   |
|    3 | jiangxi | 15   |
|    4 | xuxiao  | 16   |
+------+---------+------+
4 rows in set (0.00 sec)