MySQL完全、增量的备份与恢复

数据库备份的重要性

1. 提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据备份就没法找到数据。
2. 使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案。
3. 没有数据库就没有一切，数据库备份是一种防范灾难的强力手段。

数据库备份的分类

1. 物理备份：指对数据库操作系统的物理文件（例如数据文件、日志文件等）的备份。物理备份游客分为脱机备份（冷备份）和联机备份（热备份）。
   冷备份：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
   热备份：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。
2. 逻辑备份：指对数据库逻辑组件（如表等数据库对象）的备份。从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份。
   完全备份：每次对数据库进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
   差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库的部分内容。
   增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

完全备份（在linux模式中进行备份操作）

完全物理备份

使用tar打包文件夹备份

• 数据库压缩时会使用压缩率较大的xz格式压缩。

  rpm -q xz    //检查xz软件包是否安装
  yum -y install xz   //安装xz软件包

• 对数据库文件夹（/usr/local/mysql/data/）进行打包操作

  tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
       //备份数据库文件夹，并以年月日的形式命名
  ls /opt/
  mysql-2018-07-02.tar.xz

  

• 对数据库文件进行恢复操作
  只需将数据库文件的压缩包，解压缩即可，即使整个/data/目录都丢失仍可找回

  mkdir /abc
  tar Jxf mysql-2018-07-03.tar.xz -C /abc/

  

  完全逻辑备份

  使用mysqldump工具备份

• 备份某个数据库

  mysqldump -u root -p stady > /opt/stady.sql
  //备份stady库到/opt目录下，-p之后可跟密码，也可不写。不写就在回车之后输入密码验证。 **备份文件后缀都为.sql,前面名称见名知意即可**

  

• 查看备份文件的内容
  
  1. 备份多个数据库

     mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql

     

  2. 备份所有数据库

     mysqldump -u root -p --opt --all-databases > /opt//all.sql

  3. 备份数据库中的表

     mysqldump -u root -p stady info > /opt/stady-info.sql   //险些库名，再接表名

  4. 备份数据库的表结构

     mysqldump -u root -p -d stady info > /opt/decribe-info.sql
     // -d  库名+表名

     数据恢复

     登陆MySQL，使用source命令恢复

• 恢复”备份数据库中的表“步骤中的info表

  [root@centos7-6 /]# mysql -u root -p 
  Enter password: 
  ···   //省略部分内容
  mysql> use stady;    //切换到库
  Database changed
  mysql> drop table info;   //删除info表
  Query OK, 0 rows affected (0.02 sec)
  mysql> show tables;       //查看库中表
  Empty set (0.00 sec)       //库中为空
  mysql> source /opt/stady-info.sql   //恢复info表，格式是source+备份文件路径和文件名称
  Query OK, 0 rows affected (0.02 sec)
  ···   //省略部分内容
  mysql> show tables;    //查看库中表
  +-----------------+
  | Tables_in_stady |
  +-----------------+
  | info            |   //info表已经成功恢复
  +-----------------+
  1 row in set (0.00 sec)

• 使用mysql命令恢复表

  [root@centos7-6 /]# mysql -u root -p 
  Enter password: 
  ···   //省略部分内容
  mysql> use stady;    //切换到库
  Database changed
  mysql> drop table info;   //删除info表
  Query OK, 0 rows affected (0.02 sec)
  mysql> show tables;       //查看库中表
  Empty set (0.00 sec)       //库中为空
  mysql> quit               //退出数据库，切换到linux
  Bye
  [root@centos7-6 /]# mysql -u root -p stady < /opt/stady-info.sql   //在linux环境中恢复
  Enter password: 
  [root@centos7-6 /]# mysql -u root -p   //进入数据库查看
  Enter password: 
  ···   //省略部分内容
  mysql> use stady;
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
  Database changed
  mysql> show tables;
  +-----------------+
  | Tables_in_stady |
  +-----------------+
  | info            |   //info表已恢复
  +-----------------+
  1 row in set (0.00 sec)

  恢复库和恢复表的方式相同，但在恢复数据表时要注意，如果备份文件中只备份了表文件，此时mysql中又没有库时，若直接恢复，会失败，必须先创建库，然后再进行恢复。

  增量备份

  在MySQL数据库中创建stady库，创建info表，并插入数据

  mysql> use stady;
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  +----------+-------+
  2 rows in set (0.00 sec)

• 开启二进制日志

  vim /etc/my.cnf
  log-bin=mysql-bin   //在[mysqld]段插入该语句
  systemctl restart mysqld.service   //重启mysql服务,会在/data/目录下生成新的日志文件（空文件）

  
  

• 使用mysqldump备份stady库

  mkdir /backup
  mysqldump -u root -p stady > /backup/stady.sql
  mysqladmin -u root -p flush-logs  //刷新生成新的日志文件，存在01中，新存在的02为空文件

  

• 插入新数据，生成新的增量日志文件

  mysql> insert into info (name,score) values ('chen',90);//进入数据库，向表中插入数据
  mysqladmin -u root -p flush-logs   //刷新生成新的增量文件，增量备份保存在02中
  mysql> insert into info (name,score) values ('chen01',96);//再次进入数据库，向表中插入数据
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | chen     | 90.00 |
  | chen01   | 96.00 |
  +----------+-------+
  4 rows in set (0.00 sec)
  mysqladmin -u root -p flush-logs //再次刷新，增量备份保存在03中

  

  增量恢复

• 模拟数据丢失，删除新添加的两条记录

  [root@centos7-6 data]# mysql -u root -p 
  Enter password: 
  mysql> use stady;
  Database changed
  mysql> delete from info where name='chen';
  Query OK, 1 row affected (0.00 sec)
  mysql> delete from info where name='chen01';
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  +----------+-------+
  2 rows in set (0.00 sec)

• 使用mysqlbinlog命令，恢复数据

  [root@centos7-6 data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
  Enter password: //输入密码确认恢复数据
  [root@centos7-6 data]# mysql -u root -p 
  Enter password:   //输入密码登陆系统
  mysql> use stady;
  Database changed
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | chen     | 90.00 |//增量备份的02数据成功恢复
  +----------+-------+
  3 rows in set (0.00 sec)

  使用mysqlbinlog查看增量备份文件的内容

  mysqlbinlog --no-defaults --base64-output-decode-rows -v /usr/local/mysql/data/mysql-bin.000002
  //--base64-output=decode-rows使用64位编码机制解码，按行读取 -v 显示出来
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  ···    //省略部分内容
  # at 345
  #180703 21:12:11 server id 1  end_log_pos 389 CRC32 0xb0bc2cb1  Write_rows: table id 118 flags: STMT_END_F
  ### INSERT INTO `stady`.`info`
  ### SET
  ###   @1='chen'
  ###   @2=90.00
  ···//省略部分内容

  在开启二进制日志之后就会在/data/目录下生成空的mysql-bin.000001文件，在使用该命令mysqladmin -u root -p flush-logs 后会将增量备份写入mysql-bin.000001，同时生成一个新的mysql-bin.000002空文件，等待下次刷新写入，同时再创建mysql-bin.000003……

  增量断点恢复

  模拟环境

  模拟插入数据出现误操作，在插入两条数据的同时，删除了一条正确的数据。

  [root@centos7-6 data]# mysql -u root -p 
  Enter password: 
  mysql> use stady;
  Database changed
  mysql> insert into info (name,score) values ('test01',88);
  Query OK, 1 row affected (0.00 sec)
  mysql> delete from info where name='chen';
  Query OK, 1 row affected (0.00 sec)
  mysql> insert into info (name,score) values ('test02',88);
  Query OK, 1 row affected (0.01 sec)
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | test01   | 88.00 |
  | test02   | 88.00 |
  +----------+-------+
  4 rows in set (0.00 sec)    //插入了两条test01和test02，但是chen被操作删除
  mysql> quit   //退出数据库
  Bye
  [root@centos7-6 data]# mysqladmin -u root -p flush-logs  //刷新生成新的增量文件，保存在04中
  Enter password: 

  增量时间节点恢复

• 解码导出增量备份文件，便于查看和做恢复

  mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /backup/info.txt
  cd /backup

  记录错误操作的开始时间，以及正确操作的开始时间，恢复时跳过该时段的操作。
  

• 根据时间点恢复被误操作的数据

  mysql> drop table info;   //删除被误操作的表
  mysql -u root -p stady < /backup/stady.sql  //恢复完全备份的stady库
  mysqlbinlog --no-defaults --stop-datetime='2018-07-03 21:57:08' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p  //恢复误操作前的内容
  mysqlbinlog --no-defaults --start-datetime='2018-07-03 21:57:14' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作之后的内容

• 根据位置标记号恢复数据
  

  mysql> drop table info;   //删除被误操作的表
  mysql -u root -p stady < /backup/stady.sql  //恢复完全备份的stady库
  mysqlbinlog --no-defaults --stop-position='1408' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p
  mysqlbinlog --no-defaults --start-position='1674' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

  数据完成恢复