Detailed description of MySQL high-availability MMM construction scheme and architecture principle, mysqlmmm

Detailed description of MySQL high-availability MMM construction scheme and architecture principle, mysqlmmm

Let's take a look at the architecture, such:

Deployment

1. Modify hosts

Perform the same operation on all servers.

Vim/etc/hosts

192.168.137.10 master192.168.137.20 backup192.168.137.30 slave192.168.137.40 monitor

2. Add a mysql user

You only need to execute the command on all databases, but not on the monitoring end.

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.137.%' IDENTIFIED BY 'mmm_monitor'; GRANT SUPER,REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.137.%' IDENTIFIED BY 'mmm_agent';flush privileges;

Note: The repl user has already created the Master/Slave service.

3. install monitoring software

Note: The monitoring end is fully installed, but the monitoring end only needs to use mysql-mmm-monitor, and mysql-mmm-agent does not need to be started.

You only need to install mysql-mmm-agent on the Database End.

1. Run the command on the Monitoring Server

wget http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpmrpm –ivh epel-release-6-8.noarch.rpmyum –y install mysql-mmm*

2. Run the command on the database server.

wget http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpmrpm –ivh epel-release-6-8.noarch.rpmyum -y install mysql-mmm-agent

Path description:

4. Configuration File

Db server configuration file:mmm_agent.conf，mmm_common.conf

Configuration file of the monitoring server:mmm_mon.conf，mmm_common.conf(All servers of this file are the same)

Configure the db Server

1. Modify the master server

vim /etc/mysql-mmm/mmm_common.conf

Active_master_role writer ### positive master role identification. The read_only parameter must be enabled for all db servers. For writer server monitoring agents, the read_only attribute is automatically disabled. <Host default> cluster_interface eth0 ##### cluster network interface pid_path/var/run/mysql-mmm/mmm_agentd.pid #### pid path bin_path/usr/libexec/mysql- mmm/##### executable file path replication_user repl ####### copy user replication_password repl ####### copy user password agent_user mmm_agent #### ### proxy user, used to change the read-only operation agent_password mmm_agent ###### proxy User Password 
At the same time, copy the file to other servers, including the monitoring server. The configuration remains unchanged.
scp /etc/mysql-mmm/mmm_common.conf slave:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf backup:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf monitor:/etc/mysql-mmm/
2. Proxy file configuration
Vim/etc/mysql-mmm/mmm_agent.conf
 
Note: this configuration only configures the db server. You do not need to configure the monitoring server. The host name after this is changed to the host Command of the current server. master2 and slave are also changed to the host Name of the corresponding server.
3. Start the Agent process 
chkconfig mysql-mmm-agent on
service mysql-mmm-agent start
Start each db Server
Configure Monitoring Server
vim /etc/mysql-mmm/mmm_mon.conf
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 ## ### cluster status file, that is, the display source for executing the mmm_control show operation. Ping_ips 192.168.137.10, 192.168.137.20, 192.168.137.30 ###### IP address of the monitored db Server auto_set_online 0 #### set the automatic online time, by default, it is set to online after 60 s, and The default value is 60 s. Here, if it is set to 0, it is online immediately # The kill_host_bin does not exist by default, though the monitor will # throw a warning about it missing. see the section 5.10 "Kill Host # Functionality" in the PDF documentation. # kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host # </monitor> 
NOTE: Annotations in the configuration file are for ease of understanding. You are advised to remove annotations during deployment to avoid potential impact caused by annotations.
Start the monitoring process
chkconfig mysql-mmm- monitor onservice mysql-mmm-monitor start
Note: You must restart the Agent process and monitoring process to modify the configuration file on the db or monitoring end.
Operation Analysis
Log Files
Log files are often the key to analyzing errors. Therefore, you must be good at using log files for problem analysis.
Db end：/var/log/mysql-mmm/mmm_agentd.log
Monitoring end：/var/log/mysql-mmm/mmm_mond.log
Command file
Mmm_agentd: the Startup file of the db proxy process

Mmm_mond: the Startup file of the monitoring process

Mmm_backup: Backup File

Mmm_restore: restore a file

Mmm_clone

Mmm_control: monitoring operation command file
The db Server only has the mmm_agentd program, and the others are on the monitor server.
Mmm_control usage
The mmm_control program can be used to monitor the cluster status, switch writer, and set online/offline operations.
Valid commands are: help-show this message ### help information ping-ping monitor ### ping whether the current cluster is normal show-show status #### check the online status of the cluster checks [
1. Check the status of all db Server clusters.
[root@monitor mysql-mmm]# mmm_control checks allmaster ping   [last change: 2016/06/07 16:31:24] OKmaster mysql  [last change: 2016/06/07 16:31:24] OKmaster rep_threads [last change: 2016/06/07 16:31:24] OKmaster rep_backlog [last change: 2016/06/07 16:31:24] OK: Backlog is nullslave ping   [last change: 2016/06/07 16:31:24] OKslave mysql  [last change: 2016/06/07 16:31:24] OKslave rep_threads [last change: 2016/06/07 16:31:24] OKslave rep_backlog [last change: 2016/06/07 16:31:24] OK: Backlog is nullbackup ping   [last change: 2016/06/07 16:31:24] OKbackup mysql  [last change: 2016/06/07 16:31:24] OKbackup rep_threads [last change: 2016/06/07 16:31:24] OKbackup rep_backlog [last change: 2016/06/07 16:31:24] OK: Backlog is null
Check items include ping, mysql running, and replication thread running.
2. Check the online status of the Cluster Environment
[root@monitor mysql-mmm]# mmm_control show backup(192.168.137.20) master/ONLINE. Roles: reader(192.168.137.120) master(192.168.137.10) master/ONLINE. Roles: writer(192.168.137.100) slave(192.168.137.30) slave/ONLINE. Roles: reader(192.168.137.130)
3. Execute the offline operation on the specified host
[root@monitor mysql-mmm]# mmm_control set_offline backupOK: State of 'backup' changed to ADMIN_OFFLINE. Now you can wait some time and check all roles![root@monitor mysql-mmm]# mmm_control show backup(192.168.137.20) master/ADMIN_OFFLINE. Roles:  master(192.168.137.10) master/ONLINE. Roles: writer(192.168.137.100) slave(192.168.137.30) slave/ONLINE. Roles: reader(192.168.137.120), reader(192.168.137.130)
4. Perform onine operations on the specified host
[root@monitor mysql-mmm]# mmm_control set_online backupOK: State of 'backup' changed to ONLINE. Now you can wait some time and check its new roles![root@monitor mysql-mmm]# mmm_control show backup(192.168.137.20) master/REPLICATION_FAIL. Roles:  master(192.168.137.10) master/ONLINE. Roles: writer(192.168.137.100) slave(192.168.137.30) slave/ONLINE. Roles: reader(192.168.137.120), reader(192.168.137.130)[root@monitor mysql-mmm]# mmm_control show backup(192.168.137.20) master/ONLINE. Roles: reader(192.168.137.120) master(192.168.137.10) master/ONLINE. Roles: writer(192.168.137.100) slave(192.168.137.30) slave/ONLINE. Roles: reader(192.168.137.130)
5. Execute write Switch
1. view the master corresponding to the current slave
[root@slave ~]# mysql -uroot -proot -e 'show slave status \G;'Warning: Using a password on the command line interface can be insecure.*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event     Master_Host: 192.168.137.10     Master_User: repl     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000073   Read_Master_Log_Pos: 1461    Relay_Log_File: mysql-relay-bin.000006    Relay_Log_Pos: 283  Relay_Master_Log_File: mysql-bin.000073    Slave_IO_Running: Yes   Slave_SQL_Running: Yes
2. For writer switchover, make sure that the writer attribute in the mmm_common.conf file has the host configured. Otherwise, the writer cannot be switched.
[root@monitor mysql-mmm]# mmm_control move_role writer backupOK: Role 'writer' has been moved from 'master' to 'backup'. Now you can wait some time and check new roles info![root@monitor mysql-mmm]# mmm_control show backup(192.168.137.20) master/ONLINE. Roles: reader(192.168.137.120), writer(192.168.137.100) master(192.168.137.10) master/ONLINE. Roles:  slave(192.168.137.30) slave/ONLINE. Roles: reader(192.168.137.130)
3. the save function automatically switches from the database to the new master.
[root@slave ~]# mysql -uroot -proot -e 'show slave status \G;'Warning: Using a password on the command line interface can be insecure.*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event     Master_Host: 192.168.137.20     Master_User: repl     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000039   Read_Master_Log_Pos: 120    Relay_Log_File: mysql-relay-bin.000002    Relay_Log_Pos: 283  Relay_Master_Log_File: mysql-bin.000039    Slave_IO_Running: Yes   Slave_SQL_Running: Yes
Other troubleshooting problems
If you do not want the writer to switch from the master to the backup (including the master/Slave latency will also lead to the switch of the write VIP), you can configure/etc/mysql-mmm/mmm_common.conf,Remove backup from <role write>
<Role writer >#### configure the hosts master for the writer role ### configure only one Hosts ips 192.168.137.100 #### VIP mode exclusive for write operations provided externally ## ### exclusive indicates that only one master is allowed, that is, only one write IP address can be provided </role>
In this case, if the master node fails, the writer write operation will not be switched to the backup server, and the slave will not point to the new master node. At this time, the current MMM will provide the write service to the outside.
Summary
The latency of the master and slave nodes may cause the switch of the write VIP address.
1. The virtual IP addresses that provide read/write externally are controlled by the monitor program. If the monitor is not started, the db server will not be assigned a virtual ip address, however, if you have already allocated a virtual ip address, when the monitor program closes the originally assigned virtual ip address, it does not immediately close the external program and can connect to the network (as long as the network is not restarted ), the advantage is that the reliability requirements for the monitor will be lower, and I do not know how long it will last. However, if one of the db servers fails at this time, the switchover will fail, that is to say, the original virtual ip address remains unchanged, and the virtual ip address of the database that is suspended will become inaccessible.
2. The agent program is controlled by the monitor program to handle write switching and slave database switching. If the monitor process is disabled, the agent process will not be able to handle faults.
3. The monitor program monitors the status of the db server, including Mysql database, server running, replication thread running, and Master/Slave latency. It also controls the agent program to handle faults.
4. monitor monitors the status of the db server every several seconds. If the db server has changed from faulty to normal, then, the monitor automatically sets it to the online status after 60 s (the default value is 60 s and can be set to another value). The parameter "auto_set_online" in the configuration file of the monitoring end is determined, the cluster server has three statuses: HARD_OFFLINE → AWAITING_RECOVERY → online
5. by default, monitor controls the mmm_agent to change read_only of the writer db server to OFF, And read_only of other db servers to ON. Therefore, we can strictly. in the cnf file, read_only = 1 is added and controlled by the monitor to control writer and read. The root user and the copy user are not affected by the read_only parameter.
The above is all the content of this article. I hope this article will help you learn mysql.