Mysql master multi slave synchronization configuration

Mysql master multi slave synchronization configuration

Framework

The principle of one master and many slaves is the same as that of one master and one slave:

graph LR
master-->slave1
master-->slave2
master-->slave...

Environmental Science

master: 192.168.101

MYSQL edition:5.1.48-community-log

slave1:192.168.2.182

MYSQL edition:5.1.48-community-log

slave2:192.168.2.111

MYSQL edition:5.1.48-community-log

so...1 vs 2. 

master configuration

Configure my.cnf

[root@mysql101 ~]# vi /etc/my.cnf
[mysqld]
datadir = /data/mysql
socket = /tmp/mysql.sock
server-id = 1
log-bin=mysql-bin
binlog-do-db=YYY //Databases that need to be synchronized
binlog-ignore-db=mysql //Database ignored
binlog-ignore-db=information-schema //Database ignored  

Note: restart mysql service after the above configuration.

Create synchronization user

mysql> grant replication slave on *.* to 'affairlog'@'192.168.2.182' identified by 'pwd123';

//Login succeeded on slave1

mysql> grant replication slave on *.* to 'affairlog'@'192.168.2.111' identified by 'pwd123';

//Login succeeded on slave 2

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000087
Position: 106
Binlog_Do_DB: YYY
Binlog_Ignore_DB: mysql,information-schema
1 row in set (0.00 sec)

slave configuration

[root@mysql182 ~]# vi /etc/my.cnf
[mysqld]
datadir = /data/mysql
socket = /tmp/mysql.sock
server-id=182
master-host=192.168.3.101
master-user= affairlog
master-password=pwd123
master-port=3306
master-connect-retry=60
replicate-do-db=YYY //Synchronized database
replicate-ignore-db=mysql //Database ignored
replicate-ignore-db=information-schema //Database ignored

Note: restart mysql service after configuration.
Modify the configuration file my.cnf on slave2, which is similar to the above. Just change the server ID. for convenience, I use the corresponding ip bit. The server ID I set on so and slave2 is 111.

Synchronization configuration

Enter mysql in two slave machines.

mysql>start slave;

mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If the slave IO running and slave SQL running states in both slaves are Yes, the settings are successful.

common problem

Error 1:

Slave? IO? Running: no or slave? SQL? Running: no

terms of settlement:

Stop slave first:

mysql> slave stop;

master:

mysql> show master status\G;

*************************** 1. row ***************************
File: mysql-bin.000087
Position: 1845
Binlog_Do_DB: YYY
Binlog_Ignore_DB: mysql,information-schema
1 row in set (0.00 sec)

Manual synchronization to slave:

mysql>change master to
>master_host='192.168.3.101',
>master_user='affairlog',
>master_password='pwd123',
>master_log_file='mysql-bin.000087',
>master_log_pos=1845;

Start the slave service:

mysql> slave start;

Check the slave IO running and slave SQL running status again. If Yes, the settings are successful.

Error 2:

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

terms of settlement

Stop the slave service

mysql> slave stop;

Reset the slave service

mysql> reset stop;

Execute the change command again

mysql>change master to
>master_host='192.168.3.101',
>master_user='affairlog',
>master_password='pwd123',
>master_log_file='mysql-bin.000087',
>master_log_pos=1845;

Start the slave service

mysql> slave start;

Check the slave IO running and slave SQL running status again. If Yes, the settings are successful.

PS:
Slave_IO_Running: Connect to the main database, read the log of the main database to the local, and generate the local log file
Slave_SQL_Running:Read the local log file and execute the SQL Command.

Original address: http://blog.sina.com.cn/s/blog_4c197d4201017qjs.html

Tags: MySQL Database SQL socket

Posted on Mon, 04 May 2020 01:01:52 -0400 by Yanayaya