前言
在生产环境中,数据的安全性至关重要,任何数据的丢失都可能产生严重的后果。如:程序错误、人为操作错误、运算错误、磁盘故障、灾难(如火灾、地震)和盗窃等原因都会造成数据丢失。那如何避免减少类似的故障和损失呢?接下来将认识数据库备份与恢复相关内容,从而可以更好的保护数据,保证业务的连续性。
目录
一、数据备份类型
1. 容灾简图
2. 从物理与逻辑角度
2.1 物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份,适用于故障快速恢复。
- 冷备份(脱机备份):是在关闭数据库的时候进行的
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的二进制日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
2.2 逻辑备份
对数据库逻辑组件(如:表数据、结构等数据库对象)的备份,适用于可以编辑数据值或表结构。
3. 从备份策略角度
3.1 完全备份
每次对数据都进行完整备份,备份文件占用大量的空间,并且存在大量的重复数据;恢复将文件导入即可,刚开始速度很快,随着数据增加,速度会越来越慢。
3.2 差异备份
备份自从上次完全备份之后被修改过的文件,可能存在重复数据,也会占用额外的空间;恢复时,先恢复完全备份的数据,再导入差异备份的数据。
3.3 增量备份
备份上次完整或增量之后的数据,不会出现重复的数据,不会占用额外的磁盘空间;恢复时先要完全恢复,再依次做增量恢复。
3.4 备份方式比较
操作/备份方式 | 完全备份 | 差异备份 | 增量备份 |
完全备份时的状态 | 表1 | 表1 | 表1 |
第1次添加内容 | 创建表2 | 创建表2 | 创建表2 |
备份内容 | 表1、表2 | 表2 | 表2 |
第2次添加内容 | 创建表3 | 创建表3 | 创建表3 |
备份内容 | 表1、表2、表3 | 表2、表3 | 表3 |
…… | …… | …… | …… |
占用额外空间 | 大 | 有一定占用 | 不占用 |
备份速度 | 刚开始快,后面会变慢 | 比完全备份快 | 最快 |
恢复速度 | 最快 | 比完全备份慢 | 最慢 |
操作复杂度 | 简单 | 一般 | 复杂 |
4. 常见备份方法
① 物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件(tar)
- 备份速度快,恢复时也是最简单的
② 专用备份工具
- mysqldump 常用的逻辑备份工具
- mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表
③ 启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志(flush-logs)
④ 第三方工具备份
- 免费的 MySQL 热备份软件 Navicat、DBeaver、Percona XtraBackup、mysqlbackup
授权远程登录
mysql> grant all privileges on school.* to 'root'@'192.168.190.%' identified by '123456';
Navicat:
DBeaver:
二、完全备份与恢复
环境准备:新建库文件与表,并写入数据
[root@localhost ~]# mysql -u root -p'123456'
mysql> create database school;
mysql> use school;
mysql> create table class (id int(4) zerofill primary key auto_increment not null,name char(15) not null,score decimal(4,2),passwd char(45) default'',hobby varchar(18),phone varchar(11),remark text);
# int(4) zerofill :数值不满4位,前面用0填充
# primary key :主键约束
# auto_increment :自增
# not null :不为空
# decimal(4,2) :4个有效长度数字,小数点后有两位
# default'' :默认值为空
# remark text :定义字段为文本
mysql> insert into class values (1,'zhangsan',90.5,password('123'),'sing',111,'this is vip');
# password('123') :隐式哈希值密码
mysql> insert into class values (2,'lisi',88.5,456,'jump',222,'this is svip');
mysql> select * from class;
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| id | name | score | passwd | hobby | phone | remark |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| 0001 | zhangsan | 90.50 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | sing | 111 | this is vip |
| 0002 | lisi | 88.50 | 456 | jump | 222 | this is svip |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
1. 物理冷备份与恢复
InnoDB 存储引擎的数据库在磁盘上存储成三个文件: db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。备份文件夹/usr/local/mysql/data/目录下文件即可。
① 关闭 mysqld 服务
[root@localhost ~]# systemctl stop mysqld.service
② 压缩迁移
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema school sys
[root@localhost data]# cd ..
[root@localhost mysql]# tar zcvf data_$(date +%Y%m%d).tar.gz data/
[root@localhost mysql]# ls
bin COPYING data data_20240325.tar.gz docs include lib man mysql-test README share support-files usr
[root@localhost mysql]# mv data_20240325.tar.gz /opt/
③ 也可以使用 xz 格式压缩
[root@localhost mysql]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
[root@localhost mysql]# ls /opt
data_20240325.tar.gz mysql_all_2024-03-25.tar.xz
④ 模拟损坏数据库文件
[root@localhost mysql]# mv data/ /home/
⑤ 解压还原
[root@localhost mysql]# cd /opt/
[root@localhost opt]# tar zxvf data_20240325.tar.gz -C /usr/local/mysql/
[root@localhost opt]# ll /usr/local/mysql/ | grep data
drwxr-x---. 6 mysql mysql 161 3月 25 15:44 data # 属主、属组均为原来的 mysql
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin COPYING data docs include lib man mysql-test README share support-files usr
[root@localhost mysql]# cd data/
[root@localhost data]# ls
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema school sys
[root@localhost data]# cd school/
[root@localhost school]# ls
class.frm class.ibd db.opt
# 查看数据均还原
⑥ 或者使用 xz 压缩包还原
[root@localhost mysql]# mkdir data/
[root@localhost mysql]# tar Jxvf /opt/mysql_all_2024-03-25.tar.xz -C /usr/local/mysql/data/
[root@localhost mysql]# mv data/usr/local/mysql/data/* ./data/
[root@localhost mysql]# ls data/
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema school sys usr
# 查看数据均还原
[root@localhost mysql]# chown -R mysql:mysql data/ # 修改权限,否者无法启动
[root@localhost mysql]# ll | grep data
drwxr-xr-x. 7 mysql mysql 172 3月 25 16:26 data
[root@localhost mysql]# systemctl restart mysqld.service
2. mysqldump 备份(温备份)
2.1 完全备份一个或多个完整的库 (包括其中所有的表)
格式:
格式:
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql
# 导出的就是数据库脚本文件
数据库信息:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| usr |
+--------------------+
mysql> use school;
mysql> select * from class;
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| id | name | score | passwd | hobby | phone | remark |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| 0001 | zhangsan | 90.50 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | sing | 111 | this is vip |
| 0002 | lisi | 88.50 | 456 | jump | 222 | this is svip |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
示例:
① 备份数据库 school 库文件至指定目录
[root@localhost ~]# mysqldump -u root -p'123456' --databases school > /opt/school.sql
② 备份数据库 school、user 库文件至指定目录
[root@localhost ~]# mysqldump -u root -p'123456' --databases school usr > /opt/school_usr.sql
[root@localhost ~]# ll /opt/
-rw-r--r--. 1 root root 2324 3月 25 16:37 school.sql
-rw-r--r--. 1 root root 2458 3月 25 16:41 school_usr.sql
2.2 完全备份 MySQL 服务器中所有的库
格式:
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
示例:
[root@localhost ~]# mysqldump -u root -p'123456' --all-databases > /opt/all.sql
[root@localhost ~]# ll /opt
-rw-r--r--. 1 root root 776108 3月 25 16:44 all.sql
-rw-r--r--. 1 root root 2324 3月 25 16:37 school.sql
-rw-r--r--. 1 root root 2458 3月 25 16:41 school_usr.sq
2.3 完全备份指定库中的部分表
格式:
mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
# 使用“-d”选项,说明只保存数据库的表结构
# 不使用“-d"选项,说明表数据也进行备份
school库表信息:
mysql> create table class1 like class;
mysql> insert into class1 select * from class; # 克隆
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class |
| class1 |
+------------------+
示例:备份 school 库下 class class1 表
[root@localhost ~]# mysqldump -u root -p123456 school class class1 > /opt/school_class_all.sql
[root@localhost ~]# ll /opt | grep class_all
-rw-r--r--. 1 root root 3123 3月 25 17:02 school_class_all.sql
[root@localhost ~]# mysqldump -u root -p123456 school class1 > /opt/school_class1.sql
# 备份 school 库下 class1 表
2.4 查看备份文件
# 以 school_class1.sql 为例
[root@localhost ~]# grep -v "^--" /opt/school_class1.sql | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `class1`;
CREATE TABLE `class1` (
`id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` char(15) NOT NULL,
`score` decimal(4,2) DEFAULT NULL,
`passwd` char(45) DEFAULT '',
`hobby` varchar(18) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`remark` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
LOCK TABLES `class1` WRITE;
INSERT INTO `class1` VALUES (0001,'zhangsan',90.50,'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257','sing','111','this is vip'),(0002,'lisi',88.50,'456','jump','222','this is svip');
UNLOCK TABLES;
3. mysql 完全恢复
恢复数据库使用 mysqldump 导出的文件,可使用导入的方法
- source 命令:在mysql语句中恢复数据
- mysql 命令:在命令行操作导入数据
3.1 使用 source 命令恢复
① 删除 class1 表
[root@localhost ~]# mysql -u root -p123456 -e 'drop table school.class1;'
# -e:表示后面还有一个SQL语句
② 查看表信息
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class | # class1 表已被删除
+------------------+
③ 使用 source 命令恢复数据
mysql> source /opt/school_class1.sql
④ 再次查看表信息
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class |
| class1 | # class1 表已恢复
+------------------+
mysql> select * from class1;
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| id | name | score | passwd | hobby | phone | remark |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
| 0001 | zhangsan | 90.50 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | sing | 111 | this is vip |
| 0002 | lisi | 88.50 | 456 | jump | 222 | this is svip |
+------+----------+-------+-------------------------------------------+-------+-------+--------------+
3.2 使用 mysql 命令恢复
① 删除 class1 表,并查看表信息
[root@localhost ~]# mysql -u root -p123456 -e 'drop table school.class1;'
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class | # class1 表已被删除
+------------------+
② mysql 命令重定向恢复,并查看表信息
[root@localhost ~]# mysql -u root -p123456 school < /opt/school_class1.sql
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class |
| class1 | # class1 表已恢复
+------------------+
在全量备份与恢复环境中,假设现有 school 库,school 库中有一个 class1 表,需要注意:
当备份时加 --databases ,表示针对于 school 库
- 恢复命令过程为:mysql -u root -p123456 school < /opt/school_class1.sql
当备份时不加 --databases,表示针对 school 库下的所有表
- 需要先新建库:mysql> create database school; 再命令恢复
3.3 建立计划任务
[root@localhost ~]# crontab -e
0 2 * * 0 /usr/local/mysql/bin/mysqldump -uroot -p'123456'school class class1 > /opt/class_all_$(date +%Y%m%d).sql ;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs
# 每周日凌晨2点,使用mysqldump工具备份MySQL数据库school中的class class1表的数据,并将备份数据保存到/opt/目录下的scholl_classall_$(date +%Y%m%d).sql文件中,然后使用mysqladmin工具刷新MySQL的日志。
[root@localhost ~]# crontab -r
# 删除计划任务
三、增量备份与恢复
1. 增量备份
MySQL的增量备份通常使用二进制日志(binary log)来实现。二进制日志是MySQL的一种日志,它记录了所有对数据库的更改操作,包括插入、更新、删除等操作。
1.1 开启二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-error=/usr/local/mysql/data/mysql_error.log
# 指定日志的保存位置和文件名
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
# 通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
log-bin=mysql-bin 或 log_bin=mysql-bin
# 二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
# 慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便提醒优化,默认是关闭的
long_query_time=5
# 设置超过5秒执行的语句被记录,缺省时为10秒
[root@localhost ~]# systemctl restart mysqld.service
查看日志方式:
mysql> show variables like 'general%'; #查看通用查询日志是否开启
mysql> show variables like 'log_bin%'; #查看二进制日志是否开启
mysql> show variables like '%slow%'; #查看慢查询日功能是否开启
mysql> show variables like 'long_query_time'; #查看慢查询时间设置
mysql> set global slow_query_log=ON; #在数据库中设置开启慢查询的方法
[root@localhost ~]# ls /usr/local/mysql/data
auto.cnf ib_logfile0 mysql mysql_error.log performance_schema
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql_general.log sys
ibdata1 ibtmp1 mysql-bin.index mysql_slow_query.log
1.2 二进制日志(binlog)记录格式
① STATEMENT(基于SQL语句):记录的是SQL语句,日志量过大
② ROW(基于行):记录的是每一行数据的变化,如果遇到update、delete数据量会越来越大
③ MIXED(推荐使用):会根据SQL语句的类型来选择使用Statement格式还是Row格式,它的缺点是可能会因为SQL语句的类型而导致日志文件的大小和恢复的准确性有所不同
1.3 查看二进制日志文件
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240325 18:01:18 server id 1 end_log_pos 123 CRC32 0x8f464a57 Start: binlog v 4, server v 5.7.17-log created 240325 18:01:18 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#240325 18:01:18 server id 1 end_log_pos 154 CRC32 0xbef4ecb6 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# --base64-output=decode-rows:使用64位编码机制去解码(decode)并按行读取(rows)
# -v: 显示详细内容
# --no-defaults : 默认字符集(不加会报UTF-8的错误)
二进制日志中需要关注的部分
at :开始的位置点
end_log_pos:结束的位置
时间戳: 240325 18:01:18
SQL语句
1.4 备份操作
环境准备:
mysql> create database school;
mysql> use school;
mysql> create table class (id char(5),name varchar(15));
mysql> insert into class values (1,'zhangsan');
mysql> insert into class values (2,'lisi');
mysql> select * from class;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
① 进行完全备份
[root@localhost ~]# mysqldump -uroot -p123456 school class > /opt/school_class_$(date +%F).sql
[root@localhost ~]# ll /opt
-rw-r--r--. 1 root root 1848 3月 25 18:32 school_class_2024-03-25.sql
② 生成新的二进制日志文件
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p123456 flush-logs
[root@localhost data]# ls
auto.cnf ib_logfile0 mysql mysql-bin.index mysql_slow_query.log sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql_error.log performance_schema
ibdata1 ibtmp1 mysql-bin.000002 mysql_general.log school
[root@localhost data]# cp mysql-bin.000001 /opt/
2. 增量恢复
2.1 一般恢复
2.1.1 重定向恢复
① 模拟数据丢失
mysql> drop table class;
② 重定向恢复
[root@localhost data]# mysql -u root -p123456 school < /opt/school_class_2024-03-25.sql
2.1.2 二进制日志恢复
① 删除库(日志文件里有创建库的动作,避免冲突)
mysql> drop database school;
② 基于 mysql-bin.000001 恢复
[root@localhost data]# mysqlbinlog --no-defaults /opt/mysql-bin.000001 | mysql -u root -p123456
③ 查看表信息
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class |
+------------------+
2.2 断点恢复
实现目标:class 表中5条数据,1、2条记录在mysql-bin.000001日志,3-5条记录在mysql-bin.000001日志,模拟第4条数据操作故障,实现跳过第4条断点恢复。
① 插入3-5条数据
mysql> insert into class values (3,'wang');
mysql> insert into class values (4,'wu');
mysql> insert into class values (5,'zhen');
mysql> select * from class;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wang |
| 4 | wu |
| 5 | zhen |
+------+----------+
② 备份 mysql-bin.000002 日志
[root@localhost data]# ls
auto.cnf ib_logfile0 mysql mysql-bin.index mysql_slow_query.log sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql_error.log performance_schema
ibdata1 ibtmp1 mysql-bin.000002 mysql_general.log school
[root@localhost data]# cp mysql-bin.000002 /opt/
③ 生成新的二进制日志,并删除 class 表
mysqladmin -u root -p123456 flush-logs
[root@localhost data]# ls
auto.cnf ib_logfile0 mysql mysql-bin.000003 mysql_general.log school
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.index mysql_slow_query.log sys
ibdata1 ibtmp1 mysql-bin.000002 mysql_error.log performance_schema
④ 基于位置点恢复
查看mysql-bin.000001日志
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
这里注意,school 库未删除,恢复从(BEGIN)操作 ID 为“384"到(end_log_pos)操作 ID 为“1097"之前的数据
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='384' --stop-position='1097' /opt/mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from class;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
查看mysql-bin.000002日志
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
这里注意,由于mysql-bin.000002存放着3-5条数据的举例,需要先恢复从(BEGIN)操作 ID 为“2832"到(end_log_pos)操作 ID 为“3100"之前的数据
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='2832' --stop-position='3100' /opt/mysql-bin.000002 | mysql -uroot -p123456
mysql> select * from class;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wang |
+------+----------+
查看mysql-bin.000002日志
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
这里注意,由于最后一条插入记录后没有操作,直接恢复到(BEGIN)操作 ID 为“3366"之后的数据
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='3366' /opt/mysql-bin.000002 | mysql -uroot -p123456
mysql> select * from class;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wang |
| 5 | zhen |
+------+----------+
2.3 基于时间点恢复
时间点恢复与断点恢复同理,只需要将 at 值改为对应的时间即可。
格式:
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
示例:
mysqlbinlog --no-defaults --stop-datetime='2024-03-25 20:00:00' /opt/mysql-bin.000001 | mysql -uroot -p
# 仅恢复到20:00:00之前的数据
mysqlbinlog --no-defaults --start-datetime='2024-03-25 20:00:00' /opt/mysql-bin.000001 | mysql -uroot -p
#仅恢复到20:00:00之后的数据
# 如果恢复某条SQL语之前的所有数据,就stop在这个语句的位置节点或者时间点
# 如果恢复某条SQL语句以及之后的所有数据,就从这个语句的位置节点或者时间点start
四、总结
一、完全备份与恢复
1.物理冷备份
(1)关闭mysqld服务
(2)备份mydql文件夹内容(tar压缩迁移备份,或者直接cp -r)
(3)将备份文件解压或覆盖即可
2.mysqldum备份(温备份,类似锁表)
(1)完全备份一个或者多个完整的库(包括其中所有的表)
格式:mysqldump -u用户名 -p'密码' --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql
(2)完全备份mysql服务器中所有的库
格式:mysqldump -u用户名 -p'密码' --all-databases > /备份路径/备份文件名.sql
(3)完全备份指定库中的部分表
格式:mysqldump -u用户名 -p'密码' [-d] 库名 表1 表2 ... > /备份路径/备份文件名.sql
# 使用-d:只保存数据库表结构
# 不用-d:表数据和结构都备份
3.完全恢复
① source命令:在mysql语句中恢复
② mysql命令:在命令行操作
(1)使用source命令恢复数据
mysql > /备份路径/备份文件名.sql # 恢复库
use 库; mysql > /备份路径/备份文件名.sql # 恢复表,需要先进入库
(2)使用mysql重定向恢复
mysql -u用户 -p'密码' < /备份路径/备份文件名.sql # 恢复库
mysql -u用户 -p'密码' 库名 < /备份路径/备份文件名.sql # 恢复表
(3)mysql -e选项
mysql -u用户 -p'密码' -e 'SQL语句';
# -e:表示后面还有一个SQL语句
注意:
# 当备份加上--databases,针对库
# 当备份不加--databases,针对库下所有的表,备份时需要create表
二、增量备份与恢复
1.开启备份
(1)开启二进制日志功能:/etc/my.cnf
2.断点恢复
(1)查看二进制日志文件
格式:mysqlbinlog --no-defaults --base64-output=decode-rows -v /备份路径/备份文件名.sql
# --base64-output=decode-rows:使用64位编码机制去解码(decode)并按行读取(rows)
# -v: 显示详细内容
# --no-defaults : 默认字符集(不加会报UTF-8的错误)
(2)生成新的二进制日志文件
mysqladmin -u用户 -p'密码' flush-logs(分割)
(3)基于mysql-bin-000001恢复(一般恢复)
mysqlbinlog --no-defaults /备份路径/备份文件名.sql | mysql -u用户 -p'密码'
(4)断点恢复
① 基于at id值
mysqlbinlog --no-defaults --start-position='at id' --stop-position='at id' /备份路径/备份文件名.sql | mysql -u用户 -p'密码'
② 基于时间点
mysqlbinlog --no-defaults --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' /备份路径/备份文件名.sql | mysql -u用户 -p'密码'
# 单独--start:之后的
# 单独--stop:之前的
# --start和--stop:之间