基于MultiMasterMySQL(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=1 log_bin=/mariadb/data/mysql-bin 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 '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-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'@'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 status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.109 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 756 Relay_Log_File: essun-relay-bin.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000003 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: 756 Relay_Log_Space: 832 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/slave/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/slave/mysql.crt Master_SSL_Cipher: Master_SSL_Key: /etc/slave/mysql.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_SSL_Crl: /etc/slave/cacert.pem Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)

3、配置DB3

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

server-id=3 log-bin=mysql-bin log-slave-updates relay-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 status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.109 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 756 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000003 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: 756 Relay_Log_Space: 830 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/slave/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/slave/mysql.crt Master_SSL_Cipher: Master_SSL_Key: /etc/slave/mysql.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_SSL_Crl: /etc/slave/cacert.pem Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)