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

1. Percona-xtrabckup installation

Environment: redhat 6. 0

Database: mysql 5.5.28 (compile and install)

1. Download and install it on the official website (download address at the end of the article)

[root@lamp] # rpm-ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

Warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Error: Failed dependencies:

Mysql is needed by percona-xtrabackup-2.0.0-417.rhel6.x86_64

Prompt that mysql is required to install percona-xtrabackup (mysql needs to be installed through yum because the path of compilation and installation is different from the directory where yum is installed, but mysql installed in this way is not enabled)

[root@lamp ~] # yum install mysql

Loaded plugins: refresh-packagekit, rhnplugin

-> Running transaction check

-> Package mysql.x86_64 0RU 5.1.47-4.el6 set to be updated

-> Finished Dependency Resolution

.

Installing:

Mysql x8631 64 5.1.47-4.el6 Server 889k

Transaction Summary

=

Warning: RPMDB altered outside of yum.

Installing: mysql-5.1.47-4.el6.x86_64 1 Compact 1

Installed:

Mysql.x86_64 0RU 5.1.47-4.el6

Complete! # yum installation mysql completed

[root@lamp ~] # rpm-ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm # perform rpm installation again

Warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing... # [100%]

1:percona-xtrabackup # # [100%]

# percona-xtrabackup installation completed

[root@lamp ~] # rpm-ql percona-xtrabackup # View the relevant installation directory (the green directory is the main usage directory and the running program)

/ usr/bin/innobackupex

/ usr/bin/innobackupex-1.5.1

/ usr/bin/xbstream

/ usr/bin/xtrabackup

/ usr/bin/xtrabackup_51

/ usr/bin/xtrabackup_55

/ usr/share/doc/percona-xtrabackup-2.0.0

/ usr/share/doc/percona-xtrabackup-2.0.0/COPYING

/ usr/share/percona-xtrabackup-test

/ usr/share/percona-xtrabackup-test/bootstrap.sh

/ usr/share/percona-xtrabackup-test/disabled

/ usr/share/percona-xtrabackup-test/disabled/ib_include.sh

/ usr/share/percona-xtrabackup-test/disabled/tar_compressed.sh

/ usr/share/percona-xtrabackup-test/disabled/xb_lru_dump.sh

/ usr/share/percona-xtrabackup-test/experimental

.

[root@lamp ~] # innobackupex-- help # View related help

Note:

1. I installed 2.2.9 before, but the problem prompted by perl-DBI,perl-DBD-mysql has not been solved. Just switch to a lower version of xtrabackup.

2. If you are prompted for a mysql sock problem, specify the path to sock in the parameter.

2. Use

Backup

You cannot specify a backup name when backing up. Each backup folder is named after time. There are data files and log files in it. The directory needs to exist. If not, create it first.

1. Full backup

[root@lamp] # innobackupex-- user=root-- password=redhat-- host=127.0.0.1 / backup/

#-- user: specify the mysql user-- password: specify the password of the user-- host: specify the host (if it is local, you can omit this option) / backup: specify the directory to which the backup is going.

* if you want to use a least privileged user for backup, you can create such a user based on the following command:

Mysql > CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY' redhat'; # create user bkpuser backup user and set password through IDENTIFIED BY 'password'.

Mysql > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkpuser'; # revoke cancels all ALL PRIVILEGES authorizations, and the GRANT OPTION authorization option is from the bkpuser user. And put all of bkpuser's

The permission to operate the database is revoked.

Mysql > GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *. * TO 'bkpuser'@'localhost'

# GRANT authorization, RELOAD: permission to reload, LOCK TABLES: permission to lock tables, REPLICATION CLIENT:

Copy the permissions of the client, ON *. *: have permissions for that data, TO: specify which user to authorize.

Mysql > FLUSH PRIVILEGES; # refresh the permission list so that the permissions take effect.

1. Perform a full backup

While backing up, innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints-this file records backup type (such as full or incremental), backup status (if already prepared status) and LSN (log series number) range information. Each InnoDB page (usually 16K in size) contains a

The log series number, that is, LSN,LSN is the system version number of the entire database system, and the LSN associated with each page can indicate this page.

How it has changed recently.

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb-the current position of binary log files and binary log files for InnoDB or XtraDB tables.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used in backup command

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store the backup data.

2. Prepare after performing a full backup:

In general, after a full backup, 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 still handles the inconsistent state at this time.' The main function of 'prepare' is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

The-- apply-log option of the innobackupex command can be used to achieve the above functions, such as the following command

[root@lamp ~] # innobackupex-- apply-log / PATH/TO/BACKUP-DIR # / PATH/TO/BACKUP-DIR is the directory where you just performed a full backup.

If executed correctly, the last few lines of information output are usually as follows:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

170610 17:26:33 InnoDB: Starting shutdown...

170610 17:26:46 InnoDB: Shutdown completed; log sequence number 92036620

170610 17:26:58 innobackupex: completed OK!

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If you have enough memory available, you can allocate more memory to the prepare process.

To improve the speed of its completion.

3. Restore data from a full backup

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all the files related to the data files to the mysql server DATADIR directory, and innobackupex uses backup-my.cnf to obtain information about the DATADIR directory.

4. Use innobackupex for incremental backup

Each InnoDB page contains a LSN message, and the LSN of the relevant page automatically grows whenever the relevant data changes. This is the basis on which the formal InnoDB table can be backed up incrementally, that is, innobackupex is achieved by backing up the pages that have changed since the last full backup.

To achieve the first incremental backup, you can use the following command:

# innobackupex-- user=root-- password=redhat-- host=127.0.0.1-- incremental / backup-- incremental-basedir=BASEDIR #-- user: specify the user,-- password: specify the password,-- host: specify the host to be backed up (if the backup machine can omit this option),-- incremental: specify the directory where the incremental backup is stored,-- incremental-basedir: specify the directory where the full backup is located.

[root@lamp] # innobackupex-user=root-password=redhat--

Host=127.0.0.1-- incremental / backup/-- incremental-basedir=/backup/2017-06-09 / 16-34-35 /

* after executing the incremental backup command, the innobackuppex command creates a new time-named directory in the / backup directory to store all incremental backup data. In addition, when performing an incremental backup again after performing an incremental backup,-- incremental-basedir should point to the directory where the last incremental backup was located.

* * prepare * * (prepare) incremental backup differs from preparing a full backup, especially:

(1) it is necessary to "replay" committed transactions on each backup (including full and individual incremental backups), and after "replay", all backup data will be merged into the full backup.

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

Therefore, the preparation operation becomes:

1. Prepare the full backup first:

[root@lamp ~] # innobackupex-- apply-log-- redo-only BASE-DIR # prepare to perform a full backup

BASE-DIR: the path where the full backup is stored.-- redo-only: select redo to execute the transaction

2. Perform the operation of submitting the first incremental backup:

[root@lamp ~] # innobackupex-apply-log-redo-only BASE-DIR-- incremental-dir=INCREMENTAL-DIR-1 # where BASE-DIR: refers to the directory where the full backup is located, and INCREMENTAL-DIR-1 refers to the directory of the first incremental backup

3. Perform the operation of submitting the second incremental backup:

[root@lamp ~] # innobackupex-apply-log-redo-only BASE-DIR-- incremental-dir=INCREMENTAL-DIR-2 # where BASE-DIR: refers to the directory where the full backup is located, and INCREMENTAL-DIR-2 refers to the directory of the second incremental backup

If you have multiple incremental backups, you must perform the above preparation operation after each backup before you can use it for later restore operations. After performing a full backup and multiple incremental backup preparations, all data is saved in the specified full backup BASE-DIR path for later recovery

All the data can be recovered by executing innobackupex-- copy-back BASE-DIR. BASE-DIR is the directory where the full backup is stored.

5. Import or export a single form:

By default, InnoDB tables cannot be migrated between mysql servers by copying table files directly, using the innodb_file_per_table option in time, but this can be achieved by using the Xtrabackup tool, but in this case, the mysql server that needs to "export" the table has the innodb_file_per_table option enabled (strictly speaking, the table to be "exported" is the mysql service before it is created.

The innodb_file_per_table option and one tablespace per table are enabled, and the server that "imports" the table enables both the innodb_file_per_table and innodb_expand_import options.

(1) Export table

The export table is done during the prepare phase of the backup, so once the full backup is complete, you can pass the

-- the export option exports a table

[root@lamp] # innobackupex-- apply-log-- export / path/to/backup

# this command creates a file ending in .exp in the tablespace of each innodb table, and these files ending in .exp can be used to import to other servers.

(2) Import table

To import an innodb table from another server on a mysql server, you need to create a table on the current server that is consistent with the structure of the original table before you can import the table:

Mysql > CREATE TABLE mytable (...) ENGINE=InnoDB

Then delete the tablespace of this table

Mysql > ALTER TABLE mydatabase.mytable DISCARD TABLESPACE

Next, copy the mytable.ibd and mytable.exp files of the mytable table from the server of the Export table to the data directory of the current server, and then "import" them using the following command

Mysql > ALTER TABLE mydatabase.mytable IMPORT TABLESPACE

*

Example: perform backup recovery process and steps

Perform a full backup operation first:

[root@lamp ~] # innobackupex-- user=root-- password=redhat / backup # perform a full backup

InnoDB Backup Utility v1.5.1 InnoDB Backup Utility xtrabackup; Copyright 2003, 2009 Innobase Oy

And Percona Inc 2009-2012. All Rights Reserved.

This software is published under

..

170609 16:34:54 innobackupex: All tables unlocked

170609 16:34:54 innobackupex: Connection to database serve r closed

Innobackupex: Backup created in directory'/ backup/2017-06-09 / 16-34-35'

Innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 107

170609 16:34:54 innobackupex: completed OK! # backup completed

[root@lamp ~] # ls / backup # red font directory is the data directory just backed up

2017-06-09 16-34-35 incremental-2017-06-06-17-01-41.sql

Full-backup-2017-06-06 master-2017-06-06.info

[root@lamp] # cd / backup/2017-06-09 / 16-34-35 /

[root@lamp 2017-06-09 September 16-34-35] # ls

Backup-my.cnf jiaowu performance_schema testdb xtrabackup_checkpoints

Hellodb mydb stu xtrabackup_binary xtrabackup_logfile

Ibdata1 mysql test xtrabackup_binlog_info

[root@lamp ~] # innobackupex-- apply-log-- redo-only / backup/2017-06-09 backup 16-34-35 # prepare for full backup and write related transactions to the full backup directory.

InnoDB Backup Utility v1.5.1 InnoDB Backup Utility xtrabackup; Copyright 2003, 2009 Innobase Oy

And Percona Inc 2009-2012. All Rights Reserved.

This software is published under

..

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

170612 11:38:51 InnoDB: Starting shutdown...

170612 11:38:56 InnoDB: Shutdown completed; log sequence number 1631244

170612 11:38:56 innobackupex: completed OK! # execution preparation completed

Then back up the relevant binary logs so that they can be restored.

[root@lamp data] # cp-a mysql-bin.* / backup/ # copy binary log files to backup directory-a: copy files with permission attributes.

[root@lamp data] # ls / backup/

2017-06-09 16-34-35 incremental-2017-06-06-17-01-41.sql mysql-bin.000001 mysql-bin.000003 mysql-bin.000002 mysql-bin.index

[root@lamp data] # service mysqld stop # stop the mysqld service

Shutting down MySQL.. [OK]

[root@lamp data] # rm-rf. / * # simulate the loss of mysqld data directory

[root@lamp data] # ls

[root@lamp data] # pwd

/ mydata/data

[root@lamp data] # service mysqld start # starts the mysqld service, but cannot start due to data loss

Starting MySQL....The server quit without updating PID file [failed] a/data/lamp.pid.

[root@lamp ~] # innobackupex-- copy-back / backup/2017-06-09 / 16-34-35 / # perform data recovery from a full backup

(instead of initializing the database, you can perform data recovery operations directly)

InnoDB Backup Utility v1.5.1 InnoDB Backup Utility xtrabackup; Copyright 2003, 2009 Innobase Oy

And Percona Inc 2009-2012. All Rights Reserved.

This software is published under

The GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

Prints "completed OK!".

Innobackupex: Starting to copy files in'/ backup/2017-06-09 / 16-34-35'

Innobackupex: back to original data directory'/ mydata/data'

...

Innobackupex: in'/ backup/2017-06-09 / 16-34-35'

Innobackupex: back to original InnoDB log directory'/ mydata/data'

Innobackupex: Finished copying back files.

170612 14:46:18 innobackupex: completed OK! # data recovery completed

[root@lamp data] # pwd

/ mydata/data

[root@lamp data] # ll # after the recovery is completed, both the owner and the owner of the data directory are root, so you need to change the owner and group of the data directory.

Total 28712

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 hellodb

-rw-r-. 1 root root 18874368 Jun 12 11:38 ibdata1

-rw-r--r--. 1 root root 5242880 Jun 12 14:46 ib_logfile0

-rw-r--r--. 1 root root 5242880 Jun 12 14:46 ib_logfile1

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 jiaowu

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 mydb

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 mysql

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 performance_schema

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 stu

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 test

Drwxr-xr-x. 2 root root 4096 Jun 12 14:46 testdb

-rw-r--r--. 1 root root 24 Jun 12 14:46 xtrabackup_binlog_pos_innodb

-rw-r--r--. 1 root root 77 Jun 12 14:46 xtrabackup_checkpoints

[root@lamp data] # chown-R mysql:mysql / mydata/data # modifies the owner and group of the directory so that the mysql user has permission to manipulate all files in the directory, and-R recursively modifies.

[root@lamp data] # ll

Total 28712

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 hellodb

-rw-r-. 1 mysql mysql 18874368 Jun 12 11:38 ibdata1

-rw-r--r--. 1 mysql mysql 5242880 Jun 12 14:46 ib_logfile0

-rw-r--r--. 1 mysql mysql 5242880 Jun 12 14:46 ib_logfile1

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 jiaowu

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 mydb

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 mysql

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 performance_schema

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 stu

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 test

Drwxr-xr-x. 2 mysql mysql 4096 Jun 12 14:46 testdb

-rw-r--r--. 1 mysql mysql 24 Jun 12 14:46 xtrabackup_binlog_pos_innodb

-rw-r--r--. 1 mysql mysql 77 Jun 12 14:46 xtrabackup_checkpoints

[root@lamp data] # service mysqld start # start the mysqld service

Starting MySQL.. [OK]

[root@lamp data] # mysql-uroot-p # Log in to the mysql client

Enter password:

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

Your MySQL connection id is 2

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > SHOW DATABASES; # fully backed up data has been restored.

+-+

| | Database |

+-+

| | information_schema |

| | hellodb |

| | jiaowu |

| | mydb |

| | mysql |

| | performance_schema |

| | stu |

| | test |

| | testdb |

+-+

9 rows in set (0.00 sec)

Mysql > SELECT * FROM tutors; # View table contents

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

9 rows in set (0.00 sec)

Open another terminal connection to export the binary log

[root@lamp ~] # mysqlbinlog / backup/mysql-bin.000001 > / tmp/test.sql # Export binary log files to

Test.sql under / tmp directory to achieve point-in-time recovery

Mysql > SET sql_log_bin=0; # when importing binary log files, turn off binary logging first

Query OK, 0 rows affected (0.00 sec)

Mysql > SOURCE / tmp/test.sql # importing the binary log file test.sql into database source is equivalent to. /

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

9 rows in set (0.00 sec)

After mysql > SET sql_log_bin=1; # restores binary logging, don't forget to turn on binary logging.

Query OK, 0 rows affected (0.00 sec)

For incremental backup, you can also write all incremental backup data to the directory where the full backup is saved through incremental backup, and then restore as long as you directly restore the contents of the full backup. Of course, the next time you perform a data backup, you need to do a full backup again, and then do an incremental backup.

* * prepare * * (prepare) incremental backup differs from preparing a full backup, especially:

(1) it is necessary to "replay" committed transactions on each backup (including full and individual incremental backups), and after "replay", all backup data will be merged into the full backup.

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

Therefore, the preparation operation becomes:

1. Prepare the full backup first:

[root@lamp ~] # innobackupex-- apply-log-- redo-only BASE-DIR # prepare to perform a full backup

BASE-DIR: the path where the full backup is stored.-- redo-only: select redo to execute the transaction

2. Perform the operation of submitting the first incremental backup:

[root@lamp ~] # innobackupex-apply-log-redo-only BASE-DIR-- incremental-dir=INCREMENTAL-DIR-1 # where BASE-DIR: refers to the directory where the full backup is located, and INCREMENTAL-DIR-1 refers to the directory of the first incremental backup

3. Perform the operation of submitting the second incremental backup:

[root@lamp ~] # innobackupex-apply-log-redo-only BASE-DIR-- incremental-dir=INCREMENTAL-DIR-2 # where BASE-DIR: refers to the directory where the full backup is located, and INCREMENTAL-DIR-2 refers to the directory of the second incremental backup

If you have multiple incremental backups, you must perform the above preparation operation after each backup before you can use it for later restore operations. After performing a full backup and multiple incremental backup preparations, all data is saved in the specified full backup BASE-DIR path for later recovery

All the data can be recovered by executing innobackupex-- copy-back BASE-DIR. BASE-DIR is the directory where the full backup is stored.

*

Remote backup

Compressed backup

[root@lamp] # innobackupex-- user=root-- password=redhat-- host=127.0.0.1-- stream=tar / tmp | ssh root@10.1.2.208 "gzip-> / tmp/bak.tar.gz"

Or

[root@lamp ~] # innobackupex-user=root-password=redhat-host=127.0.0.1-stream=tar / tmp | gzip | ssh root@10.1.2.208 "/ tmp/bak.tar.gz"

-- stream=tar:tar format

Gzip: compression

Uncompressed backup

[root@lamp] # innobackupex-- user=root-- password=redhat-- host=127.0.0.1-- stream=tar / tmp | ssh root@10.1.2.208 "cat-> / tmp/bak.tar"

Remote recovery

Data compressed files need to be added with "I"

[root@lamp ~] # tar-izxvf bak.tar.gz

Percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm

Download address: https://www.percona.com/downloads/XtraBackup/LATEST/

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.