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

This article mainly explains "the steps of building MySQL5.6 Master-Slave in Linux environment". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the steps of building MySQL5.6 Master-Slave in Linux environment".

Steps for building Master-Slave:

1. Install the MySQL database software on the two hosts

2. Create and initialize MySQL database instances on two hosts respectively

3. Import dump data (library) from the main library to the standby database.

4. Modify master-slave (Master-Slave) configuration

5. Master library grant, start replication from slave library (start slave)

6. Verify the synchronization of active and standby data

-- 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 (2000 1)

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=594

(root@localhost) [(none)] > change master to master_host='192.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)

You can see that both values of Slave_IO_Running | Slave_SQL_Running are YES, indicating that the configuration is successful. You can perform DML or DDL validation in the main library.

If the synchronization is not successful:

1: stop the slave library

Mysql > slave stop

2: find the log and location on the main library

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 to

> 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 the slave library

Mysql > slave start

1 row in set (0.00 sec)

If there is an exception, you can skip it:

> slave stop

> SET GLOBAL sql_slave_skip_counter = 1

> slave start

Created by Tony.Tang [TangYun] 2016.02

-- End--

Thank you for reading, the above is the content of "Building steps of MySQL5.6 Master-Slave in Linux". After the study of this article, I believe you have a deeper understanding of the steps of building MySQL5.6 Master-Slave in Linux environment, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.