MySQL数据库备份和恢复

数据库备份

• 数据库复制不能取代备份的作用
• 备份分类：
  • 全量备份：整个数据库的完整备份
  • 增量备份：在上一次备份基础上，对更改数据进行备份。mysqldump不支持这种
  • 逻辑备份：结果为SQL语句，适用于所有存储引擎
  • 物理备份：对数据库目录的靠背，对于内存表只备份结构
  • 备份内容：
  • 备份方式：

mysqldump全备介绍

• mysqldump备份

mysqldump database [tables]
mysqldump --database DB1 [DB2]
mysqldump --all-databases

• 常用参数
  • 账户具备的权限SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS
  • -u
  • -p
  • --single-transaction 开启事务来操作，innodb推荐用这个
  • -l 依次锁表，一般myisam用。备份时锁住一个数据库下的表。如果混合引擎的表也用这个参数，和 --single-transaction互斥。只能保持一个db下的一致
  • -x，锁所有db的所有表
  • --master-data=[1或2] 时间恢复和新的slave实例用。1把change master to被记录，2则是把1放在注释里，配合 --single-transaction一起指定
  • -R 备份存储过程
  • --triggers 触发器
  • -E 备份调度事件
  • --hex-blob 把数据文本变成hex格式
  • --tab=path 指定路径下，生成表结构和表数据两个文件
  • -w='过滤条件'，单表导出用

mysqldump恢复

• bash: mysql-u-p dbname<backup.sql
• mysql client: mysql>source/tmp/backup.sql

指定时间点的恢复

先决条件

• 具有指定时间点前的一个全备
• 具备自上次全备后到指定时间点的所有“二进制日志”（相当于重复操作从备份时间点的到现在的数据库操作）

步骤：

• 还原某个时间点的全备 mysql-uroot-p mc_orderdb<mc_order_backup.sql
• 查找全备时开始的mysql-bin的log操作的日志点(change master那行)
• 查看最近的误操作mysql-bin的log的日志点
• mysqlbinlog--start-position=84882--stop-position=169348--database=mc_orderdb>mc_order_diff.sql
• mysql-uroot-p mc_orderdb<mc_order_diff.sql

实时备份binlog

• GRANT REPLICATION SLAVE ON*.*TO'repl'@'ip'IDENTIFIED BY'xxxxxx'
• mkdir-p binlog_backup
• mysqlbinlog--raw--read-from-remote-server--stop-never--host localhost--port3306-u repl-p xxxxxx二进制日志名

xtrabackup备份和恢复

• xtrabackup用于在线备份innodb存储引擎的表
  • 只会备份数据文件，不会备份表结构
• innobackupex是对extrabackup的封装并提供MyISAM表的备份功能
  • innobackupex是Xtrabackup的插件支持MyISAM备份，但也会锁表

全备和恢复

• 全备 innobackupex--user=root--password=pwd--parallel=2/home/db_backup/
• 备份恢复 innobackupex--apply-log/path/to/BACKUP-DIR

增量备份和恢复

• 增量备份 innobackupex--user=root--password=pwd--incremental/home/db_backup/--incremental-basedir=/home/db_backup/back-dir 参数 --incremental-basedir上一次全备的文件夹
• 增量备份恢复
  1. innobackupex--apply-log--redo-only全备目录
  2. innobackupex--apply-log--redo-only全备目录--incremental-dir=第一次增量目录
  3. innobackupex--apply-log全备目录
  4. mv/path/to/BACKUP-DIR/home/mysql/data 恢复后的数据直接替换原本的
  5. 记得改变属于的用户chmod

制定备份计划

• 每天凌晨对数据库进行一次全备
• 实时对二进制日志进行远程备份
  • 使用linux定时任务：crontab

参考

1. 高性能可扩展MySQL数据库设计及架构优化 电商项目，sqlercn，https://coding.imooc.com/class/79.html