一:简介
MMM 即Master-Master Replication Manager for MySQL（mysql主主复制管理器）关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写 入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟ip，除此之外，它还有实 现数据备份、节点之间重新同步功能的脚本。MySQL本身没有提供replication failover的解决方案，通过MMM方案能实现服务器的故障转移，从而实现mysql的高可用。MMM不仅能提供浮动IP的功能，更可贵的是如果当前 的主服务器挂掉后，会将你后端的从服务器自动转向新的主服务器进行同步复制，不用手工更改同步配置。这个方案是目前比较成熟的解决方案.
二:试验拓扑图
从拓扑图中可以看出DB2与DB1互为主从,DB1是DB3的主服务器.而上面的monitor起到动静读写分离.使DB1为只写服务器,DB2和DB3为只读服务器.
三:配置过程
1)配置的DB1 在DB1的/etc/my.cnf下添加如下配置选项 [mysqld] log-bin=master-bin #二进制日志文件 server-id=1 #server ID 各个的DB是不一样的 binlog_format=row #二进制日志文件记录格式 log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 给用户授权 MariaDB [(none)]> grant replication slave,replication client on *.* to ‘llh‘@‘172.16.16.1‘ identified by ‘replpass‘; Query OK, 0 rows affected (0.12 sec) MariaDB [(none)]> grant replication slave,replication client on *.* to ‘llh‘@‘172.16.16.3‘ identified by ‘replpass‘; Query OK, 0 rows affected (0.00 sec) 查看二进制日志 MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000009 | 245 | | | +-------------------+----------+--------------+------------------+ 2)配置DB2 log-bin=mysql-bin binlog_format=row log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=2 server-id=2 给用户授权 MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘172.16.16.5‘ identified by ‘replpass‘; Query OK, 0 rows affected (0.15 sec) MariaDB [(none)]> show master status -> ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000011 | 541 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.04 sec) 3)配置DB3 [mysqld] server-id=3 log-bin=mysql-bin log-slave-updates relay-log=relay-log-bin 在DB3上配置主从服务,连接DB1为主服务 MariaDB [(none)]> change master to master_host=‘172.16.16.5‘,master_user=‘llh‘,master_password=‘llh‘,master_log_file=‘master-bin.000009‘,master_log_pos=245; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.16.5 Master_User: llh Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000009 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log-bin.000012 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000009 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: 245 Relay_Log_Space: 866 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 1 row in set (0.00 sec) ERROR: No query specified 配置DB1为DB2的主服务器 MariaDB [(none)]> change master to master_host=‘172.16.16.5‘,master_user=‘llh‘,master_password=‘llh‘,master_log_file=‘master-bin.000009‘,master_log_pos=245; Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.16.5 Master_User: llh Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000009 Read_Master_Log_Pos: 245 Relay_Log_File: stu16-relay-bin.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000009 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: 245 Relay_Log_Space: 824 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 1 row in set (0.01 sec) 配置DB2是DB1的主服务器 MariaDB [(none)]> change master to master_host=‘172.16.16.1‘,master_user=‘llh‘,master_password=‘llh‘,master_log_file=‘master-bin.000011‘,master_log_pos=541; Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.16.1 Master_User: llh Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 541 Relay_Log_File: localhost-relay-bin.000016 Relay_Log_Pos: 486 Relay_Master_Log_File: mysql-bin.000011 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: 541 Relay_Log_Space: 1068 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 1 row in set (0.00 sec)
四:测试主从是否建立
1)在DB2上建立库llh MariaDB [(none)]> create database llh; Query OK, 1 row affected (0.08 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | llh | | mysql | | mytest | | performance_schema | | test | | testdb | +--------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> 在DB1上查看是否有表llh MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | llh | #有所创建的表说名和数据可以同步过来. | mysql | | mytest | | performance_schema | | test | | testdb | +--------------------+ 7 rows in set (0.06 sec) MariaDB [(none)]> 在DB1 上创建表name 并插入数据. MariaDB [llh]> create table name(name char(20)); Query OK, 0 rows affected (0.22 sec) MariaDB [llh]> insert into name (name) values (‘llh‘); Query OK, 1 row affected (0.02 sec) 在DB3上看是否能够查到数据 MariaDB [llh]> show tables; +---------------+ | Tables_in_llh | +---------------+ | name | +---------------+ 1 row in set (0.11 sec) MariaDB [llh]> select *from name; #有数据说明数据可以同步过来. +------+ | name | +------+ | llh | +------+ 1 row in set (0.06 sec) MariaDB [llh]> 综上验证说明三者之间的关系已经建立.
五:安装mysql-mmm-agent
在DB1~3上安装mysql-mmmo-agent 在每一个节点上要给Monitor授权用户 (三个节点都要授权) MariaDB [(none)]> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO ‘mmm_agent‘@‘172.16.16.2‘ IDENTIFIED BY ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO ‘mmm_monitor‘@‘172.16.16.2‘ IDENTIFIED BY ‘123456‘; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO ‘llh‘@‘172.16.16.2‘ IDENTIFIED BY ‘llh‘; Query OK, 0 rows affected (0.01 sec) 在Monitor节点上要安装 yum -y install mysql-mmm* #包括监控端,所有都要安装.
六:在Monitor端的设置/etc/mysql-mmm/mmm_common.conf
active_master_role writer <host default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user llh #用户 replication_password llh #密码 agent_user mmm_agent #代理用户 agent_password 123456 #代理密码 </host> <host db1> ip 172.16.16.5 mode master peer db2 </host> <host db2> ip 172.16.16.1 mode master peer db1 </host> <host db3> #默认是注销的,此处要把这个启动 ip 172.16.16.3 mode slave </host> <role writer> hosts db1, db2 ips 172.16.16.6 将此文件分发到各DB1~3中的/etc/mysql-mmm/下
七:每一个DB中都会有mmm_agent的配置文件，编辑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 修改最后一处的this db2 对应为自己host. 这个地方要修改对
八:在Monitor上，修改mmm_mon.conf文件
include mmm_common.conf <monitor> ip 172.16.16.2 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.16.5, 172.16.16.1 auto_set_online 10 # 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> <host default> monitor_user mmm_monitor #监控DB用户 monitor_password 123456 #监控DB密码 </host> debug 0
九:启动服务
DB1~DB3 :service mysql-mmm-agent start
Monitor :service mysql-mmm-monitor start
查看服务是否起来
各DB已经ONLINE 且DB1为writer; DB2和DB3为reader.
模拟DB2下线
服务自动转到DB3上.
模拟DB2上线
本文出自 “slayer” 博客，请务必保留此出处http://slayer.blog.51cto.com/4845839/1557125