Remarks about mysql dual-machine hot standby
The previous article has already introducedMaster-slave ReplicationThis article only briefly describes master-slave replication. If you are not clear about master-slave replication, you can read the previous article first.Master-slave Replication
I. Introduction
Mysql version: 5.7.20
Ip address of the first master server: 192.168.71.139
Ip address of the second master server: 192.168.71.141
Ii. Configuration
First master server 192.168.71.139
1: Modify the/etc/mysql/my. cnf file. Note that # Is a comment here. Do not write it into the configuration file.
Server-id = 141 # server id, which cannot be repeated. We recommend that you use the last three ip addresses.
Log-bin = mysql-bin
Binlog-ignore-db = mysql, information_schema # ignore the database written to binlog
Auto-increment = 2 # field change increment Value
Auto-increment-offset = 1 # the initial field ID is 1
Slave-skip-errors = all # ignore all replication errors
2: log on to mysql and create an account that allows replication by other servers.
Grant replication slave on *. * to 'mysql account' @ '%' identified by 'Password ';
3: Use show master status to query the status
Second master server 192.168.71.139
1: Modify the/etc/mysql/my. cnf file. The server-id = 139 here, and the others remain unchanged.
Use show master status to query the status
In this case, you need to restart the mysql
Execute the synchronization statement at 192.168.71.141.
The master_log_file value is from the 139 server and the File field after the show master status Command is executed.
The master_log_file value is from the 139 server and the Position field after the show master status Command is executed.
change master to master_host=’192.168.71.139’,master_user=’master2’,master_password=’123456’,master_log_file=’mysql-bin.000002’,master_log_pos=154;
Execute the synchronization Statement on 192.168.71.139.
The master_log_file value is from the 141 server and the File field after the show master status Command is executed.
The master_log_file value is from the 141 server and the Position field after the show master status Command is executed.
Change master Master_host = '2017. 192.141', Master_user ='Master1', Master_password = '000000', master_log_file = 'mysql-bin.000002', Master_log_pos =154;
At this point, the configuration is complete. Restart mysql, log on to mysql, and useShow slave status \ G check configuration status, foundSlave_IO cannot be started, and the following error occurs:
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Logs show that the uuids of the master and slave are repeated. Because the two servers are cloned, You need to modify them./Var/lib/mysql/auto. cnf
Here, I only modify the last letter. Because there are too many modifications, mysql cannot be started. After modification, restart mysql, log on to mysql, and execute show slave status \ G, as shown in figure
Iii. Test
Execute the following SQL statement on any server:
create table tab141(id int primary key);create table tab139(id int primary key);
Run the following SQL statement on the 139 Server:
insert into tab139 values(1);
Run the following SQL statement on the 141 Server:
insert into tab141 values(2);
The result is as follows:
If this article is helpful to you, you can click here for recommendations. Thank you!