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

Brief introduction of 1pm xtrabackup

For database backup and backup tools, refer to: http://blog.itpub.net/26230597/viewspace-1460065/, here to describe how xtrabackup has used xtrabackup to restore a full backup of the database.

Download 2Magnextrabackup

Download address: http://www.percona.com/downloads/XtraBackup/

Under Chorm browser, under "DOWNLOADPERCONA XTRABACKUP", select the version number and os version. Some browsers cannot be opened, and "No files found with current filters." may appear. Then you need to open it with another browser, such as chrome, as shown in figure 1.png below:

You can also download it directly under linux by using wget:

Wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.9/binary/tarball/percona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

3Query Xtrabackup installs 3.1Configuring datadir

First, whether datadir has been configured under check, check whether there is a mysql service, and if so, set datadir in the my.cnf configuration file.

[root@data01 ~] # more / etc/my.cnf | grepdatadir

[root@data01 ~] #

Ok, if you see that datadir is not configured, configure it in my.cnf.

Vim my.cnf

Datadir = / home/data/mysql/data

Then under check:

[root@data01 ~] # more / etc/my.cnf | grepdatadir

Datadir = / home/data/mysql/data

[root@data01 ~] #

[root@data01 ~] #

3.2 decompression

Tar-xvfpercona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

3.3 copy to / usr/bin/

Cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/innobackupex / usr/bin/innobackupex

Cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/xtrabackup / usr/bin/xtrabackup

Cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/xbcrypt / usr/bin/xbcrypt

Cp. / percona-xtrabackup-2.2.9-Linux-x86_64/bin/xbstream/usr/bin/xbstream

PS: innobackupex-1.5.1 is no longer available in the new version.

3.4 check to see if xtrabackup is available

[root@data01 ~] # innobackupex-- help | more

Options:

-- apply-log

Prepare a backup in BACKUP-DIR by applying the transaction log file

Named "xtrabackup_logfile" located in the same directory.Also

Create new transaction logs. The InnoDB configuration is read from

The file "backup-my.cnf".

-- backup-locks

This option controls if backup locks should be used instead of FLUSH

TABLES WITH READ LOCK on the backup stage. The option has no effect

When backup locks are not supported by the server. This option is

Enabled by default, disable with-- no-backup-locks.

3.5 installation in source mode

Reference: http://www.percona.com/doc/percona-xtrabackup/2.2/installation/compiling_xtrabackup.html

4, two major tool components

Xtrabackup: support innodb storage engine table, xtradb storage engine table

Innobackupex: support innodb storage engine table, xtradb storage engine table, myisam storage engine table.

5, some important parameters

-- defaults-file

Same as xtrabackup-- defaults-file parameter

-- apply-log

Encapsulation of xtrabackup-- prepare parameters

-- copy-back

Copy the backup data files to the datadir of the MySQL server during data recovery

-- remote-host=HOSTNAME

Store backup data on the process server through ssh

-- stream= [tar]

Backup file output format, tar using tar4ibd, the file can be obtained in the XtarBackup binary file. If-stream=tar is specified at the time of the backup, the directory where the tar4ibd file is located must be in $PATH (because you are using tar4ibd unzipping, which is available in XtraBackup's binary package).

In the use of parameter stream=tar backup, your xtrabackup_logfile may be temporarily placed in the / tmp directory, if you backup and write large xtrabackup_logfile may be very large (5G +), it is likely to fill your / tmp directory, you can use the parameter-tmpdir to specify the directory to solve this problem.

-- tmpdir=DIRECTORY

When-- remote-host or-- stream is specified, the directory where the transaction log is temporarily stored defaults to the temporary directory tmpdir specified in the MySQL configuration file

-- redo-only--apply-log group

Only redo is forced to back up the log, skipping rollback. This is necessary when doing incremental backups.

-- use-memory=#

This parameter is used in prepare to control the amount of memory used by the innodb instance during prepare to replace the buffer_pool_ size value in my.cnf. If you are a stand-alone server and the memory is large enough, you can increase the parameter-- use-memory in order to speed up the efficiency of backup and recovery.

-- throttle=IOS

Same as xtrabackup-- throttle parameter

-- sleep= is used by ibbackup to specify how many milliseconds the process stops copying each time 1m of data is backed up. It is also to minimize the impact on normal business during backup. For more information, please see the manual of ibbackup.

-- compress [= LEVEL]

Compression of backup data rows, which only supports ibbackup,xtrabackup, has not been implemented yet

-- include=REGEXP

The encapsulation of the xtrabackup parameter, tables, also supports ibbackup. Backup contains library tables, for example:-- include= "test.*", which means backing up all tables in the test library. If you need a full backup, omit this parameter; if you need to back up the two tables under the test library: test1 and test2, write:-- include= "test.test1 | test.test2". You can also use wildcards, such as:-- include= "test.test*".

-- databases=LIST

List the databases that needs to be backed up. If this parameter is not specified, all database containing MyISAM and InnoDB tables will be backed up

-- uncompress

Decompress the backup data file and support ibbackup,xtrabackup. This function has not been implemented yet.

-- slave-info

Backup slave library, plus-slave-info backup directory will generate an additional xtrabackup_slave_info file, where the master log file and offset will be saved, the file content is similar to: CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

-- socket=SOCKET

Specify the location of the mysql.sock so that the backup process logs in to mysql. Exe.

-- safe-slave-backup

The Slave SQL thread is paused and wait until the backup starts when the temporary table is not open. The SQL thread starts automatically after the backup, which ensures a consistent replication state.

6 full backup 6.1 full backup

Backup only, no compression:

Xtrabackup-defaults-file=/etc/my.cnf--user=root-password= ""-port=3306-backup--target-dir=/data/backups/mysql/$ (date +% Y%m%d) /

Backup compression:

Xtrabackup-defaults-file=/etc/my.cnf--user=root-password= ""-port=3306-backup-socket=/usr/local/mysql/mysql.sock--stream=tar-target-dir=/data/backups/mysql/$ (date +% Y%m%d) / | gzip > alldbfullbackup.tar.gz

Reference: http://www.percona.com/doc/percona-xtrabackup/2.2/howtos/recipes_ibkx_compressed.html

The backup process is as follows:

7.1 full backup

Innobackupex-defaults-file=/etc/my.cnf--user=root-password= ""-target-dir=/data/backups/mysql/$ (date+%Y%m%d) /-- no-timestamp-- parallel=2

The backup process is as follows:

Xtrabackup backup and recovery tool is more suitable for data growth databases. For data growth libraries, the space and time of data backup and recovery are under great pressure due to the growth of data. Xtrabackup also has the function of incremental backup, which can ensure the security of data in a short period of time. In the long run, full-library backup is still needed intermittently. In addition, because xtrabackup does not lock innodb's database, it is suitable for data backup and recovery that do not affect online services. However, for the newer database with no obvious increase in the amount of data and mainly updated, xtrabackup appears to be too complex. The performance of xtrabackup operation is not as high as that of mysqldump.

-

Original blog address: http://blog.itpub.net/26230597/viewspace-1465772/

Original author: yellow fir (mchdba)

-

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.