Shulou(Shulou.com)06/01 Report--

Brief introduction:

Xtrabackup is a mysql database backup tool provided by percona, which can do hot backup for Innodb database.

Features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) Fast reduction speed.

Preparatory work:

1. Install xtrabackup (download the rpm package on percona's official website)

# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

two。 Create a minimum privilege user

MariaDB [(none)] > grant reload,lock tables,replication client on *. * to 'backupuser'@'localhost' identified by' backuppassword'

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.04 sec)

Start backup: (simulate online environment)

1. Full backup (the backup directory generated is 2015-04-09-02-35-22)

# innobackupex-user=backupuser-password=backuppassword / backup/

The backup is successful if the following is displayed:

150409 02:35:25 innobackupex: Connection to database server closed

150409 02:35:25 innobackupex: completed OK!

If the following error message is displayed: add innodb_log_file_size = 5m to the my.cnf file [mysqld]

InnoDB: Error: logfile. / ib_logfile0 is of different size 5242880 bytes

InnoDB: than specified in the .cnf file 50331648 bytes!

Innobackupex: Error: The xtrabackup child process has died at / usr/bin/innobackupex line 2672.

two。 Insert data into the database to simulate the first day of running msyqld on the normal line

MariaDB [(none)] > create database xx

Query OK, 1 row affected (0.00 sec)

3. Perform an incremental backup (generate 2015-04-09 backup 02-59-13, at which time-- incremental-basedir should refer to the directory of the full backup 2015-04-09 backup 02-35-22)

Note: incremental backups can only be applied to InnoDB or XtraDB tables. For MyISAM tables, incremental backups are actually performed as full backups.

# innobackupex-- incremental / backup/-- incremental-basedir=/backup/2015-04-09 / 02-35-22 /

The incremental backup is successful when the following information is displayed:

Innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 324

150409 02:59:18 innobackupex: Connection to database server closed

150409 02:59:18 innobackupex: completed OK!

4. Insert data into the database again to simulate mysqld running on the normal line the next day.

MariaDB [(none)] > create database yy

Query OK, 1 row affected (0.00 sec)

5. Perform an incremental backup again (generate 2015-04-09 backup 03-00-38, at which time-- incremental-basedi should refer to the directory 2015-04-09 backup 02-59-13 where the last incremental backup was made)

# innobackupex-- incremental / backup/-- incremental-basedir=/backup/2015-04-09 / 02-59-13 /

6. Insert data into the database again to simulate the third day of running mysqld on the normal line.

MariaDB [(none)] > create database zz

Query OK, 1 row affected (0.00 sec)

7. Simulate the accident, stop the mysqld, delete the data in the / data directory

Note: online environments should store binary log files (binlog) and data files separately

# service mysqld stop

Note: because I did not store the binary log file and the data separately in this experiment, I copied the binary log to another directory before deleting the data

# cp mysql-bin.000001 / backup/

# rm / data/*-rf

8. View the last incremental backup where the position was last recorded in the binary log

# cat / backup/2015-04-09 / 03-00-38/xtrabackup_binlog_info

Mysql-bin.000001 479

9. Export the binary log, starting with the position that was last backed up

# mysqlbinlog mysql-bin.000001-- start-position= "479" > / backup/binlog.sql

Restore data:

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

There are some differences between prepare incremental backups and collating full backups, especially the following:

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay, all backup data will be merged into the full backup.

(2) "rollback" uncommitted transactions based on all backups.

At this point, back up all files in the directory:

In order: full backup files first incremental backup files second incremental backup files exported partial binary log files all binary log files

1. Organize and merge backup files

# innobackupex-- apply-log-- redo-only / backup/2015-04-09 / 02-35-22 /

Merge the first incremental backup into a full backup

# innobackupex-- apply-log-- redo-only / backup/2015-04-09 / 02-35-22 /-- incremental-dir=/backup/2015-04-09 / 02-59-13 /

Merge the second incremental backup into a full backup

# innobackupex-- apply-log-- redo-only / backup/2015-04-09 / 02-35-22 /-- incremental-dir=/backup/2015-04-09 / 03-00-38 /

Note: if we do a full backup at 00: 00:00 every Sunday night and an incremental backup at 00: 00:00 every Monday-Saturday night, there happens to be a user who has been operating before 00: 00:00 and commits the transaction at 00:01, so in order to avoid the system automatically rolling back the transactions that were not committed at the time of backup, we need to add the-- redo-only parameter to make it only commit the transaction, but not roll back the transaction. In the end, the system will determine which operations need to be rolled back to perform the rollback.

two。 Restore operation

# innobackupex-- copy-back / backup/2015-04-09 / 02-35-22 /

3. Change the master group of the files under the recovered data directory (/ data) to msyql

# chown-R mysql.mysql / data/*

4. Check whether the contents of the database are backed up or restored.

# service mysqld start

You can see that the backed-up data has been restored

5. Recover data that has not been backed up in time through binary logs

# mysql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.