mysql备份之mysqldump+mysqlbinlog+outfile

目录

 

一、备份的目的

二、备份需要考虑的问题

三、备份的类型

四：备份的对象

五、备份和恢复的实现

---

一、备份的目的

做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用

二、备份需要考虑的问题

可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完； 
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表。

经常恢复测试

三、备份的类型

1、根据是否需要数据库离线

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；

温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；

热备（hot backup）：备份的同时，业务不受影响。

注：

1、这种类型的备份，取决于业务的需求，而不是备份工具

2、MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具

 

2、根据要备份的数据集合的范围

完全备份：full backup，备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。

差异备份：differential backup 上次完全备份以来改变了的数据。

建议的恢复策略：

完全+增量+二进制日志

完全+差异+二进制日志

 

3、根据备份数据或文件

物理备份：直接备份数据文件

优点：

备份和恢复操作都比较简单，能够跨mysql的版本，
恢复速度快，属于文件系统级别的

建议：

不要假设备份一定可用，要测试
mysql>check tables `test`.test`,test2`.`test2`；检测表是否可用

四：备份的对象

1、 数据；
2、配置文件；
3、代码：存储过程、存储函数、触发器
4、os相关的配置文件
5、复制相关的配置
6、二进制日志

五、备份和恢复的实现

1、利用select into outfile实现数据的备份与还原，单表操作

MySQL 默认的导出目录my.ini配置文件  secure_file_priv = '/tmp/data'

show variables like '%secure%';查看变量

导出

select * from `test` where `id`>4580 into outfile '/tmp/data/back.txt';

导入

load data infile '/tmp/data/back.txt' into table `test`;//可做大量数据导入

 

2、利用mysqldump工具对数据进行备份和还原

mysqldump 常用来做温备，对备份的数据加锁

备份的策略：完全备份+增量备份+二进制日志

2.1 完全备份

mysqldump -uroot -proot --single-transaction --master-data=2 --databases test > E:/back.sql

--single-transaction: 基于此选项能实现热备InnoDB表；

--master-data=2  记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的

--databases hellodb 指定备份的数据库

 

2.2 mysql服务器端更新/添加数据

show master status;

File                               Position    Binlog_Do_DB    Binlog_Ignore_DB

mysql-bin.000001        492

 

2.3增量备份

mysqlbinlog 命令，以用户可视的方式展示出二进制日志中的内容。同时，也可以将其中的内容读取出来，供其他MySQL实用程序使用。

mysqlbinlog --no-defaults --start-position=107 --stop-position=492 D:/phpstudy/PHPTutorial/MySQL/data/mysql-bin.000001 > E:/back1.sql

--disable-log-bin 禁用二进制日志SQL_LOG_BIN=0

--start-datetime="2017-08-16 15:00:00"

--stop-datetime="2017-08-16 15:00:00"

 

mysqlbinlog: unknown variable 'default-character-set=utf8'

原因是mysqlbinlog这个工具无法识别my.ini中的配置中的default-character-set=utf8这个指令

default-character-set=utf8 修改为 character-set-server = utf8重启服务

或加 mysqlbinlog --no-defaults

 

2.4修改后再次增量备份

mysqlbinlog --no-defaults --start-position=492 --stop-position=753 D:/phpstudy/PHPTutorial/MySQL/data/mysql-bin.000001 > E:/back2.sql

 

2.5关闭二进制日志

mysql> set sql_log_bin=0;  关闭二进制日志

mysql> flush logs; 滚动下日志，重新生成日志文件mysql-bin.index+1

 

2.6删除test库 

drop database test;

 

2.7开始恢复数据

mysql -uroot -proot < E:/back.sql

mysql -uroot -proot < E:/back1.sql

mysql -uroot -proot < E:/back2.sql

2.7 mysqlbinlog Options

https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

Format	Description
--base64-output	使用base-64编码打印二进制日志
--bind-address	使用指定的网络接口连接到MySQL服务器	5.6.1
--binlog-row-event-max-size	二进制日志最大事件大小
--character-sets-dir	安装字符集的目录
--connection-server-id	用于测试和调试	5.6.20
--database	仅列出此数据库的条目
--debug	写调试日志
--debug-check	程序退出时打印调试信息
--debug-info	程序退出时打印调试信息,内存,CPU统计信息
--default-auth	要使用的身份验证插件	5.6.2
--defaults-extra-file	除常用选项文件外，还可以读取命名选项文件
--defaults-file	只读命名选项文件
--defaults-group-suffix	选项组后缀值
--disable-log-bin	禁用二进制日志
--exclude-gtids	不要在提供的GTID集中显示任何组	5.6.5
--force-if-open	即使打开或未正确关闭，也要读取二进制日志文件
--force-read	如果mysqlbinlog读取它无法识别的二进制日志事件，则会输出警告
--help	显示帮助消息并退出
--hexdump	在注释中显示日志的十六进制转储
--host	连接到给定主机上的MySQL服务器
--include-gtids	仅显示提供的GTID集中的组	5.6.5
--local-load	在指定目录中为LOAD DATA准备本地临时文件
--login-path	从.mylogin.cnf中读取登录路径选项	5.6.6
--no-defaults	不读选项文件
--offset	跳过日志中的前N个条目
open_files_limit	指定要保留的打开文件描述符的数量
--password	连接到服务器时使用的密码
--plugin-dir	安装插件的目录	5.6.2
--port	用于连接的TCP / IP端口号
--print-defaults	打印默认选项
--protocol	要使用的连接协议
--raw	将原始（二进制）格式的事件写入输出文件
--read-from-remote-master	从MySQL主服务器读取二进制日志而不是读取本地日志文件	5.6.5
--read-from-remote-server	从MySQL服务器读取二进制日志而不是本地日志文件
--result-file	直接输出到命名文件
--secure-auth	不要以旧的（4.1之前的）格式向服务器发送密码	5.6.17
--server-id	仅提取由具有给定服务器ID的服务器创建的事件
--server-id-bits	当mysqld的server-id-bits设置为小于最大值时，告诉mysqlbinlog如何解释二进制日志中的服务器ID; 仅受MySQL Cluster版本的mysqlbinlog支持
--set-charset	将SET NAMES charset_name语句添加到输出
--shared-memory-base-name	用于共享内存连接的共享内存的名称
--short-form	仅显示日志中包含的语句
--skip-gtids	不要打印任何GTID; 从包含GTID的二进制日志中写入转储文件时使用此选项。	5.6.5
--socket	对于localhost的连接，要使用的Unix套接字文件
--ssl-crl	包含证书吊销列表的文件	5.6.3
--ssl-crlpath	包含证书吊销列表文件的目录	5.6.3
--ssl-mode	与服务器连接的安全状态	5.6.30
--start-datetime	从第一个事件读取二进制日志，时间戳等于或晚于datetime参数
--start-position	从第一个事件读取二进制日志，其位置等于或大于参数
--stop-datetime	在时间戳等于或大于datetime参数的第一个事件处停止读取二进制日志
--stop-never	阅读完最后的二进制日志文件后，保持与服
--stop-never-slave-server-id	连接到服务器时要报告的从服务器ID
--stop-position	在位置等于或大于参数的第一个事件处停止读取二进制日志
--to-last-log	不要在MySQL服务器请求的二进制日志结束时停止，而是继续打印到最后一个二进制日志的末尾
--user	连接到服务器时使用的MySQL用户名
--verbose	将行事件重建为SQL语句
--verify-binlog-checksum	验证二进制日志中的校验和	5.6.1
--version	显示版本信息并退出

 

参考文档：https://www.cnblogs.com/fengzhongzhuzu/p/9101782.html