MySQL系列——MySQL备份和恢复

原创

TomJames 2019-08-03 14:59:15 ©著作权

©著作权归作者所有：来自51CTO博客作者TomJames的原创作品，请联系作者获取转载授权，否则将追究法律责任

MYSQL的备份和还原备份：副本 RAID1，RAID10 ：保证硬件损坏而不会业务中止；备份和还原：可以保证因逻辑错误引起的数据丢失备份类型：热备份：读写不受影响温备份：仅可以执行读操作冷备份：离线备份，读、写操作均中止

物理备份和逻辑备份物理备份：复制数据文件逻辑备份：将数据导出至文本文件中

完全备份、增量备份和差异备份备份什么：数据、配置文件、二进制日志、事务日志主从服务器架构

物理备份：速度快逻辑备份：速度慢、丢失浮点数精度：方便使用文本处理工具直接对其处理，可移植能力强;

备份策略：完全+增量、完全+差异 MYSQL备份工具： mysqldump : 逻辑备份工具、MyISAM（温）、InnoDB（热备份） mysqlhotcopy : 物理备份工具、温备份文件系统工具： cp ：冷备 lv : 逻辑卷的快照功能，几乎热备； FLUSH TABLES； LOCK TABLES 创建快照，释放锁，而后复制数据第三组工具： ibbackup : 商业工具 xtrabackup : 开源工具 MYSQL备份工具比较列表

mysqldump : 逻辑备份 mysqldump(完全备份)+二进制日志完全+增量备份单个数据库，或库中单个表 mysqldump DB_NAME [tb1] [tb2] mysqldump --master-data={0|1|2} 0 : 不记录二进制日志文件及路位置 1 ：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器 2 ：以CHNAGE MASTER TO的方式记录位置,但默认为被注释； --lock-all-tables : 锁定所有表 --flush-logs : 执行日志flush; 如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；备份多个库： --all-databases : 备份所有库 --datebases DB_NAME,DB_NAME,... 备份指定库 --events 事件 --routines 存储过程、存储函数 --triggers 触发器

FLUSH TABLES WITH READ LOCK；刷新表并使用读锁

备份策略：周完全+每日增量完全备份：mysqldump 增量备份：备份二进制日志文件（flush logs）二进制日志： set sql_log_bin=1 1=ON 0=OFF 二进制日志： format statement row mixed mysqldump : --databases DB1,DB2,... --all-databases

逻辑备份： 1、浮点数据丢失精度； 2、备份出的数据更占用存储空间，压缩后可大大节省空间； 3、不适合对大数据库做完全备份；

通常做单张表的备份 SELECT INTO OUTFILE '/path/to/somefile.txt' FORM tb_name [where clause] ; 只备份数据，不备份SQL命令 LOAD DATA INFILE '/path/to/somefile.txt' INTO ta_name;

几乎热备：LVM snapshot 前提： 1、数据文件要在逻辑卷上； 2、此逻辑卷所在卷组必须有足够空间使用快照卷； 3、数据文件和事务日志要在同一个逻辑卷上；步骤： 1、打开会话，施加读锁，锁定所有表； FLUSH TABLES WITH READ LOCK； FLUSH LOGS； 2、通过另一个终端，保存二进制日志文件及相关位置信息； mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info 3、创建快照卷 lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv 4、释放锁 UNLOCK TABLES； 5、挂载快照卷，备份 mount cp 6、删除快照卷 7、备份二进制日志

二进制日志相关的几个选项： innodb_support_xa={TRUE|FLASE} sync_binlog=# 建议设定为1