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

This article is about how mysql 5.7GTID implements master-slave configuration. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Binlog-format: binary log format, including row, statement and mixed. It should be noted that when setting the isolation level to READ-COMMITED, the binary log format must be set to ROW. Now MySQL officials think that STATEMENT is no longer suitable for further use. However, the mixed type under the default transaction isolation level may lead to inconsistency between master and slave data.

Log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port and report-host: used to launch GTID and meet other ancillary needs

Master-info-repository and relay-log-info-repository: enable these two items, which can be used to secure binary and slave servers in the event of a crash

Sync-master-info: enable to ensure that no information is lost

Slave-paralles-workers: set the number of SQL threads of the slave server, according to the number of cpu cores; 0 means to turn off multithreaded replication

Binlog-checksum, master-verify-checksum, and slave-sql-verify-checksum: enable all validation features related to replication

Binlog-rows-query-log-events: enabled to record event-related information in binary logs, reducing the complexity of troubleshooting

Log-bin: enable binary logging, which is a basic prerequisite for ensuring replication

Server-id: the id number of all servers in the same replication topology must be unique.

Report-host:

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

Report-port:

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

Master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

Relay-log-info-repository:

This option causes the server to log its relay log info to a file or a table.

Log_slave_updates:

Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

Master server configuration

Edit the parameter file for master

# GTID parameter

Gtid-mode=on

Enforce-gtid-consistency=true

Slave-parallel-workers=10

Binlog-checksum=CRC32

Binlog-format=ROW

Log-slave-updates=true

Report-port=3306

Report-host=192.168.56.212

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Restart master's mysql database

[root@ray ~] # / data/3306/mysqld restart

Stoping MySQL...

Warning: Using a password on the command line interface can be insecure.

Starting MySQL...

Mysql > show global variables like'% gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | enforce_gtid_consistency | ON |

| | gtid_executed |

| | gtid_mode | ON | # indicates that the gti function has been enabled |

| | gtid_owned |

| | gtid_purged |

+-+ +

5 rows in set (0.01 sec)

Create a synchronization user

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'rep'@'%' IDENTIFIED BY' 123456'

Query OK, 0 rows affected (0.78 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Slave configuration from server

My.cnf parameter file configuration

# GTID parameter

Gtid-mode=on

Enforce-gtid-consistency=true

Slave-parallel-workers=10

Binlog-checksum=CRC32

Relay-log = / data/3307/logs/relay-log

Relay-log-index = / data/3307/logs/relay-log.index

Binlog-format=ROW

Log-slave-updates=true

Report-port=3307

Report-host=192.168.56.212

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Sync_relay_log = 1

Sync_relay_log_info = 1

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Relay_log_recovery = ON

Restart the mysql database

[root@ray ~] # / data/3307/mysqld restart

Stoping MySQL...

Warning: Using a password on the command line interface can be insecure.

Starting MySQL...

Mysql > show global variables like'% gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | enforce_gtid_consistency | ON |

| | gtid_executed |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

+-+ +

5 rows in set (.56 sec)

Change master to

Master_host='192.168.56.212'

Master_user='rep'

Master_password='123456'

Master_port=3306

Master_auto_position = 1

Mysql > change master to

-> master_host='192.168.56.212'

-> master_user='rep'

-> master_password='123456'

-> master_port=3306

-> master_auto_position = 1

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

Mysql > start slave

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

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.212

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: ray-bin.000009

Read_Master_Log_Pos: 588

Relay_Log_File: relay-log.000003

Relay_Log_Pos: 797

Relay_Master_Log_File: ray-bin.000009

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

Relay_Log_Space: 1175

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: 97e8847a-ffdf-11e6-87ed-08002736c224

Master_Info_File: mysql.slave_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: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2

Executed_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Parameters:

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Save master.info and relay.info in the table. The default is myisam engine. It is officially recommended to change it to innodb.

Mysql > use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > alter table slave_master_info engine=innodb

Query OK, 0 rows affected (0.29 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table slave_relay_log_info engine=innodb

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table slave_worker_info engine=innodb

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Ignore the filter table:

Configuration file, need to restart

Replicate-ignore-table=test.t1

Online dynamic modification, no need to restart

CHANGE REPLICATION FILETER REPLICATE_DO_DB= (DB1,DB2)

CHANGE REPLICATION FILETER REPLICATE_IGNORE_DB= (DB1,DB2)

CHANGE REPLICATION FILETER REPLICATE_DO_TABLE= (DB1.T1)

CHANGE REPLICATION FILETER REPLICATE_IGNORE_TABLE= (DB1.T1)

CHANGE REPLICATION FILETER REPLICATE_WILD_DO_TABLE= (DB1.T%)

CHANGE REPLICATION FILETER REPLICATE_WILD_IGNORE_TABLE= (DB%.T%)

CHANGE REPLICATION FILETER REPLICATE_REWRITE_DB= (FROM_DB,TO_DB)

Thank you for reading! This is the end of the article on "how to realize the master-slave configuration of mysql 5.7GTID". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.