MySQL Hot Standby tool-xtrabackup-1.5

MySQL can certainly use its own dual-machine hot backup, but only one server can use dual-machine hot backup. Here we will talk about the hot backup tool xtrabackup

Xtrabackup-1.5http: // download.csdn.net/detail/zzaihl/4104181

Xtrabackup is an open-source project of percona that supports hot backup of InnoDB, xtradb, and MyISAM(Locks the table)

Official Website http://www.percona.com/docs/wiki/percona-xtrabackup:start

1. Tool Installation

After extracting the file, install two useful tools in the directory: xtrabackup, innobackupex-1.5.1:

Xtrabackup can back up InnoDB data tables without locking. However, this tool cannot operate on MyISAM.
The innobackupex-1.5.1 is a script encapsulation that can process both 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. The actual application is generally the direct use of innobackupex-1.5.1 method, it mainly has three kinds of operation methods, according to the manual introduction:

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

(1) The first command line is hot backup of the MySQL database.
(2) The command with the -- apply-log option is to start the MySQL service on a backup.
(3) 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.

The following describes the backup and recovery commands.

2. Back up the database

1. Normal backup:
Innobackupex-1.5.1 [-- defaults-file =/etc/My. CNF]-- User = root [-- Host = 192.168.1.52] [-- Password = xxx] [-- Port = 3306]/Data/back_data/

For example, back up the database to/home/MySQL/back.

Export Path = $ path:/home/xtrabackup-1.5/bin

/Home/xtrabackup-1.5/bin/innobackupex-1.5.1 -- user = MySQL -- Password = MySQL/home/MySQL/back

Note that you must set the environment variable before using the command.

3. Restore the database

Stop the database and delete all database files in the database directory.

Service MySQL stop
CD/data/mysql_data
Rm-RF *   # Deleting all files in the Data Directory

/Opt/gjnp/xtrabackup-1.5/bin/innobackupex-1.5.1 -- copy-back/home/MySQL/back/backfile-Dir

By default, the innobackupex-1.5.1 stores binary log information in the xtrabackup_binlog_info file (to facilitate slave ).
CD/Data
Chown-r MYSQL: MySQL mysql_data/
Service MySQL start