MySQL数据库备份实例详解

共计 22624 个字符，预计需要花费 57 分钟才能阅读完成。

对于任何数据库来说，备份都是非常重要的

数据库复制不能取代备份的作用

比如我们由于误操作，在主数据库上删除了一些数据，由于主从复制的时间很短，在发现时，从数据库上的数据可能也已经被删除了，
我们不能使用从数据库上的数据来恢复主数据库上的数据，只能通过备份进行误删除数据的恢复

一. 备份的分类

1. 按备份的结果来分：

逻辑备份

其备份结果为 SQL 语句，适合于所有存储引擎，恢复时需要较多时间，逻辑备份时，对于 MyISAM 存储引擎是需要进行锁表操作的，通过使用的 MySQLdump 就是一种逻辑备份工具

物理备份

是对数据库目录的拷贝，其备份结果的大小通常也与备份数据的数据目录大小相同，物理备份和恢复通常比逻辑备份要快，因为只需要对 mysql 数据目录拷贝即可，也正是因为这点，对于内存表只能备份其结构，无法备份数据(因为其数据存储在内存中，没有实际的物理数据文件)

物理备份的方式

进行物理备份，我们可以采用离线备份和在线备份的方式进行备份

 离线备份：需要对数据库进行停机，或对整个数据库进行锁定的情况下进行
 在线备份：需要使用第三方工具，如  XtraBackup

2. 按备份的数据库的内容来分

2.1 全量备份

是对整个数据库的一个完整备份

2.2 增量备份

是在上次全量或增量备份的基础上，对更改过的数据进行的备份

注意

Mysql 官方提供的 mysqldump 命令并不支持增量备份

通常情况要使用 mysqldump 来进行增量备份的话，增量备份只能通过备份 Mysql 的二进制日志来实现

XtraBackup 本身就提供了增量备份的功能，所以对于 Innodb 来说，使用 XtraBackup 进行备份更加安全高效

无论是使用 XtraBackup 还是 Mysqldump 进行备份，要进行基于时间点的恢复时都需要利用 Mysql 的二进制日志，所以通常情况下我们需要对 Mysql 的二进制日志也进行备份

二. 使用 mysqldump 进行备份

mysqldump 是 mysql 官方提供的逻辑备份工具，其备份结果是可读的 SQL 文件

mysqldump 支持多种语法

1. 常用语法

对一个数据库下的一个或多个表进行备份

mysqldump [OPTIONS] database [tables]

备份多个表时，table 直接用空格进行分隔

对指定的多个数据库进行备份

mysqldump [OPTIONS] --database [OPTIONS] DB1 [DB2..]

对整个 mysql 实例下的所有数据库进行备份

mysqldump [OPTIONS] --all-database [OPTIONS]

2. 常用参数

-u，--user = name    #  指定备份时所使用的数据库账号
-p. --password [=name]    # 指定账号的密码
--single-transaction  # 使用此参数会在备份前先执行 start transaction 命令启动一个事务，以此来获得数据库备份时的数据的一致性，由于是通过事务保证数据的一致性，所以此参数只对 Innodb 存储引擎有效；当使用此参数进行备份时，要确保没有任何 DDL 语句在执行，因为 Innodb 的数据一致性的隔离级别并不能隔离 DDL 操作

-l, --lock-tables  # 如果没有使用非事务存储引擎，使用此参数保证备份时数据的一致性，在备份时会依次锁住每个数据库下的所有表，一般用于 MyISAM 存储引擎的备份，使用了此参数，在数据库备份时，只能进行读操作，由于此参数是锁住一个数据库下的所有表，备份时可以保证一个数据库下的所有表的数据一致性，但不能保证整个 Mysql 实例下的所有数据库的所有表的数据一致性，这也是为什么推荐使用 Innodb 引擎的一个原因
 
lock-tables 与 single-transaction 参数是互斥的，不能同时使用，所以数据库中如果混合使用了 Innodb 表和 MyISAM 表就只能使用 lock-tables 来进行备份了

-x, --lock-all-tables  # 此参数可以对整个 Mysql 实例下的所有数据库进行加锁，可以避免 lock-tables 不能保证整个 Mysql 实例下的所有数据库的所有表的数据一致性的问题，备份时同样会将数据库变为只读的状态

--master-data = [1/2]    # 无论是时间恢复还是新建 slave 实例都要用到这个参数，此参数有两个可选值，当值为 1 时，备份中只记录 change_master 语句，当值为 2 时，change_master 语句会以注释的形式出现在备份文件中；默认值为 1，且当使用了此参数时会忽略 lock-tables 参数，在备份时如果使用了此参数，但是没有使用 single-transaction 参数，则会自动使用 lock-all-tables 参数

如果我们要备份的数据库中包含了存储过程，触发器，数据库调度事件时，要备份这些数据库对象时，必须指定以下参数才能对相应数据库进行备份

-R, --routines     # 指定要备份的数据库中存在的的存储过程
--triggers     # 指定要备份的数据库中存在的的触发器
-E,--events      # 指定要备份的数据库中存在的的调度事件

除了以上参数，还有一些参数在备份时也会用到

--hex-blob    # 因为 mysqldump 备份导出的是文本文件，如果导出的数据中含有以上类型，在文本格式下，有些字符是不可见的，如果使用了此参数使，将会对数据库中所存在 binary、varbinary、blob 类型的数据以 16 进制的形式保存，就不会出现有些字符不可见的情况了
--tab =path    # 使用了此参数会在指定的路径下对数据库的每个表生成两个文件，一个文件用于存储表结构，另一个用于存储表中的数据
-w, --where = '过滤条件'    # 导出指定条件的数据（只支持单表数据条件导出）备份账号所需要的权限：SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS
如果使用 --tab 参数则还需要：FILE 权限

3. 演示

首先创建备份用户

create user 'backup'@'localhost' identified by '123456';

赋予用户备份权限

grant select,reload,lock tables,replication client,show view,event,process on *.*  to 'backup'@'localhost';

使用 mysqldump 进行全量备份

备份某个数据库

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > mc_orderdb.sql

如果遇到以下问题

mysqldump: Error: Binlogging on server not active

MySQL 中二进制日志功能默认是关闭，去 /etc/my.cnf 文件中加入下面配置，开启 log_bin(数据库的操作日志)功能，然后重启 mysql 即可解决问题

log_bin=mysql-bin

之后使用“systemctl start mysql”重启服务器，报错

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

解决方法：

在设置 log-bin 的时候同时需要设置 server-id 变量，即在配置文件中添加：

[mysqld]  
log-bin=mysql  
server-id=1  

然后再次重启即可

补充知识

装 mysql, 运行一段时间后，在 mysql 目录下出现一堆类似 mysql-bin.000***，从 mysql-bin.000001 开始一直排列下来，而且占用了大量硬盘空间，高达几十个 G. 对于这些超大空间占用量的文件我们应该怎么办呢?

那么 mysql 数据库文件夹中的 mysql-bin.00001 是什么文件？mysql-bin.000001、mysql-bin.000002 等文件是数据库的操作日志，例如 UPDATE 一个表，或者 DELETE 一些数据，即使该语句没有匹配的数据，这个命令也会存储到日志文件中，还包括每个语句执行的时间，也会记录进去的。这些形如 mysql-bin.00001 的文件主要是用来做什么的呢?
1：数据恢复
如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。2：主从服务器之间同步数据 
主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。如果不想要这些文件应该怎么做呢?
1：只有一个 mysql 服务器，那么可以简单的注释掉这个选项就行了。vi /etc/my.cnf 把里面的 log-bin 这一行注释掉，重启 mysql 服务即可。2：如果你的环境是主从服务器，那么就需要做以下操作了。A：在每个从属服务器上，使用 SHOW SLAVE STATUS 来检查它正在读取哪个日志。B：使用 SHOW MASTER LOGS 获得主服务器上的一系列日志。C：在所有的从属服务器中判定最早的日志，这个是目标日志，如果所有的从属服务器是更新的，就是清单上的最后一个日志。D：清理所有的日志，但是不包括目标日志，因为从服务器还要跟它同步。简单地说, 这些 MySQL 目录下的形如 mysql-bin.000*** 的文件时 MySQL 的事务日志。删除复制服务器已经拿走的 binlog 是安全的，一般来说网络状况好的时候，保留最新的那一个足以。

再次执行之前的备份命令，即可成功被封 mc_orderdb 数据库下的所有表，我们可以查询一下备份的 SQL 文件中是否包含所有表

[root@localhost db_backup]# grep "CREATE TABLE" mc_orderdb.sql
CREATE TABLE `order_cart` (
CREATE TABLE `order_customer_addr` (
CREATE TABLE `order_detail` (
CREATE TABLE `order_master` (
CREATE TABLE `region_info` (
CREATE TABLE `shipping_info` (
CREATE TABLE `warehouse_info` (
CREATE TABLE `warehouse_proudct` ([root@localhost db_backup]# 

通过上面结果可以看出我们的几个表都在其中

备份某个数据库下的某个表

[root@localhost db_backup]#  mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb order_master > order_master.sql
Enter password: 
[root@localhost db_backup]# ls
mc_orderdb.sql  order_master.sql

备份 MySQL 实例下的所有数据库

[root@localhost db_backup]#  mysqldump -ubackup -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > mc.sql
Enter password: 
[root@localhost db_backup]# ls
mc_orderdb.sql  mc.sql  order_master.sql

由于 master-data 的值设置为 1，change master 命令并没有被注释掉，如果我们使用这个命令进行恢复，change master 命令就会被执行，在一些情况下可能会造成一些错误，所以建议使用时最好还是设置为 2

可以通过下面的命令查看，备份文件中包含哪些数据库

[root@localhost db_backup]# grep "Current Database" mc.sql
-- Current Database: `mc_orderdb`
-- Current Database: `mc_productdb`
-- Current Database: `mc_userdb`
-- Current Database: `mysql`
[root@localhost db_backup]# 

使用 -tab 参数指定备份文件的位置

首先在 /tmp 目录下建立一个 mc_orderdb 目录用来存放指定的备份文件，之所以使用在此目录下建立目录，是因为使用 –tab 参数时，用户必须对目标目录有可写权限，而 tmp 目录对任何用户都有可写权限

[root@localhost db_backup]#  mkdir -p /tmp/mc_orderdb

现在我们可以使用 –tab 参数指定备份路径

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'

可以发现，报错了，其实我们在这之前还缺少一步，由于用户需要有写文件的权限，所以我们还需要对备份用户赋予 file 权限

mysql> grant file on *.*  to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)

我们再次执行上面的备份命令

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=347;
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

可以很清楚地从提示看到是因为 mysql 服务启用了–secure-file-priv，所以才无法执行。
那么–secure-file-priv 又是什么呢，应该如何解决才能是它可以备份呢？
–secure-file-priv=name：
Limit LOAD DATA, SELECT … OUTFILE, and LOAD_FILE() to files within specified directory

可以看到 secure-file-priv 参数是用来限制 LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

当 secure_file_priv 的值为 null，表示限制 mysqld 不允许导入 | 导出

当 secure_file_priv 的值为 /tmp/，表示限制 mysqld 的导入 | 导出只能发生在 /tmp/ 目录下

当 secure_file_priv 的值没有具体值时，表示不对 mysqld 的导入 | 导出做限制

查看数据库当前该参数的值

mysql> show global variables like '%secure%'; 
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

清楚地看到 secure_file_priv 的值是 NULL，说明此时限制导入导出的
所以应该改变该参数
可是查看了 mysql.cnf 中居然没有对这个参数进行设定，就说明这个参数默认便是 null
所以再 mysql.cnf 中的 [mysqld] 加入 secure_file_priv =

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/mysql/data    # mysql 数据存放的目录
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log_bin=mysql-bin
server-id=1  
secure_file_priv =

再重启 mysql 服务

[root@localhost tmp]# systemctl restart mysqld

然后再查一下此时参数的值

mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.01 sec)

已经是我们要的结果，现在我们再次执行备份命令

[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
mysqldump: Got error: 1: Can't create/write to file'/tmp/mc_orderdb/order_cart.txt'(Errcode: 13 - Permission denied) when executing'SELECT INTO OUTFILE'

结果还是权限被拒绝，无法写入，我们可以查询一下目录 mc_orderdb 的权限，

[root@localhost tmp]# ls -lh mc_orderdb/
total 4.0K
-rw-r--r-- 1 root root 1.9K Jan 10 10:51 order_cart.sql

可以发现，是 root 用户建立的目录，我们需要修改其所属用户为 mysql 用户，然后再次执行备份命令

[root@localhost tmp]#  chown mysql:mysql  mc_orderdb
[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb 
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

--
-- Dumping events for database 'mc_orderdb'
--

--
-- Dumping routines for database 'mc_orderdb'
--
[root@localhost tmp]# 

可以发现，修改成功后即可备份成功

进入该目录下会发现 mc_orderdb 数据库下的每个表都有两种文件，一种.sql 结尾记录是表结构，一种是.txt 结尾的表数据

[root@localhost tmp]# cd mc_orderdb/
[root@localhost mc_orderdb]# ls
order_cart.sql  order_customer_addr.sql  order_detail.sql  order_master.sql  region_info.sql  shipping_info.sql  warehouse_info.sql  warehouse_proudct.sql
order_cart.txt  order_customer_addr.txt  order_detail.txt  order_master.txt  region_info.txt  shipping_info.txt  warehouse_info.txt  warehouse_proudct.txt

mysqldump 如何使用全备 where 参数

使用场景

假设我们要对订单 id 为 1000 到 1050 的主表进行修改，修改之前，我们需要先对数据进行备份，这里我们就可以使用 where 参数来完成此需求

执行命令进行备份

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction  --where "order_id>1000 and order_id<1050" mc_orderdb  order_master > order_master_1000_1050.sql
Enter password: 

查看备份文件可以发现，订单 id 是从 1001 开始的

在日程工作中我们不可能一直手工备份，所以我们需要将备份进行脚本话，然后使用计划任务去执行脚本

定义备份脚本

脚本文件

#!/bin/bash
###############Basic parameters##########################
DAY=`date +%Y%m%d`    # 记录发生备份的当前日期
Environment=$(/sbin/ifconfig | grep "inet" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' )    # 当前主机的 IP
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/mysql/"
DATADIR="/home/db_backup/${DAY}"    # 备份存放的目录（/home/db_backup 目录下的以日期命名的子目录中）MYSQL=`/usr/bin/which mysql`    # 定义 mysql 命令的目录
MYSQLDUMP=`/usr/bin/which mysqldump`    # 定义 mysqldump 命令的目录
mkdir -p ${DATADIR}    # 创建存储目录

# 定义备份函数，使用到上面定义的变量

Dump(){${MYSQLDUMP} --master-data=2 --single-transaction  --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database}  > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql    # 对文件进行了压缩
}

# 利用 for 循环对当前服务器下的每一个数据库（排除了一些系统视图所在的数据库）分别来调用上面的 Dump 函数来进行备份

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
   database=${db}
   Dump
done

执行脚本文件

[root@localhost home]# bash backup.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost home]# cd db_backup/
[root@localhost db_backup]# ls
20190110  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 20190110/
[root@localhost 20190110]# ls
172.17.0.1-mc_orderdb.sql.gz  172.17.0.1-mc_productdb.sql.gz  172.17.0.1-mc_userdb.sql.gz  172.17.0.1-mysql.sql.gz

可以看到结果已备份，可以使用 crontab 命令定时执行此脚本

如何恢复 mysqldump 备份的数据库

方法一：

mysql -u -p dbname < backup.sql

方法二：

mysql>  source /tmp/backup.sql

使用 mysqldump 备份时，恢复的速度完全取决于 MySQL 实例执行 SQL 的速度和服务器的 IO 性能，并且恢复过程是单线程的，所以对于非常大的数据集来说，要恢复的话可能需要很长的时间

演示：

把刚才的全备数据恢复到 bak 数据库中

创建 bak_orderdb 数据库

[root@localhost db_backup]# mysql -uroot -p -e"create database bak_orderdb"
Enter password: 

将 mc_orderdb 备份的数据恢复到 bak 数据库中

[root@localhost db_backup]#  mysql -uroot -p bak_orderdb < mc_orderdb.sql
Enter password: 
[root@localhost db_backup]# 

检验恢复结果的正确性

mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM bak_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

模拟误操作并恢复数据

假设我们现在不小心删除了 mc_orderdb 下的 order_master 中的 10 条数据，我们现在需要通过刚刚恢复的备份数据库把这 10 条数据恢复回来

mysql> DELETE FROM mc_orderdb.order_master LIMIT 10;
Query OK, 10 rows affected (0.01 sec)

mysql>  SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

首先查出被误删的数据
SELECT a.* FROM bak_orderdb.order_master a LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id WHERE b.order_id IS NULL;
然后执行 insert 语句将查出的数据插入回去

完整语句为

INSERT INTO mc_orderdb.order_master(
order_id,order_sn,customer_id,shipping_user,province,city,district,address,
payment_method,order_money,district_money,shipping_money,payment_money,
shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
order_status,order_point,invoice_title,modified_time)
SELECT a.* FROM bak_orderdb.order_master a 
LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
WHERE b.order_id IS NULL;

执行结果：

mysql> INSERT INTO mc_orderdb.order_master(
    -> order_id,order_sn,customer_id,shipping_user,province,city,district,address,
    -> payment_method,order_money,district_money,shipping_money,payment_money,
    -> shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,
    -> order_status,order_point,invoice_title,modified_time)
    -> SELECT a.* FROM bak_orderdb.order_master a 
    -> LEFT JOIN mc_orderdb.order_master b ON a.order_id=b.order_id
    -> WHERE b.order_id IS NULL;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM mc_orderdb.order_master;
+----------+
| COUNT(*) |
+----------+
|    10010 |
+----------+
1 row in set (0.00 sec)

对于一些静态型数据我们可以这样进行，但是对于一些时刻有数据在往表里写的数据还原，就不能使用这种方式了

对于生产环境中，时刻有数据写入的表如何进行数据恢复呢？

mysqldump 单表备份恢复（使用了 –tab 参数备份的结果集）

需要进入 mysql 客户端中

先恢复表结构

mysql>  source /tmp/mc_orderdb/region_info.sql;

再导入数据

mysql>     load data infile '/tmp/mc_orderdb/region_info.txt' info table region_info;

如何进行指定时间点的恢复

进行某一时间点的数据恢复，恢复到误操作的时间

先决条件：

具有指定时间点前的 mysqldump 的全备
具有全备到指定时间点的 mysql 二进制日志

演示

首先我们需要有个数据库的全备，此处我们对 mc_orderdb 数据库进行全备

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > mc_orderdb.sql

然后我们模拟一下生产环境中对数据库的操作，这样才能看到时间点恢复的效果

我们到 mc_orderdb 数据库中新建一个统计表 t, uid 列是用户 id,cnt 是用户的总消费金额，将统计结果插入表 t 中后，模拟误操作，删除表 t 中的 100 行数据

mysql> use mc_orderdb
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> create table t(id int auto_increment not null,uid int,cnt decimal(8,2),primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t(uid,cnt) select customer_id,sum(order_money) from order_master group by customer_id;
Query OK, 6314 rows affected (0.05 sec)
Records: 6314  Duplicates: 0  Warnings: 0

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|     6314 |
+----------+
1 row in set (0.00 sec)

mysql> delete from t limit 100;
Query OK, 100 rows affected (0.01 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|     6214 |
+----------+
1 row in set (0.01 sec)

先恢复一个最近的全备，进行全量数据恢复

[root@localhost db_backup]# mysql -uroot -p mc_orderdb < mc_orderdb.sql

然后查看 mc_orderdb.sql 文件中 change master 命令中

在进行这个全备时 Mysql 二进制日志的文件名（MASTER_LOG_FILE），以���时间点（MASTER_LOG_POS）

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

此处文件名为 mysql-bin.000001，日志时间点为 154;

下面我们要恢复这个时间点（154）之后到第一次删除数据之前的数据

要找到这个时间点（154）之后到第一次删除数据之前的数据

查看二进制日志，进行分析

[root@localhost db_backup]# cd /home/mysql
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=mc_orderdb mysql-bin.000001| grep -B3 DELETE | more
#190110 13:44:54 server id 1  end_log_pos 83285 CRC32 0xf679d195    Table_map: `mc_orderdb`.`t` mapped to number 119
# at 83285
#190110 13:44:54 server id 1  end_log_pos 84620 CRC32 0xa3408e6c    Delete_rows: table id 119 flags: STMT_END_F
### DELETE FROM `mc_orderdb`.`t`
--
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1042.34 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */
### DELETE FROM `mc_orderdb`.`t`
--
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=803.37 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */

... 省略

从中可以看到刚刚的二进制日志中的第一个 DELETE，在它之前的日志结束点为 84620

所以我们需要恢复的是 154 到 84620 之间，且数据库为 mc_orderdb，日志所在文件名 为 mysql-bin.000001 的数据

我们通过 mysqlbinlog 将这些数据导出来

[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=84620 --database=mc_orderdb mysql-bin.000001 > mc_order_diff.sql

将数据导入恢复

[root@localhost mysql]# mysql -uroot -p mc_orderdb < mc_order_diff.sql

实时二进制日志备份

Mysql5.6 版本之后，可以实时备份 Binlog(二进制日志)

要使用这个功能，我们需要进行以下配置
首先新建一个用户，这个用户要有 replication slave 权限

mysql>  grant replication slave on *.* to 'repl'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

建立一个存储备份的二进制日志文件的目录

mkdir -p binlog_backup

最后在这个目录下，执行以下命令，就可以实时的进行二进制日志的备份了

[root@localhost binlog_bak]# mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u repl -p123456 mysql-bin.000001
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

raw：代表这个命令是输出的是 raw 格式的二进制日志
read-from-remote-server：从 mysql 服务器上读取这个日志
stop-never：备份的这个进程会持续在后台运行
最后的是指定要备份的二进制文件的名称

此命令执行后这个终端会一直执行这个命令
现在打开另外一个终端，进入备份的目录进行查看

[root@localhost mysql]# cd /home/binlog_bak/
[root@localhost binlog_bak]# ls
mysql-bin.000001

可以看到日志已经备份成功
进入 mysql 命令行，刷新日志

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |   3560467 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

现在有两个日志，我们再回到备份目录进行查看

[root@localhost binlog_bak]# ls
mysql-bin.000001  mysql-bin.000002

可以看到最新的日志也已实时备份

三. xtrabackup

1. xtrabackup 介绍

xtrabackup 物理备份工具，用于在线备份 innodb 存储引擎的表

在所有表全是 innodb 存储引擎表的情况下：
xtrabackup 可以保证在备份过程中，不影响表的读写操作
在最初的时候，xtrabackup 本身只支持对 innodb 存储引擎表的备份，且只会备份数据文件，不会备份表的结构
innobackupex 是对 xtrabackup 的插件，提供了备份表结构及其他配置信息的功能，并支持 MyISAM 表的备份，但也会锁表

因为在当前的 mysql 版本下，还有一些系统表使用的是 MyISAM 存储引擎，所以一般情况下使用的是 innobackupex 脚本进行备份的

2. 安装 xtrabackup

3. 利用 innobackupex 进行全备

innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/

[root@localhost home]# innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/
xtrabackup: recognized server arguments: --datadir=/home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 
xtrabackup: recognized client arguments: --datadir=home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2 
190110 15:15:30 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

... 省略

190110 15:15:37 [00] Writing /home/db_backup/2019-01-10_15-15-30/xtrabackup_info
190110 15:15:37 [00]        ...done
xtrabackup: Transaction log of lsn (79088947) to (79088956) was copied.
190110 15:15:37 completed OK!

当看到 completed OK! 代表备份已完成

和 mysqldump 单线程备份不同，我们可以通过 parallel 参数指定备份的线程数
/home/db_backup/ 是我们指定的备份文件的存储目录

xtrabackup 会已当前时间在目标目录中生成一个子目录用来存放当前的备份文件

我们进入目录中查看一下

[root@localhost db_backup]# ls
20190110  2019-01-10_15-15-30  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 2019-01-10_15-15-30/
[root@localhost 2019-01-10_15-15-30]# ls
backup-my.cnf  ib_buffer_pool  mc_orderdb    mc_userdb  performance_schema  xtrabackup_binlog_info  xtrabackup_info
bak_orderdb    ibdata1         mc_productdb  mysql      sys                 xtrabackup_checkpoints  xtrabackup_logfile

与原数据文件目录相比，少了 ib_logfile0 等日志文件

xtrabackup_binlog_info 比较重要，其中记录了备份的日志名和日志点，相当于 mysqldump 中设置 master-data 参数的作用

如果我们不想以时间戳的形式自动生成子目录，我们可以通过指定 –no-timestamp 参数来实现

innobackupex --user=backup --password=123456 --parallel=2  /home/db_backup/2019-01-10 --no-timestamp

[root@localhost db_backup]#  innobackupex --user=backup --password=123456 --parallel=2  /home/db_backup/2019-01-10 --no-timestamp
[root@localhost db_backup]# ls
20190110  2019-01-10  2019-01-10_15-15-30  mc_orderdb.sql  mc.sql  order_master_1000_1050.sql  order_master.sql
[root@localhost db_backup]# cd 2019-01-10
[root@localhost 2019-01-10]# ls
backup-my.cnf  ib_buffer_pool  mc_orderdb    mc_userdb  performance_schema  xtrabackup_binlog_info  xtrabackup_info
bak_orderdb    ibdata1         mc_productdb  mysql      sys                 xtrabackup_checkpoints  xtrabackup_logfile
[root@localhost 2019-01-10]# 

4. 利用 innobackupex 进行全备的恢复

innobackupex  --apply-log /path/to/BACKUP-DIR
mv /path/to/BACKUP-DIR /home/mysql/data

利用 xtrabackup 备份产生的备份集并不能直接用于数据库的恢复
进行数据库恢复之前，我们必须把备份中产生的备份集 进行应用，此处我们使用的是备份文件的目录伪 2019-01-10

[root@localhost db_backup]# innobackupex --apply-log /home/db_backup/2019-01-10

[root@localhost db_backup]# cd 2019-01-10
[root@localhost 2019-01-10]# ls
backup-my.cnf   ib_logfile0  mc_productdb        sys                           xtrabackup_info
bak_orderdb     ib_logfile1  mc_userdb           xtrabackup_binlog_info        xtrabackup_logfile
ib_buffer_pool  ibtmp1       mysql               xtrabackup_binlog_pos_innodb  xtrabackup_master_key_id
ibdata1         mc_orderdb   performance_schema  xtrabackup_checkpoints

可以发现，使用了上面的命令后，备份集中多出了之前缺少的 ib_logfile0 等文件

使用 xtrabackup 进行数据库恢复时，我们必须对数据库实例进行重启
先停用 mysql 服务

然后将原数据库文件所在的文件夹重命名为 data_bak, 然后将此时的 2019-01-10 文件夹移动到 data_bak 所在的文件夹，并重命名为 data，即覆盖了原来的文件

然后 chown -R mysql:mysql data
对文件夹修改所属用户

最后重启 mysql 服务即可

5. 利用 innobackupex 进行增量备份

innobackupex --user=backup --password=123456 /home/db_backup    # 先进行全备
  
innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10/

incremental：表示我们要进行的是一个全备，指定全备的目录
incremental-basedir：指定增量备份所依赖的数据基础的备份目录，这个增量备份所依赖的上一个全备

演示：

先进行全备

innobackupex --user=backup --password=123456 /home/db_backup  

在 /home/db_backup 目录下生产了一个新的全备子目录 2019-01-10_16-19-37

再依赖上个全量备份进行增量备份

innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10_16-19-37/

此时在 /home/db_backup 目录下生产了一个增量备分的子目录 2019-01-10_16-22-09
增量备份会把自上一个全备后的数据变更记录下来

然后再进行一次增量备份，此时的命令和前面基本相同，只是所基于的增量备份的数据目录要变成上次增量备份生成的目录

innobackupex --user=backup --password=123456 --incremental  /home/db_backup/  --incremental-basedir=/home/db_backup/2019-01-10_16-22-09/

此时，第二次增量备份的生成的目录名为 2019-01-10_16-24-09

6. 利用 innobackupex 进行增量恢复

innobackupex  --apply-log  --redo-only 全备目录

我们要循环的在多次增量备份中应用上面步骤

 innobackupex  --apply-log  --redo-only 全备目录  --incremental-dir= 第一次增量目录

所有增量备份的都应有了上面的命令后，就可以像全备一样，在全备目录上再进行崩溃恢复的过程

    innobackupex  --apply-log /path/to/BACKUP-DIR
    mv /path/to/BACKUP-DIR /home/mysql/data

最后和全备一样，要用全备目录替换 mysql 数据库目录

演示

下面演示只恢复到第一次备份

[root@localhost db_backup]#   innobackupex  --apply-log  --redo-only    /home/db_backup/2019-01-10_16-19-37

[root@localhost db_backup]#   innobackupex  --apply-log  --redo-only    /home/db_backup/2019-01-10_16-19-37  --incremental-basedir=/home/db_backup/2019-01-10_16-22-09

[root@localhost db_backup]#   innobackupex  --apply-log   /home/db_backup/2019-01-10_16-19-37 

[root@localhost db_backup]#  mv 2019-01-10_16-19-37  /home/mysql

[root@localhost db_backup]#  cd  /home/mysql

[root@localhost mysql]#  systemctl stop  mysqld

[root@localhost mysql]#  mv 2019-01-10_16-19-37 data

[root@localhost mysql]# chown -R mysql:mysql data

[root@localhost mysql]#  systemctl start mysqld

四. 要制定备份计划

每天凌晨对数据库进行一次全备
实时对二进制日志进行远程备份

：