【MySQL】MySQL备份和恢复

一、为什么要备份数据

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.

• 硬件故障

• 软件故障

• 自然灾害

• 黑客攻击

• 误操作 (占比最大)

所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略

• 能够容忍丢失多少数据

• 恢复数据需要多长时间

• 需要恢复哪一些数据

 

二、数据的备份类型

数据的备份类型根据其自身的特性主要分为以下几组

• 完全备份

• 部分备份

  完全备份指的是备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)

而部分备份又分为以下两种

• 增量备份

• 差异备份

  增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦 
  差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单

示意图如下：

 

三、MySQL备份数据的方式

在MySQl中我们备份数据一般有几种方式

• 热备份

• 温备份

• 冷备份

  热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响 
  温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作 
  冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

MySQL中进行不同方式的备份还要考虑存储引擎是否支持

• MyISAM 

   热备 ×

   温备 √

   冷备 √

• InnoDB

   热备 √

   温备 √

   冷备 √

  我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式

  物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果 
  逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

• 物理备份

• 逻辑备份

 

四、备份需要考虑的问题

定制备份策略前, 我们还需要考虑一些问题

我们要备份什么?

一般情况下, 我们需要备份的数据分为以下几种

• 数据

• 二进制日志, InnoDB事务日志

• 代码(存储过程、存储函数、触发器、事件调度器)

• 服务器配置文件

备份工具

这里我们列举出常用的几种备份工具 
mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备 
cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份 
lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份 
mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎 
xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

 

五、设计合适的备份策略

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种

• 直接cp,tar复制数据库文件

• mysqldump+复制BIN LOGS

• lvm2快照+复制BIN LOGS

• xtrabackup

以上的几种解决方案分别针对于不同的场景

1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件

2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果

3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果

4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

 

六、实战演练

1.使用cp进行备份

 #查看数据库信息

mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monkey             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use monkey
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_monkey |
+------------------+
| locations |
| pseudohash |
| t1 |
| t2 |
| unsent_emails |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from t1;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | Jelly | 28 |
| 2 | Jerry | 30 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql>

#向数据库施加读锁

mysql> FLUSH TABLES WITH READ LOCK;    #向所有表施加读锁
Query OK, 0 rows affected (0.00 sec)

#备份数据文件

[root@A04-Test-172 ~]# mkdir /root/backup                         #创建文件夹存放备份数据库文件
[root@A04-Test-172 ~]# cp -a /usr/local/mysql/* /root/backup/     #保留权限的拷贝源数据文件
[root@A04-Test-172 ~]# ls /root/backup                            #查看目录下的文件
total 122916
-rw-r----- 1 mysql mysql 56 Jan 5 17:03 auto.cnf
-rw-r----- 1 mysql mysql 373 Jan 24 14:15 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 5 11:08 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 5 11:08 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 5 17:03 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 5 11:08 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Feb 5 11:08 monkey
drwxr-x--- 2 mysql mysql 4096 Jan 5 17:03 mysql
-rw-r----- 1 mysql mysql 6 Jan 24 14:15 mysqld.pid
drwxr-x--- 2 mysql mysql 4096 Jan 5 17:03 performance_schema
drwxr-x--- 2 mysql mysql 12288 Jan 5 17:03 sys

#模拟数据丢失并恢复

[root@A04-Test-172 ~]# rm -rf /usr/local/mysql/data/*    #删除数据库的所有文件
[root@A04-Test-172 ~]# service mysqld restart            #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库
mysql> show databases;                                   #发现数据丢失了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
[root@A04-Test-172 backup]# cp -a /root/mysqldumpbackup/backup.sql  /usr/local/mysql/data/    #将备份数据拷贝回去
[root@A04-Test-172 ~]# service mysqld restart            #重启MySQL，
mysql> show databases;                         #查看数据是否恢复
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

mysql> use monkey
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_monkey |
+------------------+
| locations |
| pseudohash |
| t1 |
| t2 |
| unsent_emails |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from t1;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | Jelly | 28 |
| 2 | Jerry | 30 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql>

 

2.使用mysqldump+复制BINARY LOG备份

我们通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复。

二进制日志需要在mysql配置文件中添加 log_bin=on 开启。

[root@A04-Test-172 backup]# more /etc/my.cnf
[client]  
port = 3306  
socket = /tmp/mysql.sock  
  
[mysqld]  
user=mysql  
port = 3306  
server_id = 1  
socket=/tmp/mysql.sock  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data  
pid-file=/usr/local/mysql/data/mysqld.pid  
log-error=/usr/local/mysql/log/mysql-error.log
log-bin=on                  #开启二进制日志

mysqldump命令介绍

mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备。

#基本语法格式

shell> mysqldump [options] db_name [tbl_name ...]    恢复需要手动CRATE DATABASES
shell> mysqldump [options] --databases db_name ...   恢复不需要手动创建数据库
shell> mysqldump [options] --all-databases           恢复不需要手动创建数据库


其他选项:
     -E, --events: 备份事件调度器
     -R, --routines: 备份存储过程和存储函数
     --triggers: 备份表的触发器; --skip-triggers 
     --master-date[=value]  
         1: 记录为CHANGE MASTER TO 语句、语句不被注释
         2: 记录为注释的CHANGE MASTER TO语句
         基于二进制还原只能全库还原

     --flush-logs: 日志滚动
         锁定表完成后执行日志滚动

查看数据库信息

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use monkey;
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_monkey |
+------------------+
| locations        |
| pseudohash       |
| t1               |
| t2               |
| unsent_emails    |
+------------------+
5 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

使用mysqldump备份数据库

[root@A04-Test-172 data]# mysql -uroot -p -e 'SHOW MASTER STATUS'               #查看二进制文件的状态，并记录Position中的数字
Enter password: 
+-----------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| on.000001 |      154 |              |                  |                   |
+-----------+----------+--------------+------------------+-------------------+

 

[root@A04-Test-172 mysqldumpbackup]# mysqldump -uroot -p --all-databases --lock-all-tables  > backup.sql  #备份数据库到backup.sql文件中
[root@A04-Test-172 mysqldumpbackup]# ll
total 768
-rw-r--r-- 1 root root 785316 Mar 13 14:13 backup.sql

 

[root@A04-Test-172 mysqldumpbackup]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE TEST1;                 #创建一个数据库
Query OK, 1 row affected (0.02 sec)

mysql> SHOW MASTER STATUS;                    #记录下现在的Position
+-----------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| on.000001 |      456 |              |                  |                   |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

 

[root@A04-Test-172 mysql-bin]# cp -a /usr/local/mysql/log/mysql-bin.000001  /root/mysqldumpbackup/mysql-bin  #复制二进制日志文件到备份目录下

[root@A04-Test-172]# service mysql.server stop                            #停止MySQL
[root@A04-Test-172 data]# rm -rf /usr/local/mysql/data/*                  #删除所有数据文件

#启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库

#再次清除/usr/local/mysql/data目录下的文件，执行数据库初始化，初始化的密码在日志文件中（/usr/local/mysql/log/mysql-error.log）
[root@A04-Test-172 mysql]# bin/mysqld --initialize --user=mysql                     #初始化MySQL

#启动mysql
[root@A04-Test-172 tmp]# service mysql.server start

#登录MySQL
[root@A04-Test-172 tmp]# mysql -uroot -p

#重置MySQL密码

  mysql> set password for root@localhost = password('mysql');
  Query OK, 0 rows affected, 1 warning (0.02 sec)

  mysql> flush privileges;
  Query OK, 0 rows affected (0.03 sec)

mysql> SHOW DATABASES;            #查看数据是否丢失
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> SET sql_log_bin=OFF;        #暂时关闭二进制
Query OK, 0 rows affected (0.00 sec)

mysql>source /root/mysqldumpbackup/backup.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SET sql_log_bin=ON;                 #开启二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;                     #查看数据是否恢复，可以看到mysql已经恢复了，但缺少TEST1
+--------------------+
| Database |
+--------------------+
| information_schema |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

 

 

#通过二进制日志增量恢复数据

[root@A04-Test-172 mysqldumpbackup]# mysqlbinlog -uroot -p --start-position=154 --stop-position=456 mysql-bin.000009
Enter password: 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180313 14:11:07 server id 1  end_log_pos 123 CRC32 0x7bbbba85  Start: binlog v 4, server v 5.7.18-log created 180313 14:11:07 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
e2unWg8BAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB7a6daEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYW6u3s=
'/*!*/;
# at 154
#180313 14:13:23 server id 1  end_log_pos 219 CRC32 0xb59c4546  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180313 14:13:23 server id 1  end_log_pos 294 CRC32 0xe7b92856  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1520921603/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
FLUSH TABLES
/*!*/;
# at 294
#180313 14:18:05 server id 1  end_log_pos 359 CRC32 0xa7afc716  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 359
#180313 14:18:05 server id 1  end_log_pos 456 CRC32 0x6a6a4762  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1520921885/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
CREATE DATABASE TEST1
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


另一种方法，将解析后的二进制日志文件保存到一个txt文件中，然后进行导入操作！

[root@A04-Test-172 mysqldumpbackup]# mysqlbinlog --start-position=154 --stop-position=456 mysql-bin.000009 > mysqlbinlog.txt
[root@A04-Test-172 mysqldumpbackup]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /root/mysqldumpbackup/mysqlbinlog.txt
Query OK, 0 rows affected (0.00 sec)

 #查看是否恢复

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| monkey |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

mysql>

 

3.使用lvm2快照备份数据

LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。 这里只简单介绍。

具体可参见：http://www.360doc.com/content/13/0522/16/11801283_287305129.shtml

 

4. Xtrabackup

使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启.

（1）安装Xtrabackup

yum -y install  perl-DBD-MySQL 
rpm -ivh libev-4.03-3.el6.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

依赖包下载地址：https://pan.baidu.com/s/1HF_FyrZAv4PZio8mJiOXpQ

 

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

1. 备份过程快速、可靠；

2. 备份过程不会打断正在执行的事务；

3. 能够基于压缩等功能节约磁盘空间和流量；

4. 自动实现备份检验；

5. 还原速度快；

 

我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份

使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.。

mkdir /root/xtrabackup           #新建备份目录
innobackupex --user=root --password=mysql xtrabackup/       #开始备份

#备份时显示的信息
180319 11:18:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3910261'
xtrabackup: Stopping log copying thread.
.180319 11:18:50 >> log scanned up to (3910270)

180319 11:18:50 Executing UNLOCK TABLES
180319 11:18:50 All tables unlocked
180319 11:18:50 [00] Copying ib_buffer_pool to /root/xtrabackup/2018-03-19_11-18-43/ib_buffer_pool
180319 11:18:50 [00]        ...done
180319 11:18:50 Backup created in directory '/root/xtrabackup/2018-03-19_11-18-43/'
MySQL binlog position: filename 'mysql-bin.000014', position '456'
180319 11:18:50 [00] Writing /root/xtrabackup/2018-03-19_11-18-43/backup-my.cnf
180319 11:18:50 [00]        ...done
180319 11:18:50 [00] Writing /root/xtrabackup/2018-03-19_11-18-43/xtrabackup_info
180319 11:18:50 [00]        ...done
xtrabackup: Transaction log of lsn (3910261) to (3910270) was copied.
180319 11:18:50 completed OK!

[root@A04-Test-172 xtrabackup]# ll
total 4
drwxr-x--- 7 root root 4096 Mar 19 11:18 2018-03-19_11-18-43

[root@A04-Test-172 xtrabackup]# pwd
/root/xtrabackup

 一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份。

 

#开始完全备份
[root@A04-Test-172 ~]# innobackupex --apply-log --user=root --password=mysql /root/xtrabackup/2018-03-19_11-18-43/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910289
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=3910289
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 3910668
InnoDB: Doing recovery: scanned up to log sequence number 3910677 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 3910677 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 3910677
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910696
180319 12:18:51 completed OK!

[root@A04-Test-172 2018-03-19_11-18-43]# ll          #查看备份文件
total 131120
-rw-r----- 1 root root      424 Mar 19 11:18 backup-my.cnf
-rw-r----- 1 root root      759 Mar 19 11:18 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 19 12:18 ibdata1
-rw-r----- 1 root root 50331648 Mar 19 12:18 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 19 12:18 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 19 12:18 ibtmp1
drwxr-x--- 2 root root     4096 Mar 19 11:18 monkey
drwxr-x--- 2 root root     4096 Mar 19 11:18 mysql
drwxr-x--- 2 root root     4096 Mar 19 11:18 performance_schema
drwxr-x--- 2 root root    12288 Mar 19 11:18 sys
drwxr-x--- 2 root root     4096 Mar 19 11:18 TEST1
-rw-r----- 1 root root       21 Mar 19 11:18 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Mar 19 12:18 xtrabackup_checkpoints
-rw-r----- 1 root root      467 Mar 19 11:18 xtrabackup_info
-rw-r----- 1 root root  8388608 Mar 19 12:18 xtrabackup_logfile

 

恢复数据：

#删除MySQL数据目录下的文件
[root@A04-Test-172 2018-03-19_11-18-43]# rm -rf /usr/local/mysql/data/*

#开始恢复数据，不用启动该数据库也可以还原
[root@A04-Test-172 2018-03-19_11-18-43]# innobackupex --copy-back --user=root --password=mysql /root/xtrabackup/2018-03-19_11-18-43/

180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/global_status.frm to /usr/local/mysql/data/performance_schema/global_status.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/replication_applier_configuration.frm to /usr/local/mysql/data/performance_schema/replication_applier_configuration.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_thread_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_waits_summary_by_user_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_history.frm to /usr/local/mysql/data/performance_schema/events_transactions_history.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_stages_current.frm to /usr/local/mysql/data/performance_schema/events_stages_current.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_user_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/memory_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/memory_summary_by_host_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_waits_history.frm to /usr/local/mysql/data/performance_schema/events_waits_history.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /usr/local/mysql/data/performance_schema/socket_summary_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/session_variables.frm to /usr/local/mysql/data/performance_schema/session_variables.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_history_long.frm to /usr/local/mysql/data/performance_schema/events_statements_history_long.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm
180319 12:23:15 [01]        ...done
180319 12:23:15 [01] Copying ./TEST1/db.opt to /usr/local/mysql/data/TEST1/db.opt
180319 12:23:15 [01]        ...done
180319 12:23:15 completed OK!

[root@A04-Test-172 data]# ll       #查看MySQL数据目录下是否数据已恢复
total 122916
-rw-r----- 1 root root      759 Mar 19 12:23 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 19 12:23 ibdata1
-rw-r----- 1 root root 50331648 Mar 19 12:23 ib_logfile0
-rw-r----- 1 root root 50331648 Mar 19 12:23 ib_logfile1
-rw-r----- 1 root root 12582912 Mar 19 12:23 ibtmp1
drwxr-x--- 2 root root     4096 Mar 19 12:23 monkey
drwxr-x--- 2 root root     4096 Mar 19 12:23 mysql
drwxr-x--- 2 root root     4096 Mar 19 12:23 performance_schema
drwxr-x--- 2 root root    12288 Mar 19 12:23 sys
drwxr-x--- 2 root root     4096 Mar 19 12:23 TEST1
-rw-r----- 1 root root      467 Mar 19 12:23 xtrabackup_info
[root@A04-Test-172 data]# pwd
/usr/local/mysql/data

#修改data目录所属用户和组
[root@A04-Test-172 data]# chown -R mysql:mysql /usr/local/mysql/data

[root@A04-Test-172 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;            #查看数据是否恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

 增量备份

#新建两个数据库TEST1和TEST2

mysql> CREATE DATABASE TEST2;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| TEST2              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> 

 

#开始增量备份
[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --incremental /root/xtrabackup/  --incremental-basedir=/root/xtrabackup/2018-03-19_11-18-43/

 

180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/session_variables.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/session_variables.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_history_long.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_history_long.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_global_by_event_name.frm
180319 13:29:28 [01]        ...done
180319 13:29:28 [01] Copying ./TEST1/db.opt to /root/xtrabackup/2018-03-19_13-29-21/TEST1/db.opt
180319 13:29:28 [01]        ...done
180319 13:29:28 Finished backing up non-InnoDB tables and files
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/xtrabackup_binlog_info
180319 13:29:28 [00]        ...done
180319 13:29:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3910715'
xtrabackup: Stopping log copying thread.
.180319 13:29:28 >> log scanned up to (3910724)

180319 13:29:28 Executing UNLOCK TABLES
180319 13:29:28 All tables unlocked
180319 13:29:28 [00] Copying ib_buffer_pool to /root/xtrabackup/2018-03-19_13-29-21/ib_buffer_pool
180319 13:29:28 [00]        ...done
180319 13:29:28 Backup created in directory '/root/xtrabackup/2018-03-19_13-29-21/'
MySQL binlog position: filename 'mysql-bin.000015', position '154'
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/backup-my.cnf
180319 13:29:28 [00]        ...done
180319 13:29:28 [00] Writing /root/xtrabackup/2018-03-19_13-29-21/xtrabackup_info
180319 13:29:28 [00]        ...done
xtrabackup: Transaction log of lsn (3910715) to (3910724) was copied.
180319 13:29:29 completed OK!

#查看备份文件

[root@A04-Test-172 xtrabackup]# ll
total 8
drwxr-x--- 8 root root 4096 Mar 19 13:03 2018-03-19_11-18-43         #全量备份
drwxr-x--- 8 root root 4096 Mar 19 13:29 2018-03-19_13-29-21         #第一次增量备份

[root@A04-Test-172 2018-03-19_13-29-21]# ll
total 140
-rw-r----- 1 root root   424 Mar 19 13:29 backup-my.cnf
-rw-r----- 1 root root   759 Mar 19 13:29 ib_buffer_pool
-rw-r----- 1 root root 81920 Mar 19 13:29 ibdata1.delta
-rw-r----- 1 root root    44 Mar 19 13:29 ibdata1.meta
drwxr-x--- 2 root root  4096 Mar 19 13:29 monkey
drwxr-x--- 2 root root  4096 Mar 19 13:29 mysql
drwxr-x--- 2 root root  4096 Mar 19 13:29 performance_schema
drwxr-x--- 2 root root 12288 Mar 19 13:29 sys
drwxr-x--- 2 root root  4096 Mar 19 13:29 TEST1
drwxr-x--- 2 root root  4096 Mar 19 13:29 TEST2
-rw-r----- 1 root root    21 Mar 19 13:29 xtrabackup_binlog_info
-rw-r----- 1 root root   117 Mar 19 13:29 xtrabackup_checkpoints
-rw-r----- 1 root root   553 Mar 19 13:29 xtrabackup_info
-rw-r----- 1 root root  2560 Mar 19 13:29 xtrabackup_logfile

BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/root/xtrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。

另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

 

整理增量备份：

 

#  2018-03-19_11-18-43为全量备份目录

[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --apply-log --redo-only /root/xtrabackup/2018-03-19_11-18-43   
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3910705
InnoDB: Number of pools: 1
180319 14:57:16 completed OK!

#  2018-03-19_13-29-21为增量备份目录

[root@A04-Test-172 ~]# innobackupex --user=root --password=mysql --apply-log --redo-only /root/xtrabackup/2018-03-19_11-18-43 --incremental-dir=/root/xtrabackup/2018-03-19_13-29-21

180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_history.frm to ./performance_schema/events_transactions_history.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_stages_current.frm to ./performance_schema/events_stages_current.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_user_by_event_name.frm to ./performance_schema/events_statements_summary_by_user_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/memory_summary_by_host_by_event_name.frm to ./performance_schema/memory_summary_by_host_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_waits_history.frm to ./performance_schema/events_waits_history.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/socket_summary_by_event_name.frm to ./performance_schema/socket_summary_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/session_variables.frm to ./performance_schema/session_variables.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_by_thread_by_event_name.frm to ./performance_schema/events_statements_summary_by_thread_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_transactions_summary_by_host_by_event_name.frm to ./performance_schema/events_transactions_summary_by_host_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_history_long.frm to ./performance_schema/events_statements_history_long.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/performance_schema/events_statements_summary_global_by_event_name.frm to ./performance_schema/events_statements_summary_global_by_event_name.frm
180319 14:58:24 [01]        ...done
180319 14:58:24 [01] Copying /root/xtrabackup/2018-03-19_13-29-21/TEST1/db.opt to ./TEST1/db.opt
180319 14:58:24 [01]        ...done
180319 14:58:24 [00] Copying /root/xtrabackup/2018-03-19_13-29-21//xtrabackup_binlog_info to ./xtrabackup_binlog_info
180319 14:58:24 [00]        ...done
180319 14:58:24 [00] Copying /root/xtrabackup/2018-03-19_13-29-21//xtrabackup_info to ./xtrabackup_info
180319 14:58:24 [00]        ...done
180319 14:58:24 completed OK

[root@A04-Test-172 ~]# rm -rf  /usr/local/mysql/data/*            #删除数据目录下文件


#开始恢复

#2018-03-19_11-18-43为全量备份目录

[root@A04-Test-172 ~]# innobackupex --copy-back /root/xtrabackup/2018-03-19_11-18-43 
name.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./performance_schema/events_waits_history.frm to /usr/local/mysql/data/performance_schema/events_waits_history.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /usr/local/mysql/data/performance_schema/socket_summary_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/session_variables.frm to /usr/local/mysql/data/performance_schema/session_variables.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_transactions_summary_by_host_by_event_name.frm 
180319 15:00:30 [01] ...done 180319 15:00:30 [01] Copying ./performance_schema/events_statements_history_long.frm to /usr/local/mysql/data/performance_schema/events_statements_history_long.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /usr/local/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm 
180319 15:00:30 [01] ...done 
180319 15:00:30 [01] Copying ./TEST1/db.opt to /usr/local/mysql/data/TEST1/db.opt 180319 15:00:30 [01] ...done 
180319 15:00:30 completed OK!

#修改data数据目录所属用户和组 
[root@A04-Test-172 mysql]# chown -R mysql:mysql /usr/local/mysql/data

#杀掉僵尸进程
[root@A04-Test-172 data]# ps -ef|grep mysql
root      4246  3167  0 13:12 pts/3    00:00:00 tail -f /usr/local/mysql/log/mysql-error.log
root      4575     1  0 13:19 pts/4    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid
mysql     4778  4575  0 13:19 pts/4    00:00:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/mysql-error.log --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root      5723  5061  0 15:12 pts/2    00:00:00 tail -f mysql-error.log
root      5725  5033  0 15:12 pts/0    00:00:00 grep mysql
[root@A04-Test-172 data]# kill -9 4575 4778
[root@A04-Test-172 data]# ps -ef|grep mysql
root      4246  3167  0 13:12 pts/3    00:00:00 tail -f /usr/local/mysql/log/mysql-error.log
root      5723  5061  0 15:12 pts/2    00:00:00 tail -f mysql-error.log
root      5729  5033  0 15:14 pts/0    00:00:00 grep mysql

#启动数据库
[root@A04-Test-172 data]# service mysql.server start
Starting MySQL.[  OK  ]

 

#查看数据是否已恢复
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| TEST2              |
| monkey             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> exit

 

 

七、总结

备份方法	备份速度	恢复速度	便捷性	功能	一般用于
cp	快	快	一般、灵活性低	很弱	少量数据备份
mysqldump	慢	慢	一般、可无视存储引擎的差异	一般	中小型数据量的备份
lvm2快照	快	快	一般、支持几乎热备、速度快	一般	中小型数据量的备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份

八、常见问题解答

1.启动mysql时报错：

[root@A04-Test-172 ~]# service mysql.server start
Starting MySQL..The server quit without updating PID file (/usr/local/mysql/data/mysqld.pid).[FAILED]

【解决方法】

（1）查看MySQL日志，报错如下：

.
2018-03-19T05:12:30.839470Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-03-19T05:12:30.839551Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-03-19T05:12:30.839569Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-03-19T05:12:31.440444Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-03-19T05:12:31.440568Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-03-19T05:12:31.440587Z 0 [ERROR] Failed to initialize plugins.
2018-03-19T05:12:31.440596Z 0 [ERROR] Aborting

2018-03-19T05:12:31.440618Z 0 [Note] Binlog end
2018-03-19T05:12:31.440745Z 0 [Note] Shutting down plugin 'CSV'
2018-03-19T05:12:31.441182Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

（2）根据上述错误信息，加上百度之。判断是/usr/local/mysql/data目录没有写权限。经核查,/usr/local/mysql/data目录的所属用户和组均为root.故授权之。

chown -R mysql:mysql  /usr/local/mysql/data

（3）查看是否有僵尸进程，有则kill -9 

ps -ef|grep mysql

（4）重新启动数据库，完美启动

[root@A04-Test-172 backup]# service mysql.server start
Starting MySQL..[  OK  ]