mysql-mmm实战安装

来源：互联网发布：太极拳软件编辑：程序博客网时间：2024/06/05 05:50

一．mysql-mmm实现mysql 高可用架构

MMM 即Master-Master Replication Manager for MySQL（mysql 主主复制管理器）关于 mysql

主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个

节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读

负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟 ip，除此之外，它还

有实现数据备份、节点之间重新同步功能的脚本。

MySQL 本身没有提供 replication failover 的解决方案，通过 MMM 方案能实现服务器的故障转移，从而实现 mysql 的高可用。

MMM 项目来自 Google：http://code.google.com/p/mysql-master-master

官方网站为：http://mysql-mmm.org

mysql-mmm 主要功能由下面三个脚本提供

mmm_mond 负责所有的监控工作的监控守护进程，决定节点的移除等等

mmm_agentd 运行在 mysql 服务器上的代理守护进程，通过简单远程服务集提供给监控节点

mmm_control 通过命令行管理 mmm_mond 进程

mysql-mmm 的监管端会提供多个虚拟 IP（VIP），包括一个可写 VIP，多个可读 VIP，通过监管的管理，这些 IP 会绑定在可用 mysql 之上，当某一台 mysql 宕机时，监管会将 VIP

迁移至其他 mysql。

在整个监管过程中，需要在 mysql 中添加相关授权用户，以便让 mysql 可以支持监理机的维护。授权的用户包括一个 mmm_monitor 用户和一个 mmm_agent 用户，如果想使用 mmm的备份工具则还要添加一个 mmm_tools 用户。

部署开始，由于机器资源有限，这里的实验，slave 就用一台了。

二．部署的前期工作

1．环境描述

kvm 虚拟机：4 台

root@bd-qa-oracle-86:~#cat /proc/version

Linux version 2.6.32-573.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-16) (GCC) ) #1 SMP Thu Jul 23 15:44:03 UTC 2015

root@bd-qa-oracle-86:~#uname -a

Linux bd-qa-oracle-86 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

root@bd-qa-oracle-86:~#cat /etc/issue

CentOS release 6.7 (Final)

Kernel \r on an \m

系统版本：CentOS release 6.7 (Final)

mysql版本：5.7.9

mysql-mmm版本：

4台虚拟机信息：

MMM管理机:172.16.57.55 Monitor T2

master1:172.16.57.86 server-id=1 bd-qa-oracle-86

master2:172.16.57.90 server-id=3 bd-qa-vertica-90

slave:172.16.57.95 server-id=2 qadb

虚拟IP：

10.1.1.13 write

10.1.1.14 read

10.1.1.15 read

10.1.1.16 read

虚拟ip配置方法

root@bd-qa-oracle-86:~#ifconfig eth1:0 10.1.1.14 netmask 255.255.255.0

root@bd-qa-oracle-86:~#ifconfig eth1:0

eth1:0 Link encap:Ethernet HWaddr 52:54:00:E5:23:C2

inet addr:10.1.1.14 Bcast:10.1.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

Interrupt:11 Base address:0xe000

root@bd-qa-oracle-86:~#ifconfig eth1:0 up

root@bd-qa-oracle-86:~#ping 10.1.1.14

PING 10.1.1.14 (10.1.1.14) 56(84) bytes of data.

64 bytes from 10.1.1.14: icmp_seq=1 ttl=64 time=0.013 ms

64 bytes from 10.1.1.14: icmp_seq=2 ttl=64 time=0.016 ms

Mysql-MMM 架构配置简介:

1.master1, master2 两台安装 mysql,并做主主的配置

2.slave1 上安装 mysql,并配置作为 master1 的从服务器。

3.master1/2, slave1,Monitor 这四台都要安装 mysql-mmm,并配置：mmm_common.conf、

mmm_agent.conf 以及 mmm_mon.conf 文件

三、配置 mysql-master-1/2（主主同步),mysql-master-1 与 mysql-slave（主从同步）

安装master1，master2，slave上的mysql数据库

首先在master1，master2，slave，建立如下文件夹

root@bd-qa-oracle-86:~#mkdir -p /opt/app/mysql

在master1，master2，slave解压

root@bd-qa-oracle-86:/opt/app/mysql#tar xzf mysql-5.7.9.tar.gz

在master1，master2，slave安装依赖包

root@bd-qa-oracle-86:/opt/app/mysql#yum -y install gcc gcc-c++ ncurses ncurses-devel cmake

在master1，master2，slave下载相应源码包

wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.9.tar.gz

在master1，master2，slave添加mysql用户

useradd -M -s /sbin/nologin mysql

在master1，master2，slave解压

tar xzf boost_1_59_0.tar.gz

tar xzf mysql-5.7.9.tar.gz

在master1，master2，slave创建文件夹

root@bd-qa-oracle-86:/opt/app/mysql#mkdir -p /data/mysql

root@bd-qa-oracle-86:/opt/app/mysql#cd mysql-5.7.9

在master1，master2，slave预编译

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost_1_59_0 -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DENABLE_DTRACE=0 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1

在master1，master2，slave编译安装

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#make -j `grep processor /proc/cpuinfo | wc -l`

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#make install

在master1，master2，slave启动脚本，设置开机自启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig mysqld on

master1配置文件

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#cat /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

log_bin = mysql-bin

log-slave-updates

auto_increment_offset=1

auto_increment_increment=2

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

#skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 128

max_allowed_packet = 4M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1

query_cache_size = 8M

query_cache_limit = 2M

ft_min_word_len = 4

binlog_format = mixed

expire_logs_days = 30

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

master2配置文件

root@bd-qa-vertica-90:/opt/app/mysql/mysql-5.7.9#cat /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 3

log_bin = mysql-bin

log-slave-updates

auto_increment_offset=2

auto_increment_increment=2

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

#skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 128

max_allowed_packet = 4M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1

query_cache_size = 8M

query_cache_limit = 2M

ft_min_word_len = 4

binlog_format = mixed

expire_logs_days = 30

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

解释：

mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。

auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535

auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

在master1，master2接着初始化数据库

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

在master1，master2启动数据库

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#service mysqld start

Starting MySQL.. SUCCESS!

在master1，master2配置环境变量和设置用户密码

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#export MYSQL_HOME=/usr/local/mysql

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#export PATH=$PATH:$MYSQL_HOME/bin

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.9-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password=password('fafa123');

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> grant all on *.* to dba_user@'%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.18 sec)

1.2、配置master1和master2 做主主同步

master1 和 master2 都需要创建链接用户

master1操作：

root@bd-qa-oracle-86:/opt/app/mysql/mysql-5.7.9#mysql -uroot -pfafa123

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 849 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> grant replication slave on *.* to 'rep'@'172.16.57.%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.01 sec)

master2操作：

mysql> change master to master_host='172.16.57.86',master_port=3306,master_user='rep',master_password='fafa123',master_log_file='mysql-bin.000002',master_log_pos=849;

Query OK, 0 rows affected, 2 warnings (0.79 sec)

mysql> start slave;

Query OK, 0 rows affected (0.10 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.57.86

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1146

Relay_Log_File: bd-qa-vertica-90-relay-bin.000002

Relay_Log_Pos: 617

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1146

Relay_Log_Space: 835

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: e2422e7c-45ab-11e6-8eb6-525400e523c2

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> grant replication slave on *.* to 'rep'@'172.16.57.%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 1443 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

master1操作：

mysql> change master to master_host='172.16.57.90',master_port=3306,master_user='rep',master_password='fafa123',master_log_file='mysql-bin.000002',master_log_pos=1443;

Query OK, 0 rows affected, 2 warnings (0.83 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.57.90

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1443

Relay_Log_File: bd-qa-oracle-86-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1443

Relay_Log_Space: 537

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 3

Master_UUID: 3db08ab9-45ad-11e6-8838-525400a94703

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

2.1、 slave修改my.cnf并启动服务，配置作为 master1 的从服务器

root@qadb:/opt/app/mysql/mysql-5.7.9#cat /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 2

log_bin = mysql-bin

log-slave-updates

auto_increment_offset=1

auto_increment_increment=2

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

#skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 128

max_allowed_packet = 4M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1

query_cache_size = 8M

query_cache_limit = 2M

ft_min_word_len = 4

binlog_format = mixed

expire_logs_days = 30

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

初始化数据库

root@qadb:/opt/app/mysql/mysql-5.7.9#/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

启动数据库

root@qadb:/opt/app/mysql/mysql-5.7.9#service mysqld start

Starting MySQL.. SUCCESS!

root@qadb:/opt/app/mysql/mysql-5.7.9#export MYSQL_HOME=/usr/local/mysql

root@qadb:/opt/app/mysql/mysql-5.7.9#export PATH=$PATH:$MYSQL_HOME/bin

mysql> set password=password('fafa123');

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on *.* to dba_user@'%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.07 sec)

2.2、配置同步参数

查看 master1 主库的记录点信息

锁表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 1146 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

备份主库

root@bd-qa-oracle-86:/data/backup#mysqldump -uroot -pfafa123 --events -A -B > /data/backup/master1_all.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

root@bd-qa-oracle-86:/data/backup#ll /data/backup/master1_all.sql

-rw-r--r-- 1 root root 718623 Jul 9 16:46 /data/backup/master1_all.sql

解锁主库

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

slave 从库进行操作:

mysql> change master to master_host='172.16.57.86',master_port=3306,master_user='rep',master_password='fafa123',master_log_file='mysql-bin.000002',master_log_pos=1146;

Query OK, 0 rows affected, 2 warnings (0.81 sec)

mysql> start slave;

Query OK, 0 rows affected (0.09 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.57.86

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1146

Relay_Log_File: qadb-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1146

Relay_Log_Space: 526

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: e2422e7c-45ab-11e6-8eb6-525400e523c2

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

四、配置mysql-mmm

4.1、安装mysql-mmm

注：需要在这四台 server 上都安装 mysql-mmm

root@n3:~# yum install epel-release

root@n3:~# yum install -y mysql-mmm*

4.2、配置mmm代理和监控账号的权限

现在环境已经配置好，我没有配置忽略 mysql库和 user表，所以只要在任意一台主库上执

行下面的操作，其他的库就都有这俩账号了。

mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'172.16.57.%' IDENTIFIED BY 'fafa123';

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'172.16.57.%' IDENTIFIED BY 'fafa123';

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.06 sec)

查看任何机器用户

mysql> select user,host from mysql.user;

+-------------+-------------+

| user | host |

+-------------+-------------+

| dba_user | % |

| mmm_agent | 172.16.57.% |

| mmm_monitor | 172.16.57.% |

| rep | 172.16.57.% |

| mysql.sys | localhost |

| root | localhost |

+-------------+-------------+

6 rows in set (0.00 sec)

都一样

4.3、所有服务器均需配置/etc/mysql-mmm/mmm_common.conf

root@n3:~# cat /etc/mysql-mmm/mmm_common.conf

active_master_role writer

cluster_interface eth1

pid_path /var/run/mysql-mmm/mmm_agentd.pid

bin_path /usr/libexec/mysql-mmm/

replication_user rep

replication_password fafa123

agent_user mmm_agent

agent_password fafa123

</host>

ip 172.16.57.86

mode master

peer db2

</host>

ip 172.16.57.90

mode master

peer db1

</host>

ip 172.16.57.95

mode slave

</host>

hosts db1, db2

ips 10.1.1.13

mode exclusive

</role>

hosts db1, db2, db3

ips 10.1.1.14,10.1.1.15,10.1.1.16

mode balanced

</role>

4.4、数据库主机配置/etc/mysql-mmm/mmm_agent.conf ，在数据库节点修改

master1：

root@bd-qa-oracle-86:/etc/sysconfig/network-scripts#vi /etc/mysql-mmm/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 db1

master2：

root@bd-qa-vertica-90:/opt/app/mysql/mysql-5.7.9#vi /etc/mysql-mmm/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 db2

slave：

root@qadb:/opt/app/mysql/mysql-5.7.9#vi /etc/mysql-mmm/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 db3

4.5、 monitor主机配置/etc/mysql-mmm/mmm_mon.conf ，在监控机器上配置

[root@T2 mysql]# vi /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

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

ping_ips 172.16.57.86,172.16.57.90,172.16.57.95

auto_set_online 60

# 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>

monitor_user mmm_monitor

monitor_password fafa123

</host>

debug 0

4.6、启动mysql-mmm

master-1,master-2,slave 启动代理：

编辑/etc/default/mysql-mmm-agent 来开启

[root@T2 mysql]# vi /etc/default/mysql-mmm-agent

# mysql-mmm-agent defaults

ENABLED=1

所有节点进行

所有数据库主机启动 mmm-agent: 含义数据库的节点

root@bd-qa-oracle-86:/etc/sysconfig/network-scripts#/etc/init.d/mysql-mmm-agent start

Starting MMM Agent Daemon: [ OK ]

monitor 主机启动 mmm-monitor

[root@T2 mysql]# /etc/init.d/mysql-mmm-monitor start

Starting MMM Monitor Daemon: [ OK ]

4.7、 mmm_control命令监控mysql 服务器状态，监控节点执行

[root@T2 mysql]# mmm_control show

# Warning: agent on host db3 is not reachable

db1(172.16.57.86) master/ONLINE. Roles: reader(10.1.1.14), writer(10.1.1.13)

db2(172.16.57.90) master/ONLINE. Roles: reader(10.1.1.15), reader(10.1.1.16)

db3(172.16.57.95) slave/ONLINE. Roles:

4.8、测试两个mysql服务器能否实现故障自动切换

将db1的mysql服务停止

root@bd-qa-oracle-86:/etc/sysconfig/network-scripts#service mysqld stop

Shutting down MySQL............ SUCCESS!

等待60秒在 mysql-mmm-monitor 服务器上进行监控查看

[root@T2 mysql]# mmm_control show

# Warning: agent on host db3 is not reachable

db1(172.16.57.86) master/HARD_OFFLINE. Roles:

db2(172.16.57.90) master/ONLINE. Roles: reader(10.1.1.14), reader(10.1.1.15), reader(10.1.1.16), writer(10.1.1.13)

db3(172.16.57.95) slave/ONLINE. Roles:

slave检查master_host 是否切换到了另一个主库地址：

root@qadb:/opt/app/mysql/mysql-5.7.9#mysql -uroot -pfafa123

mysql> show slave status\G;

或者这样查询

root@qadb:/opt/app/mysql/mysql-5.7.9#mysql -uroot -pfafa123 -e "show slave status\G" -S /tmp/mysql.sock

恢复master-1(db1)

root@bd-qa-oracle-86:/etc/sysconfig/network-scripts#service mysqld start

Starting MySQL.. SUCCESS!

monitor端检查恢复情况

[root@T2 mysql]# mmm_control show

# Warning: agent on host db3 is not reachable

db1(172.16.57.86) master/ONLINE. Roles: reader(10.1.1.14), reader(10.1.1.15)

db2(172.16.57.90) master/ONLINE. Roles: reader(10.1.1.16), writer(10.1.1.13)

db3(172.16.57.95) slave/ONLINE. Roles:

发现db1只能读，可以看到当 db1 恢复后就充当 slave 的角色了！只有当 db2 挂了以后db1 又会担当起主服务器的写入功能。

4.9、 mmm_control命令介绍

[root@T2 mysql]# 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>

1 0