Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.