如何使用xtrabackup备份

2025-10-16 22:16:29

1、查看使用的引擎

[root@CMDB ~]# mysql -u root -p

mysql>

mysql> use cmdb;

Database changed

mysql> show table status\G;

*************************** 1. row ***************************

Name: xsmart_news

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 279

mysql> exit

2、下载安装 xtrabackup

https://www.percona.com/downloads/XtraBackup/LATEST/因为我的mysql是5.1的，所以下载了 2.0版本

[root@CMDB ~]# ll

total 107860

-rw-------. 1 root root 1661 Mar 16 2017 anaconda-ks.cfg

-rw-r--r--. 1 root root 80142649 May 11 2017 cmdb.zip

-rw-r--r--. 1 root root 30223 Mar 16 2017 install.log

-rw-r--r--. 1 root root 8792 Mar 16 2017 install.log.syslog

-rw-r--r-- 1 root root 72436 Apr 8 12:44 lrzsz-0.12.20-27.1.el6.x86_64.rpm

-rw-r--r-- 1 root root 13079288 Apr 8 11:53 percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

-rw-r--r--. 1 root root 17076672 Dec 18 2016 Python-2.7.13.tgz

-rw-r--r-- 1 root root 9489 May 12 2017 sign_in

[root@CMDB ~]# rpm -ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing... ########################################### [100%]

1:percona-xtrabackup ########################################### [100%]

3、添加mysql用户到mysql组

[root@CMDB ~]# gpasswd -a mysql mysql

Adding user mysql to group mysql

[root@CMDB ~]#

4、

创建备份存放目录

[root@CMDB ~]# mkdir -p /data/backups

[root@CMDB ~]# chown -R mysql: /data

[root@CMDB ~]#

5、创建备份用户

[root@CMDB ~]# mysql -u root -p

mysql> CREATE USER 'beifenyonghu'@'localhost' IDENTIFIED BY 'bfyhmima';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'beifenyonghu'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql>

如何使用xtrabackup备份

6、允许mysql组成员访问数据文件

[root@CMDB ~]# ll /var/lib/mysql/

total 28684

drwx------ 2 mysql mysql 4096 Apr 8 12:57 cmdb

drwx------. 2 mysql mysql 4096 May 13 2017 cmdb_v2

-rw-rw----. 1 mysql mysql 18874368 Apr 8 12:57 ibdata1

-rw-rw----. 1 mysql mysql 5242880 Apr 8 12:57 ib_logfile0

-rw-rw----. 1 mysql mysql 5242880 May 12 2017 ib_logfile1

drwx------. 2 mysql mysql 4096 May 12 2017 mysql

srwxrwxrwx 1 mysql mysql 0 Apr 8 12:27 mysql.sock

[root@CMDB ~]# chown -R mysql: /var/lib/mysql

[root@CMDB ~]# find /var/lib/mysql -type d -exec chmod 770 "{}" \;

[root@CMDB ~]#

如何使用xtrabackup备份

7、

创建一个全备

[root@CMDB ~]# innobackupex --user=beifenyonghu --password=bfyhmima --no-timestamp /data/backups/xinbeifen

180408 13:14:26 innobackupex: All tables unlocked

180408 13:14:26 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/data/backups/xinbeifen'

innobackupex: MySQL binlog position: filename '', position

180408 13:14:26 innobackupex: completed OK!

[root@CMDB ~]# ll /data/backups/xinbeifen/

total 18468

-rw-r--r-- 1 root root 220 Apr 8 13:14 backup-my.cnf

drwxr-xr-x 2 root root 4096 Apr 8 13:14 cmdb

drwxr-xr-x 2 root root 4096 Apr 8 13:14 cmdb_v2

-rw-r----- 1 root root 18874368 Apr 8 13:14 ibdata1

drwxr-xr-x 2 root root 4096 Apr 8 13:14 mysql

-rw-r--r-- 1 root root 13 Apr 8 13:14 xtrabackup_binary

-rw-r--r-- 1 root root 1 Apr 8 13:14 xtrabackup_binlog_info

-rw-r----- 1 root root 83 Apr 8 13:14 xtrabackup_checkpoints

-rw-r----- 1 root root 2560 Apr 8 13:14 xtrabackup_logfile

[root@CMDB ~]#

如何使用xtrabackup备份

8、创建热备

[root@CMDB ~]# innobackupex --apply-log /data/backups/xinbeifen

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

180408 13:17:39 InnoDB: Starting shutdown...

180408 13:17:44 InnoDB: Shutdown completed; log sequence number 0 1716748

180408 13:17:44 innobackupex: completed OK!

9、备份恢复

[root@CMDB ~]# service mysqld stop # 停止数据库

Stopping mysqld: [ OK ]

[root@CMDB ~]# mkdir /tmp/mysql

[root@CMDB ~]# mv /var/lib/mysql/* /tmp/mysql/

[root@CMDB ~]#

[root@CMDB ~]# innobackupex --copy-back /data/backups/xinbeifen

innobackupex: Starting to copy InnoDB log files

innobackupex: in '/data/backups/xinbeifen'

innobackupex: back to original InnoDB log directory '/var/lib/mysql'

innobackupex: Finished copying back files.

180408 13:43:26 innobackupex: completed OK!

[root@CMDB ~]# chown -R mysql: /var/lib/mysql

[root@CMDB ~]# service mysqld start

Starting mysqld: [ OK ]

[root@CMDB ~]#

如何使用xtrabackup备份

10、报错：

[root@CMDB ~]# innobackupex --user=beifenyonghu --password=bfyhmima --no-timestamp /data/backups/xinbeifen

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

180408 13:07:40 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'beifenyonghu' (using password: YES).

180408 13:07:40 innobackupex: Connected to MySQL server

innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 4754

main::check_server_version() called at /usr/bin/innobackupex line 1541

innobackupex: Error: Built-in InnoDB in MySQL 5.1 is not supported in this release. You can either use Percona XtraBackup 2.0, or upgrade to InnoDB plugin.

[root@CMDB ~]#

原因：XtraBackup-2.2.9 和 MySQL 5.1 不兼容，请安装 XtraBackup-2.0，或者升级mysql

声明：本网站引用、摘录或转载内容仅供网站访问者交流或参考，不代表本站立场，如存在版权或非法内容，请联系站长删除，联系邮箱：site.kefu@qq.com。