MySQL 数据库自动备份

• MySQL 数据库自动备份
  • MySQL 备份命令
    • mysqldump 介绍
    • 数据备份
    • 数据恢复
    • gzip 介绍
  • MySQL 备份脚本
  • Linux 定时任务-crontab

MySQL 备份命令

手抖、写错条件、写错表名、错连生产库造成的误删库表和数据总有听说，那么删库之后除了跑路，还能做什么呢，当然是想办法恢复，恢复数据的基础就在于完善的备份策略。下面就来介绍下MySQL自带备份工具mysqldump。

mysqldump 介绍

• 用法

  Usage: mysqldump [OPTIONS] database [tables]
  OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
  OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
  

• 常用参数

  • --opt 如果有这个参数表示同时激活了mysqldump命令的quick，add-drop-table，add-locks，extended-insert，lock-tables参数，它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。当备份大表时，这个参数可以防止占用过多内存。（反之使用 --skip-opt）
  • --single-transaction 设置事务的隔离级别为可重复读，然后备份的时候开启事务，这样能保证在一个事务中所有相同的查询读取到同样的数据。注意，这个参数只对支持事务的引擎有效，如果有 MyISAM 的数据表，并不能保证数据一致性。(自动关闭选项 --lock-tables)
  • --default-character-set=charset 指定转储数据时采用何种字符集。(默认使用数据库的字符集)
  • --master-data=2 表示在备份过程中记录主库的 binlog 和 pos 点，并在dump文件中注释掉这一行，在使用备份文件做新备库时会用到
  • -x --lock-all-tables 锁表备份。由于 MyISAM 不能提供一致性读，如果要得到一份一致性备份，只能进行全表锁定。
  • -l --lock-tables 锁定所有的表以便读取。(默认为打开；使用--skip-lock-tables来禁用。)
  • --dump-date 在输出的最后加上转储日期。(默认为打开；使用--skip-dump-date来禁用。)
  • -h --host=name 连接主机
  • -P --port=# 端口号
  • -u --user=username 用户名
  • -p --password[=passwd] 密码
  • -A --all-databases 转储全部数据库
  • -Y --all-tablespaces 转储所有的表空间。(反之 -y --no-tablespaces不转储任何表空间信息)
  • -B --databases 转储指定数据库
    • --tables 转储指定表， 覆盖选项 --databases
  • --ignore-table=name 不转储指定的表。要指定一个以上的忽略的表，请多次使用该指令，每个表一次为每个表使用一次。每个表都必须同时指定数据库和表名(如--ignore-table=database.table)
  • -d --no-data 不转储行记录。（只有表结构，没有表数据）
  • --add-drop-database 在每次创建前添加一个DROP DATABASE。
  • --add-drop-table 在每次创建前添加一个DROP TABLE。(默认添加)

数据备份

• InnoDB 全库备份

  mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -h -u -p -A > backup.sql

• MyISAM 全库备份

  mysqldump --opt --lock-all-tables --master-data=2 --default-character-set=utf8 -h -u -p -A > backup.sql

• 备份带上压缩

  mysqldump -h -u -p -A | gzip >> backup.sql.gz

• 备份指定库（可多个库）

  mysqldump -h -u -p --databases > backup.sql

数据恢复

• SQL文件恢复

  mysql -h -u -p < backup.sql

• 压缩文件恢复

  gzip -d backup.sql.gz | mysql -h -u -p

  note: gzip -d为解压， 下面介绍下gzip用法与参数介绍（gzip命令只是压缩，不做打包操作）

gzip 介绍

• 用法：

  gzip [OPTION]... [FILE]...

• 常用参数

  • -d --decompress 解压
  • -c --stdout 保留原始文件，把压缩/解压流重定向到新文件（如： gzip -c aa > aa.gz）
  • -l --list 列出压缩文件信息，并不解压
  • -r --recursive 对目录进行递归操作
  • -t --test 测试压缩文件的完整性
  • -v --verbose 冗长模式
  • -num num为压缩效率，是一个介于1~9的数值，预设值为“6”，指定愈大的数值，压缩效率就会愈高；
    • -1 --fast 最快压缩方法（低压缩比）
    • -9 --best 最慢压缩方法（高压缩比）

• 栗子

  • 把 test目录下的每个文件压缩成.gz文件

    gzip test1`表示 第`2`列表示小时 `1~23` (0表示0点) 第`3`列表示日期 `1~31` 第`4`列表示月份 `1~12` 第`5`列表示星期 `0~6` (0表示星期日) 第`6`列是要运行的命令

Reference

• https://segmentfault.com/a/1190000019955399
• https://wangchujiang.com/linux-command/c/gzip.html