mysql的物理备份_MySQL物理备份基本操作

Ⅰ、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,