GTID replication and problem handling-Mysql Tutorial-php.cn

First, let’s take a look at what GTID is:

GTID (Global Transaction ID) is the number of a submitted transaction and is a globally unique number.

GTID is actually composed of UUID+TID. The UUID is the unique identifier of a MySQL instance. TID represents the number of transactions that have been committed on this instance, and increases monotonically as transactions are committed. Based on the GTID, you can know which instance the transaction was originally submitted on, and it facilitates failover.

Let’s take a look at how to quickly add a slave in GTID mode:

We know that before there was no GTID replication, MySQL replication was based on binary log and position To do this, we have to execute the following change statement for the previous copy:

CHANGE MASTER TO MASTER_HOST='',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='*****',MASTER_LOG_FILE='mysqlbinlog.000003',MASTER_LOG_POS=99721204;

Copy after login

And we can execute the following change statement in GTID:

CHANGE MASTER TO  MASTER_HOST='****', MASTER_USER='repl',  MASTER_PASSWORD='******',  MASTER_PORT=3306,  master_auto_position=1;

Copy after login

We can see that basically the original binary log method requires specifying MASTER_LOG_FILE and MASTER_LOG_POS when specifying replication, but GTID replication does not need to know these parameters.

Let’s take a look at how to create master-slave replication in GTID mode:

As you can see from the above, in GTID mode we no longer need to know the two parameters MASTER_LOG_FILE and MASTER_LOG_POS. In contrast, we only need to specify the master, which is much simpler to create replication. In GTID mode we need to know the following two global

variables:

root@perconatest09:23:44>show global variables like 'GTID_%'\G*************************** 1. row ***************************Variable_name: gtid_executed
Value: 5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-24*************************** 2. row ***************************Variable_name: gtid_executed_compression_period
Value: 1000*************************** 3. row ***************************Variable_name: gtid_mode
Value: ON*************************** 4. row ***************************Variable_name: gtid_owned
Value:*************************** 5. row ***************************Variable_name: gtid_purged
Value: 5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-12

Copy after login

What we mainly need to see are the two parameters gtid_executed and gtid_purged.

gtid_executed: This is a series of GTIDs of all things that have been executed, that is The serial number of the things that have been placed in the binary log. This parameter is read-only and cannot be set.

gtid_purged: This sequence refers to the sequence number of the GTID of the thing we delete

in the binary log. We can set it manually to facilitate some management.

After understanding these two parameters, let’s take a look at how to add a slave database for GTID replication:

(1): Make a full backup from the master database, and record the master database gtid_executed

at the library backup time point (2): Restore from the library, and set the gtid_purged from the library to the gtid_executed

of the master we obtained in the first step (3): Execute CHANGE MASTER statement.

use mysqldump to back up the main database and restore the backup to a new machine as the slave database. Before executing, check the parameters in the main library:

root@perconatest09:23:58>show global variables like 'GTID_e%'\G*************************** 1. row ***************************Variable_name: gtid_executed
Value: 5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-242 rows in set (0.01 sec)
root@perconatest09:41:33>show global variables like 'GTID_p%'\G*************************** 1. row ***************************Variable_name: gtid_purged
Value: 5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-121 row in set (0.01 sec)

Copy after login

Then make a backup in the main database:

mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=18675 --user=root--p > /home/sa/backup.sql

Copy after login

We can Take a look at the backup file:

[root@localhost sa]# head -30 backup.sql

Copy after login

We can see that there are The following parameters:

SET @@GLOBAL.GTID_PURGED='5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-24';

Copy after login

That is to say, when we proceed When restoring, GTID_PURGED will be automatically set, and this value happens to be the master's gtid_executed, so we basically don't need to specify it after restoring from the library.

Enter data recovery from the database:

source backup.sql;

We know that there is no need to specify the value of GTID_PURGE. If you are not sure, you can confirm it:

show global variables like 'gtid_executed';
show global variables like 'gtid_purged';

Copy after login

Just specify the copy directly afterwards:

CHANGE MASTER TO MASTER_HOST="***", MASTER_USER="root", MASTER_PASSWORD="*****", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

Copy after login

Replace * with the relevant information of the main library you need to specify That's OK.

If an error occurs in GTID master-slave replication mode, how should we recover?

If the log of our main library has been purged and

reset

and other operations are performed, our slave library will have the following error:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

Copy after login

prompts us that we cannot find the log, and the master-slave replication will stop. Let’s take a look at the processing method:

(1 ) The main library performs the following operations:

root@perconatest09:41:38>show global variables like 'GTID_EXECUTED';+---------------+---------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+---------------+---------------------------------------------------------------------------------------------------------------------------------+| gtid_executed | 5031589f-3551-11e7-89a0-00505693235d:1-12,
806ede0c-357e-11e7-9719-00505693235d:1-11,
a38c33ee-34b7-11e7-ae1d-005056931959:1-24 |+---------------+---------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

Copy after login

(2) From the library

root@(none)03:04:49>set global GTID_PURGED='5031589f-3551-11e7-89a0-00505693235d:1-12,806ede0c-357e-11e7-9719-00505693235d:1-11,a38c33ee-34b7-11e7-ae1d-005056931959:1-24';

Copy after login

##Note, you must confirm first before specifying This value is empty, otherwise we have to do the following:

root@(none)03:04:49>reset master;
root@(none)03:04:49>set global GTID_PURGED='5031589f-3551-11e7-89a0-00505693235d:1-12,806ede0c-357e-11e7-9719-00505693235d:1-11,a38c33ee-34b7-11e7-ae1d-005056931959:1-24';
root@(none)03:04:49>start slave;
root@(none)03:04:49>show slave status\G

Copy after login

The repair is completed, but we'd better use checksum to verify the consistency of the master-slave data.

Error message:

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires

（ Paste Error messageIn order to increase the number of views)

Of course the above method does not guarantee the complete consistency of the data. We also need to verify using pt-table-checksum and pt-table- sync, but this is not necessarily the most efficient. The best way is to do a full backup, then restore, and then specify the master as introduced earlier. This is the most reliable.

The above is the detailed content of GTID replication and problem handling. For more information, please follow other related articles on the PHP Chinese website!