Mysql备份恢复--mysqldump

mysqldump是mysql服务自带的备份工具；逻辑备份工具，可以完成完全、部分备份；对于不同的引擎，也有着不同的方案

InnoDB：热备；

MyISAM：温备；

其生成备份文件的命令如下

mysqldump是客户端工具，支持连接到远程客户端进行备份

mysqldump [OPTIONS] database [tables] # 备份单库，可以只备份其中的一部分表(部分备份)

OR

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] # 备份多库；单库也推荐使用，省去创建数据库步骤

OR

mysqldump [OPTIONS] --all-databases [OPTIONS] # 备份所有库

MyISAM存储引擎：支持温备， 备份时要锁定表；

-x, --lock-all-tables：锁定所有库的所有表，读锁；# 所有库备份时使用

-l, --lock-tables：锁定指定库所有表；# 单库备份时使用

InnoDB存储引擎：支持温备和热备；

--single-transaction：创建一个事务，基于此快照执行备份；

其它选项：

-R, --routines：备份指定库的 存储过程和存储函数；

--triggers：备份指定库的 触发器；

-E, --events：备份指定库的 事件调度器

事先准备的简单数据库和数据表

MariaDB> SELECT * FROM student;

+---+--------+------+----------+

| id | name | age | gender

+---+--------+------+----------+

| 1 | zhao | 15 | M |

| 2 | yang | 16 | F |

+---+--------+-------+---------+

编辑mariadb的配置文件，开启二进制日志记录功能

[root@CentOS7 ~]$vim /etc/my.cnf.d/server.cnf

log-bin=mater-log

重启服务，配置生效，因为填写的是相对路径，所以到默认的目录下查看生成文件

[root@CentOS7 ~]$systemctl restart mariadb

[root@CentOS7 ~]$ls /var/lib/mysql/

aria_log.00000001ib_logfile0master-log.000002mysqltestaria_log_controlib_logfile1master-log.000003mysql.sockbdata1 master-log.000001master-log.indexperformance_schema

温备数据库

[root@CentOS7 ~]$mkdir /backup

[root@CentOS7 ~]mysqldump -x -R -E --triggers --all-databases --master-data=2 --flush-logs >/backup/fullbackup-$(date "+%F-%T").sql

[root@CentOS7 ~]vim /backup/fullbackup-2017-11-12-14:27:42.sql #查看生成的备份文件

-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000006', MASTER_LOG_POS=245;#此位置指明了自备份后二进制日志记录的开始位置

mysqldump只支持全量备份，备份后继续对数据库进行修改，那么备份后到下一次备份之间的段真空期，就用到了之前开启的二进制日志记录功能，二进制文件进行重放可以帮助我们修复此段时间内的数据库操作，从而完成修复

MariaDB>INSERT INTO student (name,age,gender)values('WEI','14','F');

INSERT INTO student (name,age,gender)values('WEI','14','F')

查看一下二进制日志文件是否正常记录

[root@CentOS7 ~]$mysqlbinlog /var/lib/mysql/master-log.000006

...

use `study`/*!*/;
SET TIMESTAMP=1510468188/*!*/;
INSERT INTO student (name,age,gender)values('WEI','14','F')
/*!*/;

...

将备份文件及其期间的二进制日志文件拷贝的从属服务器，进行数据库的同步

[root@CentOS7 ~]$scpfullbackup-2017-11-12-14:27:42.sql root@172.18.46.5:/backup

[root@CentOS7 ~]$scpmaster-log.000006 root@172.18.46.5:/backup

使用source命令读取.sql文件

MariaDB>source /backup/fullbackup-2017-11-12-14:27:42.sql

mysqlbinlogmaster-log.000006 > /backup/binlog20171112.sql

MariaDB>source/backup/binlog20171112.sql

这样数据库的同步或者是修复就完成了，此上针对的是温备，其实也可以完成热备，只是改变一下备份文件生成语句

mysqldump --single-transaction -R -triggers--all-databases-E --databases --master-data=2 --flush-logs > /backup/fullbackup-$(date "+%F-%T").sql

原理上是不再进行表的锁定，而是生成一个快照，针对快照进行备份，从而不扰乱数据的读写

基于单库的备份也是支持的，也只是修改生成备份文件时进行选项的修改，具体操作就不再赘述

注意：在恢复时，我们应该关闭从服务器二进制日志的记录，原因是避免二进制日志重复的记录（这在大量数据情况下是必要的），但是我们的例子是运用的是新数据库，默认为关闭状态，所以省略该步骤，在恢复之后，我们也要再次开启二进制文件。