Detailed analysis of MySQL-Replication monitoring and automatic failover

1. Server planning

Master:192.168.0.152
Slave: 192.168.0.153 192.168.0.154
Monitoring server: 192.168.0.154
Note: True In the production environment, it is best to have a separate server to monitor the database Replication. Here I will directly use a Salve server as the monitoring server.

2, InstallationMySQL Utilities and MySQL Connectors

Download MySQL Utilities and MySQL Connectors and install
at 192.168.0.154 (Monitoring server) Execute the following commands on the server to install the downloaded MySQL Utilities and MySQL Connectors

yum install mysql-connector-python-2.1.4-1.el6.x86_64.rpm mysql-utilities-1.6.4-1.el6.noarch.rpm

3. Configure the Replcation environment, one Master and two Slave environments

Master:192.168. 0.152
Slave: 192.168.0.153 192.168.0.154
Monitoring Server: 192.168.0.154
For configuring Replication, please refer to the blog posts "MySQL - MS Master-Slave Replication (Read-Write Separation) Implementation" and "MySQL - —MSS master-slave replication (read-write separation) implementation》 , "MySQL - Four steps to upgrade BinLog Replication to GTIDs Replication".

4. Authorize the monitoring machine

on 192.168.0.152, 192.168.0.153 and 192.168.0.154 servers respectively:

mysql>grant create,insert ,drop, select, super, replication slave, reload on *.* to 'replm'@'192.168.0.154' identified by password 'replm' with grant option;

on 192.168.0.152, 192.168.0.153 and 192.168.0.154 execute

mysql>show grants for replm@192.168.0.154;

respectively to verify whether the authorization is successful.

Note: If a separate server monitors Replication, authorization operations are only performed on the Master and Slave servers involved in Replication, and the authorization target IP is the monitoring server IP

5. Modify the configuration files of the Master and Slave servers and enable the configuration

The following is the core configuration in the my.cnf file when configuring Replication monitoring and failover

[mysqld]
log-bin=/data/mysql3306log/mysql-bin
binlog-format=mixed
server-id = 001
sync_binlog=1
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
report-host=192.168.0.154
master-info-repository=TABLE
relay-log-info-repository=TABLE

After modifying the configuration, restart the mysql server, and then the mysql server will save the two files master.info and relay-log.info stored in the binary log to the database. At this time, execute use mysql;show tables; You will find that two tables, slave_master_info and slave_relay_log_info, are newly created in the mysql database.

6. Start the monitoring terminal

Execute

#mysqlfailover --master=replm:replm@192.168.0.152 --discover-slaves-login=replm:replm

on 192.168.0.154 (monitoring server). At this point, we have configured Replication monitoring and automatic Failover functionality now.
Note: When the Master server hangs up, mysqlfailover will automatically select one of the slave servers as the Master, and the other slaves will correspond to the new Master server.
Note: When the Master hangs up, mysqlfailover automatically selects a server among other slaves as the Master. When we manually restart the original Master server, mysqlfailover cannot automatically find that the original hung mysql server has been restarted. At this time, We need to manually restart the originally hung mysql server and add it to Replication as a Slave server.

The above is the detailed content of Detailed analysis of MySQL-Replication monitoring and automatic failover. For more information, please follow other related articles on the PHP Chinese website!