失眠网 > MySQL数据库完全备份与恢复

MySQL数据库完全备份与恢复

时间：2020-12-23 23:19:49

相关推荐

MySQL数据库完全备份与恢复

数据库的备份与恢复

一、数据库备份的重要性二、数据库的备份分类三、完全备份、增量备份概述与对比四、完全备份的实例1.4.1、冷备份与数据恢复1.4.2、mysqldump备份与恢复五、增量备份的实例（基于完全备份）1.5.1、一般恢复1.5.2、时间点恢复

一、数据库备份的重要性

数据库备份的重要性主要体现在：

提高系统的高可用性和灾难可恢复性，在数据库系统崩溃是。没有数据库备份就没办法找到数据。使用数据库备份还原数据库，是数据库崩溃是提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价太大。没有数据就没有一切，数据库备份是一种防范灾难的强力手段。

使用数据库的过程中，有多种原因造成数据的丢失：

程序错误：指对数据库操作的程序难免有些错误，造成数据丢失。人为错误：误操作造成的数据被破坏，也可能是黑客对系统攻击造成的。计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏。磁盘失败：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数据丢失。

二、数据库的备份分类

从物理与逻辑的角度，备份可分为

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

物理备份方法

冷备份（脱机备份）：是在关闭数据库的时候进行的热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

从数据库的备份策略角度备份可分为：

完全备份：每次对数据库进行完整的备份差异备份：备份自从上次完全备份之后被修改过的文件增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

三、完全备份、增量备份概述与对比

完全备份：

是对整个数据库、数据库结构和文件结构的备份；保存的是备份完成时刻的数据库；是差异备份与增量备份的基础每次对数据进行完整的备份

优点：备份与恢复操作简单方便

缺点：

数据存在大量的重复；

占用大量的备份空间；

备份与恢复时间长。

增量备份：

MySQL增量备份是自上一次备份后增加/变化的文件或者内容特点没有重复数据，备份量不大，时间短；依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低

MySQL二进制日志对增量备份有重要的作用

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

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

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

备份方式比较

四、完全备份的实例

1.4.1、冷备份与数据恢复

备份前需要先停止数据库服务，在直接打包压缩数据库文件

数据库所有文件目录位置：/usr/local/mysql/data

故障时，停止服务，将故障的数据库文件移走到备份文件夹中，解压刚才备份数据库包到/restore目录下，再移动到/usr/local/mysql/data下，再重启服务

恢复成功

1.4.2、mysqldump备份与恢复

备份方法：

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql###备份所有数据库到当前目录下的all-data-$(date +%F).sql 文件mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库mysqldump -u root -p auth > auth-$(date +%F).sql ###备份auth数据库mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

实例一

单独备份一个auth库

mysql> drop database auth;Query OK, 3 rows affected (0.02 sec)mysql> create database test;#命名可根据需要Query OK, 1 row affected (0.00 sec)mysql> exitBye方法一：在数据库外导入[root@server1 ~]# mysql -u root -p test < auth--11-02.sql #将刚刚备份的导入testEnter password: 方法二：在数据库内用source#这里必须要use 数据库，否则无法有对应的数据库可以导入mysql> use test;mysql> source /auth--11-02.sql;查看恢复结果mysql> show tables;+-------------------+| Tables_in_student |+-------------------+| info || test || zf|+-------------------+3 rows in set (0.00 sec)mysql> select * from info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)

实例2

备份多个数据库

[root@server1 ~]# mysqldump -uroot -pzbc123 --databases mysql student > mysql-student-bak.sql#备份数据库mysql和student到当前目录下的mysql-student.sql，可查看到，也可加绝对路径保存[root@server1 ~]# ll总用量 48784-rw-------. 1 root root1878 8月 11 04:02 anaconda-ks.cfg-rw-r--r--. 1 root root1926 8月 11 04:49 initial-setup-ks.cfgdrwxr-xr-x. 38 7161 314154096 10月 22 11:11 mysql-5.7.20-rw-r--r--. 1 root root 48833145 10月 22 10:31 mysql-boost-5.7.20.tar.gz ####-rw-r--r--. 1 root root 1101429 11月 2 12:14 mysql-student-bak.sql......

误删除两个库

mysql> drop database mysql;Query OK, 32 rows affected, 2 warnings (0.04 sec)mysql> drop database student;Query OK, 3 rows affected, 2 warnings (0.00 sec)

恢复：无需再创建数据库，可直接恢复

mysql> source /root/mysql-auth-bak.sql查看恢复结果mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || student || sys|+--------------------+5 rows in set (0.00 sec)mysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)

五、增量备份的实例（基于完全备份）

增量备份分类：

一般恢复将所有备份的二进制日志内容全部恢复断点恢复

基于位置恢复：

据库在某一时间点可能既有错误的操作也有正确的操作可以基于精准的位置跳过错误的操作

基于时间点恢复：

跳过某个发生错误的时间点实现数据恢复

MySQL二进制日志对增量备份有重要的作用

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

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

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

修改配置文件，开启二进制日志功能

vi /etc/f.....[root@server1 ~]# [mysqld]末尾加log-bin=mysql-bin 前面是功能名称，后面是二进制日志文件名称#重启数据库[root@server1 ~]# systemctl restart mysqld[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll总用量 122924-rw-r-----. 1 mysql mysql 56 10月 22 11:29 f-rw-r-----. 1 mysql mysql917 11月 2 15:45 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibdata1-rw-r-----. 1 mysql mysql 50331648 11月 2 15:45 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibtmp1drwxr-x---. 2 mysql mysql4096 11月 2 12:51 mysql-rw-r-----. 1 mysql mysql154 11月 2 15:45 mysql-bin.000001 #生成二进制日志文件-rw-r-----. 1 mysql mysql 19 11月 2 15:45 mysql-bin.index

以下所有的数据库操作都会记录在mysql-bin.000001这个二进制文件中

#原有的记录，已做了完全备份ysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+3 rows in set (0.00 sec)#先做一些数据库操作mysql> insert into auth.info values(4,'yangli','南京');Query OK, 1 row affected (0.02 sec)#误删除了一条记录mysql> delete from auth.info where name='lisi';Query OK, 1 row affected (0.01 sec)#又插入了一条数据mysql> insert into auth.info values(5,'lili','南京');Query OK, 1 row affected (0.01 sec)mysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)

恢复：先刷新二进制文件，mysql-bin.000002里会存储后续的数据库操作，而不会在存储在bysql-bin.000001中了

[root@server1 ~]# mysqladmin -uroot -p flush-logsEnter password: [root@server1 ~]# ll /usr/local/mysql/data/总用量 122928-rw-r-----. 1 mysql mysql 56 10月 22 11:29 f-rw-r-----. 1 mysql mysql917 11月 2 15:45 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 11月 2 16:49 ibdata1-rw-r-----. 1 mysql mysql 50331648 11月 2 16:49 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 11月 2 15:45 ibtmp1drwxr-x---. 2 mysql mysql4096 11月 2 12:51 mysql-rw-r-----. 1 mysql mysql1019 11月 2 16:57 mysql-bin.000001##刷新之前的操作存储在这里-rw-r-----. 1 mysql mysql154 11月 2 16:57 mysql-bin.000002##新增的二进制文件-rw-r-----. 1 mysql mysql 38 11月 2 16:57 mysql-bin.index

1.5.1、一般恢复

直接把整个二进制文件的内容进行恢复

mysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)

误删除表后，先用完全备份恢复，在用二进制文件恢复表至上述状态

mysql> drop database auth;Query OK, 0 rows affected (0.01 sec)#完全备份mysql> source /root/mysql-auth-bak.sql;mysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 1 | lisi | 苏州 || 2 | liqi | 杭州 || 3 | wangwu | 北京 |+----+--------+---------+#一般备份[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pzbc123#查看恢复结果mysql> select * from auth.info;+----+--------+---------+| id | name | address |+----+--------+---------+| 2 | liqi | 杭州 || 3 | wangwu | 北京 || 4 | yangli | 南京 || 5 | lili | 南京 |+----+--------+---------+4 rows in set (0.00 sec)