MySQL master replication with slave server
wx59e2f041219d8 2021-08-01 19:57:14

 

 

This article will introduce... In active and passive mode MySql Master master copy , Compared with the primary replication in the active mode mentioned in the previous article , The biggest difference is , One of the primary servers is a read-only passive server ( Not fixed , Dynamic switching ), Therefore, it solves the conflict problem in the active mode and the possible data errors caused by inserting data at the same time ( High performance MySql There is a detailed analysis in that book ), In addition, the suspension of a primary server does not affect the writing of data , To achieve high availability ( Not completely ).

 

To implement this structure , We need to use open source projects MySql-MMM( In fact, this tool is perl Script ), He abstracted the role of the server into writer and reader, And mixed with fixed IP And virtual floating IP, When the monitor finds one Master When the server fails , Will automatically float IP hold writer Switch roles to another master The server , So as to realize the change of role , And this tool realizes the load balancing of read operation to a certain extent .

 

Okay , Get to the point , We configure it while reading the instructions on the official website .
Our structure is 1 Monitoring machine monitor,2 platform Master,2 platform slave, The structure diagram is as follows ( Click on the picture to see the big picture ):

 

Various setting information of each machine :

 

 ?
1
2
3
4
5
6
Host name      Fix ip Address        MySql The server ID
monitor   192.168.0.100   Monitor , No installation required MySql The server
master1   192.168.0.101   101
master2   192.168.0.102   102
slave1    192.168.0.103   103
slave2    192.168.0.104   104

 

fictitious IP And reading and writing role agreement ( When we're done configuring MySql-MMM after , When the program reads and writes data IP):

 

 ?
1
2
3
4
5
6
fictitious IP           role
192.168.0.200   writer(MySql Used when writing IP)
192.168.0.201   reader(MySql Used in read operations IP, The same below )
192.168.0.202   reader
192.168.0.203   reader
192.168.0.204   reader

 

The test system OS:CentOS5.6

 

First step , stay 4 platform MySql Install... In the server respectively MySql, And configure the setting file :
1. install MySql The server , We use the official my-medium.cnf As an initial setting file .

 

 ?
1
2
[root@m /m/s/s ~] # yum -y install mysql mysql-server
[root@m /m/s/s ~] # \cp -f /usr/share/doc/mysql-server-*/my-medium.cnf /etc/my.cnf

 

2. modify /etc/my.cnf, stay [mysqld] Add the following content after the field :
Particular attention : If the configuration file already has the configuration parameter , Be sure to delete it first and then , Add our settings !

 

 ?
1
2
3
4
5
6
7
8
9
server_id                = 101 /102/103/104 <- Each server shall be set with the corresponding settings determined by us server_id
log_bin                  = mysql-bin
log_bin_index            = mysql-bin.index
log_slave_updates        = 1
relay_log                = mysql-relay-bin
relay_log_index          = mysql-relay-bin.index
sync_binlog              = 1
max_binlog_size          = 200M
slave-skip-errors        = 1062,1053

 

The second step , Create for monitoring , Copy , And managed MySql user , And turn on copy :
1. start-up MySql And set up root Initial password , stay 4 platform MySql Execute... Separately on the server :

 

 ?
1
2
[root@m /m/s/s ~] # /etc/init.d/mysqld start
[root@m /m/s/s ~] # /usr/bin/mysqladmin -u root password 'mysql_password'

 

2. Additional 3 Users , stay 4 platform MySql Execute... Separately on the server ( Or on a single computer , Then export the database , use scp Upload to another 3 platform , Reimport data ):

 

 ?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@m /m/s/s ~] # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.77-log Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor' @ '192.168.0.%' IDENTIFIED BY
  'mmm_monitor_pass' ;
Query OK, 0 rows affected (0.05 sec)
 
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent' @ '192.168.0.%'
    IDENTIFIED BY 'mmm_agent_pass' ;
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli_user' @ '192.168.0.%' IDENTIFIED BY '
repli_user_pass';
Query OK, 0 rows affected (0.03 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

 

mmm_monitor be used for monitor Machine monitoring 4 platform MySql The state of the server
mmm_agent be used for MySql The server itself changes the read-write state and copies the master server object ( For example, originally from A Copy , Now from B Copy )
repli_user Users for replication
Particular attention : The user name and password used in the following commands and configuration files , It must be consistent with what we just added .

 

3. Turn on copy ( Used in this step IP Don't use the virtual we agreed earlier IP):
stay master1 On the implementation :

 

 ?
1
2
3
4
5
6
7
8
9
10
11
12
[root@master1 ~] # mysql -uroot -p
Enter password:
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.0.102' ,
     -> MASTER_PORT=3306,
     -> MASTER_USER= 'repli_user' ,
     -> MASTER_PASSWORD= 'repli_user_pass' ,
     -> MASTER_LOG_FILE= 'mysql-bin.000001' ,
     -> MASTER_LOG_POS=0;
Query OK, 0 rows affected (0.20 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G

 

stay master2/slave1/slave2 On the implementation of separate :

 

 ?
1
2
3
4
5
6
7
8
9
10
11
12
[root@master2 /slave1/slave2 ~] # mysql -uroot -p
Enter password:
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.0.101' ,
     -> MASTER_PORT=3306,
     -> MASTER_USER= 'repli_user' ,
     -> MASTER_PASSWORD= 'repli_user_pass' ,
     -> MASTER_LOG_FILE= 'mysql-bin.000001' ,
     -> MASTER_LOG_POS=0;
Query OK, 0 rows affected (0.20 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G

 

Only this and nothing more , The replication relationship has been configured , You can separate in 2 individual master Insert some data into the server to test .
hot tip :
ad locum , We default to slave1 and slave2 Copy master The machines are set to master1(192.168.0.101), In fact slave1 and slave2 Copy master Whether the machine is set to master1(192.168.0.101) still master2(192.168.0.102), It doesn't really matter , Because when 2 individual master When one of them dies ,mysql-mmm Will automatically help us slave Copy master Switch to another normal master On .

 

The third step , Install and configure MySql-MMM
1. Because of the standard yum The software is not in the source , So let's install it first EPEL Of yum Source , stay 5 Execute... Separately in each machine :

 

 ?
1
2
[root@m /m/m/s/s ~] # wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
[root@m /m/m/s/s ~] # rpm -ivh epel-release-5-4.noarch.rpm

 

If your system is RHEL6.x perhaps CentOS6.x, Then download and install :

 

http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm

 

2. Use yum install MySql-MMM Related components of ( Other dependent components will be installed automatically ).
A. stay monitor You need to install mysql-mmm and mysql-mmm-monitor:

 

 ?
1
yum -y install mysql-mmm mysql-mmm-monitor

 

B. stay 4 platform MySql You need to install... On the server mysql-mmm and mysql-mmm-agent

 

 ?
1
yum -y install mysql-mmm-common mysql-mmm-agent

 

3. stay 5 It's configured on one machine /etc/mysql-mmm/mmm_common.conf file , The content is exactly the same , as follows :

 

 ?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
active_master_role          writer
 
<host default>
     cluster_interface       eth0
 
     pid_path                /var/run/mysql-mmm/mmm_agentd .pid
     bin_path                /usr/libexec/mysql-mmm/
 
     replication_user        repli_user
     replication_password    repli_user_pass
 
     agent_user              mmm_agent
     agent_password          mmm_agent_pass
< /host >
 
<host master1>
     ip                      192.168.0.101
     mode                    master
     peer                    master2
< /host >
 
<host master2>
     ip                      192.168.0.102
     mode                    master
     peer                    master1
< /host >
 
<host slave1>
     ip                      192.168.0.103
     mode                    slave
< /host >
 
<host slave2>
     ip                      192.168.0.104
     mode                    slave
< /host >
 
<role writer>
     hosts                   master1, master2
     ips                     192.168.0.200
     mode                    exclusive
< /role >
 
<role reader>
     hosts                   master1, master2, slave1, slave2
     ips                     192.168.0.201, 192.168.0.202, 192.168.0.203, 192.168.0.204
     mode                    balanced
< /role >

 

* If you don't like it 5 It's troublesome to configure all the machines , Also to ensure that the configuration files are consistent , You can monitor(192.168.0.100) The configuration is finished , Let's use the following scp Command to copy the file to MySql The corresponding directory on the server ( Of course, the premise is that we can ssh Sign in ).

 

 ?
1
2
3
4
scp /etc/mysql-mmm/mmm_common .conf root@192.168.0.101: /etc/mysql-mmm/mmm_common .conf
scp /etc/mysql-mmm/mmm_common .conf root@192.168.0.102: /etc/mysql-mmm/mmm_common .conf
scp /etc/mysql-mmm/mmm_common .conf root@192.168.0.103: /etc/mysql-mmm/mmm_common .conf
scp /etc/mysql-mmm/mmm_common .conf root@192.168.0.104: /etc/mysql-mmm/mmm_common .conf

 

4. stay 4 platform MySql Configure the server separately /etc/mysql-mmm/mmm_agent.conf, The contents are as follows :

 

 ?
1
2
include mmm_common.conf
this master1 /master2/slave1/slave2 <-- Use your own hostname

 

5. Monitoring the server monitor On the configuration /etc/mysql-mmm/mmm_mon.conf, The contents are as follows :

 

 ?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
include mmm_common.conf
 
<monitor>
     ip                      127.0.0.1
     pid_path                /var/run/mysql-mmm/mmm_mond .pid
     bin_path                /usr/libexec/mysql-mmm
     status_path             /var/lib/mysql-mmm/mmm_mond .status
     ping_ips                192.168.0.1, 192.168.0.101, 192.168.0.102, 192.168.0.103, 192.168.0.104
#   auto_set_online         10
< /monitor >
 
<host default>
     monitor_user            mmm_monitor
     monitor_password        mmm_monitor_pass
< /host >
 
debug 0

 

It should be noted that , above ping_ips The function of that configuration parameter is to check the corresponding... From the monitoring machine ip Yes no ping through , It helps us understand our environment and network , Here I set up the gateway ip and 4 platform MySql The server is fixed ip.

 

Step four , start-up MySql-MMM
1. First, in the 4 platform MySql Start on Service mysql-mmm-agent, And added to boot auto start :

 

 ?
1
2
3
[root@m /m/s/s ~] # /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [  OK  ]
[root@m /m/s/s ~] # chkconfig mysql-mmm-agent on

 

2. On the monitor monitor Start the mysql-mmm-monitor, And added to boot auto start :

 

 ?
1
2
3
[root@monitor ~] # /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon: [  OK  ]
[root@monitor ~] # chkconfig mysql-mmm-monitor on

 

Step five , stay monitor Confirm our cluster situation in , And then MySql The server is placed in ONLINE state :
1. Wait a minute , Check the status :

 

 ?
1
2
3
4
5
[root@monitor ~] # mmm_control show
   master1(192.168.0.101) master /AWAITING_RECOVERY . Roles:
   master2(192.168.0.102) master /AWAITING_RECOVERY . Roles:
   slave1(192.168.0.103) slave /AWAITING_RECOVERY . Roles:
   slave2(192.168.0.104) slave /AWAITING_RECOVERY . Roles:

 

2. Set up ONLINE state , First set up master1, Because by default slave1 and slave2 Of master It's all master1:

 

 ?
1
2
3
4
5
6
7
8
[root@monitor ~] # mmm_control set_online master1
OK: State of 'master1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor ~] # mmm_control set_online master2
OK: State of 'master2' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor ~] # mmm_control set_online slave1
OK: State of 'slave1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor ~] # mmm_control set_online slave2
OK: State of 'slave2' changed to ONLINE. Now you can wait some time and check its new roles!

 

More operations , Like change role etc. , perform mmm_control help View help information .

 

Particular attention : Now explain monitor On board /etc/mysql-mmm/mmm_mon.conf The sentence I annotated in auto_set_online Parameters :
Do not set this parameter :MySql The server has been down for more than 60 Seconds , After the server is restored, it will always be in AWAITING_RECOVERY The state of , We need to manually set_online Set it to zero ONLINE state , If lower 60 second , And not Flapping state , It is automatically set to ONLINE state .
Set this parameter : The server recovered to AWAITING_RECOVERY Post state , Will wait for the number of seconds we set , Then it is automatically set to ONLINE state ( Also need to consider Flapping state ).
therefore , Set and not set auto_set_online The parameters are based on your needs , I prefer not to set .

 

Next , You can go to the main server master Create a user you want to use , Then link to our writer role and reader role To test .
If you encounter problems during configuration or testing , Please carefully check the parameters you set , And whether the references of the same parameter in various places are consistent . Please refer to the official documentation for more detailed configuration parameters and help .

 

 
 
 
 
Please bring the original link to reprint ,thank
Similar articles

2021-06-04

2021-06-05

2021-06-13