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

For larger databases, we generally use innobackup for backup, backup and recovery faster.

Test environment:

CentOS6.8 x86_64

MySQL5.6.34 Community rpm Edition

Xtrabackup version: percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

Main library: node0 192.168.2.10 (need to install xtrabackup and lz4)

Slave library: node1 192.168.2.11 (need to install xtrabackup and lz4)

5.6.The parameters required for GTID replication (these three lines of parameters should be added for both master and slave libraries):

Gtid-mode=ON

Enforce_gtid_consistency = ON

Log_slave_updates=ON

Step1, in the directory where backup files are created from the library:

Mkdir / tmp/db_restore

Step2, perform a backup in the master database (it is best to open a screen operation to prevent network interruption), and export directly to the slave database machine:

# # Note here we also need to install the lz4 compression tool on two machines ahead of time, because our script will call lz4 to compress and extract backup files

Innobackupex-- user=root\

-- password=123456\

-- parallel=4\

-- socket=/tmp/mysql.sock\

-- no-timestamp\

-- stream=xbstream. | |\ |

Lz4-B4 |\

Ssh node1\

"cat-| lz4-d-B7 | xbstream-x-C / tmp/db_restore"

Step3, look at the gtid location of the master library on the slave library node1

Cd / tmp/db_restore

The cat xtrabackup_binlog_info content is as follows:

Mysql.000008 1949 013bfb27-2edd-11e7-89c7-000c296a2c0d:1-72

To make it easier to understand, go to the main database to check the binlog of the corresponding time period. The screenshot is as follows:

Add: xtrabackup_binlog_info content interpretation:

Mysql.000008 represents the binlog file name of the main library, and 1949 is the binlog position that has not been executed (that is, MASTER_LOG_FILE='master2-bin.001',MASTER_LOG_POS=1949 when we use the traditional change master mode).

013bfb27-2edd-11e7-89c7-000c296a2c0d:1-72 refers to the GTID number that has been executed (that is, when we change master, we need to execute set global gtid_purged='013bfb27-2edd-11e7-89c7-000c296a2c0d:1-72numbers; to purge these GTID).

Step4, drop the gtid purge obtained in the previous step from the library, because these have actually been executed.

Set global gtid_purged='013bfb27-2edd-11e7-89c7-000c296a2c0d:1-72'

If you prompt ERROR 1840 (HY000): @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty. Then you need to execute the reset master

Step5, configure and start replication:

CHANGE MASTER TO MASTER_HOST='192.168.2.10'

MASTER_USER='rpl'

MASTER_PASSWORD='rpl'

MASTER_PORT=3306

MASTER_AUTO_POSITION=1

Start slave

Show slave status\ G

You can then try to create several tables under the test library of the main library to verify that the replication is normal.

Questions to be confirmed:

For some versions of innobackup, the backup xtrabackup_binlog_info contains only mysql.000008 1949 without a GTID number. So when we execute purge, we have to go to the binlog of the main library according to the location of binlog pos 1949 to find its last gtid number (for example, 013bfb27-2edd-11e7-89c7-000c296a2c0d:72 above). Or use traditional mode replication instead of GTID replication.

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.