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

How to expand a read-only instance in GTID-based replication?

Here, we use the mysqldump tool to backup the data on the Master node. Note a key parameter:-- set-gtid-purged [= name], which represents which GTID has been executed in the backup file. In the case of change master, replication will take place after these GTID.

-- set-gtid-purged [= name]

Add 'SET @ @ GLOBAL.GTID_PURGED' to the output. Possible

Values for this option are ON, OFF and AUTO. If ON is

Used and GTIDs are not enabled on the server, an error is

Generated. If OFF is used, this option does nothing. If

AUTO is used and GTIDs are enabled on the server, 'SET

@ @ GLOBAL.GTID_PURGED' is added to the output. If GTIDs

Are disabled, AUTO does nothing. If no value is supplied

Then the default (AUTO) value will be considered.

The mysqldump command is as follows:

Bin/mysqldump-- user=root-- password='123456'-- socket=/home/mysql/mysql3309/tmp/mysql.sock-- default-character-set=utf8mb4-- events-- routines-- triggers-- force-- hex-blob-- quick-- single-transaction-- set-gtid-purged=ON-- databases db1 lg > / tmp/bak/data.sql

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

If you look at the backup file data.sql, you will find two noteworthy points:

When importing data using backup files, set the session-level system variable @ @ SESSION.SQL_LOG_BIN to 0, that is, no binary logs are generated.

SET @ MYSQLDUMP_TEMP_LOG_BIN = @ @ SESSION.SQL_LOG_BIN

SET @ @ SESSION.SQL_LOG_BIN= 0

...

SET @ @ SESSION.SQL_LOG_BIN = @ MYSQLDUMP_TEMP_LOG_BIN

The global system variable @ @ GLOBAL.GTID_PURGED is set.

SET @ @ GLOBAL.GTID_PURGED='0c34233d-b2e1-11e9-85cf-080027f22add:1-2

32a0c858-b59f-11e9-b069-0800270c3d91:1-2

447e96e1-b59f-11e9-95fe-0800270c3d91:1-2

4fdc13e1-b59e-11e9-b5e0-080027f22add:1-9

B8282f18-b59e-11e9-83b0-0800270c3d91:1-5'

@ @ GLOBAL.GTID_EXECUTED is also set, which is the basis for where replication begins. To be exact, @ @ GLOBAL.GTID_EXECUTED can only be set when @ @ GLOBAL.GTID_PURGED is empty, otherwise the error is as follows. At the same time, @ @ GLOBAL.GTID_EXECUTED is set to have the same value as @ @ GLOBAL.GTID_PURGED.

ERROR 1840 (HY000): @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty

Query OK, 1 row affected (0.00 sec)

Before importing backup data, observe the values of @ @ GLOBAL.GTID_PURGED, and @ @ GLOBAL.GTID_EXECUTED on the new Slave node.

[root@mysql.sock] [(none)] > select @ @ GLOBAL.GTID_PURGED

+-+

| | @ @ GLOBAL.GTID_PURGED |

+-+

| | |

+-+

1 row in set (0.00 sec)

[root@mysql.sock] [(none)] >

[root@mysql.sock] [(none)] > select @ @ GLOBAL.GTID_EXECUTED

+-- +

| | @ @ GLOBAL.GTID_EXECUTED |

+-- +

| | |

+-- +

1 row in set (0.00 sec)

After importing the data, observe again.

[root@mysql.sock] [(none)] > source / tmp/bak/data.sql

...

[root@mysql.sock] [lg] > select @ @ GLOBAL.GTID_PURGED

+-- +

| | @ @ GLOBAL.GTID_PURGED |

+-- +

| | 0c34233d-b2e1-11e9-85cf-080027f22add:1-2 |

32a0c858-b59f-11e9-b069-0800270c3d91:1-2

447e96e1-b59f-11e9-95fe-0800270c3d91:1-2

4fdc13e1-b59e-11e9-b5e0-080027f22add:1-9

B8282f18-b59e-11e9-83b0-0800270c3d91:1-5 |

+-- +

1 row in set (0.00 sec)

[root@mysql.sock] [lg] > select @ @ GLOBAL.GTID_EXECUTED

+-- +

| | @ @ GLOBAL.GTID_EXECUTED |

+-- +

| | 0c34233d-b2e1-11e9-85cf-080027f22add:1-2 |

32a0c858-b59f-11e9-b069-0800270c3d91:1-2

447e96e1-b59f-11e9-95fe-0800270c3d91:1-2

4fdc13e1-b59e-11e9-b5e0-080027f22add:1-9

B8282f18-b59e-11e9-83b0-0800270c3d91:1-5 |

+-- +

1 row in set (0.00 sec)

Finally, change master can be done.

CHANGE MASTER TO

MASTER_HOST='10.0.2.6'

MASTER_USER='repl'

MASTER_PASSWORD='123456'

MASTER_PORT=3309

MASTER_AUTO_POSITION = 1

Note that in some special scenarios, you may need to set @ @ GLOBAL.GTID_PURGED manually.

In GTID-based replication, how to set up cascading replication requires the following replication topology, where the arrow represents the replication direction of the data, and the part in parentheses does not yet exist.

S M'- > S')

If you first establish a replication relationship of M'- > S', use mysqldump to back up the data on M, and then import it, will the data be copied from M'to S'? as you know, @ @ SESSION.SQL_LOG_BIN= 0 is set when the data is imported, so the data will not be copied from M'to S', which is different from Position-based replication, that is, SQL_LOG_BIN is not set in the following way.

Bin/mysqldump-- user=root-- password='123456'-- socket=/home/mysql/mysql3309/tmp/mysql.sock-- default-character-set=utf8mb4-- events-- routines-- triggers-- force-- hex-blob-- quick-- single-transaction-- set-gtid-purged=OFF-- master-data=2-- databases db1 lg > / tmp/bak/2data.sql

So the correct idea is to restore both M' and slots with backup data, then set up M' > slots, and finally establish M-> M'.

For Failover, because there is no need to specify site information, it is possible to directly change master, which is much simpler than Position-based replication. In addition, through the introduction of previous articles, we can also find that GTID-based replication is more stringent in terms of data consistency than Position-based replication, unlike Position-based replication to specify a random point, if there is no error in the replication process, it can continue.

PS: the above idea holds true when backing up data using xtrabackup.

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.