Use Xtrabackup to back up and restore the mysql database

For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn) I. Xtrabackup Overview: Xtrabackup is a mysql database backup tool provided by percona and supports online hot backup. According to official introduction

For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn) I. Xtrabackup Overview: Xtrabackup is a mysql database backup tool provided by percona and supports online hot backup. According to official introduction

[Root @ client103 ~] # Rpm-ivh percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm

Iv. Description of innobackupex parameters:

Note: The xtrabackup command only backs up data files and does not back up the data table structure (. frm), so when xtrabackup is used for recovery, you must have the corresponding table structure file (. frm), so I personally recommend using the innobackupex Command [this command is equivalent to cold backup, copying the index, Data, structure file of the Data Directory, but there will be a temporary lock table (the time depends on the MyISAM size)]

Parameters are described as follows:

-- Defaults-file: Specifies the location of the my. cnf parameter file [datadir must be specified in this configuration file]

-- Apply-log: Same as the -- prepare parameter of xtrabackup. Generally, after the backup is complete, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not synchronized to the data file. Therefore, the data files are still inconsistent. -- Apply-log is used to roll back uncommitted transactions and synchronize committed transactions to data files so that the data files are consistent.

-- Copy-back: copy the backup data file to the datadir of the MySQL server during data recovery.

-- Remote-host = HOSTNAME: store the backup data to the process server through ssh.

-- Stream = [tar]: backup file output format, which can be obtained from the XtarBackup binary file. when using stream = tar for backup, your xtrabackup_logfile may be temporarily stored in the/tmp directory, xtrabackup_logfile may be large (5 GB +) and may fill your/tmp directory. You can solve this problem by specifying the directory -- tmpdir parameter.

-- Tmpdir = DIRECTORY: When -- remote-host or -- stream is specified, the temporary DIRECTORY for storing transaction logs is tmpdir by default.

-- Redo-only -- apply-log: only redo logs are forced to be backed up. rollback is skipped, which is necessary for Incremental backup.

-- Use-memory = *: this parameter is used when prepare is used to control the memory used by the innodb instance during prepare.

-- Databases = LIST: lists the databases to be backed up. If this parameter is not specified, all databases including MyISAM and InnoDB tables will be backed up.

-- Slave-info: Backup slave database. An xtrabackup_slave_info file will be generated in the -- slave-info Backup Directory. The main log file and offset will be saved here. The file content is similar: change master to MASTER_LOG_FILE = '', MASTER_LOG_POS = 0

-- Socket = SOCKET: Specifies the location of mysql. sock so that the backup process can log on to mysql.

5. Use the innobackupex command to back up the database:

1. Full backup and recovery:

# Specify to back up a database [root @ client103 ~] # Innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306 -- databases = a/tmp/backup/>/tmp/backup/innoback. log 2> & 1 # Back up all databases [root @ client103 backup] # innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306/tmp/backup/>/tmp/backup/innoback. log 2> & 1 # Note: The data directory needs to be cleared during restoration. Therefore, if backup is recommended, perform full-Database Backup # After the backup is complete, apply the log, is the backup data to be consistent [/tmp/backup/2014-01-11_14-46-21/: for backup Storage location] [root @ client103 ~] # Innobackupex -- apply-log/tmp/backup/2014-01-11_14-46-21/# restore the database using the backup just now [root @ client103 ~] # Innobackupex -- copy-back/tmp/backup/2014-01-11_14-46-21/### back up data to a remote machine [ssh here requires key synchronization on both sides without a password, otherwise, this method cannot be implemented. This method should not be used. It is to be tested] ## [root @ client103 ~] # Innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306 -- databases = a -- stream = tar/tmp/backup/| ssh root@192.168.1.100 cat ">"/tmp/backup

2. Incremental backup and recovery:

# Full backup [root @ client103 ~] # Innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306/tmp/backup/>/tmp/backup/innoback. log 2> & 1 # Full-Backup Incremental Backup [root @ client103 ~] # Innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306 -- incremental-basedir =/tmp/backup/full backup file name/tmp/backup/# incremental backup Based on incremental backup [root @ client103 ~] # Innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306 -- incremental-basedir =/tmp/backup/incremental backup file name/tmp/backup/# backup application log, ensure data consistency # Full backup application log [root @ client103 ~] # Innobackupex -- apply-log/tmp/backup/2014-01-11_15-37-31/# After the application logs are applied for the first Incremental backup, the logs are merged to the full backup, use full backup for recovery [root @ client103 ~] # Innobackupex -- apply-log/tmp/backup/2014-01-11_15-37-31/-- incremental-dir =/tmp/backup/2014-01-11_15-45-06/# merge the second incremental backup application log to full backup [root] @ client103 ~] # Innobackupex -- apply-log/tmp/backup/2014-01-11_15-37-31/-- incremental-dir =/tmp/backup/2014-01-11_15-46-33/# at this time, the two incremental backups are actually merged to the full backup, to recover a database, you only need to use full backup for recovery. # simulate a data failure. [Delete the database data directory and run the following command to restore the database:] [root @ client103 ~] # Innobackupex -- copy-back/tmp/backup/2014-01-11_15-37-31/# The default data permission after restoration is incorrect. Therefore, you need to modify the data directory permission, as shown in [root @ client103 ~] # Chown-R mysql: mysql/var/lib/mysql/# after the data recovery is complete, restart the Database Service and perform the test. If you have any questions, leave a message!

3. Briefly describe the backup and recovery steps:

(1). Backup:

A. Full backup

B. Multiple incremental backups based on full backup

(2). Recovery:

A. Full backup

B. Multiple incremental backups based on full backup

C. Full backup of application logs to ensure data consistency

D. Merge multiple incremental backups to full backup

E. Use full backup to restore Data

F. After the restoration is completed, modify the permissions of the data directory.

G. Restart the mysql service.

This article is from the blog "children without umbrellas must be running hard". Please keep this source