基于Multi Master MySQL(MMM)实现Mariadb10读写分离

—-本文大纲简介资源配置拓扑图实现过程====================一、简介MMM即Master-MasterReplicationManagerforMySQL（mysql主主复制管理器）关于mysql主主复制

方案优缺点

主机属性

系统名字角色主机名ip地址关系

Centos6.5x86_64DB1
Masteressun.mariadb1.com192.168.1.109与DB2互为主从

Centos6.5x86_64DB2Masteressun.mariadb2.com192.168.1.112
与DB1互为主从

Centos6.5x86_64DB3Slaveessun.mariadb3.com192.168.1.113
DB1的从库

Centos6.5x86_64MonitorMonitoressun.monitor.com192.168.1.116
监控所有主机

虚拟ip(VIP)

DB1    192.168.1.109  `192.168.1.24

DB2    192.168.1.112    192.168.1.24,192.168.1.22

DB3    192.168.1.113    192.168.1.23

三、拓扑图

四、实现过程

1、配置DB1

修改配置文件/etc/my.cnf,添加如下语句

server-id=1log_bin=/mariadb/data/mysql-binbinlog_format=rowlog-slave-updatessync_binlog=1auto_increment_increment=2auto_increment_offset=1

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.112’ identified by ‘replpass’;Query OK, 0 rows affected (0.12 sec)MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.113’ identified by ‘replpass’;Query OK, 0 rows affected (0.00 sec)

查看binlog日志标记

MariaDB [(none)]> show master status;+——————+———-+————–+——————+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000003 |756 |||+——————+———-+————–+——————+1 row in set (0.00 sec)

2、配置DB2

修改配置文件/etc/my.cnf,添加如下语句

log-bin=mysql-binbinlog_format=ROWlog-slave-updatessync_binlog=1auto_increment_increment=2auto_increment_offset=2server-id=2

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.109’ identified by ‘replpass’;Query OK, 0 rows affected (0.15 sec)

查看binlog日志标记

MariaDB [(none)]> show master status;+——————+———-+————–+——————+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000007 |548 |||+——————+———-+————–+——————+1 row in set (0.00 sec)

连接DB1

MariaDB [(none)]> change master to master_host=’192.168.1.109′,master_user=’repluser’,master_password=’replpass’,master_log_file=’mysql-bin.000003′,master_log_pos=756;Query OK, 0 rows affected (0.06 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.06 sec)MariaDB [(none)]> show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.109Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 756Relay_Log_File: essun-relay-bin.000002Relay_Log_Pos: 535Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 756Relay_Log_Space: 832Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: YesMaster_SSL_CA_File: /etc/slave/cacert.pemMaster_SSL_CA_Path:Master_SSL_Cert: /etc/slave/mysql.crtMaster_SSL_Cipher:Master_SSL_Key: /etc/slave/mysql.keySeconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_SSL_Crl: /etc/slave/cacert.pemMaster_SSL_Crlpath:Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec)

3、配置DB3

修改配置文件/etc/my.cnf添加如下语句

server-id=3log-bin=mysql-binlog-slave-updatesrelay-log=relay-log-bin

连接DB1

MariaDB [(none)]> change master to master_host=’192.168.1.109′,master_user=’repluser’,master_password=’replpass’,master_log_file=’mysql-bin.000003′,master_log_pos=756;Query OK, 0 rows affected (0.03 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.109Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 756Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 535Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 756Relay_Log_Space: 830Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: YesMaster_SSL_CA_File: /etc/slave/cacert.pemMaster_SSL_CA_Path:Master_SSL_Cert: /etc/slave/mysql.crtMaster_SSL_Cipher:Master_SSL_Key: /etc/slave/mysql.keySeconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_SSL_Crl: /etc/slave/cacert.pemMaster_SSL_Crlpath:Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec)