MySQL 备份方案

Percona XtraBackup

介绍

官方文档

它是一个MySQL的开源的热备工具，备份时不会锁数据库。当前最新版本为2.2，它可以备份MySQL 5.1, 5.5和5.6的InnoDB, XtraDB和MyISAM类型的表。

相比MySQL自带的mysqldump，它有以下特点：

非常快速和可靠
备份期间，不会中断处理事务处理
节约磁盘空间和网络带宽（传输时，会进行流压缩）
自动备份验证
由于恢复非常快，所以可用时间高
可以增量备份
可在线中进行MySQL服务器之间的表迁移
非常容易地创建一个MySQL从复制
备份时，不会添加额外的服务器负载

它的详细介绍，请看官方手册介绍：Intro

安装

下载地址

Download

Ubuntu 使用 apt 方式

官方文档

好像由于要翻墙，我搬了下来：

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

# 添加到 /etc/apt/sources.list
# 注意，这里的 VERSION 请替换为自己的ubuntu版本
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

sudo apt-get update
sudo apt-get install percona-xtrabackup

源码方式，请参看源码目录的安装说明，在此不多说了。

工具说明

innobackupex: 它是一个包装的Perl脚本，可以备份整个表类型为MyISAM, InnoDB和XtraDB的MySQL实例。

xtrabackup: 一个用C写的二进制工具，仅用于复制InnoDB和XtraDB数据

xbcrypt：用于加/解密备份的文件的工具

xbstream: 用于sbstream格式文件。

注意：虽然可以单独使用xtrabackup工具，但是，还是建议使用innobackupex这个包装的脚本来执行，让它来为你调用xtrabackup。

使用方式

全量备份

备份

$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
$ innobackupex --user=LUKE  --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -
$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

如果不指定--user，它会假定数据库的用户和你当前执行这些命令的用户名一样。

指定my.cnf文件：

$ innobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=SECRET /path/to/backup/dir/

注意，这个必须是第一个参数.

连接数据库的其他选项：

-port: MySQL端口 -socket: MySQL的Socket文件 -host: MySQL主机

这些选项是传递给mysql的，你可以参考其他mysql --help允许的选项。

权限

你需要在文件系统级别对服务器的datadir（即数据目录）拥有READ, WRITE和EXECUTE权限

在创建MySQL用户时，最小权限的例子：

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

准备 $ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/这步只是备份而已，但没有恢复数据，还要执行以下操作

$ innobackupex --apply-log --use-memory=4G /path/to/backup/dir 如果不指定--use-memory，默认是100M

恢复

innobackupex --copy-back /path/to/BACKUP-DIR

增量

备份阶段

首先使用以下命令，创建一个基础的增量序列

innobackupex /data/backups

然后创建第一个增量备份:

innobackupex --incremental /data/backups --incremental-basedir=BASEDIR

然后再创建另一个增量备份：

innobackupex --incremental /data/backups --incremental-basedir=INCREMENTAL-DIR-1 这时，要特别注意，以后每一次的增量备份，都要修改 --incremental-basedir=参数为上一次的值

准备阶段

innobackupex --apply-log --redo-only BASE-DIR

然后依次恢复（从旧到新）增量备份的数据

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1 innobackupex --apply-log --incremental-dir=INCREMENTAL-DIR-2 …

注意，第二条没有--redo-only参数。这个参数应该用于当合并所有增量，但除了最近一次的。

一旦完成了所有增量恢复，就可以执行以下命令了：

innobackupex --apply-log BASE-DIR

恢复阶段

innobackupex --copy-back BASE-DIR

参考资料

[1] xtrabackup原理

xtrabackup 实战

全量备份及恢复

备份

注意：这里的--user以及--password是上连接该DB的用户名和密码，而且必须要拥有相应的权限，请参考上面的[权限]说明。 /home/mysql/3306/backup：这里是保存备份的位置

mysql@yang:/usr/local/mysql-5.6$ innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf /home/mysql/3306/backup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

...中间省略输出....一定要看到有以下一句才算是成功!
innobackupex: Backup created in directory '/home/mysql/3306/backup/2015-09-01_11-17-17'
150901 11:17:22  innobackupex: Connection to database server closed
150901 11:17:22  innobackupex: completed OK!

还原

这里模拟数据损坏（直接删除mysql数据目录的相应的数据部分数据文件）

cd /usr/local/mysql-data/3306/test
#删除aa表的所有数据
rm -rf aa.*

#这时看表时，可以发现没有了aa表，而且启动时也有警告提示:

2015-09-01 11:56:34 28638 [ERROR] InnoDB: Table test/aa in the InnoDB data dictionary has tablespace id 6, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.


mysql> mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mvcc           |
+----------------+
1 row in set (0.00 sec)

这表示物理删除了数据文件，但是MySQL的数据字典里，还存在有表aa的信息，MySQL在启动的时候忽略了这个错误，继续启动。

现在，准备还原数据看看：

准备阶段


innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf  --apply-log --use-memory=4G /home/mysql/3306/backup

执行完后，发现报错：

mysql@yang:~$ innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf  --apply-log --use-memory=4G /home/mysql/3306/backup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150901 12:06:36  innobackupex: Starting the apply-log operation

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

Could not open required defaults file: /home/mysql/3306/backup/backup-my.cnf
Fatal error in defaults handling. Program aborted
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 4545.
	main::get_option('innodb_data_file_path') called at /usr/bin/innobackupex line 2631
	main::apply_log() called at /usr/bin/innobackupex line 1578
innobackupex: Error: no 'innodb_data_file_path' option in group 'mysqld' in server configuration file '/usr/local/mysql-data/3306/my.cnf' at /usr/bin/innobackupex line 4545.
mysql@yang:~$

这时，打开MySQL终端，输入以下命令：

mysql> show variables like 'innodb_data_file_path'
    -> ;
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql>

然后，在配置文件 /home/mysql/3306/backup/backup-my.cnf的[mysqld]组中，添加以下参数:

innodb_data_file_path=ibdata1:12M:autoextend

注意，这个是备份目录下的文件。

然后，又发现报错：

xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)
xtrabackup: cd to /home/mysql/3306/backup
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: error: xtrabackup_read_metadata()
xtrabackup: This target seems not to have correct metadata...
2015-09-01 12:20:19 7f3854c9c780  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
2015-09-01 12:20:19 7f3854c9c780  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2649.
	main::apply_log() called at /usr/bin/innobackupex line 1578
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 2649.

解决办法：因为在备份时，默认情况下生成的路径为/home/mysql/3306/backup/2015-09-01_11-17-17，所以，应该将最后的目录参数改为/home/mysql/3306/backup/2015-09-01_11-17-17，再试一次：

mysql@yang:~$ innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf --apply-log --use-memory=4G /home/mysql/3306/backup/2015-09-01_11-17-17/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150901 12:33:35  innobackupex: Starting the apply-log operation

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


150901 12:33:35  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/home/mysql/3306/backup/2015-09-01_11-17-17/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/home/mysql/3306/backup/2015-09-01_11-17-17 --use-memory=4G
...输出省略...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 232371734
150901 12:33:39  innobackupex: completed OK!

这次终于成功了，判断的标志是最后是否打印innobackupex: completed OK!

正式还原

innobackupex --copy-back --defaults-file=/usr/local/mysql-data/3306/my.cnf /home/mysql/3306/backup/2015-09-01_11-17-17

很悲剧地又发现报以下错误：

mysql@yang:~$ innobackupex --copy-back --defaults-file=/usr/local/mysql-data/3306/my.cnf /home/mysql/3306/backup/2015-09-01_11-17-17

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150901 12:39:40  innobackupex: Starting the copy-back operation

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

innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2201.
	main::if_directory_exists_and_empty('/usr/local/mysql-data/3306', 'Original data') called at /usr/bin/innobackupex line 2474
	main::copy_back(0) called at /usr/bin/innobackupex line 1578
innobackupex: Error: Original data directory '/usr/local/mysql-data/3306' is not empty! at /usr/bin/innobackupex line 2201.

原来，还要令/usr/local/mysql-data/3306 这个目录为空才行.所以，为了安全见，再备份下：

mv /usr/local/mysql-data/3306 /usr/local/mysql-data/3306.old
mkdir /usr/local/mysql-data/3306
sudo chown mysql:mysql /usr/local/mysql-data/ -R

然后再试一次：

这里要注意，这使用了3306.old/my.cnf

mysql@yang:/usr/local/mysql-data$ innobackupex --copy-back --defaults-file=/usr/local/mysql-data/3306.old/my.cnf /home/mysql/3306/backup/2015-09-01_11-17-17
....输出省略...
150901 12:51:07  innobackupex: completed OK!
mysql@yang:/usr/local/mysql-data$

重启重启后，再检查数据：

mysql> use test;
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_test |
+----------------+
| aa             |
| mvcc           |
+----------------+
2 rows in set (0.00 sec)

mysql>

增量备份与还原

备份

进行基础备份

mysql@yang:~/3306$ innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf /home/mysql/3306/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

...中间省略输出....一定要看到有以下一句才算是成功!

150901 13:02:13  innobackupex: completed OK!

进行第一次增量备份

mysql@yang:~/3306$ innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf --incremental-basedir=/home/mysql/3306/base/2015-09-01_13-02-10 --incremental /home/mysql/3306/incremental/one

结果如下：

mysql@yang:~/3306$ innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf --incremental-basedir=/home/mysql/3306/base/2015-09-01_13-02-10 --incremental /home/mysql/3306/incremental/one

...中间省略输出....一定要看到有以下一句才算是成功!
150901 13:07:32  innobackupex: completed OK!

这中间做一些其他的数据库操作，比如插入几条新数据，然后再进行第二次境量备份注意 incremental-basedir和-incremental参数的变化

mysql@yang:~/3306$ innobackupex --user=bkpuser --password=s3cret --defaults-file=/usr/local/mysql-data/3306/my.cnf --incremental-basedir=/home/mysql/3306/incremental/one/2015-09-01_13-07-29 --incremental /home/mysql/3306/incremental/two

特别注意

因为默认情况下，备份的目录会自动生成时间截，所以，参数要定位到时间截的目录。

还原

准备阶段

mysql@yang:~$ innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf  --apply-log --redo-only --use-memory=4G /home/mysql/3306/base/2015-09-01_13-02-10

模拟数据损坏

删除某几条数据

mysql> select * from aa;
+----+--------+
| id | name   |
+----+--------+
|  1 | yang   |
|  2 | yang2  |
|  3 | yang-s |
|  4 | y      |
|  5 | yy     |
+----+--------+
5 rows in set (0.00 sec)

mysql> insert into aa (name) values ('yy');mysql>
mysql> delete from aa where id < 3;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from aa;
+----+--------+
| id | name   |
+----+--------+
|  3 | yang-s |
|  4 | y      |
|  5 | yy     |
+----+--------+
3 rows in set (0.00 sec)

mysql>

还原

mv /usr/local/mysql-data/3306 /usr/local/mysql-data/3306.old2
mysql@yang:~$ innobackupex --defaults-file=/usr/local/mysql-data/3306.old2/my.cnf  --copy-back /home/mysql/3306/base/2015-09-01_13-02-10

##复制回配置文件及启动脚本

`copy /usr/local/mysql-data/3306.old2/my.cnf /usr/local/mysql-data/3306.old2/startup.sh /usr/local/mysql-data/3306/`

启动mysql，这时看到数据：

mysql> select * from aa;
+----+--------+
| id | name   |
+----+--------+
|  1 | yang   |
|  2 | yang2  |
|  3 | yang-s |
+----+--------+
3 rows in set (0.00 sec)

mysql>

关闭掉mysql，还原第一，二次的增量备份数据:

innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf  --apply-log --redo-only --use-memory=4G /home/mysql/3306/base/2015-09-01_13-02-10 --incremental-dir=/home/mysql/3306/incremental/one/2015-09-01_14-22-53

innobackupex --defaults-file=/usr/local/mysql-data/3306/my.cnf  --apply-log --redo-only --use-memory=4G /home/mysql/3306/base/2015-09-01_14-21-12/ --incremental-dir=/home/mysql/3306/incremental/two/2015-09-01_14-24-06/

注意，这里你按需准备好想要恢复的增量的数据即可。（比如，我只想要恢复base + one的数据）

然后，正式还原：

mysql@yang:/usr/local/mysql-data$ innobackupex --defaults-file=/usr/local/mysql-data/3306.old/my.cnf   --copy-back /home/mysql/3306/base/2015-09-01_14-21-12/

还原时，只需要指定基础备份的目录即可

参考资料

mysqldump

备份

mysqldump -S /usr/local/mysql-data/3306/3306.socket --single-transaction --skip-opt -q --routines --triggers --create-options --no-autocommit -B test | gzip > /tmp/test.sql.gz

还原

先删除test数据库，再还原：
mysql>drop database test;

shell:
gunzip < /tmp/test.sql.gz |  mysql -S /usr/local/mysql-data/3306/3306.socket -h localhost -u root -p

参考资料

mysqldump

主从复制(master/slave)

主配置

[mysqld]
port       = 3306
socket     = /usr/local/mysql-data/3306/3306.socket
datadir    = /usr/local/mysql-data/3306
pid-file   = /usr/local/mysql-data/3306/3306.pid
log-error    = /usr/local/mysql-data/3306/3306.err
slow_query_log = 1
long_query_time = 0.3
slow_query_log_file = /usr/local/mysql-data/3306/query-slow.log
general_log = /usr/local/mysql-data/3306/general-log.log
language    = /usr/local/mysql-5.6/share/english
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# replication

log-bin = mysql-bin
server-id = 1

enforce-gtid-consistency
gtid-mode=ON

#replication the db
replicate-do-db=test

#must
log-slave-updates

然后重启master服务器

然后创建一个用于复制的用户

CREATE USER 'repl-user'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl-user'@'%';
flush privileges;

从配置

[mysqld]
port       = 3307
socket     = /usr/local/mysql-data/3307/3307.socket
datadir    = /usr/local/mysql-data/3307
pid-file   = /usr/local/mysql-data/3307/3307.pid
log-error    = /usr/local/mysql-data/3307/3307.err
slow_query_log = 1
long_query_time = 0.3
slow_query_log_file = /usr/local/mysql-data/3307/query-slow.log
general_log = /usr/local/mysql-data/3307/general-log.log
language    = /usr/local/mysql-5.6/share/english
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = mysql-bin
server-id = 2
relay-log = relay-log-slave
read-only = ON

gtid-mode =ON
enforce-gtid-consistency
log-slave-updates

然后重启slave服务器

然后在slave里执行以下命令：

CHANGE MASTER TO \
MASTER_HOST = '10.0.0.81', \
MASTER_PORT = 3306, \
MASTER_USER = 'repl-user', \
MASTER_PASSWORD = 'slavepass', \
MASTER_AUTO_POSITION = 1;

然后正式开始：
mysql>START SLAVE;

检查复制状态

SHOW SLAVE STATUS;

Slave_SQL_Running 为No

到slave执行以下命令：

mysql> stop slave;

到master执行以下命令：
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1790
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 3bf331ce-5074-11e5-9a27-7824af3fdd83:1-7
1 row in set (0.00 sec)


到slave执行以下命令：

mysql>CHANGE MASTER TO \
MASTER_HOST = '10.0.0.81', \
MASTER_PORT = 3306, \
MASTER_USER = 'repl-user', \
MASTER_PASSWORD = 'slavepass', \
MASTER_LOG_FILE = 'mysql-bin.000001', \
MASTER_AUTO_POSITION = 0, \
MASTER_LOG_POS = 1790;

到slave执行以下命令启动slave
mysql> start slave;

从库延后的解决

不知道为什么，做好复制好，发现从库没有更上主库，如下：

主库：

mysql> select * from test_slave;
+----+---------+
| id | name    |
+----+---------+
|  1 | aa      |
|  2 | aa      |
|  3 | aa      |
|  4 | a32323a |
+----+---------+
4 rows in set (0.00 sec)

mysql>

从库:

mysql> select * from test_slave;
+----+---------+
| id | name    |
+----+---------+
|  3 | aa      |
|  4 | a32323a |
+----+---------+
2 rows in set (0.00 sec)

mysql>

解决：

在master上执行以下命令


RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

mysqldump -uroot -p -S /usr/local/mysql-data/3306/3306.socket --master-data=2 --all-databases > /tmp/3306.all.sql

UNLOCK TABLES;

在slave上执行以下命令

STOP SLAVE;
RESET MASTER;

mysql -S /usr/local/mysql-data/3307/3307.socket -u root -p -h localhost -P 3307 < /tmp/3306.all.sql

RESET SLAVE;

CHANGE MASTER TO \
MASTER_HOST = '10.0.0.81', \
MASTER_PORT = 3306, \
MASTER_USER = 'repl-user', \
MASTER_PASSWORD = 'slavepass', \
MASTER_LOG_FILE = 'mysql-bin.000001', \
MASTER_AUTO_POSITION = 0, \
MASTER_LOG_POS = 151;

START SLAVE;

执行完，记得再次检查slave状态：

show slave status;

看看两个线程是不是都是Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

参考资料

CSND Stackoverflow

文件同步方式（冷备）

这个方式最好就是停掉服务器，然后进行文件同步.

rsync -avh --delete /usr/local/mysql-data/3306 /home/mysql/3306/backup

rsync是个神器！它可以增量同步文件。这个没有什么特别的了。

Contents

Percona XtraBackup

介绍

安装

下载地址

Ubuntu 使用 apt 方式

工具说明

使用方式

全量备份

增量

参考资料

xtrabackup 实战

全量备份及恢复

备份

还原

增量备份与还原

备份

还原

参考资料

mysqldump

备份

还原

参考资料

主从复制(master/slave)

主配置

从配置

检查复制状态

Slave_SQL_Running 为No

从库延后的解决

参考资料

文件同步方式（冷备）