Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use Percona Xtrabackup to recover MySQL from the library, the quality of the article content is high, so Xiaobian shared with you as a reference, I hope you have a certain understanding of related knowledge after reading this article.

Again, two instances on one server. The instance of port 3306 is the master library, and the instance of port 3307 is the slave library.

Now, manually empty the backup database, simulating the backup database being destroyed.

drop database test2;

Use Percona Xtrabackup to recover slave libraries.

Percona Xtrabackup is an open source backup tool that can be downloaded and installed from Percona's official website.

The recovery steps are as follows:

1. Make a complete backup of the data of the main database. Note that this is a hot backup and does not require a shutdown.

innobackupex --default-file=/usr/local/mysql/my.cnf --user=root --password=root --socket=/usr/local/mysql/mysql.sock /tmp/backup

If the screen output ends with the print "completed Ok! "The command was executed successfully.

2. Attach the logs generated by the master library to the backup

innobackupex --apply-log /tmp/backup/2016-08-30_09-56-23

As in step 1, if the screen output ends with the word completed OK, the command execution is successful.

3. View the backup information generated by the master database

[root@single1 2016-08-30_09-56-23]# ls -l

total 131108

-rw-r-----. 1 root root 418 Aug 30 09:56 backup-my.cnf

-rw-r-----. 1 root root 12582912 Aug 30 09:56 ibdata1

-rw-r-----. 1 root root 50331648 Aug 30 09:56 ib_logfile0

-rw-r-----. 1 root root 50331648 Aug 30 09:56 ib_logfile1

-rw-r-----. 1 root root 12582912 Aug 30 09:56 ibtmp1

drwxr-x---. 2 root root 4096 Aug 30 09:56 lxm

drwxr-x---. 2 root root 4096 Aug 30 09:56 mysql

drwxr-x---. 2 root root 4096 Aug 30 09:56 performance_schema

drwxr-x---. 2 root root 4096 Aug 30 09:56 test

-rw-r-----. 1 root root 21 Aug 30 09:56 xtrabackup_binlog_info

-rw-r--r--. 1 root root 21 Aug 30 09:56 xtrabackup_binlog_pos_innodb

-rw-r-----. 1 root root 113 Aug 30 09:56 xtrabackup_checkpoints

-rw-r-----. 1 root root 504 Aug 30 09:56 xtrabackup_info

-rw-r-----. 1 root root 8388608 Aug 30 09:56 xtrabackup_logfile

Note:

a) The xtrabackup_binlog_info file records binary log files currently in use by the mysql server and the location of binary log events up to the time of backup.

b) The xtrabackup_binlog_pos_innodb file records the binary log file and the current position of the binary log file for the InnoDB or XtraDB table.

(Difference between xtrabackup_binlog_info file and xtrabackup_binlog_pos_innodb file: For pure InnoDB operation, the contents of the above two files in the backed up data are consistent.) For mixed InnoDB and non-transactional storage engine operations, the position shown in xtrabackup_binlog_info should be greater than the value shown in xtrabackup_pos_innodb. In this case, xtrabackup_binlog_info shall prevail; the latter is consistent with the contents displayed in InnoDB recovery log when apply-log, only for this part of InnoDB data.)

c) xtrabackup_checkpoints file is used to describe backup type, backup status, LSN range information.

d) The xtrabackup_info file records some information about the xtrabackup command used during backup

e) The backup-my.cnf file is the configuration option information used by the backup command;

4. Restore slave library

Stop the slave library service and clear the original data in the slave library.

service mysql3307 stop;

cd /usr/local/mysql3307

mv data data.bak

Then start to recover:

innobackupex --defaults-file=/usr/local/mysql3307/my.cnf --copy-back --rsync /tmp/backup/2016-08-30_09-56-23

If the screen output ends with the print "completed Ok! "The command was executed successfully.

5. Modify attributes of slave database data file

After successful recovery from the library, you can see a data file in/usr/local/mysql3307:

Remember to change the restored data directory group back to mysql, otherwise the following error will occur when starting the slave library:

chown -R mysql:mysql data

6. View binary log information in backup

[root@single1 2016-08-30_09-56-23]# cat xtrabackup_binlog_info

mysql-bin.000003 329

7. Start from the library and synchronize again:

service mysql3307 start

mysql -S /usr/local/mysql3307/mysql.sock -P 3307 -uroot -p

mysql> change master to master_host='localhost', master_user='root', master_password='root', master_log_file='mysql-bin.000003', master_log_pos=329, master_port=3306;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

#The master_log_file parameter and master_log_pos parameter are set according to step 6

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 329

Relay_Log_File: single1-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 329

Relay_Log_Space: 458

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 9c534d95-6849-11e6-bae6-000c295ae8a5

Master_Info_File: /usr/local/mysql3307/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

8. Check whether the master library and slave library are consistent.

About how to use Percona Xtrabackup to recover MySQL from the library to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.