Use the Xtrabackup tool to back up and restore (a required tool for MySQLDBA) bitsCN.com
Xtrabackup------ Necessary tools for MySQL DBA
Note:
1) documents reference http://www.percona.com/docs/wiki/percona-xtrabackup:start
2) mysql version 5.1.50 or later.
1. introduction and installation of Xtrabackup
1. Xtrabackup is an open-source project of percona. it supports hot backup of innodb, XtraDB, and MyISAM (locks tables). It can be seen as a free alternative to InnoDB Hotbackup.
Percona Support for MySQL
Benefit |
Silver (Per Server) |
Gold (Unlimited) |
Platinum (Unlimited) |
Annual Price |
$1,500/server |
From $15,000. |
From $30,000. |
Number of Servers Covered |
Per-Server |
Unlimited |
Unlimited |
Number of Support Incidents |
Unlimited |
Unlimited |
Unlimited |
Response Time SLA |
60 Minutes |
30 Minutes |
30 Minutes |
Named Support Contact Persons |
5 |
10 |
20 |
Email, Web, & Chat Support |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
Phone Support |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
Login Support |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
24 × 7 Support |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
Fixes For Verified Bugs |
|
650) this. width = 650; "height = 24> |
650) this. width = 650; "height = 24> |
Hot Bug Fixes |
|
|
650) this. width = 650; "height = 24> |
Onsite System Audit |
|
Option |
650) this. width = 650; "height = 24> |
Covers Old Server Versions |
|
|
650) this. width = 650; "height = 24> |
Covers Non-Standard Platforms |
|
|
Option |
Covers Custom Code |
|
|
Option |
Reference: http://www.percona.com/mysql-support/
First, let's take a look at how to install Xtrabackup. the simplest installation method is to use the RPM Package. However, if you want to use the source code for installation, the installation method is a bit odd, it is installed by patching the MySQL source code.
2. installation:
Wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.4/Linux/binary/i686/
Tar zxf xtrabackup-1.4.tar.gz
Cd xtrabackup-1.4
./Configure
Make
At this point, do not make install. then MySQL will be installed. the correct method is:
Cd innobase/xtrabackup/
Make
Make install
Installation reference: http://www.percona.com/docs/wiki/percona-xtrabackup:installation:from-source
3. in this way, two useful tools will be installed in the/usr/bin directory: xtrabackup and innobackupex.
1) xtrabackup can only back up InnoDB and XtraDB data tables. it supports online hot backup and can back up Innodb data tables without locking. However, this tool cannot operate Myisam engine tables.
2) innobackupex is a script encapsulation that encapsulates xtrabackup and can simultaneously process Innodb and Myisam, but a read lock is required when processing Myisam.
As described above, because the read lock is required for Myisam operations, this will block write operations for online services, and Innodb does not have such restrictions, so the proportion of Innodb table types in the database is greater, it is more advantageous. In actual application, the innobackupex method is generally used directly. There are three main operation methods, as described in the manual:
Usage:
Innobackup [-- sleep = MS] [-- compress [= LEVEL] [-- include = REGEXP] [-- user = NAME]
[-- Password = WORD] [-- port = PORT] [-- socket = SOCKET] [-- no-timestamp]
[-- Ibbackup = IBBACKUP-BINARY] [-- slave-info] [-- stream = tar]
[-- Defaults-file = MY. CNF]
[-- Databases = LIST] [-- remote-host = HOSTNAME] BACKUP-ROOT-DIR
Innobackup -- apply-log [-- use-memory = MB] [-- uncompress] [-- defaults-file = MY. CNF]
[-- Ibbackup = IBBACKUP-BINARY] BACKUP-DIR
Innobackup -- copy-back [-- defaults-file = MY. CNF] BACKUP-DIR
――――――――――――――――――――――――――――――――――
The first command line is to hot back up the mysql database.
The command with the -- apply-log option is to start the mysql service on a backup.
Commands with the -- copy-back option copy data, indexes, and logs from the backup directory to the specified initial location in the my. cnf file.
Xtrabackup can also be used to moving InnoDB tables between servers. For more information, see the official documentation and examples.
Reference link:
1. Documents: http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual
2. Xtrabackup online backup for InnoDB/XTraDB (pdf ):
Http://www.percona.com/ppc2009/PPC2009_xtrabackup.pdf
II. innobackupex and xtrabackup backup details
Note: innobackupex determines the data location of MySQL based on/et/my. cnf.
1. normal backup:
Innobackupex [-- defaults-file =/etc/my. cnf] -- user = root [-- host = 192.168.1.52] [-- password = xxx] [-- port = 3306]/data/back_data/2>/data/back_data/1.log
The backup directory is/data/back_data/. here 2>/data/back_data/1.logis used to redirect the output information in the backup process to 1.log.
Innobackupex-1.5.1