mysql逻辑备份 mysqldump

mysql5.7官方手册

mysql5.7备份和恢复官方文档

一、mysql备份

1.1 为什么要备份

①防止数据丢失

②恢复数据,误操作最多

1.2 数据的备份类型

①完全备份-->备份整个数据库

②部分备份-->备份部分数据库,例如只备份一个库、一张表

③增量备份-->备份自上一次备份以来(增量或完全)以来变化的数据

特点: 节约空间、还原麻烦

④差异备份-->备份自上一次完全备份以来变化的数据

特点: 浪费空间、还原比增量备份简单

iShot2020-07-0211.46.36

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 READSTART 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是以覆盖的形式恢复数据的
泡泡吐肥皂o © gitbook.pptfz.top 2021 all right reserved,powered by Gitbook文件修订时间: 秃笔南波湾!!!

results matching ""

    No results matching ""