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

MySQL 5.7Master-Slave replication based on GTID

I. Construction process

1.1 prepare three MySQL instances mysqld-- initialize-insecure-- user=mysql-- basedir=/usr/local/mysql-- datadir=/mysql/3307/data/mysqld-- initialize-insecure-- user=mysql-- basedir=/usr/local/mysql-- datadir=/mysql/3308/data/mysqld-- initialize-insecure-- user=mysql-- basedir=/usr/local/mysql-- datadir=/mysql/3309/data/1.2 server_id, server_uuid is different

Mysql-uroot-P123-e "select @ @ server_id" mysql-uroot-p123-e "select @ @ server_uuid" 1.3 whether gtid_mode is enabled:

Mysql-uroot-p123-e "select @ @ gtid_mode" mysql-uroot-p123-e "select @ @ server_gtid" vi 330 {7.. 9} / my.cnfgtid_mode=ONenforce_gtid_consistency=ONlog_slave_updates=ON1.4 confirms that binlog is on

Mysql-uroot-p123-e "select @ @ log_bin" 1.5 backup master database data to slave database remotely

Mysqldump-uroot-p123-h 192.168.84.30-P 3307 > / tmp/full.sql1.6 restore data

Mysql > source / tmp/full.sql1.7 main Library create replication user

Grant replication slave on *. * to repl@'192.168.84.30' identified by '123 replication 1.8 initiates replication from the library

Help: mysql > help change master to. Find the configuration template: CHANGE MASTER TO MASTER_HOST='master2.example.com', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10; 3308Pol 3309 use the following configuration: CHANGE MASTER TO MASTER_HOST='192.168.84.30', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, master_auto_position=1 Start configuring 3308mysql > CHANGE MASTER TO-> MASTER_HOST='192.168.84.30',-> MASTER_USER='repl',-> MASTER_PASSWORD='123',-> MASTER_PORT=3307,-> master_auto_position=1 Query OK, 0 rows affected, 2 warnings (0.00 sec) start to configure 3309mysql > CHANGE MASTER TO-> MASTER_HOST='192.168.84.30',-> MASTER_USER='repl',-> MASTER_PASSWORD='123',-> MASTER_PORT=3307,-> master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.00 sec)

1.9 View status:

Mysql > show slave status\ G; check slave status mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Master_Host: 192.168.84.30 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: open_source-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No 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: 0 Relay_Log_Space: 154 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: NULLMaster_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: 0 Master_UUID: Master_Info_File : / mysql/3308/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified2.0 starts and shuts down replication:

Start slave;stop slave

Remarks: new highlights of 5.7MGR, MGR is recommended for 8.0.17

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.