MySQL备份恢复基础知识及MySQLdump讲解
数据库备份恢复知识要点：时间轴备份类型分类：
完全备份：备份整个数据集
增量备份：上一次完全备份，或上一次增量备份以后变化的数据的备份(还原麻烦，节省空间)
差异备份：仅备份最近一次完全备份以来变化的数据（还原简单，空间消耗大）
什么是物理备份、逻辑备份：
物理备份：直接复制数据文件进行备份(有可能占用更多的空间，备份速度快，做热备较难)
逻辑备份：从数据库中导出数据“另存为”而进行的备份(从二进制转化为文本格式，有可能丢失精度，需要专门的协议客户端才能进行，和数据存储引擎无关、备份恢复时间较长、做热备容易)
应该备份什么？
① 数据
② 二进制日志、innodb的事务日志
③ 代码(存储过程、存储函数、触发器、事件调度器)
④ 服务器的配置文件
备份工具：
① MySQLdump#mysql自带逻辑备份工具
② cp、tar 物理备份工具，冷备
③ lvm2的快照：几乎热备（请求施加全局持锁），借助于文件系统管理工具进行备份
④ mysqlhotcopy ：几乎冷备，仅适用于myisam存储引擎
备份方案的选择：
方案一：MySQLdump+复制binlog(时间较慢，支持远程备份)
MySQLdump：完全备份
复制binlog中指定时间范围的event：增量备份
方案二：lvm2快照+复制binlog
lvm2快照：使用cp或tar等做物理备份：完全备份
复制binlog中指定时间范围的event：增量备份
方案三：XtraBackup
由percona提供的支持对innodb做热备（物理备份）的工具
支持完全备份，增量备份
MySQLdump备份工具讲解：
什么是MySQL的dump？
mysql或mariadb自带客户端命令，逻辑备份工具。是图形化备份管理工具mydumper、phpmyadmin的备份调用工具。基于mysql客户端协议，适用于所有存储引擎，温备：完全备份、部分备份。对innodb支持热备(时间较长)适合备份较小的数据库(GB以下).
对常见存储引擎支持：
InnoDB：热备或温备；
MyISAM：温备；
备份机制：
通过mysql协议连接至mysql服务器。向mysql服务器发起一个全量查询操作，把所有数据拿到本地以后，并且将读取到的数据保存在文件中从而完成备份。
库：CREATE DATABASE
表：CREATE TABLE
数据：INSERT INTO
使用方法Usage:
① mysqldump database # 备份单库，可以只备份其中的一部分表（部分备份,还原时需手动创建数据库）；
② mysqldump -B/--databases DB1 # 备份多库(推荐使用)；
③ mysqldump -A/--all-databases # 备份所有库；
初级备份实验示例：
① 备份：mysqldump -uroot -p -B test > test.sql
② 登录mysql删除test库：drop databasetest；
③ 恢复：mysql -uroot -p shudian.sql
--single-transaction：创建一个事务,在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
--master-data=2:记录备份时二进制文件记录的位置，此语句被注释
--flush-logs：锁定表完成后，即进行日志滚动操作
--lock-all-tables,-x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
mysqldump-uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables,-l
开始导出前，锁定所有表。用READLOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump-uroot -p --host=localhost --all-databases --lock-tables
2.修改主机A数据
insert into mybook values (5,'liangzi',30,412); 修改后数据：
3.发送备份信息
① 全局备份文件
scp shudian.sql B主机IP：/app ② 查看全局备份文件看备份到哪个节点并记录：
grep " MASTER_LOG_POS" shudian.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='test.000002', MASTER_LOG_POS=1545;
③ 备份节点后二进制日志
mysqlbinlog --start-position=1545 /app/test.000002 -D >incre.sql
#mysqlbinlog -d -D ops test.000002 >ops_incre_bin.sql
--start-position= 起始位置
-d #指定数据库
-D / --disable-log #禁用二进制日志,生成的sql文件需要root权限才能执行
如误操作后需修改sql文件剔除相关操作的条目，例误删除某表要删除：
DROP TABLE `t_bind_card` /* generated by server */
/*!*/;
# at 341
#180824 15:04:11 server id 1end_log_pos 389 CRC32 0x2f0eec92 Rotate to master-bin.000004pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
发送二进制日志至主机B：
scp incre.sql B主机IP：/app ④ 主机B数据恢复
set sql_log_bin=0;临时关闭记录二进制日志
mysql
页:
[1]