Ⅰ、xtrabackup介绍
xtrabackup只能备份innodb引擎的数据,不能备份表结构,percona开源的,强烈推荐最新版本(旧版本bug多)
innobackupex可以备份myisam和innodb两种引擎的数据和表结构,一般用这个
备份时,默认读取MySQL配置文件(datadir)
Ⅱ、xtrabackup安装使用
2.1 安装
[root@VM_0_5_centos src]# yum install perl-DBD-MySQL
不安装这个备份会报错:Failed to connect to MySQL server: DBI connect
[root@VM_0_5_centos src]# cd /usr/local/src
[root@VM_0_5_centos src]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
[root@VM_0_5_centos src]# tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C ..
添加环境变量
[root@VM_0_5_centos src]# cd ..
[root@VM_0_5_centos src]# ln -s percona-xtrabackup-2.4.7-Linux-x86_64/ xtrabackup
[root@VM_0_5_centos src]# echo "PATH=/usr/local/xtrabackup/bin:$PATH" >> /etc/profile
[root@VM_0_5_centos src]# source /etc/profile
2.2 玩一手
[root@VM_0_5_centos src]# innobackupex --compress --compress-threads=8 --stream=xbstream -S /tmp/mysql.sock --parallel=4 /data/backup > backup.xbstream
建议用-S连接,默认走socket,不用-S可能报连不上
常用参数:throttle
指定备份时用到的iops是多少,限制速度
8个压缩线程,4个备份线程
输出内容(简化)
180122 19:47:53 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!".
180122 19:47:53 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
180122 19:47:53 version_check Connected to MySQL server
180122 19:47:53 version_check Executing a version check against the server...
180122 19:47:53 version_check Done.
180122 19:47:53 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.20-log
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: uses posix_fadvise().
# 连接数据库并做两次版本检查
xtrabackup: cd to /mdata/mysql_test_data
xtrabackup: open files limit requested 0,