mysql逻辑备份 mysqldump
一、mysql备份
1.1 为什么要备份
①防止数据丢失
②恢复数据，误操作最多
1.2 数据的备份类型
①完全备份-->备份整个数据库
②部分备份-->备份部分数据库，例如只备份一个库、一张表
③增量备份-->备份自上一次备份以来(增量或完全)以来变化的数据
特点: 节约空间、还原麻烦
④差异备份-->备份自上一次完全备份以来变化的数据
特点: 浪费空间、还原比增量备份简单
1.3 备份方式
- 热备份
- 热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
- 温备份
- 温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
- 冷备份
- 冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
MyISAM
- 热备 ×
- 温备 √
- 冷备 √
InnoDB
- 热备 √
- 温备 √
- 冷备 √
1.4 备份命令
mysqldump（逻辑）
- mysql原生自带很好用的逻辑备份工具
mysqlbinlog（逻辑）
- 实现binlog备份的原生态命令
xtrabackup（物理）
- precona公司开发的性能很高的物理备份工具
1.5 mysql备份方式总结
| 备份方法 | 备份速度 | 恢复速度 | 便捷性 | 功能 | 一般用于 |
|---|---|---|---|---|---|
| cp | 快 | 快 | 一般、灵活性低 | 很弱 | 少量数据备份 |
| mysqldump | 慢 | 慢 | 一般、可无视存储引擎的差异 | 一般 | 中小型数据量的备份 |
| lvm2快照 | 快 | 快 | 一般、支持几乎热备、速度快 | 一般 | 中小型数据量的备份 |
| xtrabackup | 较快 | 较快 | 实现innodb热备、对存储引擎有要求 | 强大 | 较大规模的备份 |
二、mysqldump备份
2.0 利用存储过程生成大量数据
#1.创建数据库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
#2.创建表
mysql> use db1;
Database changed
mysql> create table db1_t1(
id int,
name varchar(20),
gender char(6),
email varchar(50),
first_name char(10),
last_name char(10)
);
Query OK, 0 rows affected (0.01 sec)
#3.创建存储过程
mysql> delimiter $$ #声明存储过程的结束符号为$$
mysql> create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<100001)do #插入10万条数据
insert into db1_t1 values(i,'xboyww','man',concat( 'xboyww',i,'@qq'),concat('a',i),concat('b',i));
set i=i+1;
end while;
END$$ #$$结束
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; #重新声明分号为结束符号，注意有空格
#4.查看存储过程
show create procedure auto_insert1\G
#5.调用存储过程
call auto_insert1();
#6.查看数据
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.04 sec)
mysql> select * from s1 limit 10;
+------+--------+--------+-------------+------------+-----------+
| id | name | gender | email | first_name | last_name |
+------+--------+--------+-------------+------------+-----------+
| 1 | xboyww | man | xboyww1@qq | a1 | b1 |
| 2 | xboyww | man | xboyww2@qq | a2 | b2 |
| 3 | xboyww | man | xboyww3@qq | a3 | b3 |
| 4 | xboyww | man | xboyww4@qq | a4 | b4 |
| 5 | xboyww | man | xboyww5@qq | a5 | b5 |
| 6 | xboyww | man | xboyww6@qq | a6 | b6 |
| 7 | xboyww | man | xboyww7@qq | a7 | b7 |
| 8 | xboyww | man | xboyww8@qq | a8 | b8 |
| 9 | xboyww | man | xboyww9@qq | a9 | b9 |
| 10 | xboyww | man | xboyww10@qq | a10 | b10 |
+------+--------+--------+-------------+------------+-----------+
10 rows in set (0.00 sec)
#删除存储过程
DROP PROCEDURE auto_insert1;
生成的数据库和表
db1
db1_t1 10万条数据
db1_t2 10万条数据
db2
db2_t1 10万条数据
db2_t2 10万条数据
mysqldump连接服务端参数
-u 指定用户
-p 指定密码
-S 指定套接字文件
-h 指定主机
-P 指定端口
mysqldump的三种语法
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases
2.1 全库备份 -A,--all-databases
mysqldump -uroot -p -A >all.sql
2.2 单库、多库备份 -B,--databases
单库备份，可以不加选项-B
mysqldump -uroot -p -B db1 > db1.sql
或者
mysqldump -uroot -p db1 > db1.sql
多库备份，必须加参数-B
mysqldump -uroot -p -B db1 db2 > db1_db2.sql
2.3 单表、多表备份 不需要参数
单表备份
mysqldump -uroot -p db1 db1_t1 > db1.db1_t1.sql
多表备份
mysqldump -uroot -p db1 db1_t1 db1_t2 > db1.db1_t1_t2.sql
2.4 备份的一些选项
2.4.1 --master-data
备份时加入change master语句，需要开启binlog日志
有3个参数
0 没有
1 不注释
2 注释
当参数=0时，备份的文件中是没有change master to语句的
mysqldump -uroot -p -B db1 --master-data=0 > db1.sql
当参数=1时，备份的文件中就会有change master to语句，并且没有注释
mysqldump -uroot -p -B db1 --master-data=1 > db1.sql
#备份的sql文件中会有change master to语句，并且没有注释
$ grep 'CHANGE MASTER' db1.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=154;
当参数=2时，备份的文件中就会有change master to语句，并且是注释的
mysqldump -uroot -p -B db1 --master-data=2 > db1.sql
#备份的sql文件中会有change master to语句，并且是注释的
$ grep 'CHANGE MASTER' db1.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=154;
2.4.2 -R, --routines 备份存储过程和函数数据
mysqldump -uroot -A -R --master-data=2 > all.sql
2.4.3 --triggers 备份触发器数据
默认启用，使用选项--skip-triggers禁用
mysqldump -uroot -A --triggers --master-data=2 > all.sql
2.4.4 --single-transaction 快照备份，仅对InnoDB引擎生效
此选项将事务隔离模式设置为， REPEATABLE READ并START TRANSACTION
mysqldump -uroot -A --single-transaction --master-data=2 > all.sql
2.4.5 --lock-all-tables，-x 锁表备份
锁定所有数据库中的所有表，此选项将自动关闭 --single-transaction和 --lock-tables
mysqldump -uroot -A -x --master-data=2 > all.sql
三、mysqldump恢复
#先不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/all.sql
#库外恢复操作
mysql -uroot -p < /backup/all.sql
mysqldump恢复特点
- mysqldump在备份和恢复时都需要MySQL实例启动为前提
- 一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
- mysqldump是以覆盖的形式恢复数据的