MySQL数据库备份与恢复

一.数据备份的重要性

1.在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。

2.造成数据丢失的原因

程序错误、人为错误、计算机失败、磁盘失败、物理灾难

二.数据库备份的分类

1.从物理与逻辑的角度区分

物理备份：

指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。

物理备份又可以脱机备份（冷备份：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性）和联机备份（热备份：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件

逻辑备份：

指对数据库的逻辑组件（如表等数据库对象）的备份

2.从数据库的备份策略角度区分

完全备份：

每次都对数据库进行完整的备份

差异备份：

备份那些自从上次完全备份之后被修改过的文件

增量备份：

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

三.完全备份与恢复

1.完全备份是对整个数据库的备份，数据库结构和文件结构的备份

2.完全备份保存的是备份完成时刻的数据库

3.完全备份时增量备份的基础

4.优点：

备份与恢复操作简单方便

5.缺点：

数据存在大量重复，占用大量的备份空间，备份时间长

四.备份操作

首先创建数据库数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

mysql> create database school;

mysql> use school;

mysql> create table info (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal(4,1) not null);

mysql> desc info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(4)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
| score | decimal(4,1) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> insert into info (name,score) values ('stu01',88),('stu02',77);

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+

1.使用tar打包文件夹备份

[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin  COPYING  COPYING-test  data  docs  include  lib  man  mysqld.pid  mysql.sock  mysql.sock.lock  mysql-test  README  README-test  share  support-files  usr
[root@localhost mysql]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/

[root@localhost mysql]# ls /opt
mysql-2020-11-05.tar.xz  mysql-5.7.20  rh

2.mysqldump命令对单个库进行完全备份

[root@localhost mysql]# mysqldump -uroot -p school > /opt/school.sql
Enter password: 
[root@localhost mysql]# ls /opt
mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school.sql

3.mysqldump命令对多个库进行完全备份

[root@localhost mysql]# mysqldump -u root -p --databases school mysql > /opt/db_school_mysql.sql
Enter password: 
[root@localhost mysql]# ls /opt
db_school_mysql.sql  mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school.sql

4.对所有库进行完全备份

[root@localhost mysql]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql
Enter password: 
[root@localhost mysql]# ls /opt
all.sql  db_school_mysql.sql  mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school.sql

5.对一张表的备份

[root@localhost mysql]# mysqldump -uroot -p school info > /opt/school_info.sql
Enter password: 
[root@localhost mysql]# ls /opt
all.sql  db_school_mysql.sql  mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school_info.sql  school.sql

6.对表结构的备份

[root@localhost mysql]# mysqldump -uroot -p -d school info > /opt/info.sql
Enter password: 
[root@localhost mysql]# ls /opt
all.sql  db_school_mysql.sql  info.sql  mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school_info.sql  school.sql

7.使用source恢复

mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

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

mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/school.sql

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+

8.使用外部mysql命令进行恢复

mysql> drop table info;

mysql> show tables;
Empty set (0.00 sec)

[root@localhost mysql]# mysql -uroot -pabc123 school < /opt/school.sql 
[root@localhost mysql]# mysql -uroot -p
Enter password: 
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+

五.增量备份与恢复

1.增量备份的优点

没有重复数据，备份量不大，时间短；

2.缺点

需要上次完全备份以及完全备份之后的所有增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁琐

3.MySQL没有直接提供增量备份的方法，但是通过MySQL的二进制日志间接实现增量备份

4.二进制日志保存了所有更新或者可能更新或者可能更新数据库的操作

5.二进制日志在启动MySQL服务器后开始记录，并且在文件达到max_binlog_size所设置的大小或者接收到的flush logs命令之后重新创建日志文件

6.只需要定时执行flush logs 方法重新创建新的日志，生成日志文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

六.具体操作

1.开启二进制日志功能，重启服务（mysqld里添加）

[root@localhost opt]# vim /etc/my.cnf

log-bin=mysql-bin
[root@localhost opt]# systemctl restart mysqld.service

2.使用mysqldump工具进行完全备份

[root@localhost data]# mysqldump -uroot -pabc123 school > /opt/school.sql
[root@localhost data]# ls /opt
mysql-2020-11-05.tar.xz  mysql-5.7.20  rh  school.sql

3.创建增量备份支持的文件

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs

4.进行误操作

[root@localhost data]# mysql -u root -pabc123

mysql> use school;
mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
mysql> insert into info (name,score) values ('test01',66);
mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | stu01  |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
+----+--------+-------+

mysql> delete from info where name='stu01';

mysql> insert into info (name,score) values ('test02',99);

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
|  4 | test02 |  99.0 |
+----+--------+-------+

5.基于完全备份进行恢复：

[root@localhost opt]# mysql -u root -pabc123

mysql> use school;

mysql> drop table info;

mysql> source /opt/school.sql

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+

基于时间：

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2020-11-05 17:18:37' /usr/local/mysql-bin.000002 | mysql -u root -p

[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2020-11-05 17:20:25' /usr/local/mysql-bin.000002 | mysql -u root -p

基于位置：

[root@localhost opt]# mysqlbinlog --no-defaults --stop-postion='443' /usr/local/mysql-bin.000002 | mysql -u root -p

[root@localhost opt]# mysqlbinlog --no-defaults --start-postion='456' /usr/local/mysql-bin.000002 | mysql -u root -p

[root@localhost data]# mysql -u root -pabc123

mysql> use school;
mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02  |  77.0 |
|  3 | test01 |  66.0 |
|  4 | test02 |  99.0 |
+----+--------+-------+

posted @ 2020-11-05 22:44 escape丶丶阅读(118) 评论(0) 收藏举报

刷新页面返回顶部

刘伟233