MySQL Backup tool xtraback full-volume and incremental testing

ysql backup tool xtraback full and incremental testing

 

xtrabackup is an open source project of percona, can hot backup innodb, XtraDB, and MyISAM (will lock the table)

 

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

 

    Xtrabackup is an open source software developed by percona, this software can be said to be an open source alternative to innodb hot backup tool ibbackup. This software is composed of 2 parts: xtrabackup and innobackupex. Xtrabackup is specially used for innodb engine and xtraDB engine; and innobackupex is specially used for myisam and innodb engine, and mixed-use engines.

1. Installation

1. RPM installation:

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/RPM/rhel5/x86_64/percona-xtrabackup-debuginfo-2.0.0-417.rhel5.x86_64.rpm

rpm -ivh --nodeps percona-xtrabackup-debuginfo-2.0.0-417.rhel5.x86_64.rpm

--Nodeps (does not check the dependencies between software), because installing xtrabackup requires mysql_client, but mine is installed via source code. So the environment variables should be set correctly

For example, export PATH = $ PATH: / usr / local / mysql / bin If mysql is not set in PATH, an error will be reported.

2. Download the binary source package:

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/binary/Linux/x86_64/percona-xtrabackup-2.0.0.tar.gz

tar -zxvf percona-xtrabackup-2.0.0.tar.gz

mv percona-xtrabackup-2.0.0 / usr / loca / xtrabackup

3. Use source code to install

If you want to use source code to install, you will find that the installation method is a bit weird, because it uses a patched build on the MySQL source code.

tar zxf xtrabackup-0.8.tar.gz
cd xtrabackup-0.8
./configure
make
When proceeding here, don't make inertia and then make install, then MySQL will be installed. The correct way is to continue:
cd innobase / xtrabackup /
make
make install

Check the version number:

mysqld configuration file: (backup is necessary when there are multiple instances)

[mysqld]
innodb_data_home_dir = / www / mysqldata / ibdata #Data used to store files
innodb_log_group_home_dir = / www / mysqldata / iblogs
innodb_data_file_path = ibdata1: 10M; ibdata2: 10M: autoextend
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
Note: There are no multiple instances in the text test, the default my.cnf is used.

 

Second, xtrabackup backup and recovery

 

    Xtrabackup has two main tools: xtrabackup, innobackupex. xtrabackup can only backup InnoDB and XtraDB data tables, support online hot backup, will not lock the table innobackupex encapsulates xtrabackup, and can also backup MyISAM data tables, if you have innodb and myisam storage engines in your database, you can only use innobackupex Backup.
* xtrabackup-a tool for hot backup innodb, xtradb tables, can not backup other tables.
* innobackupex-Perl script encapsulated in xtrabackup, provides the ability to backup myisam tables. (Can back up the entire library and data tables). This article aims to introduce the practice of incremental backup of database by Xtrabackup tool.

1. Innobackupex-1.5.1 backup

 

When implementing a backup, you need to perform it online while the database is running, and first establish a path for the backup in advance.

innobackupex [--defaults-file = / etc / my.cnf] –user = root [--host = 192.168.10.198] [--password = xxx] [--port = 3306] / www / backup / 2> / www / backup / backup.log
parameter:

/ www / backup / #Backup directory

2> /www/backup/backup.log #Redirect the output information during the backup process to /www/backup/backup.log

–Slave-info #The replication point of the replication master log will be recorded, which is convenient for re-copying. (Used in backup slave machine)

xtrabackup_binlog_info #Store binlog information. (Binlog needs to be copied and backed up, if binlog is needed)

xtrabackup_checkpoints #Store the start and end positions of the backup.

–-Stream = tar #Tell xtrabackup that the backup needs to be output as a tar file stream.

–-Include = ‘test’ #Back up the included library tables, for example: means to back up all the tables in the test library. If you need a full backup, omit this parameter; if you need to back up the 2 tables under the test library: tableA & tableB, write: –-include = 'test.tableA | test.tableB'; then if there are only 2 under the test library The table prefix is table, you can also write: –-include = 'test.table *'.

–-Throttle = 500 #xtrabackup During the backup process, there are many IO operations, so you need to limit the IO operations. In order to avoid too much pressure on the server, but it seems that the effect is not obvious, do not know whether it is set or too large. To be tested ...

–-Socket = / data / mysql / backup / mysql.sock #Specify the location of mysql.sock so that the backup process can log in to mysql

--apply-log

--redo-only

 

(1), complete data backup

E.g:

mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| information_schema |
| jss |
| my_db |
| mysql |
| test |
+ -------------------- +
5 rows in set (0.00 sec)
Full backup:

innobackupex --defaults-file = / etc / my.cnf / www / backup /
A directory according to the current date and time will be generated under the / www / backup / directory, such as 2012-07-18_15-05-06. When restoring, be sure to shut down the server first, and clear the datadir directory first. After the data must be reset the permissions of the directory.

Simulate the fault, the specific operation is as follows:

/ usr / local / mysql5 / bin / mysqladmin -uroot shutdown

mv / www / mysqldata / tmp /

mkdir / www / mysqldata

Apply full backup:

innobackupex --apply-log / www / backup / 2012-07-18_15-05-06 /
 

 

innobackupex --copy-back / www / backup / 2012-07-18_15-05-06 /
 

ln -s / usr / local / mysql5 / share / english / www / mysqldata / mysql / english #Restore the initial directory tree, otherwise it will not start

chown -R mysql.mysql / www / mysqldata

/ usr / local / mysql5 / bin / mysqld_safe --user = mysql &

 

Tip, you can directly compress during backup to save disk space:

innobackupex --defaults-file = / etc / my.cnf --stream = tar / www / backup / | gzip> /www/backup/compress/mysql_backup.tar.gz
However, note that decompression needs to be performed manually, and the -i parameter is added, otherwise all files cannot be decompressed.

tar zxfi /www/backup/compress/mysql_backup.tar.gz
innobackupex --apply-log / www / backup / compress
innobackupex --copy-back / www / backup / compress
(2) Innobackupex incremental backup for a certain library:

 

    Incremental backups are only for engines such as InnoDB that support transactions, and engines such as MyISAM are still fully prepared.

Full backup:

innobackupex-1.5.1 --defaults-file = / etc / my.cnf --databases = "jss" / www / backup / full /
 

Log in to the database and create a few new objects:

mysql> use jss;
Database changed
mysql> show tables;
+ --------------- +
| Tables_in_jss |
+ --------------- +
| j1 |
+ --------------- +
1 row in set (0.00 sec)
mysql> truncate table j1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table ja (id int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into ja values (1);
Query OK, 1 row affected (0.01 sec)
Do the first incremental backup on the full backup directory

innobackupex-1.5.1 --defaults-file = / etc / my.cnf --databases = "jss" --incremental --incremental-basedir = / www / backup / full / 2012-07-18_19-47-49 / / www / backup / inc

If the default storage engine is based on MyISAM, the files backed up during incremental backup are .frm, .MYD, and .MYI.

If the default storage engine is based on InnoDB, then only incremental backups will be .frm files, and my configuration file uses independent tablespaces.

Incremental files will be generated under / www / backup / rec /:

[[email protected] jss] # cd / www / backup / inc / 2012-07-18_19-55-17 /

[[email protected] 2012-07-18_19-55-17] # ls -l

total 384

-rw-r--r-- 1 root root 250 Jul 18 19:55 backup-my.cnf

-rw-r ----- 1 root root 360448 Jul 18 19:55 ibdata1.delta

-rw-r ----- 1 root root 18 Jul 18 19:55 ibdata1.meta

drwxr-xr-x 2 root root 4096 Jul 18 19:55 jss

-rw-r--r-- 1 root root 13 Jul 18 19:55 xtrabackup_binary

-rw-r--r-- 1 root root 26 Jul 18 19:55 xtrabackup_binlog_info

-rw-r ----- 1 root root 81 Jul 18 19:55 xtrabackup_checkpoints

-rw-r ----- 1 root root 2560 Jul 18 19:55 xtrabackup_logfile.

[[email protected] 2012-07-18_19-55-17] # cd jss /

[[email protected] jss] # ls -l

total 28

-rw-r--r-- 1 mysql mysql 61 Jul 18 19:18 db.opt

-rw-r--r-- 1 mysql mysql 8556 Jul 18 19:18 j1.frm

-rw-rw ---- 1 mysql mysql 8556 Jul 18 19:53 ja.frm


note: This process only affects XtraDB or InnoDB-based tables, other tables with different storage engines will be completely copied when the incremental backup appears

The recovery process also requires prepare
First, commit the transaction must re-execute each backup
Then, uncommitted transactions must be rolled back

Simulated failure

rm -rf / www / mysqldata / jss

Restore the data from the full backup to the database

innobackupex-1.5.1 --defaults-file = / etc / my.cnf --databases = "jss" --apply-log / www / backup / full / 2012-07-18_19-47-49 /
 

The first incremental backup is applied to the full backup

innobackupex-1.5.1 --defaults-file = / etc / my.cnf --databases = "jss" --apply-log / www / backup / full / 2012-07-18_19-47-49 / --incremental- dir = / www / backup / inc / 2012-07-18_19-55-17 /
 

You will see a new file in the full backup directory:

 

After analysis, it turned out that when preparing, XtraBackup did not copy the incremental backup set. The .frm file belonging to the newly created object was copied to the complete backup set directory. Therefore, when copy-back is executed, this part of the file is naturally not copied back to data file path. Only manually restore to datadir.

Copy to the datadir directory

cp -rp 2012-07-18_19-47-48jss / www / mysqldata / jss

Enter datadir to view the file:

[[email protected] backup] # cd / www / mysqldata

[[email protected] mysqldata] # ls

ibdata1 ib_logfile0 ib_logfile1 jss my_db mysql test xtrabackup_binlog_pos_innodb xtrabackup_checkpoints

[[email protected] mysqldata] # chown -R mysql.mysql / www / mysqldata

Restart mysql

 

 

2. Detailed explanation of xtrabackup backup

 

xtrabackup only backs up InnoDB data files, the table structure is not backed up, so when restoring, you must have a corresponding table structure file (.frm)
 mysql
 use jss;
 alter table j1 engine = innodb;

(1) Full backup and restore

(Using xtrabackup, only InnoDB and xtradb tables, and note that "default_table_type = InnoDB" in the mysql configuration file my.cnf otherwise unsuccessful).

xtrabackup --defaults-file = / etc / my.cnf --backup --target-dir = / www / backup /
Performed twice:

xtrabackup --defaults-file = / etc / my.cnf --prepare --target-dir = / www / backup /
xtrabackup --defaults-file = / etc / my.cnf --prepare --target-dir = / www / backup /
Stop the database and delete the ib * files (all beginning with ib) under the database.

Copy the ib * files in the / www / backup / directory to the database directory.

  Setting permissions:

#chown mysql: mysql ib *

After restarting the database, test whether it is successful.

[[email protected] backup] # cp ib * / www / mysqldata /

[[email protected] backup] # chown mysql.mysql / www / mysqldata / ib *

[[email protected] backup] # / usr / local / mysql / bin / mysqld_safe --user = mysql &

[1] 1695

[[email protected] backup] # 120719 15:56:19 mysqld_safe Logging to ‘/www/mysqldata/localhost.localdomain.err’.

120719 15:56:19 mysqld_safe Starting mysqld daemon with databases from / www / mysqldata

 

[[email protected] backup] # mysql

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 1

Server version: 5.1.57-log MySQL Community Server (GPL)

 

Type ‘help;’ or ‘\ h’ for help. Type ‘\ c’ to clear the buffer.

 

mysql> use jss;

Database changed

mysql> select * from j2;

+ ------ +

| id |

+ ------ +

| 2 |

+ ------ +

1 row in set (0.01 sec)

 

Note that xtrabackup only backs up data files, not data table structure (.frm), so when using xtrabackup to restore, you must have a corresponding table structure file (.frm).

 

(2) Incremental backup and recovery

    Again, the increment of xtrabackup is limited to InnoDB and xtradb tables, and note that "default_table_type = InnoDB" in the mysql configuration file my.cnf is otherwise unsuccessful.

Before doing the increment, of course, you need to make a full backup, and then increase the total amount.

First make a full backup.

xtrabackup --defaults-file = / etc / my.cnf --backup --target-dir = / www / backup /
Incremental based on full backup.

xtrabackup --defaults-file = / etc / my.cnf --backup --target-dir = / www / inc / 1 --incremental-basedir = / www / backup /
(Note: / www / inc / 1 / needs to be modified every time. For example, the second increment will be changed to / www / inc / 2. Of course, you can write a script for automatic backup. The backup script will be provided one after another. .... the same when recovering)

Incremental recovery. (The steps are the same as full recovery, only one more step in the middle when executing the recovery command)

xtrabackup --defaults-file = / etc / my.cnf --prepare --target-dir = / www / backup /
xtrabackup --target-dir = / www / backup / --prepare --incremental-dir = / www / inc / 1
xtrabackup --defaults-file = / etc / my.cnf --prepare --target-dir = / www / backup /
  Stop the database and delete the ib * table file in / www / mysqldata / ibdata under the database. Copy the ib * files in the / www / backup / directory to the database directory / www / mysqldata / ibdata.

Setting permissions:

#chown mysql: mysql ib *

Test after restart. whether succeed.




Note: Before backing up, log in to the database, open a table to see the number of records, and then back up, then modify the final records of the table, and then back up and then test.

 

3. Innobackupex-1.5.1 combined with xtrabackup

Backup strategy:

The entire database backup directly uses the innobackupex-1.5.1 script.

For InnoDB or xtradb tables, use xtrabackup directly for backup.

    Since xtrabackup cannot back up the table structure, first use the innobackupex-1.5.1 script to back up the entire database of the database when backing up. Then use xtrabackup for table backup. Separately. The recovery is also performed separately during recovery. If a table structure loss occurs, first use innobackupex-1.5.1 to recover, and then use xtrabackup to recover the incremental data. Overwrite the ib * file restored with innobackupex-1.5.1 with the generated file ib *.

================================================== =====================================

First, innobackupex-1.5.1 full backup:

innobackupex --defaults-file = / etc / my.cnf / www / backup / full / 2> /www/backup//1.log
# Will generate a time folder, here is 2012-7-20_15-57-44

Then, xtrabackup makes incremental backups:

innobackupex-1.5.1--defaults-file = / etc / my.cnf --incremental --incremental-basedir = / www / backup / full / 2012-7-20_15-57-44 / / www / backup / inc / 1
restore:

First stop the database, back up the binary log (if any), and then delete all database files in the database directory.

cd / www / mysqldata

rm -rf * # Delete all files in the data directory

Restore full backup:

innobackupex-1.5.1 --apply-log / www / backup / full / 2012-7-20_15-57-44 / # application log
innobackupex-1.5.1 --copy-back / www / backup / full / 2012-7-20_15-57-44 / # copy file
Restore incremental backup:

xtrabackup --prepare --target-dir = / www / backup / full / 2012-7-20_15-57-44 --incremental-dir = / www / backup / inc / 1
Copy the ib * files under the / www / backup / directory to the database directory

Restart the mysql service.

 

Reprinted: http://blog.chinaunix.net/uid-25266990-id-3314699.html

Mysql backup tool xtraback full and incremental testing