简介
MMM即:Master-Master Replication Manager For MySQL,MySQL主主复制管理器的功能包括监控、故障转移和等一系列脚本构成,
这个脚本也能对基本的主从复制配置的任意数量的从服务器进行读负载均衡，所以可以用它来实现一组居于复制的虚拟IP,同时它还有数据备份、节点之间重新同步功能的能力.
IP
DB1:192.168.11.198
DB2:192.168.11.88
DB3:192.168.11.238
MONITOR:192.168.11.116
结构图如下:
以下就看看如何搭建MySQL-MMM
一、编译MySQL5.6
此步省略
二、编辑my.cnf
db1
[mysql@localhost ~]$ sudo cat /etc/my.cnf
[sudo] password for mysql:
[client]
socket=/tmp/mysql.sock
[mysqld]
server-id=2
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=1
max_allowed_packet=100M
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
log_bin_trust_function_creators = 1
innodb_fast_shutdown = 0
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
db2
[mysql@localhost ~]$ sudo cat /etc/my.cnf
[client]
socket=/tmp/mysql.sock
[mysqld]
server-id=4
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=InnoDB
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
log_bin_trust_function_creators = 1
log-bin=/mysql/log/mysql-bin
report_host=192.168.23.164
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
slave-skip-errors=1007,1050,1146,1051
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
db3
[client]
socket=/tmp/mysql.sock
port=3306
[mysqld]
server-id=3
port=3306
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
log-bin=/mysql/log/mysql-bin
slave-skip-errors=1007,1050
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
relay-log=relay-bin
relay-log-index=relay-bin.index
binlog_format=mixed
log-slave-updates=on
replicate-do-db=test
slave-skip-errors=1146
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
三、主从配置(master1和master2配置成主主,slave1配置成master1的从)
1、在master1上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;
2、在master2上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;
在master2、slave1执行
change master to master_host='192.168.11.198', master_port=3306, master_user='repl', master_password='XXXX';start slave;
把master1配置成master2的从库:
change master to master_host='192.168.11.88', master_port=3306, master_user='repl', master_password='XXXX';start slave;
在各个机器上执行:
db1
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.88
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000024
Read_Master_Log_Pos: 124156
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1068
Relay_Master_Log_File: mysql-bin.000024
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 124156
Relay_Log_Space: 1242
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: 4
Master_UUID: a7e4c60d-62ca-11e3-8710-080027e08a30
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
db2
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.198
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000064
Read_Master_Log_Pos: 3324
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1870
Relay_Master_Log_File: mysql-bin.000064
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 3324
Relay_Log_Space: 2044
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: 2
Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
db3
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.198
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000064
Read_Master_Log_Pos: 3324
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2655
Relay_Master_Log_File: mysql-bin.000064
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 3324
Relay_Log_Space: 2822
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: 2
Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.01 sec)
mysql>
mysql-mmm安装
1、db节点：
yum -y install mysql-mmm-agent
2、monitor节点：
yum -y install mysql-mmm*
mysql-mmm的配置:
1、在三个db节点授权:
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.11.%' identified by 'XXXX';
grant replication client on *.* to 'mmm_monitor'@'192.168.11.%' identified by 'XXXX';flush privileges;
修改配置文件
sudo vim /etc/mysql-mmm/mmm_common.conf (同时编辑db、monitor)
[mysql@localhost ~]$ sudo cat /etc/mysql-mmm/mmm_common.conf
[sudo] password for mysql:
active_master_role writer
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repl
replication_password XXXX
agent_user mmm_agent
agent_password 123456
ip 192.168.11.198
mysql_port 3306
mode master
peer db2
ip 192.168.11.88
mysql_port 3306
mode master
peer db1
ip 192.168.11.238
mysql_port 3306
mode slave
peer db3
hosts db1, db2
ips 192.168.11.170
mode exclusive
hosts db3
ips 192.168.11.171,192.168.11.172
mode balanced
peer的意思相当于等同，表示db1与db2同等。
ips指定VIP
mode exclusive 有两种模式：exclusive排他，此模式下任何时候只能一个host拥有该角色
balanced模式可以有多个host同时拥有此角色。一般writer是exclusive，reader是balanced
sudo vim /etc/mysql-mmm/mmm_agent.conf (同时编辑master1、master2、slave1分别修改为：this db1、this db2、this db3)
sudo vim /etc/mysql-mmm/mmm_mon.conf (仅编辑monitor节点)
mysql@localhost bin]$ sudo cat /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 192.168.11.198,192.168.11.88
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_user mmm_monitor
monitor_password 123456
debug 0
mmm启动
1、db节点：
[mysql@localhost mysql-mmm]$ sudo /etc/init.d/mysql-mmm-agent start
[sudo] password for mysql:
Starting MMM Agent Daemon: [ OK ]
[mysql@localhost bin]$ sudo /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon: [ OK ]
[mysql@localhost ~]$ sudo mmm_control show
db1(192.168.11.198) master/ONLINE. Roles: writer(192.168.11.170)
db2(192.168.11.88) master/ONLINE. Roles:
db3(192.168.11.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)
[mysql@localhost bin]$ sudo mmm_control checks all
db2 ping [last change: 2014/05/06 17:53:36] OK
db2 mysql [last change: 2014/05/06 17:53:36] OK
db2 rep_threads [last change: 2014/05/06 17:53:36] OK
db2 rep_backlog [last change: 2014/05/06 17:53:36] OK: Backlog is null
db3 ping [last change: 2014/05/06 17:53:36] OK
db3 mysql [last change: 2014/05/06 19:04:39] OK
db3 rep_threads [last change: 2014/05/06 19:04:36] OK
db3 rep_backlog [last change: 2014/05/06 19:04:39] OK: Backlog is null
db1 ping [last change: 2014/05/06 17:53:36] OK
db1 mysql [last change: 2014/05/06 17:53:36] OK
db1 rep_threads [last change: 2014/05/06 17:53:36] OK
db1 rep_backlog [last change: 2014/05/06 17:53:36] OK: Backlog is null
测试:
停止DB1看192.168.11.170会不会漂移到DB2上去,同时DB3的Slave的IP会不会从DB1改到DB2
DB1:
[mysql@localhost ~]$ mysqladmin -u root -pXXXXXX shutdown
Warning: Using a password on the command line interface can be insecure.
140522 16:38:47 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[1]+ Done mysqld_safe
MONITOR:
[mysql@localhost ~]$ sudo mmm_control show
db1(192.168.23.198) master/ONLINE. Roles: writer(192.168.11.170)
db2(192.168.23.88) master/ONLINE. Roles:
db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)
[mysql@localhost ~]$ sudo mmm_control show
db1(192.168.23.198) master/HARD_OFFLINE. Roles:
db2(192.168.23.88) master/ONLINE. Roles:
db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)
[mysql@localhost ~]$ sudo mmm_control show
db1(192.168.23.198) master/HARD_OFFLINE. Roles:
db2(192.168.23.88) master/ONLINE. Roles: writer(192.168.11.170)
db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)
DB3:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.11.88
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000065
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000065
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 120
Relay_Log_Space: 120
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: 4
Master_UUID:
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
^_^,OK.切换成功了.在生产环境中可以利用NAGIOS把AGENT、MONITOR、PEPLICATION等进程监控起来发现问题迅速处理解决.今天先到此吧.
