Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Detailed tutorials of mysql master-slave configuration under linux

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. Modify MySQL configuration:

Main library configuration

Server-id = 3

Binlog-do-db=xmcp_gxfc # the db need to sync

Binlog-ignore-db = mysql # databases that do not require synchronization

Binlog-ignore-db = redmine # databases that do not require synchronization

Log_slave_updates = 1

Binlog_format=mixed

Relay_log = / usr/local/mysql/relay_log/mysql-relay-bin

Read_only = 1

2 create an account

Grant replication slave on. To 'slave2'@'%' identified by' FjAfj6#xajot#K%V'

Grant replication slave on. To 'slave3'@'%' identified by' FjAfj6#xajot#K%V'

Update database permissions

Mysql > flush privileges

Mysql > show master status

Record that File is mysql-bin.000001

Recorded a position of 154,

3. Modify the slave MySQL configuration:

From the library configuration:

Server-id = 5

Log-bin = mysql-bin

Replicate-do-db=xmcp_gxfc

Binlog_format=mixed

Relay_log=/usr/local/mysql/relay_log/mysql-relay-bin

Read_only = 1

4. Execute the synchronization command

Execute the synchronization command, set the main database ip, synchronization account password, synchronization location

Mysql > change master to master_host='10.2.2.2',master_user='slave2',master_password='FjAfj6#xajot#K%V',master_log_file='mysql-bin.000001',master_log_pos=154

Turn on synchronization function

Mysql > start slave

5. Check the slave database status:

Mysql > show slave status\ G

Note: Slave_IO_Running and Slave_SQL_Running processes must be running normally, that is, YES status, otherwise synchronization failed. These two items can be used to determine whether the slave server hung up or not.

Mysql > SET GLOBAL server_id=2

6 、 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Cause analysis:

The replication of mysql 5.6introduces the concept of uuid. The server_uuid in each replication structure has to be guaranteed to be different, but the server_uuid is the same after viewing the direct copy data folder, show variables like'% server_uuid%'.

Solution:

Find the auto.cnf file under the data folder, modify the UUID value in it, make sure that the uuid of each db is different, and restart db.

Scenario 2: when creating a master-slave relationship, copy uses the same my.cnf file and reports an error.

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

Cause analysis:

Like server_uuid, servier_id has to make sure it's different.

Solution:

Find the server_id in the my.cnf configuration file, modify the server_id of the slave library to ensure that it is different from other db in the replication structure, and restart db.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report