How to configure MySQL5.6 Master-Slave in Linux

How to configure MySQL5.6 Master-Slave in Linux

The installation of MySQL is very simple compared with that of Oracle. I personally think it is closer to the installation configuration of TimesTen, and the configuration of the Master-Slave or Master-Master cluster is simpler, it is even easier than TimesTen's ASP/Active-Active/Active-Standby configuration.
Steps for building Master-Slave:
1. Install MySQL database software on both hosts
2. Create and initialize MySQL database instances on both hosts
3. import data (databases) from the master database to the slave Database
4. Modify the Master-Slave Configuration
5. master database grant and slave database start replication (start slave)
6. Verify master-slave Data Synchronization

-- Master:
Grant replication slave, replication client on *. * to 'slave '@ '192. 168.100.81' identified by 'slave ';

Export:
Flush tables with read lock;

Mysql> insert into tony (tid, tdept) values (20001, 'ty ');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
Mysqldump-uroot-p-E-R-A -- databases tyms>/mysql/data/tyms_master. SQL

Slave import:
Mysql Command Line
Mysql> source/mysql/data/tyms_master. SQL;

-- Master:
(Root @ localhost) [(none)]> show master status;
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| Mysql-bin.000014 | 594 |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
1 row in set (0.00 sec)

--- Slave:
Change master to master_host = '192. 168.100.71 ', master_user = 'slave', master_password = 'slave ', master_log_file = 'mysql-bin.000014', master_log_pos = 192;
(Root @ localhost) [(none)]> change master to master_host = '2017. 168.100.71 ', master_user = 'slave', master_password = 'slave ', master_log_file = 'mysql-bin.000014', master_log_pos = 594;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

-- Master:
Unlock tables;

--- Slave:
Mysql> start slave;

[Tyms]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 3051261
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 3050950
Relay_Master_Log_File: mysql-bin.000014
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: 3051261
Relay_Log_Space: 3051123
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: 1000
Master_UUID: 845f69fa-c558-11e5-9462-000c297b003d
Master_Info_File:/mysql/data/mysqldata/mydata/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: 0
1 row in set (0.00 sec)

[Tyms]> show binlog events in '/mysql/data/mysqldata/mydata/mysql-bin.000014' from 3051261;

[(None)]> select version ();
+ ------------ +
| Version () |
+ ------------ +
| 5.6.28-log |
+ ------------ +
1 row in set (0.00 sec)

We can see that the values Slave_IO_Running and Slave_ SQL _Running are both YES, indicating that the configuration is successful. You can perform DML or DDL verification in the master database.
 

If the synchronization fails:
1: Stop slave Database
Mysql> slave stop
2: log and location found on the master database
Mysql> show master status;
(Root @ localhost) [(none)]> show master status;
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| Mysql-bin.000014 | 594 |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
3: manual synchronization
Mysql> change master
> Master_host = 'master _ ip ',
> Master_user = 'gechong ',
> Master_password = 'gechong ',
> Master_port = 3306,
> Master_log_file = 'mysql-bin.000014 ',
> Master_log_pos = 594;
1 row in set (0.00 sec)
4: Start slave Database
Mysql> slave start;
1 row in set (0.00 sec)

If exceptions occur, skip the following steps:
> Slave stop;
> Set global SQL _slave_skip_counter = 1;
> Slave start;

This article permanently updates the link address: