MySQL数据库备份和恢复

数据库备份

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

mysqldump全备介绍

• mysqldump备份

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

• 常用参数
  • -u
    • 账户具备的权限SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS
  • -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 --port 3306 -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

关于我：

linxinzhe，全栈工程师，目前供职于某500强通信企业。人工智能，区块链爱好者。

GitHub:https://github.com/linxinzhe

欢迎留言讨论，也欢迎关注我~
我也会关注你的哦！