mysql数据库备份与恢复

最新推荐文章于 2024-10-10 21:13:48 发布

Op_Wa

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量147

点赞数

分类专栏： Mysql 文章标签： mysql big data 数据库

本文链接：https://blog.csdn.net/Op_Wa/article/details/119939090

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql数据库备份与恢复

数据库常用备份方案

数据库备份方式：
- 全量备份
- 增量备份
- 差异备份

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

mysql备份工具mysqldump

mysqldump用法

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

备份所有的数据库

//因为每周数据库都需要备份，所以备份的时候可以加上日期，方便查看，备份的文件需要以.sql结尾
[root@localhost ~]# mysqldump -uroot -p --all-databases > all-database-$(date '+%Y%m%d').sql
Enter password: 
[root@localhost ~]# ls 
all-database-20210826.sql  anaconda-ks.cfg

备份库wa里面的表student

[root@localhost ~]# mysqldump -uroot -p wa student > student-$(date '+%Y%m%d').sql
Enter password: 
[root@localhost ~]# ls 
all-database-20210826  anaconda-ks.cfg  student-20210826.sql

备份指定的库school

[root@localhost ~]# mysqldump -uroot -p  --databases school > school-$(date '+%Y%m%d').sql
Enter password: 
[root@localhost ~]# ls 
all-database-20210826  anaconda-ks.cfg  school-20210826  student-20210826.sql

误删库wa中的表student，恢复表student，恢复表时要指定库（如果是针对某一个表备份时，在恢复时可以恢复到其他的表，恢复的表可以是之前的库，也可以是其他的库，可以做迁移）

//删除表student
MariaDB [wa]> drop table student;
Query OK, 0 rows affected (0.003 sec)

//恢复表student，回复时需要指定库
[root@localhost ~]# mysql -uroot -p wa  < student-20210826.sql 
Enter password: 

MariaDB [wa]> show tables;
+--------------+
| Tables_in_wa |
+--------------+
| student      |
+--------------+
1 row in set (0.000 sec)

//第二种恢复表的方法，可以进入指定的库，在库里面恢复，因为之前已经恢复，先删除表student
MariaDB [wa]> drop table student;
Query OK, 0 rows affected (0.003 sec)
//恢复表student
MariaDB [wa]> source student-20210826.sql;
//查看是否恢复成功
MariaDB [wa]> show tables;
+--------------+
| Tables_in_wa |
+--------------+
| student      |
+--------------+
1 row in set (0.000 sec)

误删库school，使用备份恢复库school

//误删除库school
MariaDB [(none)]> drop database school;
Query OK, 2 rows affected (0.004 sec)

//恢复库school
[root@localhost ~]# mysql -uroot -p < school-20210826.sql 
Enter password: 

//查看是否恢复成功
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| wa                 |
+--------------------+
5 rows in set (0.001 sec)

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能
开启日志功能后，所有的写操作（增、删、改）都会被记录到日志中

[root@localhost ~]# vim /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  //设置服务器标识符，只能设置为整数，这里设置为1
log-bin=mysql_bin //开启二进制功能，名字可以自己设置，一般设置为mysql_bin

[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port    Process    
LISTEN    0         128                0.0.0.0:22               0.0.0.0:*                  
LISTEN    0         128                   [::]:22                  [::]:*                  
LISTEN    0         80                       *:3306                   *:*

对数据库进行完全备份

[root@localhost ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d').sql
Enter password: 

[root@localhost ~]# ls 
all-20210826.sql           

//新增几条内容，修改一些数据
mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   25 |
|  3 | lisi      |   30 |
|  4 | zhangshan |   35 |
+----+-----------+------+
4 rows in set (0.00 sec)

mysql> insert student(name,age) values('qianliu',21),('wangwu',33),('xiaowang',45);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update student set age = 22 where name = 'tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   22 |
|  2 | jerry     |   25 |
|  3 | lisi      |   30 |
|  4 | zhangshan |   35 |
|  5 | qianliu   |   21 |
|  6 | wangwu    |   33 |
|  7 | xiaowang  |   45 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql 差异备份恢复

模拟误删数据

//删除数据库school
mysql> drop database school;
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_bin.000002
[root@localhost ~]# ll /opt/data 
total 122988
-rw-r-----. 1 mysql mysql       56 Aug 26 14:56 auto.cnf
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 client-cert.pem
-rw-------. 1 mysql mysql     1676 Aug 26 14:56 client-key.pem
-rw-r-----. 1 mysql mysql      429 Aug 26 18:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:11 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 26 19:11 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 26 14:56 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:01 ibtmp1
-rw-r-----. 1 mysql mysql    26928 Aug 26 18:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug 26 14:56 mysql
-rw-r-----. 1 mysql mysql      902 Aug 26 19:09 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Aug 26 19:01 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Aug 26 18:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 performance_schema
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Aug 26 14:56 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 server-cert.pem
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 sys

//刷新日志
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password: 

//查看新生成的日志文件为 mysql_bin.000003
[root@localhost ~]# ll /opt/data 
total 122992
-rw-r-----. 1 mysql mysql       56 Aug 26 14:56 auto.cnf
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 client-cert.pem
-rw-------. 1 mysql mysql     1676 Aug 26 14:56 client-key.pem
-rw-r-----. 1 mysql mysql      429 Aug 26 18:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:11 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 26 19:11 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 26 14:56 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:01 ibtmp1
-rw-r-----. 1 mysql mysql    26928 Aug 26 18:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug 26 14:56 mysql
-rw-r-----. 1 mysql mysql      949 Aug 26 19:12 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 Aug 26 19:12 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Aug 26 19:12 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Aug 26 18:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 performance_schema
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Aug 26 14:56 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 server-cert.pem
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 sys

模拟后续有人继续操作数据库，这里以创建一个新的库为例

//创建库study，创建表info
mysql> create database study;
Query OK, 1 row affected (0.00 sec)

mysql> use study ;
Database changed
mysql> create table info(id int(11) not null , name varchar(100) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert info(id,name) values(1,'xiaozhang'),(2,'xiaoli');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

恢复完全备份

//恢复完全备份
[root@localhost ~]# mysql -uroot -p < all-20210826.sql 
Enter password: 

//查看是否恢复成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| study              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
//由以上恢复完成备份可以看到，我们在删除库school之后创建的库study依然存在，没有被恢复完全备份覆盖

恢复差异备份

[root@localhost ~]# ll /opt/data
total 123816
-rw-r-----. 1 mysql mysql       56 Aug 26 14:56 auto.cnf
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 client-cert.pem
-rw-------. 1 mysql mysql     1676 Aug 26 14:56 client-key.pem
-rw-r-----. 1 mysql mysql      429 Aug 26 18:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:23 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 26 19:23 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 26 14:56 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 26 19:17 ibtmp1
-rw-r-----. 1 mysql mysql    26928 Aug 26 18:57 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug 26 19:21 mysql
-rw-r-----. 1 mysql mysql      949 Aug 26 19:12 mysql_bin.000002
-rw-r-----. 1 mysql mysql   844038 Aug 26 19:21 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Aug 26 19:12 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Aug 26 18:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 performance_schema
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Aug 26 14:56 public_key.pem
drwxr-x---. 2 mysql mysql       96 Aug 26 19:21 school
-rw-r--r--. 1 mysql mysql     1112 Aug 26 14:56 server-cert.pem
-rw-------. 1 mysql mysql     1680 Aug 26 14:56 server-key.pem
drwxr-x---. 2 mysql mysql       52 Aug 26 19:16 study
drwxr-x---. 2 mysql mysql     8192 Aug 26 14:56 sys

//检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         293 | BEGIN                                 |
| mysql_bin.000002 | 293 | Table_map      |         1 |         349 | table_id: 140 (school.student)        |
| mysql_bin.000002 | 349 | Write_rows     |         1 |         426 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 | 426 | Xid            |         1 |         457 | COMMIT /* xid=484 */                  |
| mysql_bin.000002 | 457 | Anonymous_Gtid |         1 |         522 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 522 | Query          |         1 |         596 | BEGIN                                 |
| mysql_bin.000002 | 596 | Table_map      |         1 |         652 | table_id: 140 (school.student)        |
| mysql_bin.000002 | 652 | Update_rows    |         1 |         708 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 | 708 | Xid            |         1 |         739 | COMMIT /* xid=486 */                  |
| mysql_bin.000002 | 739 | Anonymous_Gtid |         1 |         804 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 804 | Query          |         1 |         902 | drop database school                  |
| mysql_bin.000002 | 902 | Rotate         |         1 |         949 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=804 /opt/data/mysql_bin.000002 | mysql -uroot -p
Enter password: 
[root@localhost ~]# mysql -uroot -p -e'select * from school.student'
Enter password: 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   22 |
|  2 | jerry     |   25 |
|  3 | lisi      |   30 |
|  4 | zhangshan |   35 |
|  5 | qianliu   |   21 |
|  6 | wangwu    |   33 |
|  7 | xiaowang  |   45 |
+----+-----------+------+
//以上恢复可以看到我们之前插入的id=6,id=7和修改的tom年龄为22都以恢复

在恢复差异备份时，除了使用命令mysqlbinlog，还可以使用修改日志文件内容来恢复

[root@localhost ~]# ll /opt/data 
total 123816
-rw-r-----. 1 mysql mysql      949 Aug 26 19:12 mysql_bin.000002
-rw-r-----. 1 mysql mysql   844611 Aug 26 19:33 mysql_bin.000003

//将日志文件写入一个文件，名字自己定义
[root@localhost ~]# mysqlbinlog /opt/data/mysql_bin.000002 > 02.sql

//编辑文件02.sql，找到删除库school的操作，删除这条操作记录
[root@localhost ~]# vim 02.sql
drop database school //找到此条记录，删除掉，因为之前在删除库school时，我们及时刷新了日志，所以一般这条记录在最下方，查找时可以直接去最下方

//使用文件02.sql恢复
[root@localhost ~]# cat 02.sql | mysql -uroot -pwa123