2、备份实战案例2

（1）特点

  1. 自动记录日志position位置
  2. 锁表机制

（2）语法

  1. mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql // -h可以不输入
  • -A, --all-databases 所有库
  • school 数据库名
  • school stu_info t1 是指school数据库的表stu_info、t1
  • -B, --databases bbs test mysql 多个数据库
  • --single-transaction #InnoDB 一致性 服务可用性？
  • --master-data=1|2 #该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释
  • --opt #同时启动各种高级选项
  • -R, --routines #备份存储过程和存储函数
  • -F, --flush-logs #备份之前刷新日志，截断日志。备份之后新binlog。
  • --triggers #备份触发器
  1. 帮助 // mysqldump --help

（2）备份实战

  1. 准备两套root密码
  • QianFeng@123
  • QianFeng@1234 // 备用
  1. 准备库1
  • 请清理掉之前的test库。实验后半部分，需要继续创建test库，避免混淆。
  • testdb1.t1
  1. 执行备份

  • [root@localhost ~]# mysqldump -p'QianFeng@123' \

    --all-databases --single-transaction \

    --master-data=2 \

    --flush-logs \

    (>)/backup/date +%F-%H-mysql-all.sql --- 括号无用 // master-data=2 注释掉日

    志记录

  1. 观察备份细节

  • vim /backup/2016-11-25-14-mysql-all.sql

  • LOCK TABLES user WRITE; // 观察各种锁机制，用来保证数据一致性

    22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154; // 二进制日志截断位置。第22行

  1. 备份后的，数据变更行为

  • 在testdb2.t2 中插入数据1，2,3； 创testdb3库 切断二进制日志（重启数据库）

    在testdb2.t2 中插入数据4； 删testdb3库 在testdb2.t2 中插入数据5；

（3）恢复实战

  1. 备份二进制日志文件
  • cp /var/lib/mysql/bin ~
  1. 停止数据库
  • systemctl stop mysqld
  1. 清理环境
  • rm -rf /var/lib/mysql/*
  1. 启动数据库
  • systemctl start mysqld
  • grep 'password' /var/log/mysqld.log // 找密码，再改一下密码。请使用备用密码
  • mysqladmin -uroot -p'Kyzw;u2dq<mh' password 'QianFeng@1234'
  1. mysql恢复数据
  • mysql -p'QianFeng@1234' < /backup/2016-12-08-04-mysql-all.sql
  • mysql -p'QianFeng@1234' -e 'flush privileges'
  1. 二进制日志恢复

  • vim /backup/2016-11-25-14-mysql-all.sql

  • CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', MASTER_LOG_POS=154; // 语法

  • mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-position=154 | mysql -p'QianFeng@123' //注意后续有多少日志，要跟多少日志名字。

  1. 观察数据
  • 是否完整
  1. 误删除的问题

  • 如何保留testdb3库

    [root@localhost ~]#mysqlbinlog localhost-bin.00004 ......005.....006...... > 1.txt

    删除1.txt中不需要的at（比如test3库是误操作）

    cat 1.txt | mysql -p'QianFeng@123'

  1. 关于数据恢复时的多余日志

  • 方法一

    每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。

    mysql> set sql_log_bin =0

    再mysql> source /backup/*.sql // 二进制日志并没有发生变化。

  • 方法2

    在备份文件中，加入关闭二进制日志。