评论

收藏

[MySQL] Mysql热备xtrabackup的使用

数据库 数据库 发布于:2021-07-03 21:45 | 阅读数:349 | 评论:0

  InnoDB 有个商业的InnoDB Hotbackup,可以对InnoDB引擎的表实现在线热备。而 percona出品的Xtrabackup,是InnoDB Hotbackup的一个开源替代品,可以在线对InnoDB/XtraDB引擎的表进行物理备份。mysqldump支持在线备份,不过是逻辑备份,效率比较差。当数据量比较小的时候,mysqldump还可以胜任,当数据量大的时候,恢复时间却让人无法忍受,于是开源工具xtrabackup就应运而生了,xtrabackup属于物理备份,效率很不错。
  

  xtrabackup提供了两种命令行工具:
  xtrabackup:用于备份InnoDB引擎的数据(不会备份myisam比如mysql权限相关表等,也不会自动copy frm文件);
innobackupex:一个perl脚本,在执行过程中会调用xtrabackup命令,用该命令即可以备份InnoDB,也可以备份MyISAM/copy frm文件,只不过在备份myisam表时候会添加一个读锁。
  

  实验环境:CentOS release 6.5 (Final),mysql  Ver 14.14 Distrib 5.6.14
  xtrabackup安装
#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.13/binary/redhat/6/x86_64/percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm
#yum  -y install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package  bzr bison ncurses-devel zlib-devel
#rpm -ivh percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm
  部分参数说明
--user           指定执行备份的用户。
  --password         指定执行备份用户的密码。
  --defaults-file      指定mysql的选项文件路径。
  --no-timestamp       不要显示时间戳。
  --incremental      告诉xtrabackup这次是创建增量备份。  
  --incremental-basedir  指定一个全量备份的路径作为增量备份的基础。
  --redo-only     如果进行准备工作完成后,还有其他的增量备份集待处理,就需要指定这个参数。
  --apply-log     从指定的选项文件中读取配置信息并应用日志等,这就意味对备份集做恢复的准备工作。
  --copy-back     将指定备份集恢复到指定的路径下。
  

  全备
#将全备的数据备份到/data/backup/base
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp  /data/backup/base
   ......................
innobackupex: Backup created in directory '/data/backup/base'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 733
160201 15:26:07  innobackupex: Connection to database server closed
160201 15:26:07  innobackupex: completed OK!
  先对数据库进行一些修改。
mysql> create database sharelinux;
Query OK, 1 row affected (0.00 sec)
mysql> use sharelinux;
Database changed
mysql> create table t1(id int,name varchar(10));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+----------+
| id   | name   |
+------+----------+
|  1 | zhangsan |
|  2 | lisi   |
|  3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)
  第一次增量备份
#第一次增量备份目录/data/backup/incremental_one
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp --incremental /data/backup/incremental_one --incremental-basedir=/data/backup/base/
  ......................................................
xtrabackup: Creating suspend file '/data/backup/incremental_one/xtrabackup_log_copied' with pid '19979'
xtrabackup: Transaction log of lsn (22333659) to (22333659) was copied.
160201 15:39:26  innobackupex: All tables unlocked
innobackupex: Backup created in directory '/data/backup/incremental_one'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1238
160201 15:39:26  innobackupex: Connection to database server closed
160201 15:39:26  innobackupex: completed OK!
  再对数据库进行修改
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)
mysql> use db01;
Database changed
mysql> create table t2(id int,name varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t2 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t2;
+------+----------+
| id   | name   |
+------+----------+
|  1 | zhangsan |
|  2 | lisi   |
|  3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)
  第二次增量备份
#第二次增量备份目录/data/backup/incremental_two
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --no-timestamp --incremental /data/backup/incremental_two --incremental-basedir=/data/backup/incremental_one/
   ........................................
innobackupex: Backup created in directory '/data/backup/incremental_two'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1689
160201 15:59:10  innobackupex: Connection to database server closed
160201 15:59:10  innobackupex: completed OK!
  模拟故障,删除数据库的数据文件
# ls /usr/local/webserver/mysql5.6/data/
auto.cnf  ib_logfile0  mysql-bin.000001  node1.pid       test
db01    ib_logfile1  mysql-bin.index   performance_schema  zabbix
ibdata1   mysql    node1.err     sharelinux
# rm -rf /usr/local/webserver/mysql5.6/data/*
  恢复准备
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log --redo-only /data/backup/base/
   ........................................
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 22327338
160201 16:09:44  innobackupex: completed OK!
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log --redo-only /data/backup/base/ --incremental-dir=/data/backup/incremental_one/
  ...........................................
innobackupex: Copying '/data/backup/incremental_one/performance_schema/events_stages_history.frm' to '/data/backup/base/performance_schema/events_stages_history.frm'
innobackupex: Copying '/data/backup/incremental_one/performance_schema/setup_instruments.frm' to '/data/backup/base/performance_schema/setup_instruments.frm'
160201 16:13:15  innobackupex: completed OK!
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log  /data/backup/base/ --incremental-dir=/data/backup/incremental_two/
innobackupex: Copying '/data/backup/incremental_two/performance_schema/setup_instruments.frm' to '/data/backup/base/performance_schema/setup_instruments.frm'
innobackupex: Copying '/data/backup/incremental_two/db01/db.opt' to '/data/backup/base/db01/db.opt'
innobackupex: Copying '/data/backup/incremental_two/db01/t2.frm' to '/data/backup/base/db01/t2.frm'
160201 16:18:27  innobackupex: completed OK!
  数据恢复
# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6/my.cnf --copy-back  /data/backup/base/
  ...........................................
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/data/backup/base'
innobackupex: back to original InnoDB data directory '/usr/local/webserver/mysql5.6/data'
innobackupex: Copying '/data/backup/base/ibdata1' to '/usr/local/webserver/mysql5.6/data/ibdata1'
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/data/backup/base'
innobackupex: back to '/usr/local/webserver/mysql5.6/data'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/data/backup/base'
innobackupex: back to original InnoDB log directory '/usr/local/webserver/mysql5.6/data'
innobackupex: Finished copying back files.
160201 16:23:08  innobackupex: completed OK!
  查看目录
# ll /usr/local/webserver/mysql5.6/data/  #这个目录下的数据已经恢复回来
total 79916
drwxr-x---. 2 root root   4096 Feb  1 16:18 db01
-rw-r-----. 1 root root 79691776 Feb  1 16:18 ibdata1
drwx------. 2 root root   4096 Feb  1 15:26 mysql
drwxr-xr-x. 2 root root   4096 Feb  1 15:26 performance_schema
drwxr-x---. 2 root root   4096 Feb  1 16:13 sharelinux
drwxr-xr-x. 2 root root   4096 Feb  1 15:26 test
-rw-r--r--. 1 root root     22 Feb  1 16:18 xtrabackup_binlog_info
-rw-r-----. 1 root root     91 Feb  1 16:18 xtrabackup_checkpoints
-rw-r--r--. 1 root root    722 Feb  1 16:18 xtrabackup_info
-rw-r-----. 1 root root  2097152 Feb  1 16:09 xtrabackup_logfile
drwx------. 2 root root  12288 Feb  1 15:26 zabbix
#chown mysql:mysql /usr/local/webserver/mysql5.6/data/ -R  #将目录更改为mysql用户
# pkill mysql                        #杀死原来的mysql进程,然后重启数据库
# service mysqld start
Starting MySQL..                       [  OK  ]
  数据已经恢复回来了
mysql> show databases;
+--------------------+
| Database       |
+--------------------+
| information_schema |
| db01         |
| mysql        |
| performance_schema |
| sharelinux     |
| test         |
| zabbix       |
+--------------------+
7 rows in set (0.14 sec)
mysql> select * from sharelinux.t1;   #第一次增量备份的数据
+------+----------+
| id   | name   |
+------+----------+
|  1 | zhangsan |
|  2 | lisi   |
|  3 | wangwu   |
+------+----------+
3 rows in set (0.03 sec)
mysql> select * from db01.t2;      #第二次增量备份的数据
+------+----------+
| id   | name   |
+------+----------+
|  1 | zhangsan |
|  2 | lisi   |
|  3 | wangwu   |
+------+----------+
3 rows in set (0.08 sec)
  


  
关注下面的标签,发现更多相似文章