Detailed Mysql high available MMM build scheme and framework principle _mysql

First look at the architecture, as shown in the following figure:

Deployment

1. Modify the Hosts

Perform the same operation on all servers.

Vim/etc/hosts

192.168.137.10 Master
192.168.137.20 backup
192.168.137.30 slave
192.168.137.40 Monitor

2. Add MySQL User

Only need to execute at all database end, the monitor end does not need.

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. Installation of monitoring software

Note: The monitor end is all installed, but the monitoring end only needs to use the mysql-mmm-monitor,mysql-mmm-agent does not need to start.

The database side only needs to install Mysql-mmm-agent

1. In the monitor server execution

wget http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
RPM–IVH epel-release-6-8.noarch.rpm
yum–y Install mysql-mmm*

2. Execute on the database server, on each database server

wget http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
RPM–IVH epel-release-6-8.noarch.rpm
yum-y Install mysql-mmm-agent

Path Description:

4. Configuration file

DB Server configuration file:mmm_agent.conf，mmm_common.conf

Monitor server configuration file: mmm_mon.conf，mmm_common.conf (same as all servers in this file)

Configuring DB Servers

1. Modify master server

Vim/etc/mysql-mmm/mmm_common.conf

Active_master_role Writer # # #积极的master角色的标示, all DB servers need to turn on the read_only parameters, and the writer server Monitoring agent automatically closes the Read_Only attribute.  

At the same time copy this file to other servers including the monitoring server, configuration 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, the monitor server does not need to be configured, the host name after this is changed to the host command of the current server, and Master2 and slave are also changed to the host name of the corresponding server.

3. Start Agent process

Chkconfig mysql-mmm-agent on

Service Mysql-mmm-agent Start

Need to start on each DB server

Configuring the 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 ##### The status file for the cluster, which is the display source for performing the Mmm_control show operation.
 ping_ips   192.168.137.10,192.168.137.20,192.168.137.30 ##### #被监控的db服务器的ip地址
 auto_set_online  0 ### #设置自动online的时间, the default is more than 60s to set it to online, the default is 60s, where it is set to 0 is immediately online

 # The Kill_host_bin does not exist by Default, though the monitor would
 # throw a warning about it missing. 5.10 "Kill Host
 # functionality" in the PDF documentation.
 # #
 Kill_host_bin  /usr/libexec/mysql-mmm/monitor/kill_host
 #
</monitor>

 
 

Note: The comments in the configuration file are intended to be understandable and should be removed from the potential impact of annotations when deployed.

Start the monitoring process

Chkconfig mysql-mmm-monitor on
service mysql-mmm-monitor start

Note: either on the DB side or on the monitor side, you need to reboot the agent process and monitor the process if there is a modification to the configuration file.

Operation Analysis

Log files

Log files are often the key to parsing errors, so 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

startup files for the MMM_AGENTD:DB agent process

Mmm_mond: Monitor the process's startup files

Mmm_backup: Backing up files

Mmm_restore: Restoring Files

Mmm_clone

Mmm_control: Monitor operation command File

DB Server side only Mmm_agentd program, the other is on the monitor server side.

Mmm_control usage

Mmm_control programs can be used to monitor cluster status, toggle writer, set online\offline operations, and so on.

Valid commands are: Help-show this message        # # #帮助信息
 Ping        -ping Monitor #
 #ping当前的群集是否正常        Show -Show status ### #群集在线状态检查
 checks [ 
 

1. Check all DB server cluster status

[Root@monitor mysql-mmm]# Mmm_control checks all
master ping   [last change:2016/06/07 16:31:24] OK
Master MySQL  [last change:2016/06/07 16:31:24] OK
master rep_threads [last change:2016/06/07 16:31:24] OK
master R Ep_backlog [last change:2016/06/07 16:31:24] ok:backlog are null
slave ping   [last change:2016/06/07 16:31:24] OK
slave MySQL  [Last change:2016/06/07 16:31:24]  OK
slave rep_threads [last change:2016/06/07 16:31:24] OK
slave rep_backlog [last change:2016/06/07 16:31:24] Ok:backlog is null
backup ping   [last change:2016/06/07 16:31:24] OK
backup MySQL  [last change:2016 /06/07 16:31:24] OK
backup rep_threads [last change:2016/06/07 16:31:24] OK
backup Rep_backlog [Last Change:20 16/06/07 16:31:24] Ok:backlog is null

Checks include: Ping, whether MySQL is running correctly, replication thread is normal, etc.

2. Check the status of the cluster environment online

[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. Perform offline operations on the specified host

[Root@monitor mysql-mmm]# Mmm_control set_offline backup
ok:state of ' backup ' changed to Admin_offline. Now I 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 backup
ok:state of ' backup ' changed to online. Now, can, wait some, 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. Perform write switch

1. View the current slave corresponding master

[Root@slave ~]# mysql-uroot-proot-e ' show slave status \g; '
Warning:using a password on the command line interface can is 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.writer switch, to ensure that the writer property in the mmm_common.conf file has a configuration corresponding host, otherwise you cannot switch

[Root@monitor mysql-mmm]# Mmm_control move_role writer backup ok:role ' writer ' has been ' moved from
' master ' to ' Backu P '. Now I 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.save automatically switches from the library to the new master

[Root@slave ~]# mysql-uroot-proot-e ' show slave status \g; '
Warning:using a password on the command line interface can is 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 handling issues

If you do not want writer from master to backup (including the master-slave delay will also lead to write VIP switch), then you can configure/ etc/mysql-mmm/mmm_common.conf , remove <role write> in the backup

<role writer> ### #writer角色配置
 hosts master # #这里只配置一个Hosts
 IPs  192.168.137.100 # # # # #对外提供的写操作的虚拟IP
 mode exclusive #exclusive代表只允许存在一个主, which is to provide only a written IP
</role>

In this case, when master fails, the writer write operation does not switch to the backup server, and slave does not point to the new master, which then provides a write service to the current MMM.

Summarize

The master-slave delay will lead to the handover of VIP writing.

1. The external provision of read and write virtual IP is controlled by the monitor. If monitor does not boot then DB Server will not be assigned a virtual IP, but if the virtual IP has been allocated when the monitor program shut down the original assigned virtual IP will not immediately shut down the external program can also connect access (as long as the network does not restart), The advantage is that the reliability requirements for monitor are lower, currently do not know how long to maintain, but if this time one of the DB server failure can not handle the switch, that is, the original virtual IP or maintain the same, the missing db of the virtual IP will become inaccessible.

The 2.agent program is controlled by the monitor program to handle write switching, switching from library to operation. If the monitor process shuts down then the agent process will not work, and it cannot handle the failure itself.

3.monitor Program is responsible for monitoring the status of DB server, including MySQL database, the server is running, the replication thread is normal, master-slave delay, etc. it is also used to control the agent process failure.

4.monitor will monitor the status of the DB server every few seconds, and if the DB server has gone from failure to normal, then monitor will automatically set it to online after 60s (the default is 60s can be set to other values), with the configuration file parameters of the monitor side "auto_ Set_online "Decides that there are three different states of the cluster Server: Hard_offline→awaiting_recovery→online

5. The default monitor will control Mmm_agent will writer DB Server Read_Only modified to OFF, the other DB server Read_Only modified to on, so in order to be rigorous in all the server my.cnf file to add Read_ Only=1 is controlled by Monitor to control writer and read,root users and replication users from READ_ONLY parameters

The above is the entire content of this article, I hope this article for everyone to learn about MySQL help.