使用xtrabackup进行数据库备份

二进制日志相关的选项

相关的参数	值	作用
innodb_support_xa	ON	存储引擎是否支持分布式事务，存储引擎事务在存储引擎的内部赋予了ACID属性，分布式事务（XA）是医用高层次的事务，两步“准备“ + ”提交“
sync_binlog	0	设定多久同步一次二进制日志到磁盘中，0，表示不同步，任意正数表示对二进制多少次写操作之后同步一次，当autocommit的值设置为1时，每条语句执行之后都会同步，每个事务的提交会引起二进制日志同步。该值设置为1时，可以保证备份的安全操作。避免由于备份导致二进制日志的损坏。

ibbackup：
对Innodb的存储引擎key执行在线物理备份 ，全备和增量备份
MyISAM的存储引擎支持温备，全量备份

mysqldump：逻辑备份

LVM： mylvmbackup（perl script）

percona：
xtrabackup
xtradb：Innodb的增强版
Innodb：

xtrabackup的简介
xtrabackup和ibbackup对比
xtrabackup包的下载位置

安装xtrabackup

[root@miner-k ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm

[root@miner-k ~]# wget http://ftp.tu-chemnitz.de/pub/linux/dag/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

[root@miner-k ~]# yum -y localinstall percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm libev-4.15-1.el6.rf.x86_64.rpm

备份操作

全量备份

[root@miner-k ~]# innobackupex --user=root /backup
xtrabackup: recognized server arguments: --datadir=/mydata/data --innodb_file_per_table=1 --log_bin=mysql-bin --server-id=1 
xtrabackup: recognized client arguments: --datadir=/mydata/data --innodb_file_per_table=1 --log_bin=mysql-bin --server-id=1 
180713 21:10:06 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!".

180713 21:10:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
180713 21:10:07  version_check Connected to MySQL server
180713 21:10:07  version_check Executing a version check against the server...
180713 21:10:07  version_check Done.
180713 21:10:07 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.5.59-log
innobackupex version 2.4.12 based on MyS
........
xtrabackup: Transaction log of lsn (292684947) to (292684947) was copied.
180713 21:10:19 completed OK!

[root@miner-k ~]# cd /backup
[root@miner-k backup]# ls
2018-07-13_21-10-06
[root@miner-k backup]# cd 2018-07-13_21-10-06/
[root@miner-k 2018-07-13_21-10-06]# ls
backup-my.cnf  ibdata1  mydb1  performance_schema  test                    xtrabackup_checkpoints  xtrabackup_logfile
employees      mydb     mysql  studb               xtrabackup_binlog_info  xtrabackup_info         zabbix

备份之后，备份中各文件的作用

文件名	作用
backup-my.cnf	mysql的配置文件
ibdata1	innodb存储空间文件
employees、mydb、mysql、studb、zabbix	数据库
xtrabackup_binlog_info	记录备份时刻对应的二进制日志文件以及position
xtrabackup_info
xtrabackup_logfile
xtrabackup_checkpoints	显示备份的参数，Innodb存储引擎会记录每一个数据块的的日志序列号，当数据改变该值也会改变。可以根据该值做增量备份 。innodb_to_lsn = 292684947

恢复数据之前的准备

准备（prepare）一个完全备份

一般情况下，在备份完成之后，数据尚且不能用于恢复操作，因为备份的数据中可能包含尚未提交的事务或已经提交但是尚未同步至数据文件中的事务，因此数据文件仍然处于不一致状态，“备份”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

# 最后指定的文件位置必须是备份保存的位置
[root@miner-k ~]# innobackupex --apply-log /backup/2018-07-13_21-10-06/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
........
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.19 started; log sequence number 292685333
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 292685352
180713 21:42:10 completed OK!

保留全量备份之后的二进制日志

mysql> desc student
    -> ;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(5)              | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)         | NO   |     | NULL    |                |
| cid    | tinyint(3) unsigned | NO   | MUL | NULL    |                |
| gender | enum('M','F')       | YES  |     | NULL    |                |
| age    | tinyint(4)          | NO   |     | NULL    |                |
| cid2   | tinyint(4)          | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.33 sec)

mysql> insert into student (name,cid,gender,age) values ('jk',2,'F',22);
Query OK, 1 row affected (0.05 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      842 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

[root@miner-k data]# cp -a mysql-bin.000008 /backup

删除数据的数据文件

[root@miner-k ~]# rm -rf /mydata/data/

关闭mysqld服务

[root@miner-k mydata]# killall mysqld
[root@miner-k mydata]# ps aux | grep mysql
root      20494  0.0  0.0 103316   832 pts/0    S+   22:06   0:00 grep mysql

使用innobackupex 工具，从备份的位置进行恢复数据。

[root@miner-k mydata]# innobackupex --copy-back /backup/2018-07-13_21-10-06/

180713 22:11:24 [01] Copying ib_logfile0 to /mydata/data/ib_logfile0
180713 22:11:24 [01]        ...done
180713 22:11:24 [01] Copying ib_logfile1 to /mydata/data/ib_logfile1
180713 22:11:24 [01]        ...done
180713 22:11:24 [01] Copying ibdata1 to /mydata/data/ibdata1
180713 22:11:24 [01]        ...done
........
180713 22:11:35 [01] Copying ./mydb1/db.opt to /mydata/data/mydb1/db.opt
180713 22:11:35 [01]        ...done
180713 22:11:35 [01] Copying ./xtrabackup_binlog_pos_innodb to /mydata/data/xtrabackup_binlog_pos_innodb
180713 22:11:35 [01]        ...done
180713 22:11:35 [01] Copying ./ibtmp1 to /mydata/data/ibtmp1
180713 22:11:35 [01]        ...done
180713 22:11:35 completed OK!

设置数据库数据保存位置文件的的属组、属主

[root@miner-k data]# pwd
/mydata/data

[root@miner-k data]# chown -R mysql.mysql ./*

启动mysqld服务

[root@miner-k data]# service mysqld start
Starting MySQL.Logging to '/mydata/data/miner-k.com.err'.
.                                                          [  OK  ]
[root@miner-k data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.59-log Source distribution

Copyright (c) 2000, 2018, 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 

恢复二进制日志

[root@miner-k ~]# cd /backup
[root@miner-k backup]# ls
2018-07-13_21-10-06  mysql-bin.000008


# 查看备份时的二进制日志位置
[root@miner-k backup]# cat 2018-07-13_21-10-06/xtrabackup_binlog_info 
mysql-bin.000008    590

# 从首次二进制日志备份位置，提取mysql语句
[root@miner-k backup]# mysqlbinlog --start-position=590 mysql-bin.000008 > add_studb.sql

# 恢复二进制日志
[root@miner-k backup]# mysql -u root -p <add_studb.sql 
Enter password:

创建增量备份

如果全量备份已经恢复过一次，最好重新做全量备份。然后再做增量备份。

创建备份

全量备份

[root@miner-k backup]# xtrabackup --uroot /backup

第一次增量备份

[root@miner-k backup]# innobackupex --incremental /backup --incremental-basedir=/backup/2018-07-13_22-48-50/

第二次增量备份，以第一次的增量备份为基准

[root@miner-k backup]# innobackupex --incremental /backup --incremental-basedir=/backup/2018-07-13_22-55-58/

[root@miner-k backup]# cat 2018-07-13_22-48-50/xtrabackup_info 

tool_name = innobackupex
tool_command = --user=root /backup
innodb_from_lsn = 0
innodb_to_lsn = 292685707

[root@miner-k backup]# cat 2018-07-13_22-55-58/xtrabackup_info 

tool_name = innobackupex
tool_command = --incremental /backup --incremental-basedir=/backup/2018-07-13_22-48-50/
innodb_from_lsn = 292685707
innodb_to_lsn = 292685707

[root@miner-k backup]# cat 2018-07-13_23-05-00/xtrabackup_info 

tool_name = innobackupex
tool_command = --incremental /backup --incremental-basedir=/backup/2018-07-13_22-55-58/
binlog_pos = filename 'mysql-bin.000001', position '612'
innodb_from_lsn = 292685707
innodb_to_lsn = 292686043

还原前的准备

# 全量备份恢复的准备
[root@miner-k backup]# innobackupex --apply-log --redo-only /backup/2018-07-13_22-48-50/

# 第一次增量备份的准备
[root@miner-k backup]# innobackupex --apply-log --redo-only /backup/2018-07-13_22-48-50/ --incremental-dir=/backup/2018-07-13_22-55-58/


# 第二次增量备份的准备
[root@miner-k backup]# innobackupex --apply-log --redo-only /backup/2018-07-13_22-48-50/ --incremental-dir=/backup/2018-07-13_23-05-00/

删除数据库

[root@miner-k backup]# rm -rf /mydata/data/

备份恢复

[root@miner-k data]# pwd
/mydata/data

[root@miner-k backup]# innobackupex --copy-back /backup/2018-07-13_22-48-50/

[root@miner-k data]# chown -R mysql.mysql ./*

启动mysqld服务

[root@miner-k data]# service mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL..                                           [  OK  ]

导入或导出单张表

# 完成全量备份
[root@miner-k backup]# innobackupex --user=root /backup


# 在准备阶段，使用参数--export，对所有的表生成exp文件。
[root@miner-k backup]# innobackupex --apply-log --export /backup/2018-07-14_00-25-24/

查看执行结果

[root@miner-k studb]# pwd
/backup/2018-07-14_00-25-24/studb


[root@miner-k studb]# ls
courses.cfg  db.opt    stu1.frm     student.frm   stu_test.frm  tb3.frm    tbtwo.exp    teacher.exp     testcourse.exp   testcourses.exp
courses.exp  sct.frm   stu1.ibd     student.ibd   stu_test.ibd  tb3.ibd    tbtwo.frm    teacher.frm     testcourse.frm   testcourses.frm
courses.frm  stu1.cfg  student.cfg  stu_test.cfg  tb3.cfg       tb4.frm    tbtwo.ibd    teacher.ibd     testcourse.ibd   testcourses.ibd
courses.ibd  stu1.exp  student.exp  stu_test.exp  tb3.exp       tbtwo.cfg  teacher.cfg  testcourse.cfg  testcourses.cfg

导入表

要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：

mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;

然后将此表的表空间删除：

mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：

mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;