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

The following brings you about the configuration of mysql5.6.x GTID master-slave replication method, if you are interested, let's take a look at this article, I believe that after reading the configuration of mysql5.6.x GTID master-slave replication method will be of some help to you.

The context of this article:

Main library: CentOS6.7 x64 192.168.0.65 mysql-5.6.29

Standby library: CentOS6.7 x64 192.168.0.66 mysql-5.6.29

1. Configure Mysql5.6 GTID master / slave (this section is suitable for situations where both master and slave are empty libraries) 1. Mysql master CVM configuration

Note: GTID-based master-slave replication requires the following to be added to the configuration file.

# vi / etc/my.cnf

[mysqld]

Binlog-format = ROW

Log-bin = master-bin

Log-bin-index = master-bin.index

Log-slave-updates = true

Gtid-mode = on

Enforce-gtid-consistency = true

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

Sync-master-info = 1

Slave-parallel-workers = 2

Binlog-checksum = CRC32

Master-verify-checksum = 1

Slave-sql-verify-checksum = 1

Binlog-rows-query-log_events = 1

Report-host = 192.168.1.120

Server-id = 1

Restart the database:

# service mysqld restart

View gtid information:

Mysql > show global variables like'% GTID%'

+-+

| | Variable_name | Value |

+-+

| | binlog_gtid_simple_recovery | OFF |

| | enforce_gtid_consistency | ON |

| | gtid_executed |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

| | simplified_binlog_gtid_recovery | OFF |

+-+

two。 The primary CVM configures synchronous replication account

Grant replication slave on *. * to 'repl'@'%' identified by' 123456'

Flush privileges

3. Mysql configuration from CVM

Description: by default, as long as the server-id is different.

# vi / etc/my.cnf

[mysqld]

Binlog-format = ROW

Log-bin = mysql-bin

Relay-log = slave-relay-bin

Relay-log-index = slave-relay-bin.index

Log-slave-updates = true

Gtid-mode = on

Enforce-gtid-consistency = true

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

Sync-master-info = 1

Slave-parallel-workers = 2

Binlog-checksum = CRC32

Master-verify-checksum = 1

Slave-sql-verify-checksum = 1

Binlog-rows-query-log_events = 1

Report-host = 192.168.1.121

Server-id = 11

Restart the database:

# service mysqld restart

View gtid status:

Mysql > show global variables like'% GTID%'

+-+

| | Variable_name | Value |

+-+

| | binlog_gtid_simple_recovery | OFF |

| | enforce_gtid_consistency | ON |

| | gtid_executed |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

| | simplified_binlog_gtid_recovery | OFF |

+-+

7 rows in set (0.00 sec)

4. Connect to master Mysql and configure master-slave (1) connect to master database

Mysql >

CHANGE MASTER TO

MASTER_HOST='192.168.0.65'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='123456'

MASTER_AUTO_POSITION=1

(2) start the slave synchronization process

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.65

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000007

Read_Master_Log_Pos: 290

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 502

Relay_Master_Log_File: master-bin.000007

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: mysql.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 290

Relay_Log_Space: 706

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: c8bb22a1-024e-11e6-a1e8-000c29225fa0

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

# check that the following two parameters are YES, which means that the slave library is running normally.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. Verify synchronization (1) create a database for the master database

Mysql > create database abc

Query OK, 1 row affected (0.02 sec)

Mysql > show master status\ G

* * 1. Row *

File: master-bin.000007

Position: 290

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

(2) check the synchronization from the database

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | abc |

| | mydb |

| | mysql |

| | performance_schema |

+-+

5 rows in set (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.65

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000007

Read_Master_Log_Pos: 290

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 502

Relay_Master_Log_File: master-bin.000007

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: mysql.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 290

Relay_Log_Space: 706

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: c8bb22a1-024e-11e6-a1e8-000c29225fa0

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Second, configure Mysql5.6 GTID master-slave mode (master-slave mode from asynchronous mode to GTID mode)

Mysql-5.6 Master-Slave synchronization configuration sample http://koumm.blog.51cto.com/703525/1764093

The original environment itself is already in asynchronous master-slave synchronization mode.

1. Add GTID related configuration files as above from the main database. After restarting the database, the master database locks the table and backs up the database.

Mysql > flush tables with read lock

2, reconfigure the slave database from the library, clear the original configuration information of the master database, or import the database.

Mysql > stop slave

Mysql > reset slave

3, reconnect the master library synchronization from the slave library under the condition of ensuring data synchronization.

Mysql >

CHANGE MASTER TO

MASTER_HOST='192.168.0.65'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='123456'

MASTER_AUTO_POSITION=1

4, main library

Mysql > unlock tables

5, start from the library and test the synchronization

III. Summary

Semi-automatic synchronous replication can be configured on the basis of GTID, and relevant documents can be viewed. In the actual environment, you need to know a lot about GTID before using it in the production environment.

Read the details of configuring mysql5.6.x GTID master-slave replication method above and see if there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

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.