MySQL 备份恢复
================================================================================
概述:
================================================================================
1. 介绍
2. 备份类型
3. 备份需要考虑的因素、备份策略及备份内容
4. 备份工具:
演示:
1. 备份 hellodb 数据库的所有表,操作如下:
- [ ~]# mkdir mysql.bak # 这里我先创建一个专门存放备份的目录# 使用mysqldump 指明用户账户,要备份的数据库和重定向的位置即可 [ ~]# mysqldump - uroot - hlocalhost - ptaoxiu hellodb > . / mysql.bak / hellodb.sql.1[ ~]# ls . / mysql.bak / hellodb.sql.1# 查看备份的hellodb数据库,可以看到表头显示的版本信息,数据库名称等 [ ~]# cat mysql.bak / hellodb.sql.1 -- MySQL dump 10.14 Distrib 5.5.44 - MariaDB,
- for Linux (x86_64)---- Host: localhost Database: hellodb-- -------------------------------------------------------- Server version 5.5.44 - MariaDB
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
- ;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
- ;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
- ;
- /*!40101 SET NAMES utf8 */
- ;
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
- ;
- /*!40103 SET TIME_ZONE='+00:00' */
- ;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
- ;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
- ;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
- ;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
- ;
2. 如上题,因为我们仅是备份指定数据库的所有表,所以没有 create database 语句,现在我们添加
--databases 选项来备份,可以指明要备份的单个或者多个数据库,这样的话就 create database 语句了;
- [ ~]# mysqldump - uroot - hlocalhost - ptaoxiu --databases hellodb > . / mysql.bak / hellodb.sql.2[ ~]# ll - h mysql.bak total 16K - rw - r--r-- 1 root root 7.6K Dec 2 18 : 27 hellodb.sql.1 - rw - r--r-- 1 root root 7.8K Dec 2 18 : 41 hellodb.sql.2[ ~]# cat mysql.bak / hellodb.sql.2-- MySQL dump 10.14 Distrib 5.5.44 - MariaDB,
- for Linux (x86_64)---- Host: localhost Database: hellodb-- -------------------------------------------------------- Server version 5.5.44 - MariaDB
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
- ;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
- ;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
- ;
- /*!40101 SET NAMES utf8 */
- ;
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
- ;
- /*!40103 SET TIME_ZONE='+00:00' */
- ;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
- ;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
- ;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
- ;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
- ; ---- Current Database: `hellodb`--# 存在创建数据库的语句CREATE DATABASE
- /*!32312 IF NOT EXISTS*/
- `hellodb`
- /*!40100 DEFAULT CHARACTER SET utf8 */
- ;
- USE `hellodb`;
3. 备份整个数据库,使用 --all-databases 选项
- [ ~]# mysqldump - uroot - hlocalhost - ptaoxiu --all - databases > . / mysql.bak / data.sql.3[ ~]# ll - h mysql.bak total 15M - rw - r--r-- 1 root root 15M Dec 2 18 : 45 data.sql.3 # 备份的所有数据库,明显文件大很多 - rw - r--r-- 1 root root 7.6K Dec 2 18 : 27 hellodb.sql.1 - rw - r--r-- 1 root root 7.8K Dec 2 18 : 41 hellodb.sql.2
4. 假如我们的备份策略为完全 + 增量 + binlog 备份,要使用 binlog 二进制日志重读,就要确定从备份那一刻开始,binlog 的起始文件位置,这时就要使用 --master-data=[#] 选项,(确保二进制日志是开启的)
- [ ~]# mysqldump - uroot - hlocalhost - ptaoxiu --master - data = 2 --databases hellodb > . / mysql.bak / hellodb.sql.2[ ~]# cat mysql.bak / hellodb.sql.2-- MySQL dump 10.14 Distrib 5.5.44 - MariaDB,
- for Linux (x86_64)---- Host: localhost Database: hellodb-- -------------------------------------------------------- Server version 5.5.44 - MariaDB - log
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
- ;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
- ;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
- ;
- /*!40101 SET NAMES utf8 */
- ;
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
- ;
- /*!40103 SET TIME_ZONE='+00:00' */
- ;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
- ;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
- ;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
- ;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
- ; ---- Position to start replication or point - in-time recovery from-- # 可以看到在备份的那一刻,二进制日志处在master - log.000004,POS为245-- CHANGE MASTER TO MASTER_LOG_FILE = 'master - log.000003',
- MASTER_LOG_POS = 245; ---- Current Database: `hellodb`--CREATE DATABASE
- /*!32312 IF NOT EXISTS*/
- `hellodb`
- /*!40100 DEFAULT CHARACTER SET utf8 */
- ;
- USE `hellodb`;
-----------------------------------------------------------------------
基于备份做时间点恢复数据库:
如上,我们已经备份好了 hellodb 的数据库,假如在备份之后,用户又修改了 hellodb 数据库的内容,之后 hellodb 数据库因为某些原因挂了,这时,我们除了借助于备份的数据库之外,还要借助于二进制日志文件 binlog 才能把数据库恢复到崩溃前的时间点。
1. 为了演示效果这里我首先修改一下 hellodb 数据库,模拟在 hellodb 数据库数据备份之后,崩溃之前,用户增加,修改的 hellodb 数据库中的内容,如下:
- MariaDB [hellodb] > SELECT * FROM courses; # 修改之前的hellodb数据库courses表 + ----------+----------------+| CourseID | Course | +----------+----------------+| 1 | Hamo Gong | | 2 | Kuihua Baodian | | 3 | Jinshe Jianfa | | 4 | Taijiquan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | | 7 | Dagou Bangfa | | 14 | Zabbix | +----------+----------------+8 rows in set (0.00 sec) MariaDB [hellodb] > INSERT INTO courses (Course) VALUES ('Puppet'),
- ('Ansible');
- Query OK,
- 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [hellodb] > DELETE FROM courses WHERE CourseID = 2;
- Query OK,
- 1 row affected (0.07 sec) MariaDB [hellodb] > SELECT * FROM courses; # 修改之后的hellodb数据库courses表 + ----------+---------------+| CourseID | Course | +----------+---------------+| 1 | Hamo Gong | | 3 | Jinshe Jianfa | | 4 | Taijiquan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | | 7 | Dagou Bangfa | | 14 | Zabbix | | 15 | Puppet | | 16 | Ansible | +----------+---------------+9 rows in set (0.00 sec) MariaDB [(none)] > drop database hellodb; # 删除hellodb数据库,模拟数据库崩溃Query OK,
- 7 rows affected (0.08 sec)
2. 如上 hellodb 数据库已经崩溃,现在要做恢复,就要准备好备份的数据库文件 hellodb.sql 和要做时间点恢复的二进制日志文件,如下:
1) 首先准备要做时间点恢复的二进制日志文件
- # 首先准备二进制日志文件 [ ~]# ls /
- var / lib / mysql / aria_log.00000001 centos7.log ibdata1 ib_logfile1 master - log.000002 master - log.000004 mydb mysql.sock Syslog testdb zabbix aria_log_control centos7 - slow.log ib_logfile0 master - log.000001 master - log.000003 master - log.index mysql performance_schema test ultrax# 因为我最后执行的那个DELETE删除hellodb数据库的命令也会被记录在二进制日志文件中,所以,# 要把最后的那个position截取掉,这里我重定向到 / tmp / 如下: [ ~]# mysqlbinlog --stop - position = 7996 /
- var / lib / mysql / master - log.000004 > / tmp / mylog.sql
2) 准备好备份好的 hellodb 数据库的备份文件 hellodb.sql
- [ ~]# ls mysql.bak data.sql.3 hellodb.sql.1 hellodb.sql.2 hellodb.sql.4[ ~]# cp mysql.bak / hellodb.sql.2 / tmp / hellodb.sql # 假设在 / tmp目录下 [ ~]# ls / tmp /
3. 测试,登录数据库,因为在恢复数据时会执行大量的写操作,但没有必要记录在二进制日志文件中,所以,可以关闭当前会话的二进制日志文件;然后倒入 hellodb.sql 脚本,可以发现 hellodb 数据库正常恢复到备份的时间点的数据,如下:
- [ ~]# mysql - ptaoxiu Welcome to the MariaDB monitor. Commands end with ; or \g.MariaDB [(none)] > SET sql_log_bin = OFF; # 关闭当前会话的二进制日志文件Query OK,
- 0 rows affected (0.00 sec) MariaDB [(none)] > \. / tmp / hellodb.sql # 倒入hellodb.sql脚本文件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)...MariaDB [hellodb] > select * from courses; # 查询发现数据已经恢复到备份时间点的数据 + ----------+----------------+| CourseID | Course | +----------+----------------+| 1 | Hamo Gong | | 2 | Kuihua Baodian | | 3 | Jinshe Jianfa | | 4 | Taijiquan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | | 7 | Dagou Bangfa | | 14 | Zabbix | +----------+----------------+8 rows in set (0.00 sec)
4. 接下来,我们要想恢复到数据库崩溃之前的时间点,就要借助于二进制日志文件来恢复,如下:
- MariaDB [(none)] > \. / tmp / mylog.sql # 导入二进制日志文件的sql脚本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)...MariaDB [hellodb] > select * from courses; # 查看数据可以发现,hellodb库的courses表恢复到了崩溃前我们修改的数据状态了 + ----------+---------------+| CourseID | Course | +----------+---------------+| 1 | Hamo Gong | | 3 | Jinshe Jianfa | | 4 | Taijiquan | | 5 | Daiyu Zanghua | | 6 | Weituo Zhang | | 7 | Dagou Bangfa | | 14 | Zabbix | | 15 | Puppet | | 16 | Ansible | +----------+---------------+9 rows in set (0.00 sec) MariaDB [(none)] > SET sql_log_bin = ON; # 开启记录二进制日志的文件Query OK,
- 0 rows affected (0.00 sec)
来源: http://www.bubuko.com/infodetail-1857789.html