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

This article mainly shows you "MySQL 5.7 how to use GTID to build a replication environment", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL 5.7 how to use GTID to build a replication environment" this article.

When using GTIDs (global transaction identifiers), each transaction will be marked with a unique transaction number and used in the slave database, so that when building a replication environment, there is no need to use the traditional way of building log files and log locations, which greatly simplifies the process of building a replication environment. Statement-level and row-level replication formats can be used, and row-level replication formats are recommended.

The format of GTID is as follows

GTID = source_id:transaction_id

Source_id represents the source server, and transaction_id represents the sequence number of the transaction.

Restrictions:

Because GTID-based replication is transaction-based, some features are limited in replication.

Non-transactional tables, such as MyISAM tables, are not supported.

CREATE TABLE is not supported. SELECT statement. CREATE TABLE... SELECT is not safe for statement-level replication formats. When using a row-level replication format, this statement is recorded in the log as two separate events, one for the creation of the table and the other for the insert operation of the table. When this statement is executed in a transaction, in some cases the two events are assigned the same transaction number, so that the second transaction that performs the insert operation may be skipped from the library.

Temporary watch. Within a transaction, GTID replication does not support CREATE TEMPORARY TABLE, DROP TEMPORARY TABLE statements.

The sql_slave_skip_counter parameter is not supported for GTID replication. If you need to skip transactions, use the gtid_executed parameter on the main library.

The main library gtid_purged parameter contains all transactions cleared from the main library binary log.

Build the process:

Edit the configuration file of the main library and restart the main library

# Log

Server-id = 27100

Log-bin = production-bin

# log-bin-index = / log/production-bin.index

Binlog_format = row

Log_slave_updates

Gtid-mode = ON

Enforce-gtid-consistency = ON

Edit the configuration file of the slave library and restart the slave library

# Log

Server-id = 35100

Log-bin = production-bin

# log-bin-index = / log/production-bin.index

Binlog_format = row

Log_slave_updates

Gtid-mode = ON

Enforce-gtid-consistency = ON

Export the backup on the master library and transfer it to the slave library

[root@localhost 20160609] # mysqldump-uroot-paired Systems 2013-S / var/lib/mysql/mysql.sock-A-R-- single-transaction-- default-character-set=utf8 > 20160609.sql

Apply a backup to the slave library

[root@localhost 20160609] # mysql-uroot-paired system 2013'

Grant replication slave on *. * to 'repl'@'192.168.78.%' identified by' Mysql#2015'

Query OK, 0 rows affected, 1 warning (0.17 sec)

Execute the CHANGE MASTER command on the slave library

Mysql > change master to

-> master_host='192.168.78.141'

-> master_port=3306

-> master_user='repl'

-> master_password='Mysql#2015'

-> master_auto_position = 1

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

-- start IO and SQL threads

Mysql > start slave

Query OK, 0 rows affected (0.04 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.78.141

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: production-bin.000002

Read_Master_Log_Pos: 448

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 671

Relay_Master_Log_File: production-bin.000002

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: 448

Relay_Log_Space: 882

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: 27100

Master_UUID: cf291e84-2c89-11e6-b6f0-000c29631605

Master_Info_File: / var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: cf291e84-2c89-11e6-b6f0-000c29631605:1

Executed_Gtid_Set: cf291e84-2c89-11e6-b6f0-000c29631605:1

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Mysql > show processlist

+-+-- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- +

| | 4 | root | localhost | fire | Query | 0 | starting | show processlist | |

| | 6 | system user | | NULL | Connect | 480 | Waiting for master to send event | NULL |

| | 7 | system user | | NULL | Connect | 153 | Slave has read all relay log; waiting for more updates | NULL |

+-+-- +

3 rows in set (0.02 sec)

These are all the contents of the article "how to build a replication environment with GTID in MySQL 5.7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.