4. mysql数据库备份与恢复

本文主要是介绍4. mysql数据库备份与恢复，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

4.1 数据库常用备份方案

数据库备份方案：

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

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

4.2 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;  # 进入scholl数据库
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_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_course;  #  查看course表
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
+----+-------------+
6 rows in set (0.00 sec)

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

#  全量备份
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases >all-$(date '+%Y%m%d%H%M%S').sql  # 全量备份，备份到all加实时时间的文件里去
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630210748.sql  anaconda-ks.cfg   # 备份好的文件名字根据实时时间来取名
[root@localhost ~]# file all-20220630210748.sql  # 查看文件类型
all-20220630210748.sql: UTF-8 Unicode text, with very long lines # 是一个文本文件，很长的行

# 备份库里的其中一个表
[root@localhost ~]# mysqldump -uroot -p123456 school tb_course >tb_course-$(date '+%Y%m%d%H%M%S').sql # 备份school中的course表
[root@localhost ~]# ls
all-20220630210748.sql  tb_course-20220630212532.sql # 备份完成
anaconda-ks.cfg

# 备份school库
[root@localhost ~]# mysqldump -uroot -p123456 school > school-$(date'+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630210748.sql  school-20220630213209.sql
anaconda-ks.cfg         tb_course-20220630212532.sql

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

mysql> drop database school;   # 删除school库
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;   # 查看数据库已经没有school库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

4.3 mysql数据恢复

//恢复school数据库
[root@localhost ~]# mysql -uroot -p123456 < all-20220630210748.sql   #  回复全量备份就恢复了
mysql> show databases;  # 查看
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 误删school中的course表
mysql> show tables;   #查看school中的表
+------------------+
| Tables_in_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table tb_course;  # 删除course表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;    #  已经删除
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)

# 恢复方法1
mysql> use school;   进到shcool库
Database changed
mysql> source tb_course-20220630212532.sql;  # 读取备份的表文件
Query OK, 0 rows affected (0.00 sec)

mysql> show tables; # 恢复成功
+------------------+
| Tables_in_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tb_course;  # 查看里面的内容也都还在
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
+----+-------------+
6 rows in set (0.00 sec)

# 在删除，恢复方法2在外面命令行中恢复
mysql> drop table tb_course;  # 删除
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)

[root@localhost ~]# mysql -uroot -p123456 school <tb_course-20220630212532.sql  # 把这个表恢复到school库里
mysql> show tables;  # 查看恢复成功
+------------------+
| Tables_in_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

4.4 差异备份与恢复

4.4.1. 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=10    #设置服务器标识符
log-bin=mysql_bin     #开启二进制日志功能
[root@localhost ~]# systemctl restart mysqld #重启
[root@localhost ~]# cd /opt/data/ # 进到这个目录
[root@localhost data]# ll  # 会多出这两个文件
-rw-r-----. 1 mysql mysql      154 6月  30 22:14 mysql_bin.000001  # 日志文件
-rw-r-----. 1 mysql mysql       19 6月  30 22:14 mysql_bin.index #这个文件当前记录000001的日志文件

#开启二进制日志功能以后，重启，对数据库进行完全备份
[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql #单事物刷新日志全备份，会把之前000001截断重新记录在000002文件中
[root@localhost ~]# ll  #  两个全备大小不一样，所有里面的内容也有所差别
总用量 1732
-rw-r--r--. 1 root root 877661 6月  30 21:07 all-20220630210748.sql
-rw-r--r--. 1 root root 877813 6月  30 22:59 all-20220630225946.sql
-rw-------. 1 root root   1093 6月  29 01:04 anaconda-ks.cfg
-rw-r--r--. 1 root root   3095 6月  30 21:32 school-20220630213209.sql
-rw-r--r--. 1 root root   1981 6月  30 21:25 tb_course-20220630212532
[root@localhost ~]#  vimdiff all-20220630210748.sql all-20220630225946.sql  # 用vimdiff命令可以对比两个文件哪里不同
#  做完全量备份，新增一个info的表
mysql> create table info(id int not null primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert info(name) values('tom'),('jerey'),('zhangsan');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+----------+
| id | name     |
+----+----------+
|  1 | tom      |
|  2 | jerey    |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.01 sec)

#新增一些内容到表里
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> insert tb_course(course_name) value('linux');
Query OK, 1 row affected (0.01 sec)

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

mysql> update tb_students_info set height = 190 where id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0  # 修改id是10的身高
mysql>  delete from tb_students_info where id = 2;
Query OK, 1 row affected (0.00 sec)    # 删除id为2的
mysql> select * from tb_students_info;   #查看
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   23 | F    |    190 |         5 |
| 11 | Liming |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

4.4.2. mysql差异备份恢复

模拟误删数据

# 刚才只是做了全量备份，增加，删除，修改的内容都没有备份，此时删除school数据库
mysql> drop database school;  #  误删school库

Query OK, 3 rows affected (0.01 sec)

mysql>
mysql> show databases;   #查看已经没有了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新创建新的二进制日志

[root@localhost ~]# ll /opt/data/
-rw-r-----. 1 mysql mysql     1715 6月  30 23:55 mysql_bin.000002# 备份之后就变成000002了，包括了刚才所作的操作新建的info表，增加，修改，删除，包括删除shcool数据库

刷新创建新的二进制日志，刷新的原因是数据库一般都是很多人使用，往里面存储的数据非常多，难以定位我们要恢复的位子，刷新以后存储的数据会存储到新的文件里，我们只需要在老文件最后查找误删的位子即可

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs  # 刷新日志文件
[root@localhost ~]# ll /opt/data/  # 刷新以后会生产一个新的日志文件000003，我们只需要去000002里去找到误删的位子即可
-rw-r-----. 1 mysql mysql     1762 7月   1 00:07 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 7月   1 00:07 mysql_bin.000003

恢复完全备份

[root@localhost ~]# mysql -uroot -p123456 < all-20220630225946.sql  #恢复之前做的全量备份
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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

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

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

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   23 | F    |    165 |         5 |
| 11 | Liming |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
  #  恢复全量备份成功，但是后来增加的info表，和增加，删除，修改的数据都没有

恢复差异备份

mysql> show binlog events in 'mysql_bin.000002';# 在000002日志中查看日志事件，找到误删的位子1617

| mysql_bin.000002 | 1394 | Table_map      |        10 |        1464| table_id: 141 (school.tb_students_info)                          |
| mysql_bin.000002 | 1464 | Delete_rows    |        10 |        1521| table_id: 141 flags: STMT_END_F                          |
| mysql_bin.000002 | 1521 | Xid            |        10 |        1552| COMMIT /* xid=493 */                          |
| mysql_bin.000002 | 1552 | Anonymous_Gtid |        10 |        1617| SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                          |
| mysql_bin.000002 | 1617 | Query          |        10 |        1715| drop database school                          |
| mysql_bin.000002 | 1715 | Rotate         |        10 |        1762| mysql_bin.000003;pos=4                          |

使用mysqlbinlog恢复差异备份

[root@localhost data]# mysqlbinlog --stop-position=1617 /opt/data/mysql_bin.000002 | mysql -uroot -p123456  #恢复到1617误操作之前那一步，不用指定库，它可以自动定位

mysql> show tables;           #  查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
| tb_course        |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from tb_course;   # 新增的也恢复了
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
|  7 | linux       |
+----+-------------+
7 rows in set (0.00 sec)

mysql> select * from tb_students_info;                         # 删除修改的也恢复了
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   23 | F    |    190 |         5 |
| 11 | Liming |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

mysql> select * from info;                 #  新创建的表也恢复了
+----+----------+
| id | name     |
+----+----------+
|  1 | tom      |
|  2 | jerey    |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

这篇关于4. mysql数据库备份与恢复的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

4. mysql数据库备份与恢复

4.1 数据库常用备份方案

4.2 mysql备份工具mysqldump

4.3 mysql数据恢复

4.4 差异备份与恢复

4.4.1. mysql差异备份

4.4.2. mysql差异备份恢复

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯