浅谈Mysql备份及mysqldump具体操作

备份目的,用于数据恢复和审计不同时点的状态.
备份类型：
（1）根据备份时，服务器是否在线
1）冷备(cold backup)： 服务器离线，读写操作都不能进行
2）温备份： 全局施加共享锁，只能读不能写
3）热备(hot backup)：数据库在线，读写照样进行
（2）根据备份时的数据集分类
1）完全备份(full backup)
2）部分备份(partial backup)
（3）根据备份时的接口
1）物理备份（physical backup）：直接复制数据文件 ，打包归档
特点： 不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个G，则适用于物理备份
2）逻辑备份(logical backup)： 把数据抽取出来保存在sql脚本中
特点： 可以使用文本编辑器编辑；导入方便，直接读取sql语句即可；逻辑备份恢复时间慢，占据空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引

备份内容
1）数据库中的数据
2）配置文件
3）mysql中的代码： 存储过程，存储函数，触发器
4）OS 相关的配置文件，chrontab 中的备份策略脚本
5）如果是主从复制的场景中： 跟复制相关的信息
6）二进制日志文件需要定期备份，一旦发现二进制文件出现问题，需马上对数据进行完全备份

备份操作
1） 全量备份 full backup
2） 增量备份 incremental backup
在不同时间点起始备份一段数据，比较节约空间；针对的是上一次备份后有变化的数据，备份数据少，备份快，恢复慢
3） 差异备份 differential backup
备份从每个时间点到上一次全部备份之间的数据，随着时间增多二增多；比较容易恢复；对于很大的数据库，可以考虑主从模型，备份从服务器的内容。针对的是上一次全量备份后有变化的数据，备份数据多，备份慢，恢复快。

备份工具:
mysqldump,Xtrabackup,lvm-snapshot

现在具体介绍mysqldump命令
mysqldump [OPTIONS] database [tables…]
mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql
常用参数
–no-data, -d 只导出数据库表结构，不导出任何数据。
–quick，-q 该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
–skip-add-drop-table参数，只create语句，没有delete。
*如果当要恢复的数据库已经存在同名的表,但作用不一样的,就不会删除原来的表.不加,默认同名的表,会先删除在根据sql文件创建并恢复数据.
–all-databases:　备份所有库
–databases db1 db2 …: 备份指定的多个库，如果使用此命令，恢复时将不用手动创建库。或者是-B db1 db2 db3 ….
–lock-all-tables：请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备
–lock-table: 对正在备份的表加锁，但是不建议使用，如果其它表被修改，则备份后表与表之间将不同步
–single-transaction: 能够对InnoDB存储引擎实现热备；该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。
–opt 这只是一个快捷选项，等同于同时添加 –add-drop-tables –add-locking –create-option –disable-keys –extended-insert –lock-tables –quick –set-charset 选项。
–flush-logs为结束当前日志，生成新日志文件；
–master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称
0: 不记录1：记录为CHANGE MASTER语句2：记录为注释的CHANGE MASTER语句
–default-character-set=charset 指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
–disable-key 告诉 MySQLdump 在 INSERT 语句的开头和结尾增加 /!40000 ALTER TABLE table DISABLE KEYS /; 和 /!40000 ALTER TABLE table ENABLE KEYS /; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。
–no-create-info，-t 只导出数据，而不添加 CREATE TABLE 语句。
–routines，-R 导出存储过程以及自定义函数。
–delete-master-logs(对主从数据服务器有风险,建议用purge)

完全备份 mysqldump -h hostip -P port –all-databases > allbackup.sql
恢复 mysql -u root -p -h hostip -P port < allbackup.sql

增量备份
所谓增量备份,其实就是产生binlog,并通过binlog事件或时间点恢复数据.
每日定时使用 MySQLadmin flush-logs来创建新日志，并结束前一日志写入过程。并把前一日志备份.通过cron自己完成.

需要打开binlog日志功能
mariadb配置文件 /etc/mysql/mariadb.conf.d/50-server.conf
log_bin = /var/log/mysql/mysql-bin.log
binlog_fomat=MIXED
可以通过变量来判断是否已经打开binlog
show variables like ‘%log_bin%’;
以及查看日志目录是否有 /var/log/mysql/mysql-bin.000001 等日志文件,mysql-bin.index索引

首先全备份
mysqldump –quick –events –all-databases –flush-logs –single-transaction –master-data=2 > backupall_date +%y%m%d%H%M%S.sql
然后产生增量
mysqladmin flush-logs

恢复时候
先恢复全备份
mysql < backupall_180807.sql
然后查看binlog事件
SHOW BINARY LOGS ; 显示使用过的二进制日志文件
SHOW BINLOG EVENTS IN ‘mysql-bin.000008’ \G;以表的形式查看二进制文件

mysqlbinlog命令
mysqlbinlog [options] log-files | mysql -u root -p
-start-position：开始位置
–stop-position：结束位置
–start-date ‘yyyy-mm-dd hh:mm:ss’：开始时间
–stop-date ‘yyyy-mm-dd hh:mm:ss’ ：结束时间
注 时间点,位置点须在已发生的事件之前即可.
mysqlbinlog –stop-date=”2018-08-07 16:17:24” mysql-bin.000008 | mysql -u root -h127.0.0.1
mysqlbinlog –stop-position=”793” mysql-bin.000001| mysql -uroot -h127.0.0.1

删除binlog
命令格式：PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
MariaDB> PURGE BINARY LOGS TO ‘mysql-bin.000010’;
MariaDB> PURGE BINARY LOGS BEFORE ‘2018-05-02 11:26:26’;