Install and use Xtrabackup from Percona to back up MySQL

Install and use Xtrabackup from Percona to back up MySQL

Install and use Xtrabackup from Percona to back up MySQL

1. Install XtraBackup

If it is a 32-bit system, just put the content of xtrabackup0.9.zip in the bin directory of mysql.

You can also compile the database together. Take 5.0.88 as an example:
First download its source code:
Note: It must be compiled with the mysql source code.

# Tar zxvf mysql-5.0.88.tar.gz
# Tar zxvf xtrabackup-0.8-src.tar.gz

Move XtraBackup to the InnoDB source code directory. If MySQL5.1 is used, change the directory to/mysql-5.1. */storage/innobase/
# Music xtrabackup-0.8-src./mysql-5.0.88/innobase/
# Cd mysql-5.0.85/innobase

Patch the InnoDB source code. If MySQL5.1 is used, use fix_innodb_for_backup51.patch.
# Patch-p2 <./xtrabackup-0.8-src/fix_innodb_for_backup.patch

Go to directory mysql-5.0.88
# Cd ..
#./Configure

We only need Make, and do not need Make install
# Make
# Cd innobase/xtrabackup-0.8-src

To compile XtraBackup, MySQL5.1 must use XtraBackup0.9
# Make

After compilation is successful, we will get the executable file xtrabackup, innobackupex-1.5.1
Xtrabackup can back up innodb data tables without locking. However, this tool cannot operate myisam tables.
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.

Ii. Use XtraBackup
Common backup commands are listed here: normal backup, Tar backup, and Tar gzip backup.

1. Normal backup
Backup:
# Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- user = root -- password = pdcss -- databases = pdcss/home/mysql/backup/2>/home/mysql/backup/common. log

Here we use the mysql root User backup with a password of 123. The target directory of the backup (target dir) is/home/mysql/backup/1/innobackup/where the innobackupex-1.5.1 will generate a backup.
For example,/home/mysql/backup/2011-05-05_08-55-57 is the backup generated after I run the script. All the backup files are in the directory 2011-05-05_08-55-57.
Here, 2>/home/mysql/backup/common. log redirects the output information during the backup process to common. log.
The innobackupex-1.5.1 also supports Incremental backup.

So how to restore it? To simulate the restoration process, I first stop MySQL and delete all data files in the MySQL data DIRECTORY.
# Cd/usr/local/mysql/data/pdcss
# Rm-rf *
Recovery:
# Innobackupex-1.5.1 -- apply-log/home/mysql/backup/2011-05-05_08-55-57
# Innobackupex-1.5.1 -- copy-back/home/mysql/backup/2011-05-05_08-55-57

# Cd/usr/local/mysql/data/pdcss
Modify Object Access Permissions
# Chown-R mysql: mysql.

The command innobackupex-1.5.1 needs to specify the specific Backup Directory, apply the backup log first, and then copy back. The backup directory is/home/mysql/backup/2011-05-05_08-55-57.
The innobackupex-1.5.1 determines the location of MySQL Data Based on/et/my. cnf.

2. Package (Tar) backup
Here, it is basically similar to the previous normal backup. You must use the-I parameter to recover and decompress the backup.
Backup:
# Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- user = root -- password = pdcss -- databases = pdcss -- stream = tar/home/mysql/backup/2>/home/mysql/backup/tar. log 1>/home/mysql/backup/tar.tar

Recovery:
Extract and compress the-I parameter.
# Tar-ixvf tar.tar

# Innobackupex-1.5.1 -- apply-log/home/mysql/backup/
# Innobackupex-1.5.1 -- copy-back/home/mysql/backup/

# Cd/usr/local/mysql/data/pdcss
Modify Object Access Permissions
# Chown-R mysql: mysql.

3. tar gzip backup
Backup:
# Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- user = root -- password = pdcss -- databases = pdcss -- stream = tar/home/mysql/backup/2>/home/mysql/backup/gz. log | gzip>/home/mysql/backup/gz.tar.gz
Pipeline is used here | use the innobackupex-1.5.1 as the standard input for gzip. In the restoration process, you only need to use tar-izxvf to decompress the corresponding file and perform the same operations as normal backup.

4. Others
Solve the backup error "innobackupex-1.5.1: fatal error: no 'mysqld' group in MySQL options" problem:
Modify the MySQL configuration file my. cnf as follows:
[Mysqld]
......
# Set the value of datadir
Datadir =/usr/local/mysql/data
......