Highly available deployment for mysql MMM

What this article shares with you is the highly available deployment of mysql MMM. I believe most people have not yet learned this skill. In order to let you learn, I have summarized the following contents. Without saying much, let's read on.

1 Environment:

1.1 OS and MYSQL verson:

[root@mysql01] # uname-aLinux mysql01 3.10.0-327.18.2.el7.x86_64 # 1 SMP Thu May 12 11:03:55 UTC 2016 x86 "64 GNU/Linux [root@mysql01] # / opt/mysql7/bin/mysql-- version/opt/mysql7/bin/mysql Ver 14.14 Distrib 5.7.28, for el7 (x86x64) using EditLine wrapper

1.2 IP Planning:

192.168.1.201 mysql01 # master1192.168.1.202 mysql02 # master2192.168.1.247 slave1 # slave192.168.1.243 monitor # monitor

2 MySQL installation:

# install MYSQL on all SERVER

# prepare my.cnf. Note that server-id should be different in my.cnf of all SERVER

# cat my.cnf [client] default-character-set = utf8port = 3309socket = / data/57.3309/ mysql.sock [mysqld] server-id = 4collation-server = utf8_unicode_ciinit-connect = 'SET NAMES utf8'character-set-server = utf8port = 3309socket = / data/57.3309/mysql.sockdatadir = / data/57.3309/datalog-error = / data/57.3309/mysql.errpid-file = / data/57.3309/mysql.pidgtid_mode=on# Auto-increment-increment and auto-increment-offset avoid primary key conflicts auto-increment-increment and auto-increment-offset sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1enforce-gtid-consistency=onskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread are not set on auto-increment-increment = 2auto-increment-offset = 1#mysql02#auto-increment-offset = 2#slave _ buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin = / data/57.3309/data/mysql-binrelay_log = / data/57.3309/data/relay-bin##cascaded replication for slave to write binlog.log_slave_updates = 1read-only=1 # all SERVER settings read-onlybinlog_format = rowslow_query_log = 1slow_query_log_file = / data/57.3309/log/slowquery.loglong_query_time = 1general_log = offgeneral_ Log_file = / data/57.3309/log/general.log#skip-grant- tables [mysqldump] quickmax_allowed_packet = 16m [MySQL] no-auto- rehash [myisamchk] key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2m [root@mysql01 data] # / opt/mysql7/bin/mysql_install_db-- basedir='/opt/mysql7'-- datadir='/data/57.3310/data'-- user=mysql2020-01- 29 16:16:50 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld-- initialize2020-01-29 16:16:54 [WARNING] The bootstrap log isn't empty:2020-01-29 16:16:54 [WARNING] 2020-01-29T08:16:50.886558Z 0 [Warning]-- bootstrap is deprecated. Please consider using-- initialize instead2020-01-29T08:16:50.887365Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2020-01-29T08:16:50.887370Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) # for the first time: start [root@mysql01 57.3310] # / opt/mysql7/bin/mysqld_safe-- defaults-file='/data/57.3310/my.cnf'-- skip- in skip_grant_tables mode Grant-tables-- user=root & Logging to'/ data/57.3310/mysql.err'.2020-01-29T08:39:04.537600Z mysqld_safe Starting mysqld daemon with databases from / data/57.3310/data# modify root password / opt/mysql7/bin/mysql-uroot-S / data/57.3310/mysql.soc # password-free login # update statement modify root password mysql > update mysql.user set authentication_string=password ('password123') where user='root' and host='localhost' Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1#update statement sets password not to expire mysql > update mysql.user set password_expired='N' where user='root';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: refresh permission mysql > flush privileges Query OK, 0 rows affected (0.00 sec) # shut down normally, restart MYSQL and log in to MYSQL / opt/mysql7/bin/mysqladmin-uroot-ppassword123-S / data/57.3310/mysql.sock shutdown / opt/mysql7/bin/mysqld_safe-- defaults-file='/data/57.3310/my.cnf'-- user=root & / opt/mysql7/bin/mysql-uroot-ppassword123-S / data/57.3310/mysql.sock#, on all SERVER. Mysql > GRANT REPLICATION SLAVE ON *. * TO 'rep'@'%' IDENTIFIED BY' password123';Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)

3 set MYSQL master-slave replication

3.1replication architecture: mysql01 mysql02 master master replication, GTID mode

Mysql01=== > slave master-slave replication, traditional way

3.2 # mysql01 mysql02

# on mysql02:

Mysql > change master to MASTER_HOST='192.168.1.201',MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_PORT=3309,master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql > start slave Query OK 0 rows affected (0.00 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_ User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 76618 Relay_Log_File: relay-bin.000005 Relay_Log_Pos: 76831 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes

# on mysql01:

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_PORT=3309,MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_AUTO_POSITION = 1 * query OK, 0 rows affected, 2 warnings (0.00 sec) mysql > start slave Query OK 0 rows affected (0.00 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.202 Master_ User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 391 Relay_Log_File: relay-bin.000012 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes

# on slave

Mysql > change master to MASTER_HOST='192.168.1.201',MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_PORT=3309,MASTER_LOG_FILE='mysql-bin.000014',MASTER_LOG_POS=65754 Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql > start slave Query OK 0 rows affected (0.00 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_ User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes

4 install MMM and configure:

4. 1 # install the enplSource and install MMM:

Yum install epel-release.noarchyum install-y mysql-mmm-agentyum install-y mysql-mmm-monitor

4.2 # configure mmm users. As it is a full-library replication, as long as it is configured on mysql01, it will be automatically synchronized to other SERVER:

GRANT PROCESS, SUPER, REPLICATION CLIENT ON *. * TO 'mmm_agent'@'192.168.1.%' IDENTIFIED BY' password123';GRANT REPLICATION CLIENT ON *. * TO 'mmm_monitor'@'192.168.1.%' IDENTIFIED BY' password123'

4.3 # configure mmm profile:

# mysql01 (master1)

Cd / etc/mysql-mmm/cat mmm_common.confactive_master_role writer cluster_interface eno33554992 pid_path / run/mysql-mmm-agent.pid bin_path / usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password password123 ip 192.168.1.201 Mysql_port 3309 mode master peer mysql02 ip 192.168.1.202 mysql_port 3309 mode master peer mysql01 ip 192.168.1.247 mysql_port 3309 mode slave## ip 192.168.1.242# mysql_port 3310 # mode slave# hosts mysql01 Mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@mysql01 mysql-mmm] # cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this mysql01

# on mysql02 (master2):

[root@mysql02 mysql-mmm] # lltotal 8, RW, RW, RMI. 1 root root 235 Jan 29 20:54 mmm_agent.conf-rw-r-. 1 root root 991 Jan 30 14:02 mmm_ common.confession [root @ mysql02 mysql-mmm] # cat mmm_common.confactive_master_role writer cluster_interface eno33554992 pid_path / run/mysql-mmm-agent.pid bin_path / usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password Password123 ip 192.168.1.201 mysql_port 3309 mode master peer mysql02 ip 192.168.1.202 mysql_port 3309 mode master peer mysql01 ip 192.168.1.247 mysql_port 3309 mode slave## ip 192.168.1.242# mysql_port 3310# mode slave# hosts mysql01 Mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@mysql02 mysql-mmm] # cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this mysql02 [root@mysql02 mysql-mmm] #

# on slave:

[root@salve1 mysql-mmm] # pwd/etc/mysql-mmm [root@salve1 mysql-mmm] # lltotal 8 RW Murray. 1 root root 234 Jan 30 14:11 mmm_agent.conf-rw-r-. 1 root root 945 Jan 30 14:56 mmm_ common.confession [root @ salve1 mysql-mmm] # cat mmm_common.confactive_master_role writer cluster_interface eno16777736 pid_path / run/mysql-mmm-agent.pid bin_path / usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password Password123 mysql_port 3309 ip 192.168.1.201 mode master peer mysql02 ip 192.168.1.202 mode master peer mysql01 ip 192.168.1.247 mode slave## ip 192.168.1.242# mysql_port 3310# mode slave# hosts mysql01 Mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@salve1 mysql-mmm] # cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this slave1 [root@salve1 mysql-mmm] #

# on monitor:

[root@mysql01 mysql-mmm] # cat mmm_mon.confinclude mmm_common.conf ip 127.0.0.1 pid_path / run/mysql-mmm-monitor.pid bin_path / usr/libexec/mysql-mmm status_path / var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.1.201192.168.1.202192. 168.1.247 auto_set_online 60 # The kill_host_bin does not exist by default Though the monitor will # throw a wning 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_user mmm_monitor monitor_password password123debug 0

4.4 launch monitor and agent

# monitor:

Systemctl enable mysql-mmm-monitor.service # add startup item systemctl start mysql-mmm-monitor.service # launch monitor# status: [root@mysql01 mysql-mmm] # systemctl status mysql-mmm-monitor.service * mysql-mmm-monitor.service-MySQL MMM Monitor Loaded: loaded (/ usr/lib/systemd/system/mysql-mmm-monitor.service; enabled; vendor preset: disabled) Active: active (running) since four 2020-01-30 19:52:04 CST 21h ago Process: 1464 ExecStart=/usr/sbin/mmm_mond (code=exited Status=0/SUCCESS) Main PID: 2703 (mmm_mond) CGroup: / system.slice/mysql-mmm-monitor.service |-2703 mmm_mond |-2704 mmm_mond |-2848 perl / usr/libexec/mysql-mmm/monitor/checker ping_ip |-2851 perl / usr/libexec/mysql-mmm/monitor/checker mysql |-2853 perl / usr/libexec/mysql-mmm/monitor/checker ping |-2855 perl / usr/libexec/mysql-mmm/monitor/checker rep_backlog `- 2858 perl / usr/libexec/mysql-mmm/monitor/checker rep_ threads January 30 19:51:53 mysql01 systemd [1]: Starting MySQL MMM Monitor...1 30 19:52:04 mysql01 systemd [1]: Started MySQL MMM Monitor.

# mysql01 (master1):

Systemctl enable mysql-mmm-agent.service # add startup item systemctl start mysql-mmm-agent.service # launch mmm agent# status: [root@mysql01 mysql-mmm] # systemctl status mysql-mmm-agent.service * mysql-mmm-agent.service-MySQL MMM agent Loaded: loaded (/ usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since four 2020-01-30 19:52:03 CST 21h ago Process: 1459 ExecStart=/usr/sbin/mmm_agentd (code=exited Status=0/SUCCESS) Main PID: 2695 (mmm_agentd) CGroup: / system.slice/mysql-mmm-agent.service |-2695 mmm_agentd `- 2909 mmm_ agentd30 19:51:53 mysql01 systemd [1]: Starting MySQL MMM agent...1 30 19:52:03 mysql01 systemd [1]: Started MySQL MMM agent.1 30 21:08:07 mysql01 systemd [1]: Started MySQL MMM agent. [root@mysql01 mysql-mmm] #

# mysql02 (master2):

Systemctl enable mysql-mmm-agent.service # add startup item systemctl start mysql-mmm-agent.service # launch mmm agent# status: [root@mysql02 mysql-mmm] # systemctl status mysql-mmm-agent.service * mysql-mmm-agent.service-MySQL MMM agent Loaded: loaded (/ usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since four 2020-01-30 19:52:03 CST 21h ago Process: 1459 ExecStart=/usr/sbin/mmm_agentd (code=exited Status=0/SUCCESS) Main PID: 2695 (mmm_agentd) CGroup: / system.slice/mysql-mmm-agent.service |-2695 mmm_agentd `- 2909 mmm_ agentd30 19:51:53 mysql02 systemd [1]: Starting MySQL MMM agent...1 30 19:52:03 mysql02 systemd [1]: Started MySQL MMM agent.1 30 21:08:07 mysql02 systemd [1]: Started MySQL MMM agent. [root@mysql02 mysql-mmm] #

# slave:

Systemctl enable mysql-mmm-agent.service # add startup item systemctl start mysql-mmm-agent.service # launch mmm agent# status [root@salve1 mysql-mmm] # systemctl status mysql-mmm-agent.service [0m mysql-mmm-agent.service-MySQL MMM agent Loaded: loaded (/ usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2020-01-30 21:07:52 CST 20h ago Process: 5323 ExecStart=/usr/sbin/mmm_agentd (code=exited, status=0/SUCCESS) Main PID: 5325 (mmm_agentd) CGroup: / system.slice/mysql-mmm-agent.service 25 mmm_agentd 26 mmm_agentdJan 30 21:07:51 mysql01 systemd [1]: Starting MySQL MMM agent...Jan 30 21:07:52 mysql01 systemd [1]: Started MySQL MMM agent. [root@salve1 mysql-mmm] #

5 # View MMM status: (on monitor)

[root@mysql01 mysql-mmm] # mmm_control-- helpInvalid command'--help'Valid commands are: help-show this message ping-ping monitor show-show status checks [| all [| all]]-show checks status set_online-set host online set_ Offline-set host offline mode-print current mode. Set_active-switch into active mode. Set_manual-switch into manual mode. Set_passive-switch into passive mode. Move_role [--force]-move exclusive role to host (Only use-- force if you know what you are doing!) Set_ip-set role with ip to host [root@mysql01 mysql-mmm] #

# show status:

[root@mysql01 mysql-mmm] # mmm_control show mysql01 (192.168.1.201) master/ONLINE. Roles: reader (192.168.1.251), writer (192.168.1.200) mysql02 (192.168.1.202) master/ONLINE. Roles: reader (192.168.1.253) slave1 (192.168.1.247) slave/ONLINE. Roles: reader (192.168.1.252)

# try switching

# check slave replication status before switching: you can see that the main library at this time is mysql01

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes

# switch:

[root@mysql01 mysql-mmm] # mmm_control move_role writer mysql02OK: Role 'writer' has been moved from' mysql01' to 'mysql02'. Now you can wait some time and check new roles info! [root@mysql01 mysql-mmm] # [root@mysql01 mysql-mmm] # mmm_control show mysql01 (192.168.1.201) master/ONLINE. Roles: reader (192.168.1.251) mysql02 (192.168.1.202) master/ONLINE. Roles: reader (192.168.1.253), writer (192.168.1.200) slave1 (192.168.1.247) slave/ONLINE. Roles: reader (192.168.1.252)

# you can see that the writer role has been switched to mysql02

# check slave replication status and automatically switch to mysql02

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.202 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 64494 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes

This is the end of the high-availability deployment of mysql MMM. I hope the above content can be helpful to you and learn more. If you like this article, you might as well share it for more people to see.