基于MultiMasterMySQL(MMM)实现Mariadb10读写分离

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了基于MultiMasterMySQL(MMM)实现Mariadb10读写分离，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5307字，纯文字阅读大概需要8分钟。

内容图文

----本文大纲简介资源配置拓扑图实现过程====================一、简介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)

内容总结

以上是互联网集市为您收集整理的基于MultiMasterMySQL(MMM)实现Mariadb10读写分离全部内容，希望文章能够帮你解决基于MultiMasterMySQL(MMM)实现Mariadb10读写分离所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---