Mariadb10 read/write Splitting Based on MultiMasterMySQL (MMM)

---- Overview of the resource configuration topology implementation process 1. Introduction to MMM, that is, Master-MasterReplicationManagerforMySQL

---- Overview of the resource configuration topology implementation process ============================= 1. Introduction to MMM Is Master-MasterReplicationManagerforMySQL (mysql master replication manager) about mysql master-master Replication

Advantages and disadvantages

Host Properties

System name role host name IP address relationship

Centos6.5x86 _ 64DB1
Masteressun. mariadb1.com192.168.1.109 and DB2

Centos6.5x86 _ 64DB2Masteressun. mariadb2.com192.168.1.112
Master-slave relationship with DB1

Centos6.5x86 _ 64DB3Slaveessun. mariadb3.com192.168.1.113
Slave database of DB1

Centos6.5x86 _ 64MonitorMonitoressun. monitor. com192.168.1.116
Monitor all hosts

Virtual 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

3. Topology

IV. Implementation Process

1. Configure DB1

Modify the configuration file/etc/my. cnf and add the following statement:

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

Authorized User

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)

View binlog log tags

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

2. Configure DB2

Modify the configuration file/etc/my. cnf and add the following statement:

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

Authorized User

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)

View binlog log tags

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

Connect to DB1

MariaDB [(none)]> change master to master_host = '2017. 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 eventMaster_Host: 192.168.1.20.master _ User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: small: essun-relay-bin.000002Relay_Log_Pos: small: mysql-bin.000003Slave_IO_Running: small: 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: Role: NoLast_IO_Errno: 0Last_IO_Error: Last_ SQL _Errno: 0Last_ SQL _Error: Role: 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. Configure DB3

Modify the configuration file/etc/my. cnf and add the following statement:

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

Connect to DB1

MariaDB [(none)]> change master to master_host = '2017. 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 eventMaster_Host: 192.168.1.20.master _ User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: small: relay-log-bin.000002Relay_Log_Pos: small: mysql-bin.000003Slave_IO_Running: small: 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: Role: NoLast_IO_Errno: 0Last_IO_Error: Last_ SQL _Errno: 0Last_ SQL _Error: Role: Master_Server_Id: 1Master_SSL_Crl:/etc/slave/cacert. pemMaster_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos: 1 row in set (0.00 sec)