MySQL的逻辑备份

1、mysqldump基本备份
本质：导出的是sql语句文件
优点：无论是什么存储引擎，都可以用mysqldump备成sql语句
缺点：速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份.

提供三种级别的备份，表级，库级和全库级
㈠ 基本语法

  1. 表级别备份
  2. mysqldump [OPTIONS] database [tables]
  3. 库级别备份
  4. mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
  5. 全库级别备份
  6. mysqldump [OPTIONS] --all-databases [OPTIONS]

㈡ 常用参数

常用参数 描述说明
—flush-logs, -F 开始备份前刷新日志
—flush-privileges 备份包含mysql数据库时刷新授权表
—lock-all-tables, -x MyISAM一致性，服务可用性（针对所有库所有表）
—lock-tables, -l 备份前锁表（针对要备份的库）
—single-transaction 适用InnoDB引擎，保证一致性，服务可用性
—master-data=2 表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行
—master-data=1 表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行

—master-data参数其他说明：
1）恢复时会执行，默认是1
2）需要RELOAD privilege并必须打开二进制文件
3）这个选项会自动打开—lock-all-tables，关闭—lock-tables

㈢ 举例说明

  1. 表级备份：
  2. # mysqldump -p123 db01 emp > /tmp/mysqlbak/emp.sql    备份单个表
  3. # mysqldump -p123 db01 emp dept > /tmp/mysqlbak/emp.sql    备份多个表
  5. 表级恢复：
  6. # mysql -p db01 </tmp/mysqlbak/emp.sql
  7. 或者在mysql数据库内使用source命令来执行外部的sql文件
  8. mysql> source /tmp/mysqlbackup/emp.sql    
  10. 库级备份:
  11. # mysqldump --databases db01 -p > /tmp/mysqlbak/db01.sql        备份单个库
  12. # mysqldump --databases db01 db02 -p > /tmp/mysqlbak/db01.sql    备份多个库
  14. 库级恢复：
  15. # mysql -p </tmp/mysqlbak/db01.sql 
  16. mysql> source /tmp/mysqlbak/db01.sql 
  19. 全库级备份：
  20. # mysqldump --all-databases --master-data --single-transaction > all.sql

㈣ 小结

  1. mysqldump工具备份的是SQL语句，故备份不需要停服务
  2. 使用备份文件恢复时，要保证数据库处于运行状态
  3. 只能实现全库，指定库，表级别的某一时刻的备份，本身不能增量备份
  4. 适用于中小型数据库

2、mysqldump实现增量备份
核心思路：结合binglog实现增量

  1. 备份的目的为了数据出问题进行恢复
  2. 二进制日志文件默认会记录下所有对数据库数据变化的操作(增、删、改)
  3. 二进制文件同时还会记录具体sql执行时的环境、时间以及起始位置和结束位置（pos值）
  4. 通过获取二进制文件里的SQL操作来进行数据的恢复

㈠ 实现思路

  1. 先做全量备份（mysqldump）
  2. 对数据库做更改操作
  3. 备份二进制日志文件
  4. 模拟故障进行
  5. 数据恢复
  6. 测试验证

㈡ 实现步骤

① 全库备份
  1. # mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > /tmp/all.sql -p

② 更改数据库
  1. mysql> select * from db01.t1;
  2. +------+--------+---------+-----------+
  3. | id   | name   | salary  | dept      |
  4. +------+--------+---------+-----------+
  5. |    1 | harry  | 1300.00 | 市场部    |
  6. |    2 | amy    | 2200.00 | 人事部    |
  7. |    3 | tom    |  600.00 | 财务部    |
  8. |    4 | jack   | 3300.00 | 市场部    |
  9. |    5 | momo   | 1700.00 | 市场部    |
  10. |    6 | sarsha | 1300.00 | 人事部    |
  11. |    7 | xiaom  | 4300.00 | 市场部    |
  12. +------+--------+---------+-----------+
  13. mysql> select * from db02.t1;
  14. +----+-------+
  15. | id | name  |
  16. +----+-------+
  17. |  1 | harry |
  18. |  2 | tom   |
  19. +----+-------+
  20. 2 rows in set (0.00 sec)
  21. 更改：
  22. mysql> delete from db01.t1 where id=7;
  24. mysql> insert into db01.t1 values(88,'aaa',11111.23,'安保'),(99,'bbb',22222.22,'运维');
  26. mysql> update db02.t1 set name='jack' where id=2;
  28. mysql> select * from db01.t1;
  29. +------+--------+----------+-----------+
  30. | id   | name   | salary   | dept      |
  31. +------+--------+----------+-----------+
  32. |    1 | harry  |  1300.00 | 市场部    |
  33. |    2 | amy    |  2200.00 | 人事部    |
  34. |    3 | tom    |   600.00 | 财务部    |
  35. |    4 | jack   |  3300.00 | 市场部    |
  36. |    5 | momo   |  1700.00 | 市场部    |
  37. |    6 | sarsha |  1300.00 | 人事部    |
  38. |   88 | aaa    | 11111.23 | 安保      |
  39. |   99 | bbb    | 22222.22 | 运维      |
  40. +------+--------+----------+-----------+
  41. 8 rows in set (0.00 sec)
  42. mysql> select * from db02.t1;
  43. +----+-------+
  44. | id | name  |
  45. +----+-------+
  46. |  1 | harry |
  47. |  2 | jack  |
  48. +----+-------+
  49. 2 rows in set (0.00 sec)

③ 备份二进制文件
  1. 方法1：直接拷贝物理文件
  2. 方法2：备份改变过的数据（SQL）
  3. # mysqlbinlog --start-position=120 binlog.000005 > /opt/mybinlog.sql

④ 故障模拟
  1. mysql> drop database db01;
  2. Query OK, 7 rows affected (0.07 sec)
  4. mysql> drop database db02;
  5. Query OK, 3 rows affected (0.01 sec)

⑤ 全量恢复
  1. 1. 通过刚刚全量备份进行恢复
  2. # mysql < /tmp/all.sql -p
  3. 2. 进入数据库查看结果
  4. mysql> select * from db01.t1;
  5. +------+--------+---------+-----------+
  6. | id   | name   | salary  | dept      |
  7. +------+--------+---------+-----------+
  8. |    1 | harry  | 1300.00 | 市场部    |
  9. |    2 | amy    | 2200.00 | 人事部    |
  10. |    3 | tom    |  600.00 | 财务部    |
  11. |    4 | jack   | 3300.00 | 市场部    |
  12. |    5 | momo   | 1700.00 | 市场部    |
  13. |    6 | sarsha | 1300.00 | 人事部    |
  14. |    7 | xiaom  | 4300.00 | 市场部    |
  15. +------+--------+---------+-----------+
  16. 7 rows in set (0.01 sec)
  17. mysql> select * from db02.t1;
  18. +----+-------+
  19. | id | name  |
  20. +----+-------+
  21. |  1 | harry |
  22. |  2 | tom   |
  23. +----+-------+
  24. 2 rows in set (0.00 sec)
  25. 说明：以上数据不是最新数据

⑥ 二进制日志恢复到最新状态
  1. # mysqlbinlog --start-position=120 /opt/mybinlog.000002 |mysql -p
  2. 说明：
  3. 1.只有起始位置--start-position=120说明从120往后所有记录
  4. 2.一般SQL语句前面的at xxx代表该语句的position值
  5. mysql> select * from db01.t1;
  6. +------+--------+----------+-----------+
  7. | id   | name   | salary   | dept      |
  8. +------+--------+----------+-----------+
  9. |    1 | harry  |  1300.00 | 市场部    |
  10. |    2 | amy    |  2200.00 | 人事部    |
  11. |    3 | tom    |   600.00 | 财务部    |
  12. |    4 | jack   |  3300.00 | 市场部    |
  13. |    5 | momo   |  1700.00 | 市场部    |
  14. |    6 | sarsha |  1300.00 | 人事部    |
  15. |   88 | aaa    | 11111.23 | 安保      |
  16. |   99 | bbb    | 22222.22 | 运维      |
  17. +------+--------+----------+-----------+

㈢ 小结

  1. mysqldump工具只能恢复到备份那一时刻
  2. 通过二进制日志来恢复到最新数据
  3. 二进制日志也可以跳过sql恢复
  4. 二进制日志恢复数据的重点就是找对相应的position值或者时间点

3、逻辑导入导出（了解）

无论是什么存储引擎，以下方式本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份。备份的是数据记录。

㈠ 语法
导出数据：

  1. select  字段  into outfile '/path/file' from table_name; 
  2. select * into outfile '/tmp/emp.txt' from db01.emp;

恢复数据：

  1. 方法1：
  2. load data local infile '/tmp/emp.txt' into table emp; 
  3. 说明：该方法要求在编译数据库时要加上--enable-local-infile参数才可以使用
  4. +---------------+-------+
  5. | Variable_name | Value |
  6. +---------------+-------+
  7. | local_infile  | ON   |
  8. +---------------+-------+
  9. 方法2：
  10. # mysqlimport dbname /path/file

㈡ 举例说明1

把db01库的emp表导出成文本，然后误删掉数据后进行恢复

  1. mysql> select * into outfile '/tmp/backup/emp.bak' from emp;
  2. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
  3. 原因：没有给目录授权
  4. 解决：修改配置文件加入以下内容，重启数据库
  5. secure_file_priv=/tmp/backup/
  7. mysql> select * into outfile '/tmp/backup/emp.bak' from emp;
  8. ERROR 1 (HY000): Can't create/write to file '/tmp/backup/emp.bak' (Errcode: 13 - Permission denied)
  9. 原因：mysql用户没有权限在/tmp/backup目录里创建文件
  10. 解决办法：
  11. [root@mysql01 ~]# ll -d /tmp/backup/
  12. drwxr-xr-x 2 root root 22 Aug  3 16:35 /tmp/backup/
  13. [root@mysql01 ~]# setfacl -m u:mysql:rwx /tmp/backup/

㈢ 举例说明2
创建一个表，把你系统里的/etc/passwd导入到数据库

  1. 1. 创建表password
  2. CREATE TABLE `password` (
  3.   `uname` varchar(50) DEFAULT NULL,
  4.   `pass` char(2) DEFAULT NULL,
  5.   `uid` int(11) DEFAULT NULL,
  6.   `gid` int(11) DEFAULT NULL,
  7.   `comment` varchar(255) DEFAULT NULL,
  8.   `home` varchar(50) DEFAULT NULL,
  9.   `shell` varchar(50) DEFAULT NULL
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  12. 2. 创建password.txt文件(文件内容必须是\t分割的多列)并导入到数据库
  13. 方法一：使用sed或者awk处理成新文本
  14. 方法二：直接用mysqlimport指定分隔符
  15. # mysqlimport db01 --fields-terminated-by=':' --lines-terminated-by='\n' /tmp/mysqlbak/password -p
  17. \n 代表linux系统回车键的行结束符号
  18. windows默认为\r\n

三、MySQL的物理备份

1、xtrabackup备份介绍

㈠ xtrabackup优缺点

优点：

  1. 备份过程==快速、可靠(==因为是物理备份)；
  2. 支持增量备份，更为灵活
  3. 备份过程不会打断正在执行的事务；
  4. 能够基于压缩等功能节约磁盘空间和流量；
  5. 自动实现备份检验；
  6. 还原速度快；

缺点：

  1. 只能对innodb表增量备份，myisam表增量备份时是全备
  2. innobackupex备份MyISAM表之前要对全库进行加READ LOCK，阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写。

㈡ xtrabackup备份原理

  1. innobackupex首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql的redo log的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中。
  2. 物理拷贝innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方
  3. 复制结束后，执行flush table with read lock操作进行全库锁表准备备份非InnoDB文件
  4. 物理复制.frm .myd .myi等非InnoDB引擎文件到备份目录
  5. 查看二进制日志的位置
  6. 解锁表unlock tables
  7. 停止xtrabackup_log进程

MySQL逻辑备份 - 图1

  1.                                     图片来自网络

㈢ xtrabackup备份恢复原理

MySQL逻辑备份 - 图2

具体文字描述如下（了解）：

  • 在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
  • xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。
  • 上面就是xtrabackup的备份过程。接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了。
  • 以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。
  • 备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的recover和restore，InnoDB的prepare过程可以称为recover（恢复），MyISAM的数据复制过程可以称为restore（还原）。
  • Mysql5.7.3以后开启二进制日志需要加上server-id选项，不然报错

2、xtrabackup全备及恢复

㈠ 实现思路

  1. innobackupex工具安装
  2. innobackupex进行全备，备份集不能直接用于恢复
  3. 预备阶段，备份过程中产生的xtrabackup_log应用到全量备份集
  4. 模拟故障（删除数据）
  5. 进行全库恢复
  6. 测试验证

㈡ 实现步骤

① 安装工具
  1. [root@db01 ~]# yum -y install libev-4.15-3.el7.x86_64.rpm
  2. [root@db01 ~]# yum -y install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
  3. [root@db01 ~]# rpm -ql percona-xtrabackup-24
  4. /usr/bin/innobackupex
  5. /usr/bin/xbcloud
  6. /usr/bin/xbcloud_osenv
  7. /usr/bin/xbcrypt
  8. /usr/bin/xbstream
  9. /usr/bin/xtrabackup
  10. /usr/share/doc/percona-xtrabackup-24-2.4.7
  11. /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
  12. /usr/share/man/man1/innobackupex.1.gz
  13. /usr/share/man/man1/xbcrypt.1.gz
  14. /usr/share/man/man1/xbstream.1.gz
  15. /usr/share/man/man1/xtrabackup.1.gz

② 全库备份

1）创建测试表
  1. create database db02;
  2. use db02
  3. create table t1(id int,name varchar(10)) engine=myisam;
  4. insert into t1 values(1,'mona');
  5. create table t2(id int,name varchar(10)) engine=innodb;
  6. insert into t2 values(2,'tom');
  7. mysql> select * from db02.t1;
  8. +------+------+
  9. | id   | name |
  10. +------+------+
  11. |    1 | mona |
  12. +------+------+
  13. 1 row in set (0.00 sec)
  15. mysql> select * from db02.t2;
  16. +------+------+
  17. | id   | name |
  18. +------+------+
  19. |    2 | tom  |
  20. +------+------+
  21. 1 row in set (0.00 sec)

2）创建备份用户并授权

说明：管理员root用户拥有所有权限，可以省略

  1. 创建备份用户admin，并授予相应权限
  2. mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
  3. mysql> flush privileges;
  5. 说明：
  6. 在数据库中需要以下权限：
  7. RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
  8. REPLICATION CLIENT权限：为了获取binary log位置
  9. PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE

3）全库备份
  1. [root@db01 ~]# innobackupex --user=admin --password=123 /full_xtrabackup
  2. 说明：备份目录默认会自动创建，也可以手动创建
  4. [root@db01 ~]# ll /full_xtrabackup/2019-01-23_22-57-28/
  6. 了解相关文件：
  7. xtrabackup_checkpoints：
  8. 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。
  9. LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
  10. xtrabackup_binlog_info：
  11. mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
  12. xtrabackup_info：
  13. xtrabackup工具在备份时记录的使用工具及数据库信息
  14. backup-my.cnf：备份命令用到的配置选项信息
  15. xtrabackup_logfile：xtrabackup记录innodb事物日志的信息
③ 应用日志到备份集(预备)
  1. --apply-log    表示应用日志到备份集
  2. [root@db01 ~]# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2019-01-23_22-57-28/
  3. 说明：
  4. 在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。
④ 故障模拟
  1. 删除数据目录里的所有文件
  2. [root@db01 ~]# rm -rf /usr/local/mysql/data/*
  3. 停止mysql服务
  4. [root@db01 ~]# ps -ef|grep mysqld
⑤ 数据恢复
  1. --copy-back 表示从备份集拷贝文件到数据目录
  2. # innobackupex --copy-back /full_xtrabackup/2019-01-23_22-57-28/
  4. 说明：
  5. 1. innobackupex工具是物理拷贝文件，所以在恢复时不需要连接数据库，这样就不需要初始化数据库并启动服务。
  6. 2. 数据目录必须是为空的，innobackupex --copy-back不会覆盖已存在的文件，除非指定--force-non-empty-directories参数；
  7. 3. 还要注意，还原时需要先关闭服务
  9. 如遇以下错误：
  10. innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
  11. Error: datadir must be specified.
  13. 原因：innobackupex工具不知道数据目录在哪里
  14. 解决：指定配置文件位置--defaults-file=/path/my.cnf

说明：恢复完毕务必要更改数据文件权限！

  1. [root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/
  2. 启动数据库：
  3. [root@db01 ~]# service mysql start         
  4. Starting MySQL. SUCCESS!
⑥ 测试验证
  1. mysql> select * from db02.t1;
  2. +------+------+
  3. | id   | name |
  4. +------+------+
  5. |    1 | mona |
  6. +------+------+
  7. 1 row in set (0.00 sec)
  9. mysql> select * from db02.t2;
  10. +------+------+
  11. | id   | name |
  12. +------+------+
  13. |    2 | tom  |
  14. +------+------+
  15. 1 row in set (0.00 sec)

㈢ 小结

3、xtrabackup增量备份及恢复

㈠ 增备恢复原理

MySQL逻辑备份 - 图3

㈡ 实现思路

  1. 全量备份（增量基于全备）
  2. 应用日志到全量备份集中（预备）
  3. 更改数据
  4. 增量备份
  5. 应用增备的备份集到全量备份集中
  6. 模拟故障
  7. 数据恢复
  8. 测试验证

㈢ 实现步骤

① xtrabackup全量备份
  1. 清空环境
  2. [root@db01 ~]# rm -rf /full_xtrabackup/*
  3. [root@db01 ~]# innobackupex --user=admin --password=123 /full_xtrabackup

② 应用日志到全量备份集
  1. --apply-log    表示应用日志
  2. --redo-only    表示只应用已经提交的事务，不回滚未提交的事务
  3. 注意：如果已经回滚了未提交事务，那么就无法再应用增量备份。
  4. [root@db01 ~]# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/2019-01-23_23-59-58/
③ 修改数据
  1. mysql> insert into db02.t1 values(2,'harry'),(3,'jack');
  2. mysql> select * from db02.t1;
  3. +------+-------+
  4. | id   | name  |
  5. +------+-------+
  6. |    1 | mona  |
  7. |    2 | harry |
  8. |    3 | jack  |
  9. +------+-------+

④ xtrabackup增量备份
  1. 语法：
  2. innobackupex --incremental /incre_backup --incremental-basedir=BASEDIR
  3. 说明：
  4. 1.BASEDIR指的是完全备份所在的目录;/incre_backup目录中产生以时间命名的目录以存放所有的增量备份数据。
  5. 2.在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
  7. [root@db01 ~]# innobackupex --user=admin --password=123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/2019-01-23_23-59-58/

⑤ 应用增备备份集到全量备份集
  1. 语法：
  2. innobackupex --apply-log /全量备份集目录 --incremental-dir=/增量备份集
  4. [root@db01 ~]# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2019-01-23_23-59-58/ --incremental-dir=/incre_backup/2019-01-24_00-13-41/
  6. 说明：
  7. 1.--redo-only除了最后一个不用加之外，其他的增量应用都要加，最后一个应用的时候可以直接进入回滚未提交事务阶段;如果加了也没事，服务启动的时候会进入recovery过程，来回滚
  8. 2.应用增量备份的时候只能按照备份的顺序来应用。如果应用顺序错误，那么备份就不可用。如果无法确定顺序，可以使用xtrabackup-checkpoints来确定顺序。

⑥ 模拟故障
  1. 删除所有数据库文件并停止mysql进程
  2. [root@db01 ~]# rm -rf /usr/local/mysql/data/*
  3. [root@db01 ~]# pkill -9 mysqld

⑦ 数据恢复
  1. 注意：在准备步骤完成后，还原时只需要还原完全备份即可
  2. [root@db01 ~]# innobackupex --user=admin --password=123 --copy-back /full_xtrabackup/2019-01-23_23-59-58/
  3. 修改数据目录权限
  4. [root@db01 ~]# chown -R mysql. /usr/local/mysql/data/
  5. 启动数据库
  6. [root@db01 ~]# service mysql start
  7. Starting MySQL.Logging to '/usr/local/mysql/data/mysql3307.err'.
  8. . SUCCESS!

⑧ 测试验证
  1. mysql> select * from db02.t1;
  2. +------+-------+
  3. | id   | name  |
  4. +------+-------+
  5. |    1 | mona  |
  6. |    2 | harry |
  7. |    3 | jack  |
  8. +------+-------+
  9. 3 rows in set (0.01 sec)

四、MySQL备份经验分享

1、逻辑备份(mysqldump)

  1. 某同学使用mysqldump工具对数据库每天进行备份，有一天数据库有问题需要进行恢复，他发现数据不能恢复到最新状态，原因是因为他只是全库备份数据，没有备份二进制日志文件导致。
  3. **所以，大家一定要明白：**
  5. ① mysqldump工具只能恢复到备份那一时刻
  7. ② 通过二进制日志来恢复到最新数据

2、物理备份(xtrabackup)

  1. ① 如果数据库在第1次增量备份后发生故障，那么数据恢复时只能够使用xtrabackup全量备份加xtrabackup增量备份的时间点，即恢复到第1次增量备份结束的点
  3. ② 如果要将数据库恢复到最新状态，需要结合binlog日志恢复
  5. ③ 以上全量和增量的备份集是不能用的，需要将xtrabackup_log(事务日志，在备份过程中数据库的更改)应用到全量的备份集中才有效（应用日志必须按照顺序去应用：全备—>增备1—>增备2...）
  7. ④ 应用所有日志到全量备份集中时，需注意
  9. 1) 除了最后一次增量备份应用日志可以不加--redo-only外,其他都要加;只应用已经提交的事务，不回滚未提交的事务!!!
  11. 2) 应用日志到全量备份集中时一定要严格按照时间顺序执行，否则无效！
  13. 3) 在恢复数据时，确保数据目录为空；数据库服务是停止状态

课后实战

根据任务要求完成MySQL数据库的备份。

  1. 备份策略制定（了解业务|运营）|备份周期（1周）、备份时间点（避开业务高峰）
  2. 备份工具选择（mysqldump |xtrabackup）
  3. 脚本编写
  4. 计划任务执行
  1. 30 01 * * 0  bash full_backup.sh
  2. 30 01 * * 1     bash incremental1_backup.sh
  3. 30 01 * * 2  bash incremental2_backup.sh
  4. 30 01 * * 3  bash full_backup.sh
  5. 30 01 * * 4  bash incremental1_backup.sh
  6. 30 01 * * 5  bash full_backup.sh
  7. 30 01 * * 6  bash incremental1_backup.sh
  9. 或者
  10. 30 01 * * 0,3,5  bash full_backup.sh
  11. 30 01 * * 1,4,6  bash incremental1_backup.sh
  12. 30 01 * * 2  bash incremental2_backup.sh