搜索
首页数据库mysql教程MariaDB 10 (MySQL DB) 多主复制并实现读写分离_MySQL

MariaDB

二、资源配置

  • 主机属性

系统 名字 角色 主机名 ip地址 关系
Centos6.5x86_64 DB1 Master essun.mariadb1.com 192.168.1.109 与DB2互为主从
Centos6.5x86_64 DB2 Master essun.mariadb2.com 192.168.1.112 与DB1互为主从
Centos6.5x86_64 DB3 Slave essun.mariadb3.com 192.168.1.113 DB1的从库
Centos6.5x86_64 Monitor Monitor essun.monitor.com 192.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

三、拓扑图

wKiom1NbD-vBYTbaAAH9IMbCsBM351.jpg

四、实现过程

1、配置DB1

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

server-<span style="COLOR: rgb(0,0,255)">id</span>=<span style="COLOR: rgb(128,0,128)">1</span><span style="COLOR: rgb(0,0,0)">log_bin</span>=/mariadb/data/mysql-<span style="COLOR: rgb(0,0,0)">binbinlog_format</span>=<span style="COLOR: rgb(0,0,0)">rowlog</span>-slave-<span style="COLOR: rgb(0,0,0)">updates sync_binlog</span>=<span style="COLOR: rgb(128,0,128)">1</span><span style="COLOR: rgb(0,0,0)">auto_increment_increment</span>=<span style="COLOR: rgb(128,0,128)">2# <span>默认地,AUTO_INCREMENT 的开始值是 1,每条新记录递增 1。</span></span><span style="COLOR: rgb(0,0,0)">auto_increment_offset</span>=<span style="COLOR: rgb(128,0,128)">1 </span>

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.112</span><span style="COLOR: rgb(128,0,0)">'</span> identified by <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.12</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>> grant replication slave,replication client on *.* to <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.113</span><span style="COLOR: rgb(128,0,0)">'</span> identified by <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

查看binlog日志标记

MariaDB [(none)]><span style="COLOR: rgb(0,0,0)"> show master status;</span>+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.<span style="COLOR: rgb(128,0,128)">000003</span> |<span style="COLOR: rgb(128,0,128)">756</span> |||+------------------+----------+--------------+------------------+<span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

2、配置DB2

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

log-bin=mysql-<span style="COLOR: rgb(0,0,0)">binbinlog_format</span>=<span style="COLOR: rgb(0,0,0)">ROWlog</span>-slave-<span style="COLOR: rgb(0,0,0)">updatessync_binlog</span>=<span style="COLOR: rgb(128,0,128)">1</span><span style="COLOR: rgb(0,0,0)">auto_increment_increment</span>=<span style="COLOR: rgb(128,0,128)">2</span><span style="COLOR: rgb(0,0,0)">auto_increment_offset</span>=<span style="COLOR: rgb(128,0,128)">2</span><span style="COLOR: rgb(0,0,0)">server</span>-<span style="COLOR: rgb(0,0,255)">id</span>=<span style="COLOR: rgb(128,0,128)">2</span>

授权用户

MariaDB [(none)]> grant replication slave,replication client on *.* to <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.109</span><span style="COLOR: rgb(128,0,0)">'</span> identified by <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.15</span> sec)

查看binlog日志标记

MariaDB [(none)]><span style="COLOR: rgb(0,0,0)"> show master status;</span>+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.<span style="COLOR: rgb(128,0,128)">000007</span> |<span style="COLOR: rgb(128,0,128)">548</span> |||+------------------+----------+--------------+------------------+<span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

连接DB1

MariaDB [(none)]><span style="COLOR: rgb(0,0,0)"> show master status;</span>+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.<span style="COLOR: rgb(128,0,128)">000007</span> |<span style="COLOR: rgb(128,0,128)">548</span> |||+------------------+----------+--------------+------------------+<span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

3、配置DB3

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

server-<span style="COLOR: rgb(0,0,255)">id</span>=<span style="COLOR: rgb(128,0,128)">3</span><span style="COLOR: rgb(0,0,0)">log</span>-bin=mysql-<span style="COLOR: rgb(0,0,0)">binlog</span>-slave-<span style="COLOR: rgb(0,0,0)">updates<br></span><span>relay</span>-log=relay-log-bin

连接DB1

MariaDB [(none)]> change master to master_host=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.109</span><span style="COLOR: rgb(128,0,0)">'</span>,master_user=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>,master_password=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_file=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">mysql-bin.000003</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_pos=<span style="COLOR: rgb(128,0,128)">756</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.03</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> start slave;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.00</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> show slave status/G</span>*************************** <span style="COLOR: rgb(128,0,128)">1</span>. row ***************************<span style="COLOR: rgb(0,0,0)"> Slave_IO_State: Waiting </span><span style="COLOR: rgb(0,0,255)">for</span><span style="COLOR: rgb(0,0,0)"> master to send eventMaster_Host: </span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.109</span><span style="COLOR: rgb(0,0,0)">Master_User: repluserMaster_Port: </span><span style="COLOR: rgb(128,0,128)">3306</span><span style="COLOR: rgb(0,0,0)">Connect_Retry: </span><span style="COLOR: rgb(128,0,128)">60</span><span style="COLOR: rgb(0,0,0)">Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000003</span><span style="COLOR: rgb(0,0,0)">Read_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">756</span><span style="COLOR: rgb(0,0,0)"> Relay_Log_File: relay</span>-log-bin.<span style="COLOR: rgb(128,0,128)">000002</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">535</span><span style="COLOR: rgb(0,0,0)">Relay_Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000003</span><span style="COLOR: rgb(0,0,0)"> Slave_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: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_Error: Skip_Counter: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Exec_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">756</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Space: </span><span style="COLOR: rgb(128,0,128)">830</span><span style="COLOR: rgb(0,0,0)">Until_Condition: None Until_Log_File:Until_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Master_SSL_Allowed: Yes Master_SSL_CA_File: </span>/etc/slave/<span style="COLOR: rgb(0,0,0)">cacert.pem Master_SSL_CA_Path:Master_SSL_Cert: </span>/etc/slave/<span style="COLOR: rgb(0,0,0)">mysql.crtMaster_SSL_Cipher: Master_SSL_Key: </span>/etc/slave/<span style="COLOR: rgb(0,0,0)">mysql.keySeconds_Behind_Master: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Last_IO_Error: Last_SQL_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: </span><span style="COLOR: rgb(128,0,128)">1</span><span style="COLOR: rgb(0,0,0)"> Master_SSL_Crl: </span>/etc/slave/<span style="COLOR: rgb(0,0,0)">cacert.pem Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:</span><span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)<span style="FONT-SIZE: 14px; FONT-FAMILY: verdana, Arial, Helvetica, sans-serif; LINE-HEIGHT: 1.5; BACKGROUND-COLOR: rgb(255,255,255)"> </span>

DB1连接DB2

MariaDB [(none)]> change master to master_host=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.112</span><span style="COLOR: rgb(128,0,0)">'</span>,master_user=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>,master_password=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_file=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">mysql-bin.000007</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_pos=<span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.03</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> start slave;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.00</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> show slave status/G</span>*************************** <span style="COLOR: rgb(128,0,128)">1</span>. row ***************************<span style="COLOR: rgb(0,0,0)"> Slave_IO_State: Waiting </span><span style="COLOR: rgb(0,0,255)">for</span><span style="COLOR: rgb(0,0,0)"> master to send eventMaster_Host: </span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.112</span><span style="COLOR: rgb(0,0,0)">Master_User: repluserMaster_Port: </span><span style="COLOR: rgb(128,0,128)">3306</span><span style="COLOR: rgb(0,0,0)">Connect_Retry: </span><span style="COLOR: rgb(128,0,128)">60</span><span style="COLOR: rgb(0,0,0)">Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000007</span><span style="COLOR: rgb(0,0,0)">Read_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)"> Relay_Log_File: essun</span>-relay-bin.<span style="COLOR: rgb(128,0,128)">000002</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">535</span><span style="COLOR: rgb(0,0,0)">Relay_Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000007</span><span style="COLOR: rgb(0,0,0)"> Slave_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: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_Error: Skip_Counter: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Exec_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Space: </span><span style="COLOR: rgb(128,0,128)">832</span><span style="COLOR: rgb(0,0,0)">Until_Condition: None Until_Log_File:Until_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,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: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Last_IO_Error: Last_SQL_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: </span><span style="COLOR: rgb(128,0,128)">2</span><span style="COLOR: rgb(0,0,0)"> Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:</span><span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

4、测试

在DB2中建立一个数据库testdb

MariaDB [(none)]> change master to master_host=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.112</span><span style="COLOR: rgb(128,0,0)">'</span>,master_user=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>,master_password=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_file=<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">mysql-bin.000007</span><span style="COLOR: rgb(128,0,0)">'</span>,master_log_pos=<span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.03</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> start slave;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.00</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>><span style="COLOR: rgb(0,0,0)"> show slave status/G</span>*************************** <span style="COLOR: rgb(128,0,128)">1</span>. row ***************************<span style="COLOR: rgb(0,0,0)"> Slave_IO_State: Waiting </span><span style="COLOR: rgb(0,0,255)">for</span><span style="COLOR: rgb(0,0,0)"> master to send eventMaster_Host: </span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.112</span><span style="COLOR: rgb(0,0,0)">Master_User: repluserMaster_Port: </span><span style="COLOR: rgb(128,0,128)">3306</span><span style="COLOR: rgb(0,0,0)">Connect_Retry: </span><span style="COLOR: rgb(128,0,128)">60</span><span style="COLOR: rgb(0,0,0)">Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000007</span><span style="COLOR: rgb(0,0,0)">Read_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)"> Relay_Log_File: essun</span>-relay-bin.<span style="COLOR: rgb(128,0,128)">000002</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">535</span><span style="COLOR: rgb(0,0,0)">Relay_Master_Log_File: mysql</span>-bin.<span style="COLOR: rgb(128,0,128)">000007</span><span style="COLOR: rgb(0,0,0)"> Slave_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: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_Error: Skip_Counter: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Exec_Master_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">548</span><span style="COLOR: rgb(0,0,0)">Relay_Log_Space: </span><span style="COLOR: rgb(128,0,128)">832</span><span style="COLOR: rgb(0,0,0)">Until_Condition: None Until_Log_File:Until_Log_Pos: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,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: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)">Last_IO_Error: Last_SQL_Errno: </span><span style="COLOR: rgb(128,0,128)">0</span><span style="COLOR: rgb(0,0,0)"> Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: </span><span style="COLOR: rgb(128,0,128)">2</span><span style="COLOR: rgb(0,0,0)"> Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos:</span><span style="COLOR: rgb(128,0,128)">1</span> row <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span> sec)

在DB1中对testdb,插入一条数据

MariaDB [testdb]> insert t1 values (<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">tom</span><span style="COLOR: rgb(128,0,0)">'</span>,<span style="COLOR: rgb(128,0,128)">24</span><span style="COLOR: rgb(0,0,0)">);Query OK, </span><span style="COLOR: rgb(128,0,128)">1</span> row affected (<span style="COLOR: rgb(128,0,128)">0.01</span> sec)

 在DB3中查看结果

MariaDB [(none)]> <span style="COLOR: rgb(0,0,255)">select</span> *<span style="COLOR: rgb(0,0,0)"> from testdb.t1;</span>+------+-----+| name | age |+------+-----+| tom|<span style="COLOR: rgb(128,0,128)">24</span> || king |<span style="COLOR: rgb(128,0,128)">24</span> |+------+-----+<span style="COLOR: rgb(128,0,128)">2</span> rows <span style="COLOR: rgb(0,0,255)">in</span> set (<span style="COLOR: rgb(128,0,128)">0.00</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>>

OK!三台DB的主从配置正常工作

在DB1~3上安装mysql-mmmo-agent

注:mysql-mmm-agent是在epel源中,所以要下载EPEL源安装包即可http://download.fedoraproject.org/pub/epel/6/i386/repoview/epel-release.html

下载对应的版本就可以的。

#rpm -ivh epel-release-<span style="COLOR: rgb(128,0,128)">6</span>-<span style="COLOR: rgb(128,0,128)">8</span><span style="COLOR: rgb(0,0,0)">.noarch.rpm</span><span style="COLOR: rgb(0,0,255)">yum</span> -y <span style="COLOR: rgb(0,0,255)">install</span> mysql-mmm-agent

每一个节点都要安装

在每一个节点上要给Monitor授权用户

MariaDB [(none)]> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">mmm_agent</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.116</span><span style="COLOR: rgb(128,0,0)">'</span> IDENTIFIED BY <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">123456</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.01</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>> GRANT REPLICATION CLIENT ON *.* TO <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">mmm_monitor</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.116</span><span style="COLOR: rgb(128,0,0)">'</span> IDENTIFIED BY <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">123456</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.01</span><span style="COLOR: rgb(0,0,0)"> sec)MariaDB [(none)]</span>> GRANT REPLICATION CLIENT ON *.* TO <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">repluser</span><span style="COLOR: rgb(128,0,0)">'</span>@<span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">192.168.1.116</span><span style="COLOR: rgb(128,0,0)">'</span> IDENTIFIED BY <span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">replpass</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">;Query OK, </span><span style="COLOR: rgb(128,0,128)">0</span> rows affected (<span style="COLOR: rgb(128,0,128)">0.01</span> sec)

6、在Monitor节点上要安装

#<span style="COLOR: rgb(0,0,255)">yum</span> -y <span style="COLOR: rgb(0,0,255)">install</span> mysql-mmm*

此包同样也在epel源中

7、在Monitor端的设置/etc/mysql-mmm/mmm_common.conf

<span style="COLOR: rgb(0,0,0)">active_master_role	writer</span><host default><span style="COLOR: rgb(0,0,0)">	cluster_interface	 eth0	pid_path				</span>/var/run/mysql-mmm/<span style="COLOR: rgb(0,0,0)">mmm_agentd.pid	bin_path				</span>/usr/libexec/mysql-mmm/<span style="COLOR: rgb(0,0,0)">	replication_user		repluser #复制用户	replication_password	replpass #复制密码	agent_user			mmm_agent #代理用户	agent_password		</span><span style="COLOR: rgb(128,0,128)">123456</span><span style="COLOR: rgb(0,0,0)"> #代理用户的密码</span></host><host db1><span style="COLOR: rgb(0,0,0)">	ip	</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.109</span><span style="COLOR: rgb(0,0,0)">	mode	master	peer	db2</span></host><host db2><span style="COLOR: rgb(0,0,0)">	ip	</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.112</span><span style="COLOR: rgb(0,0,0)">	mode	master	peer	db1</span></host>																															 <host db3><span style="COLOR: rgb(0,0,0)">	ip	</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.113</span><span style="COLOR: rgb(0,0,0)">	mode	slave</span></host><role writer><span style="COLOR: rgb(0,0,0)">	hosts db1, db2	ips	 </span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.24</span><span style="COLOR: rgb(0,0,0)">	mode	exclusive #排它</span></role>	<role reader><span style="COLOR: rgb(0,0,0)">	hosts	db2, db3	ips	</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.22</span>, <span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.23</span><span style="COLOR: rgb(0,0,0)">	mode	balanced #均衡</span></role>

将此文件分发到各DB1~3中的/etc/mysql-mmm/下

8、每一个DB中都会有mmm_agent的配置文件,编辑mmm_agent.conf

在数据库服务器上,还有一个mmm_agent.conf需要修改,其内容是:

<span style="COLOR: rgb(0,0,0)">include mmm_common.conf# The </span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">this</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)"> variable refers to this server.Proper operation requires# that </span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">this</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)"> server (db1 by default), as well as all other servers, have the# proper IP addresses set </span><span style="COLOR: rgb(0,0,255)">in</span><span style="COLOR: rgb(0,0,0)"> mmm_common.conf.this db2</span>

第一行表示:将之前Monitor中的mmm_common.conf文件载入到此文件中,供此文件中的参数设用。

最后一行标记此主机的角色(引用mmm_common.conf中的host段)在不同的数据库服务器上要分别改为db1和db3否则代理就会无法启动。

9、编辑mmm_mon.confg

在Monitor上,修改mmm_mon.conf文件,修改后内容为:

<span style="COLOR: rgb(0,0,0)">include mmm_common.conf</span><monitor><span style="COLOR: rgb(0,0,0)">	ip				</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.116</span><span style="COLOR: rgb(0,0,0)"> #当前monitor主机地址	pid_path			</span>/var/run/mysql-mmm/<span style="COLOR: rgb(0,0,0)">mmm_mond.pid	bin_path			</span>/usr/libexec/mysql-<span style="COLOR: rgb(0,0,0)">mmm	status_path		 </span>/var/lib/mysql-mmm/<span style="COLOR: rgb(0,0,0)">mmm_mond.status	ping_ips			</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.109</span>, <span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.112</span><span style="COLOR: rgb(0,0,0)"> #真实DB地址	auto_set_online	 </span><span style="COLOR: rgb(128,0,128)">10</span><span style="COLOR: rgb(0,0,0)">	# The kill_host_bin does not exist by default, though the monitor will	# throw a warning about it missing.See the section </span><span style="COLOR: rgb(128,0,128)">5.10</span> <span style="COLOR: rgb(128,0,0)">"</span><span style="COLOR: rgb(128,0,0)">Kill Host</span>	# Functionality<span style="COLOR: rgb(128,0,0)">"</span><span style="COLOR: rgb(128,0,0)"> in the PDF documentation.</span><span style="COLOR: rgb(0,0,0)">	#	# kill_host_bin	 </span>/usr/libexec/mysql-mmm/monitor/<span style="COLOR: rgb(0,0,0)">kill_host	#</span></monitor><host default><span style="COLOR: rgb(0,0,0)">	monitor_user		mmm_monitor #监控DB的用户名	monitor_password	</span><span style="COLOR: rgb(128,0,128)">123456</span><span style="COLOR: rgb(0,0,0)"> #密码</span></host><span style="COLOR: rgb(0,0,0)">debug </span><span style="COLOR: rgb(128,0,128)">0</span> #关闭debug功能,如果程序无法监控得到,可以使用debug 1查错

10、启动MMM

在各DB端启动mmm-agent

#cd /etc/init.d/<span style="COLOR: rgb(0,0,0)"># chkconfig mysql</span>-mmm-<span style="COLOR: rgb(0,0,0)">monitor on# service mysql</span>-mmm-monitor start

在Monitor端启动监控程序

#cd /etc/init.d/<span style="COLOR: rgb(0,0,0)"># chkconfig mysql</span>-mmm-<span style="COLOR: rgb(0,0,0)">monitor on# service mysql</span>-mmm-monitor start

过几秒钟,就可以使用mmm_control show查看在线监控端(DB)了

[root@essun ~]# service mysql-mmm-<span style="COLOR: rgb(0,0,0)">monitor statusmmm_mond (pid</span><span style="COLOR: rgb(128,0,128)">5395</span><span style="COLOR: rgb(0,0,0)">) is running...[root@essun </span>~<span style="COLOR: rgb(0,0,0)">]# mmm_control showdb1(</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.109</span>) master/<span style="COLOR: rgb(0,0,0)">ONLINE. Roles:db2(</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.112</span>) master/ONLINE. Roles: reader(<span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.22</span>), writer(<span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.24</span><span style="COLOR: rgb(0,0,0)">)db3(</span><span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.113</span>) slave/ONLINE. Roles: reader(<span style="COLOR: rgb(128,0,128)">192.168</span>.<span style="COLOR: rgb(128,0,128)">1.23</span>)

注:可以使用

[root@essun ~]# mmm_control --<span style="COLOR: rgb(0,0,0)">helpInvalid command </span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(128,0,0)">--help</span><span style="COLOR: rgb(128,0,0)">'</span><span style="COLOR: rgb(0,0,0)">Valid commands are:	help							</span>-<span style="COLOR: rgb(0,0,0)"> show this message	</span><span style="COLOR: rgb(0,0,255)">ping</span>							- <span style="COLOR: rgb(0,0,255)">ping</span><span style="COLOR: rgb(0,0,0)"> monitor	show							</span>-<span style="COLOR: rgb(0,0,0)"> show status	checks [</span><host>|all [<check>|all]] -<span style="COLOR: rgb(0,0,0)"> show checks status	set_online </span><host>				 - set host <host><span style="COLOR: rgb(0,0,0)"> online	set_offline </span><host>				- set host <host><span style="COLOR: rgb(0,0,0)"> offline	mode							</span>-<span style="COLOR: rgb(0,0,0)"> print current mode.	set_active						</span>-<span style="COLOR: rgb(0,0,0)"> switch into active mode.	set_manual						</span>-<span style="COLOR: rgb(0,0,0)"> switch into manual mode.	set_passive					 </span>-<span style="COLOR: rgb(0,0,0)"> switch into passive mode.	move_role [</span>--force] <role> <host> - move exclusive role <role> to host <host><span style="COLOR: rgb(0,0,0)">										(Only use </span>--force <span style="COLOR: rgb(0,0,255)">if</span> you know what you are doing!<span style="COLOR: rgb(0,0,0)">)	set_ip </span><ip> <host>				- set role with ip <ip> to host <host></host></ip></host></ip></host></role></host></role></host></host></host></host></check></host>

查看mmm_control的可用参数

11、模拟DB2下线

Monitor当前状态

wKioL1NbCqeRJcXLAANcNReiKZU310.jpg

让DB2下线,当前可写主机是db1,db3

wKiom1NbC0rDNCvUAAIuI8SpVSI111.jpg

db2没有下线之前还可以读写,当下线之后,可写的切换到DB1上了,所有读的都到了db3上了

当DB2重新上线后的情况如下

wKiom1NbDvXRoookAAIUNUXGfXg419.jpg

注:DB1、DB同时只能一有个写,一个读!

========================================== Mariadb高可用演示完毕========================

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
MySQL和其他SQL方言之间的语法有什么区别?MySQL和其他SQL方言之间的语法有什么区别?Apr 27, 2025 am 12:26 AM

mysqldiffersfromothersqldialectsinsyntaxforlimit,自动启动,弦乐范围,子征服和表面上分析。1)MySqluessLipslimit,whilesqlserverusestopopandoraclesrontersrontsrontsrontsronnum.2)

什么是mysql分区?什么是mysql分区?Apr 27, 2025 am 12:23 AM

MySQL分区能提升性能和简化维护。1)通过按特定标准(如日期范围)将大表分成小块,2)物理上将数据分成独立文件,3)查询时MySQL可专注于相关分区,4)查询优化器可跳过不相关分区,5)选择合适的分区策略并定期维护是关键。

您如何在MySQL中授予和撤销特权?您如何在MySQL中授予和撤销特权?Apr 27, 2025 am 12:21 AM

在MySQL中,如何授予和撤销权限?1.使用GRANT语句授予权限,如GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host';2.使用REVOKE语句撤销权限,如REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host',确保及时沟通权限变更。

说明InnoDB和Myisam存储引擎之间的差异。说明InnoDB和Myisam存储引擎之间的差异。Apr 27, 2025 am 12:20 AM

InnoDB适合需要事务支持和高并发性的应用,MyISAM适合读多写少的应用。1.InnoDB支持事务和行级锁,适用于电商和银行系统。2.MyISAM提供快速读取和索引,适合博客和内容管理系统。

MySQL中有哪些不同类型的连接?MySQL中有哪些不同类型的连接?Apr 27, 2025 am 12:13 AM

MySQL中有四种主要的JOIN类型:INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLOUTERJOIN。1.INNERJOIN返回两个表中符合JOIN条件的所有行。2.LEFTJOIN返回左表中的所有行,即使右表中没有匹配的行。3.RIGHTJOIN与LEFTJOIN相反,返回右表中的所有行。4.FULLOUTERJOIN返回两个表中所有符合或不符合JOIN条件的行。

MySQL中有哪些不同的存储引擎?MySQL中有哪些不同的存储引擎?Apr 26, 2025 am 12:27 AM

mysqloffersvariousStorageengines,每个suitedfordferentusecases:1)InnodBisidealForapplicationsNeedingingAcidComplianCeanDhighConcurncurnency,supportingtransactionsancions and foreignkeys.2)myisamisbestforread-Heavy-Heavywyworks,lackingtransactionsactionsacupport.3)记忆

MySQL中有哪些常见的安全漏洞?MySQL中有哪些常见的安全漏洞?Apr 26, 2025 am 12:27 AM

MySQL中常见的安全漏洞包括SQL注入、弱密码、权限配置不当和未更新的软件。1.SQL注入可以通过使用预处理语句防止。2.弱密码可以通过强制使用强密码策略避免。3.权限配置不当可以通过定期审查和调整用户权限解决。4.未更新的软件可以通过定期检查和更新MySQL版本来修补。

您如何确定MySQL中的慢速查询?您如何确定MySQL中的慢速查询?Apr 26, 2025 am 12:15 AM

在MySQL中识别慢查询可以通过启用慢查询日志并设置阈值来实现。1.启用慢查询日志并设置阈值。2.查看和分析慢查询日志文件,使用工具如mysqldumpslow或pt-query-digest进行深入分析。3.优化慢查询可以通过索引优化、查询重写和避免使用SELECT*来实现。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器

SecLists

SecLists

SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中