xtrabackup
查看使用的引擎[root@CMDB ~]# mysql -u root -pmysql> mysql> use cmdb;Database changedmysql> show table status\G;*************************** 1. row *************************** Name: xsmart_news Engine: InnoDB Version: 10 Row_format: Compact Rows: 279mysql> exit
下载 安装 xtrabackuphttps://www.percona.com/downloads/XtraBackup/LATEST/因为我的mysql是5.1的，所以下载了 2.0版本[root@CMDB ~]# lltotal 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: NOKEYPreparing... ########################################### [100%] 1:percona-xtrabackup ########################################### [100%]
添加mysql用户到mysql组[root@CMDB ~]# gpasswd -a mysql mysqlAdding user mysql to group mysql[root@CMDB ~]#
创建备份存放目录[root@CMDB ~]# mkdir -p /data/backups[root@CMDB ~]# chown -R mysql: /data[root@CMDB ~]#
创建备份用户[root@CMDB ~]# mysql -u root -pmysql> 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>
允许mysql组成员访问 数据文件[root@CMDB ~]# ll /var/lib/mysql/total 28684drwx------ 2 mysql mysql 4096 Apr 8 12:57 cmdbdrwx------. 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_logfile1drwx------. 2 mysql mysql 4096 May 12 2017 mysqlsrwxrwxrwx 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 ~]#
创建一个全备[root@CMDB ~]# innobackupex --user=beifenyonghu --password=bfyhmima --no-timestamp /data/backups/xinbeifenInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.180408 13:14:26 innobackupex: All tables unlocked180408 13:14:26 innobackupex: Connection to database server closedinnobackupex: 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.cnfdrwxr-xr-x 2 root root 4096 Apr 8 13:14 cmdbdrwxr-xr-x 2 root root 4096 Apr 8 13:14 cmdb_v2-rw-r----- 1 root root 18874368 Apr 8 13:14 ibdata1drwxr-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 ~]#
创建热备[root@CMDB ~]# innobackupex --apply-log /data/backups/xinbeifenInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyxtrabackup: starting shutdown with innodb_fast_shutdown = 1180408 13:17:39 InnoDB: Starting shutdown...180408 13:17:44 InnoDB: Shutdown completed; log sequence number 0 17 13:17:44 innobackupex: completed OK!
备份恢复[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/xinbeifeninnobackupex: Starting to copy InnoDB log filesinnobackupex: 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 startStarting mysqld: [ OK ][root@CMDB ~]#
报错：[root@CMDB ~]# innobackupex --user=beifenyonghu --password=bfyhmima --no-timestamp /data/backups/xinbeifenInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p180408 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 serverinnobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 4754 main::check_server_version() called at /usr/bin/innobackupex line 1541innobackupex: 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