MySQL备份与主备配置

数据备份类型

• 全量备份：备份整个数据库
• 增量备份：备份自上一次备份以来（增量或完全）以来变化的数据
• 差异备份：备份自上一次完全备份以来变化的数据

  全量备份

  全量备份的方法有 2 种，一种是利用数据库管理工具提供的备份恢复和导入导出功能。
  例如：如果使用 Navicat、PHPMyAdmin 之类的可视化工具，可以直接点击转储 SQL 文件，或者导出 SQL 文件之类的功能。
  另一种是利用 mysqldump。
  导出：

  sudo mysqldump -u root -p student > dir/student_backup.sql
  

  导入：

  sudo mysqldump-u root -p student < dir/student_backup.sql
  source student_backup.sql #要在数据库操作 use student 之后
  

  增量备份

  增量备份的 binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，但是对库表等内容的查询不会记录。
  在配置文件中，修改配置打开 binlog。通过 show variables like '%log_bin%'; 查看 binlog 是否打开。
  
  可以看到默认是没有打开的。
  默认的配置文件可能在 /etc/mysql/my.cnf，如果是使用 XAMPP 等一键安装的，也可能在 /opt/lampp/etc/my.cnf 等位置。
  在配置文件找到 log_bin 所在的位置，取消这一行的注释。
  
  重启服务以后，可以看到启用了 binlog。
  
  binlog 的使用格式

  show binary logs;
  show binlog events in 'mysql-bin.000001';
  

  
  
  GTID 的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：`gtid=server-uuid:gno。
  server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值。
  gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
  在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。
  这样，每个MySQL 实例都维护了一个GTID 集合，用来对应“这个实例执行过的所有事务”。
  下面来测试一下 binlog。
  
  先导入测试数据，然后执行以下语句。

  update student set birth = 2019 where id = '100';
  insert into student values(200, 'jxtxzzw', '男', 2019, '计算机系', '上海');
  delete from student where id = 200;
  

  查看 binlog。
  
  通过以下两条语句可以生成新的 binlog。

  flush logs;
  show binary logs;
  

  除了 flush logs;，重启 MySQL 服务以及 mysqlbinlog 也可以生成新的 binlog。

  通过 binlog 恢复数据

  构造场景：

  insert into student values(907,'李七','男',1991,'计算机系','上海');
  insert into student values(908,'李八','男',1992,'音乐系','上海');
  delete from student where id=907;//误删
  delete from student where id=908;//误删
  

  如何通过 binlog 恢复这两条数据？
  通过查看 binlog 找到了误删的两条数据。
  

  mysqlbinlog --start-position=4183 --stop-position=4592 /opt/lampp/var/mysql/mysql-bin.000001 | mysql -u root -p
  

  在上面这条语句中，首先是设置了起点为 4183、终点为 4592，并指定了 binlog 的文件为 mysql-bin.000001。
  
  
  输入管理员密码之后，可以重新打开数据库看一下是不是成功。
  
  可以看到恢复成功。
  如果想要删除 binlog，删除 binlog 的方法是：

1. 关闭 MYSQL 主从，关闭 binlog。
2. 开启 MYSQL 主从，设置 expire_logs_days。
3. 手动清除 binlog 文件，PURGE MASTER LOGS to ‘mysqld-bin.00001’;(before ‘date’)。
4. reset master。

   练习 1

5. 删掉 student 库，通过全量备份和 binlog 对其进行恢复。
6. 尝试了解 binlog 的三种格式。
   删掉 student 库的过程非常简单，而通过全量备份恢复只需要导入即可，从略。
   下面重点说一下从 binlog 恢复的过程。
   
   需要特别说明的是，命令行下可能不允许使用 delete，这时候可以用 drop table 替换。
   删除所有数据以后再次打开数据库，看到表已经是空的了。
   
   然后打开 binlog 看一眼，找到 start position 和 stop position。
   
   然后从起点位置到结束位置执行一次恢复。
   
   可以看到数据已经恢复了。
   
   binlog 的三种格式：
   直接转载 卜算 的《使用mysql的binlog恢复误操作(update|delete)的数据》（https://blog.csdn.net/Aeroleo/article/details/77929917）中的内容：

   MYSQL binlog复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

   ① STATEMENT模式（SBR）

   每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

   ② ROW模式（RBR）

   不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

   ③ MIXED模式（MBR）

   以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

   MySQL 主备配置

   在主库上创建用户 repl，并给他权限。

   CREATE USER repl;
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
   

   
   检查在主库 my.cnf 中配置 server-id。发现已经配置了。
   
   然后进配置文