mysql MMM高可用的部署

這篇文章給大家分享的是mysql MMM高可用的部署，相信大部分人都還沒學會這個技能，為了讓大家學會，給大家總結了以下內容，話不多說，一起往下看吧。

1 環境：
1.1 OS and MYSQL verson:

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

1.2 IP 規劃：

192.168.1.201   mysql01    #master1
192.168.1.202   mysql02    #master2
192.168.1.247    slave1      #slave
192.168.1.243   monitor     #monitor

2 MySQL安裝：
#在所有SERVER上安裝MYSQL
#準備my.cnf, 注意所有SERVER的my.cnf中，server-id要不同

# cat my.cnf 
[client]
default-character-set = utf8
port = 3309
socket = /data/57.3309/mysql.sock

[mysqld]
server-id = 4
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
port        = 3309
socket        = /data/57.3309/mysql.sock
datadir = /data/57.3309/data
log-error = /data/57.3309/mysql.err
pid-file = /data/57.3309/mysql.pid
gtid_mode=on
#雙主設定auto-increment-increment 和auto-increment-offset 避免主鍵沖突
auto-increment-increment = 2
auto-increment-offset = 1
#mysql02
#auto-increment-offset = 2
#slave上不設置auto-increment-increment 和auto-increment-offset 

sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
enforce-gtid-consistency=on
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin = /data/57.3309/data/mysql-bin
relay_log = /data/57.3309/data/relay-bin
##cascaded replication for slave to write binlog.
log_slave_updates = 1
read-only=1 #所有SERVER設定read-only
binlog_format = row
slow_query_log = 1
slow_query_log_file = /data/57.3309/log/slowquery.log
long_query_time = 1
general_log = off
general_log_file = /data/57.3309/log/general.log
#skip-grant-tables

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

[root@mysql01 data]# /opt/mysql7/bin/mysql_install_db --basedir='/opt/mysql7' --datadir='/data/57.3310/data' --user=mysql
2020-01-29 16:16:50 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2020-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 instead
2020-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)
#第一次：skip_grant_tables方式啟動
[root@mysql01 57.3310]# /opt/mysql7/bin/mysqld_safe --defaults-file='/data/57.3310/my.cnf' --skip-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
#修改root密碼
 /opt/mysql7/bin/mysql -uroot -S /data/57.3310/mysql.soc #免密碼登錄
 #update語句修改root密碼
 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語句設置密碼不過期
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: 0
#刷新權限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#正常關閉，重啟MYSQL并登錄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
#權限,所有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 設定MYSQL主從復制
3.1 #復制架構：mysql01 <===>mysql02  主主復制，GTID方式，
mysql01===>slave           主從復制，傳統方式，
3.2 #mysql01 <===>mysql02
#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>
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 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

#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\G
*************************** 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

#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\G
*************************** 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 安裝MMM并配置：
4.1 #安裝enpl源并安裝MMM：

yum install epel-release.noarch
yum install -y mysql-mmm-agent
yum install -y mysql-mmm-monitor

4.2 #配置mmm用戶，由于是全庫復制，只要在mysql01上配置，會自動同步到其他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 #配置mmm設定檔：
#mysql01(master1)

 cd /etc/mysql-mmm/
cat mmm_common.conf
active_master_role      writer

<host default>
    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
</host>

<host mysql01>
    ip      192.168.1.201
    mysql_port  3309
    mode    master
    peer    mysql02
</host>

<host mysql02>
    ip      192.168.1.202
    mysql_port  3309
    mode    master
    peer    mysql01
</host>

<host slave1>
    ip      192.168.1.247
    mysql_port  3309
    mode    slave
</host>

#<host slave2>
#    ip      192.168.1.242
#    mysql_port  3310
 #   mode    slave
#</host>

<role writer>
    hosts   mysql01, mysql02
    ips     192.168.1.200
    mode    exclusive
</role>

<role reader>
    hosts   mysql01, mysql02,slave1
    ips     192.168.1.251, 192.168.1.252, 192.168.1.253
    mode    balanced
</role>

[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 

#mysql02(master2)上：

[root@mysql02 mysql-mmm]# ll
total 8
-rw-r-----. 1 root root 235 Jan 29 20:54 mmm_agent.conf
-rw-r-----. 1 root root 991 Jan 30 14:02 mmm_common.conf
[root@mysql02 mysql-mmm]# cat mmm_common.conf
active_master_role      writer

<host default>
    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
</host>

<host mysql01>
    ip      192.168.1.201
    mysql_port  3309
    mode    master
    peer    mysql02
</host>

<host mysql02>
    ip      192.168.1.202
    mysql_port  3309
    mode    master
    peer    mysql01
</host>

<host slave1>
    ip      192.168.1.247
    mysql_port  3309    
    mode    slave
</host>

#<host slave2>
#    ip      192.168.1.242
#    mysql_port  3310
#    mode    slave
#</host>

<role writer>
    hosts   mysql01, mysql02
    ips     192.168.1.200
    mode    exclusive
</role>

<role reader>
    hosts   mysql01, mysql02,slave1
    ips     192.168.1.251, 192.168.1.252, 192.168.1.253
    mode    balanced
</role>
[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]# 

#slave上：

[root@salve1 mysql-mmm]# pwd
/etc/mysql-mmm
[root@salve1 mysql-mmm]# ll
total 8
-rw-r-----. 1 root root 234 Jan 30 14:11 mmm_agent.conf
-rw-r-----. 1 root root 945 Jan 30 14:56 mmm_common.conf
[root@salve1 mysql-mmm]# cat mmm_common.conf
active_master_role      writer

<host default>
    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
</host>

<host mysql01>
    ip      192.168.1.201
    mode    master
    peer    mysql02
</host>

<host mysql02>
    ip      192.168.1.202
    mode    master
    peer    mysql01
</host>

<host slave1>
    ip      192.168.1.247
    mode    slave
</host>

#<host slave2>
#    ip      192.168.1.242
#    mysql_port  3310
#    mode    slave
#</host>

<role writer>
    hosts   mysql01, mysql02
    ips     192.168.1.200
    mode    exclusive
</role>

<role reader>
    hosts   mysql01, mysql02,slave1
    ips     192.168.1.251, 192.168.1.252, 192.168.1.253
    mode    balanced
</role>
[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]# 

#monitor上：

[root@mysql01 mysql-mmm]# cat mmm_mon.conf
include mmm_common.conf

<monitor>
    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.201,192.168.1.202,192.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>

<host default>
    monitor_user        mmm_monitor
    monitor_password    password123
</host>

debug 0

4.4 啟動monitor和agent
#monitor:

systemctl enable mysql-mmm-monitor.service   #加入啟動項
systemctl start mysql-mmm-monitor.service  #啟動monitor
#狀態：
[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 四 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

1月 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  #加入啟動項
systemctl start mysql-mmm-agent.service #啟動mmm agent
#狀態：
[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 四 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_agentd

1月 30 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  #加入啟動項
systemctl start mysql-mmm-agent.service #啟動mmm agent
#狀態：
[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 四 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_agentd

1月 30 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  #加入啟動項
systemctl start mysql-mmm-agent.service #啟動mmm agent
#狀態
[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_agentd

Jan 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 #查看MMM狀態：（monitor上）

[root@mysql01 mysql-mmm]# mmm_control --help
Invalid command '--help'

Valid commands are:
    help                              - show this message
    ping                              - ping monitor
    show                              - show status
    checks [<host>|all [<check>|all]] - show checks status
    set_online <host>                 - set host <host> online
    set_offline <host>                - set host <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] <role> <host> - move exclusive role <role> to host <host>
                                        (Only use --force if you know what you are doing!)
    set_ip <ip> <host>                - set role with ip <ip> to host <host>

[root@mysql01 mysql-mmm]# 

#show 狀態：

[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)

#嘗試切換
#切換前檢查slave復制狀態：可以看到這個時候的主庫是mysql01

mysql> show slave status\G
*************************** 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

#切換：

[root@mysql01 mysql-mmm]# mmm_control move_role writer mysql02
OK: 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)

#可以看到已經把writer角色切換到mysql02上
#檢查slave復制狀態，自動切換到mysql02上

mysql> show slave status\G
*************************** 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

關于mysql MMM高可用的部署就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果喜歡這篇文章，不如把它分享出去讓更多的人看到。