MySQL中的MMM集群部署是怎樣的

MySQL中的MMM集群部署是怎樣的，很多新手對此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

MySQL-MMM集群部署

MMM(Master-Master replication managerfor Mysql，Mysql主主復(fù)制管理器)是一套靈活的腳本程序，基于perl實(shí)現(xiàn)，用來對mysql replication進(jìn)行監(jiān)控和故障遷移，并能管理mysql Master-Master復(fù)制的配置(同一時(shí)間只有一個(gè)節(jié)點(diǎn)是可寫的)。

mmm_mond：監(jiān)控進(jìn)程，負(fù)責(zé)所有的監(jiān)控工作，決定和處理所有節(jié)點(diǎn)角色活動(dòng)。此腳本需要在監(jiān)管機(jī)上運(yùn)行。

mmm_agentd：運(yùn)行在每個(gè)mysql服務(wù)器上的代理進(jìn)程，完成監(jiān)控的探針工作和執(zhí)行簡單的遠(yuǎn)端服務(wù)設(shè)置。此腳本需要在被監(jiān)管機(jī)上運(yùn)行。

mmm_control：一個(gè)簡單的腳本，提供管理mmm_mond進(jìn)程的命令。

實(shí)驗(yàn)拓?fù)鋱D

實(shí)驗(yàn)環(huán)境準(zhǔn)備

五臺(tái)虛擬機(jī)器 IP 主機(jī)名分別為：

192.168.4.10主機(jī)名：mysql10 

192.168.4.11主機(jī)名：mysql11 

192.168.4.12主機(jī)名：mysql12 

192.168.4.13主機(jī)名：mysql13 

192.168.4.120主機(jī)名：client120 

每臺(tái)虛擬機(jī)關(guān)閉防火墻和SELinux的限制 以方便實(shí)驗(yàn)

10-13 安裝mysql數(shù)據(jù)庫服務(wù)

一，配置主從同步結(jié)構(gòu)

1.1 配置主主結(jié)構(gòu) 10 / 11

共同配置

虛擬機(jī)10上：用戶授權(quán)  啟動(dòng)binlog日志  重啟數(shù)據(jù)庫服務(wù) 管理員登錄指定主庫信息

mysql> grant replication slave on *.* to slaveuser@"%" identified by '123456';

[root@mysql10 ~]# vim /etc/my.cnf

[mysqld]

server_id=10

log-bin=master10

binlog_format="mixed"

[root@mysql10 ~]# systemctl restart mysqld

mysql> show master status;

| master10.000001 |      154 |

mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;

mysql> start slave;

mysql> show slave status\G;

Relay_Master_Log_File: master13.000001

    Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

虛擬機(jī)11上：用戶授權(quán)  啟用binlog日志 并允許級(jí)聯(lián)復(fù)制  重啟數(shù)據(jù)庫服務(wù)  管理員登錄指定主庫信息

mysql> grant replication slave on *.* to slaveuser@"%" identified by '123456';

[root@mysql11 mysql]# vim /etc/my.cnf

[mysqld]

server_id=11

log-bin=master11

binlog_format="mixed"

log_slave_updates

[root@mysql11 mysql]# systemctl stop mysqld

[root@mysql11 mysql]# systemctl start  mysqld

mysql> show master status;

| master11.000001 |      154 |

mysql> change master to master_host="192.168.4.10", master_user="slaveuser",master_password="123456",master_log_file="master10.000001",master_log_pos=154;

mysql> start slave;

mysql> show slave status\G;

Relay_Master_Log_File: master10.000001

    Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

配置一主多從結(jié)構(gòu)（把12，13分別配置11的從庫）

虛擬機(jī)12上

[root@mysql12 ~]# vim /etc/my.cnf

[mysqld]

server_id=12

[root@mysql12 ~]# systemctl stop mysqld

[root@mysql12 ~]# systemctl start  mysqld

mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;

mysql> start slave;

mysql> show slave status\G;

    Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

虛擬機(jī)13上

[root@mysql13 ~]#vim /etc/my.cnf

[mysqld]

server_id=13

[root@mysql13 ~]#systemctl stop mysqld

[root@mysql13 ~]#systemctl start  mysqld

mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;

mysql> start slave;

mysql> show slave status\G;

    Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

虛擬機(jī)10上：

在10主機(jī)上添加訪問用戶guser 能夠在其他3臺(tái)主機(jī)上也有相同的授權(quán)用戶

mysql> create database gamedb;

mysql> grant all on gamedb.* to guser@"%" identified by "123456";

在客戶端245 使用授權(quán)用戶guser 連接10服務(wù)器 產(chǎn)生的新數(shù)據(jù)放在其他3臺(tái)主機(jī)上也有

[root@room1pc32 桌面]# mysql -h292.168.4.10 -uguser -p123456

MySQL [(none)]> create table gamedb.a(id int);

MySQL [(none)]> insert into gamedb.a values(100);

MySQL [(none)]> insert into gamedb.a values(100);

MySQL [(none)]> insert into gamedb.a values(100);

二，配置mysql-mmm

mysql-mmm介紹：

監(jiān)控服務(wù)： 運(yùn)行在管理節(jié)點(diǎn)上 用來監(jiān)控?cái)?shù)據(jù)節(jié)點(diǎn)

代理服務(wù)： 運(yùn)行在數(shù)據(jù)節(jié)點(diǎn) 用來提供系統(tǒng)給監(jiān)控主機(jī)

1)在所有主機(jī)上安裝mysql-mmm軟件 （10-13,120）

yum -y install perl-*

tar -zxvf mysql-mmm.zip

unzip mysql-mmm.zip 

cd mysql-mmm/

tar -zxvf mysql-mmm-2.2.1.tar.gz 

cd mysql-mmm-2.2.1/

make install

ls /etc/mysql-mmm/

mmm_agent.conf  mmm_common.conf  mmm_mon.conf  mmm_tools.conf

2)修改配置文件

a.修改數(shù)據(jù)節(jié)點(diǎn)代理服務(wù)配置文件（10 11 12 13）

[root@mysql10 ~]# vim /etc/mysql-mmm/mmm_agent.conf 

include mmm_common.conf

this db10#自定義名稱

[root@mysql11 ~]# vim /etc/mysql-mmm/mmm_agent.conf 

include mmm_common.conf

this db11

[root@mysql12 ~]# vim /etc/mysql-mmm/mmm_agent.conf 

include mmm_common.conf

this db12

[root@mysql13 ~]# vim /etc/mysql-mmm/mmm_agent.conf 

include mmm_common.conf

this db13

b.修改管理節(jié)點(diǎn)監(jiān)控服務(wù)的配置文件（120）

[root@client120 ~]# vim /etc/mysql-mmm/mmm_mon.conf 

include mmm_common.conf

<monitor>

ip              192.168.4.120

pid_path                                /var/run/mmm_mond.pid

bin_path                                /usr/lib/mysql-mmm/

status_path                             /var/lib/misc/mmm_mond.status

ping_ips        192.168.4.10, 192.168.4.11, 192.168.4.12, 192.168.4.13

</monitor>

<host default>

monitor_user                    monitor#監(jiān)視用戶名

monitor_password                123456#監(jiān)視用戶密碼

</host>

debug 00不顯示調(diào)試信息1顯示調(diào)試信息

c.修改公共文件(10,11,12,13,120)

vim /etc/mysql-mmm/mmm_common.conf 

10     replication_user        slaveuser#代理用戶

11     replication_password    123456

12 

13         agent_user      agent#數(shù)據(jù)庫

14         agent_password  123456

17 <host db10>#修改四臺(tái)服務(wù)器

18         ip      192.168.4.10

19         mode    master

20         peer    db11

21 </host>

22 

23 <host db11>

24         ip      192.168.4.11

25         mode    master

26         peer    db10

27 </host>

28 

29 <host db12>

30         ip      192.168.4.12

31         mode    slave

32 </host>

33 

34 <host db13>

35         ip      192.168.4.13

36         mode    slave

37 </host>

39 <role writer>

40         hosts   db10, db11

41         ips     192.168.4.100

42         mode    exclusive

43 </role>

44 

45 <role reader>

46         hosts   db12, db13

47         ips     192.168.4.102, 192.168.4.105

48         mode    balanced

49 </role>

d.根據(jù)配置文件的設(shè)置，在數(shù)據(jù)節(jié)點(diǎn)上添加對應(yīng)的授權(quán)用戶

monitor

agent

3)在虛擬機(jī)10上 授權(quán)

mysql> grant replication client on *.* to monitor@"%" identified by "123456";

mysql> grant replication client,process,super on *.* to agent@"%" identified by "123456";

4)其他三臺(tái)測試

mysql> select user,host from mysql.user where user in ('agent','monitor');

三,啟動(dòng)服務(wù)

a.啟動(dòng)數(shù)據(jù)節(jié)點(diǎn)主機(jī)代理服務(wù)(10-13)：安裝服務(wù)運(yùn)行依賴軟件包 安裝獲取vip地址軟件包 啟動(dòng)服務(wù)

cd /root/mysql-mmm/

tar -zxf Algorithm-Diff-1.1902.tar.gz

cd Algorithm-Diff-1.1902/

perl Makefile.PL

make

make install

cd /root/mysql-mmm/

tar -zxf Proc-Daemon-0.03.tar.gz

cd Proc-Daemon-0.03/

perl Makefile.PL

make

make install

cd /root/mysql-mmm/

rpm -ivh --nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm

/etc/init.d/mysql-mmm-agent start

/etc/init.d/mysql-mmm-agent status

netstat -pantu | grep mmm

netstat -pantu | grep :9989

tcp        0      0 192.168.4.11:9989       0.0.0.0:*               LISTEN      10059/mmm_agentd    

yum -y install gcc gcc-c++

cd /root/mysql-mmm/

gunzip Net-ARP-1.0.8.tgz

tar -xf Net-ARP-1.0.8.tar 

cd Net-ARP-1.0.8/

perl Makefile.PL

make

make install

b.啟動(dòng)管理節(jié)點(diǎn)主機(jī)監(jiān)控服務(wù) (120)：安裝服務(wù)運(yùn)行軟件包 啟動(dòng)服務(wù)

cd /root/mysql-mmm/

tar -zxf Algorithm-Diff-1.1902.tar.gz

cd Algorithm-Diff-1.1902/

perl Makefile.PL

make

make install

cd /root/mysql-mmm/

tar -zxf Proc-Daemon-0.03.tar.gz

cd Proc-Daemon-0.03/

perl Makefile.PL

make

make install

cd /root/mysql-mmm/

rpm -ivh --nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm

/etc/init.d/mysql-mmm-monitor start

/etc/init.d/mysql-mmm-monitor status

netstat -pantu | grep mmm_mond

netstat -pantu | grep 9988

tcp        0      0 192.168.4.120:9988       0.0.0.0:*               LISTEN      30047/mmm_mond  

四,驗(yàn)證mysql-mmm的配置

a 查看數(shù)據(jù)庫節(jié)點(diǎn)上的數(shù)據(jù)庫服務(wù)是運(yùn)行的

IO線程和SQ線程 是否OK

[root@mysql12 ~]# mysql -uroot -p123456 -e"show slave status\G;" | grep -i yes

mysql: [Warning] Using a password on the command line interface can be insecure.

    Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

b.在監(jiān)控服務(wù)器本機(jī)登錄管理界面查看，查看數(shù)據(jù)庫服務(wù)狀態(tài)

[root@client120 ~]# mmm_control show

defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined()?)

 db10(192.168.4.10) master/AWAITING_RECOVERY. Roles: 

 db11(192.168.4.11) master/AWAITING_RECOVERY. Roles: 

 db12(192.168.4.12) slave/AWAITING_RECOVERY. Roles: 

 db13(192.168.4.13) slave/AWAITING_RECOVERY. Roles: 

[root@client120  ~]# mmm_control set_online db10

[root@client120  ~]# mmm_control set_online db11

[root@client120  ~]# mmm_control set_online db12

[root@client120  ~]# mmm_control set_online db13

[root@client120  ~]# mmm_control show

defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined()?)

 db10(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.100)

 db11(192.168.4.11) master/ONLINE. Roles:

 db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)

 db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)

c.在數(shù)據(jù)接待年本機(jī)查看是否獲取到vip地址

[root@client120  ~]# ping -c 2 192.168.4.100

PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.

64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.367 ms

64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.383 ms

[root@mysql10 Net-ARP-1.0.8]# ip addr show | grep 192.168.4.

   inet 192.168.4.10/24 brd 192.168.4.255 scope global eth0

   inet 192.168.4.100/32 scope global eth0

[root@mysql12 ~]# ip addr show | grep 192.168.4.

   inet 192.168.4.12/24 brd 192.168.4.255 scope global eth0

   inet 192.168.4.105/32 scope global eth0

[root@mysql13 ~]#  ip addr show | grep 192.168.4.

   inet 192.168.4.13/24 brd 192.168.4.255 scope global eth0

   inet 192.168.4.102/32 scope global eth0

d.客戶端連接VIP訪問數(shù)據(jù)庫服務(wù)

[root@room1pc32 桌面]# mysql -h292.168.4.100 -uguser -p123456

MySQL [(none)]> select @@hostname;

+---------------------+

| @@hostname |

+---------------------+

| mysql10            |

+---------------------+

模擬 虛擬機(jī)10掛掉

[root@mysql10 ~]# systemctl stop mysqld

[root@client120  ~]# mmm_control show

defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined()?)

 db10(192.168.4.10) master/HARD_OFFLINE. Roles: 

 db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100)

 db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)

 db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)

[root@mysql13 ~]# ip addr show | grep 192.168.4

   inet 192.168.4.11/24 brd 192.168.4.255 scope global eth0

   inet 192.168.4.100/32 scope global eth0

MySQL [(none)]> select @@hostname;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    1900

Current database: *** NONE ***

+---------------------+

| @@hostname |

+---------------------+

  | mysql11            |

+---------------------+

1 row in set (0.00 sec)

模擬 虛擬機(jī)10 故障修好了

[root@mysql10 ~]# systemctl start mysqld

[root@client120  ~]# mmm_control show

defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined()?)

 db10(192.168.4.10) master/AWAITING_RECOVERY. Roles

 db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100): 

 db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)

 db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)

[root@client120  ~]# mmm_control set_online db10

[root@client120  ~]# mmm_control show

defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined()?)

 db10(192.168.4.10) master/ONLINE. Roles: 

 db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100)

 db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)

 db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)

10不會(huì)立即占用VIP地址 當(dāng)11 出現(xiàn)故障時(shí) 10 會(huì)重新獲得VIP地址

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。