环境：MySQL5.7
备份方式
逻辑备份、物理备份、增量备份
物理备份与恢复
原理是直接cp文件，对于不同的存储引擎有略微不同。
优缺点
优点：备份快，数据量大时所需时间短。
缺点：是备份memory存储引擎的内容时会比较捉急。
企业版MySQL备份工具可以备份memory引擎表
myisam引擎表的物理备份和恢复
1)为了保证数据一致性，需要先把mysqld服务停了或者锁住需要备份的表
# 把所有内存中的数据刷进磁盘，并加读锁
mysql> flush tables myisam_test,myisam_test1 with read lock;read lock保证所有表都不能插入(不论myisam还是innodb)，但是能读取。
2)直接去cp对应表的.frm``.MYD``.MYI文件即可完成备份
3)最后UNLOCK TABLES释放锁。
需要恢复时直接cp到目标即可。
逻辑备份与恢复
使用mysqldump备份一堆sql语句
优缺点
备份慢，数据量大时所需时间久。
mysqldump备份实践
备份时只输出一个包含所有sql的文件
只备份一个库的全部表时，–databases参数可有可无，差别是加了它，就会在生成的备份sql前面添加create database 和use database语句。至于更多参数，可以执行mysqldump –verbose –help查阅
代码示例
# 备份所有库
shell> mysqldump --all-databases > dump.sql
# 备份指定库
shell> mysqldump --databases db1 db2 db3 > dump.sql
# 只备份test库的t1，t3，t7表
shell> mysqldump test t1 t3 t7 > dump.sql
# 恢复
shell> mysql < dump.sql备份时输出两个文件
使用--tab参数指定一个路径，就会生成一个.txt和一个.sql文件：
.txt文件内容由mysqld进程生成，文件拥有者是mysqld的运行用户，内容是表数据，由SELECT ... INTO OUTFILE语句写入。
备份时如果.txt文件已经存在就会报错。
.sql文件由mysqladump进程生成，文件的拥有者就是执行mysqldump的用户，内容是建表语句。
因为涉及到SELECT … INTO OUTFILE操作，所以需要先去my.cnf增加secure-file-priv=”，开启MySQLd对服务器的输出文件的权限
假如table名是info，就会生成info.sql和info.txt
使用--tab参数时，还可以进一步使用--fields-terminated-by等参数，来设置数据的分隔符。更多参数，可以执行mysqldump –verbose –help查阅。
–tab最好只在服务器本地使用，如果在远程服务器使用，则需要–tab指定的目录在远程和本地都要存在，其中远程目录下会保存txt，本地目录下保存sql文件。
代码示例
# 备份wordpress47表，文件就在当前目录创建
shell> mysqldump --tab=./ wordpress47 -uroot -p
# 恢复数据
shell> mysql db1 < wordpress47.sql # 执行建表语句
shell> mysqlimport db1 wordpress47.txt # 导入数据
其中mysqlimport可用LOAD DATA INFILE语句替代
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;如果备份时指定了其他参数，如列与列的分隔符，在导入时需要使用同样的参数。
增量备份和恢复
要使用增量备份，运行MySQL服务器时开启binlog即可。
代码示例
# 恢复增量备份
mysqlbinlog xxxx.binlog |mysql -uroot -p
mysqlbinlog工具
# 查看binlog日志
shell> mysqlbinlog a01-bin.000003|more
修改binlog后再恢复
有时候需要删掉binlog里的某些部分再来执行恢复，比如手误删库后，需要还原数据时
# 1,)转换进普通文本查看
shell> mysqlbinlog a01-bin.000003 > tmpfile
# 2)编辑tmpfile，把不需要的删掉
# 3)使用修改后的binlog恢复数据
shell> mysql -uroot -p < tmpfile
或者
mysql> source tmpfilebinlog恢复注意事项
1)如果有多个binlog需要执行，安全的方式是在同一个连接里执行：
# 安全的
shell> mysqlbinlog a01-bin.000001 a01-bin.000002 |mysql -uroot -p
# 不安全的
shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
为什么不安全：如果log1创建了一个临时表，log2需要使用临时表，分成两次执行，log1执行完毕后临时表会被删除，log2执行时就会找不到这个临时表。
# 另一种安全的方式
shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"
2)如果binlog里包含了gtids，转存文件时需要跳过它：
shell> mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"mysqldump实用小技巧
创建数据库副本
shell> mysqldump db1 > dump.sql # 这里不用--databases参数，因为用了就会生成use db1，但我们要创建的副本是db2
shell> mysqladmin create db2
shell> mysql db2 < dump.sql
将数据库复制到另一台服务器
# 1号服务器，备份目标数据库
shell> mysqldump --databases db1 > dump.sql
# 2号服务器，直接导入
shell> mysql < dump.sql
导出存储过程、函数、触发器、events
--events 导出事件
--routines 导出存储过程和函数
--triggers 导出触发器
--triggers参数默认是开启的，其他两个默认是关闭的。
显式的关闭参数是--skip-events, --skip-routines, or --skip-triggers.
分别导出表定义语句和数据
--no-data只会导出表定义语句，没有数据。
--no-create-info只导出数据，没有表定义语句
测试升级不兼容性
# 在生成服务器上备份，不用导出数据
shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql
# 升级后的服务器上导入，查看错误和警告
shell> mysql < dump-defs.sql
# 确定兼容性后，再导出数据
shell> mysqldump --all-databases --no-create-info > dump-data.sql
# 最后把数据导入升级后的服务器里
shell> mysql < dump-data.sql
