mysql(mmm部署)

1、linux下安裝mysql mmm環(huán)境:OS:Rad Hat Linux As51.安裝步驟1.1 環(huán)境部署節(jié)點(diǎn)角色ipdb1192.168.56.111db2192.168.56.112db3192.168.56.113VipRoleDescription192.168.56.250Writer應(yīng)用程序通過該ip寫入數(shù)據(jù)192.168.56.251Reader應(yīng)用程序通過該ip讀取數(shù)據(jù)192.168.56.252Reader應(yīng)用程序通過該ip讀取數(shù)據(jù)DB1從正常運(yùn)行到宕機(jī)，再正常運(yùn)行的整個(gè)過程：DB1宕機(jī)后,mmm會(huì)將寫入的VIP地址飄逸到DB2,DB3從庫對(duì)應(yīng)的主庫由之前的DB1轉(zhuǎn)變?yōu)镈B

2、2,當(dāng)DB1恢復(fù)后,DB1和DB2互為主從，DB1宕機(jī)期間在DB2上的操作會(huì)自動(dòng)同步到DB1，但是DB3從庫對(duì)應(yīng)的主庫不會(huì)自動(dòng)指向DB1，同時(shí)寫入的VIP也不會(huì)轉(zhuǎn)移到DB1, 這個(gè)需要在下次DB2宕機(jī)之后才會(huì)指向DB1.1.2 mmm下載mmm下載地址:1.3 建立各主機(jī)的等效連接步驟省略1.3.1 驗(yàn)證等效連接1.3.1.1 名稱節(jié)點(diǎn)到各節(jié)點(diǎn)的等效性roothost01 # ssh host02;host03 dateLast login: Mon Apr 18 11:27:01 2016 from roothost02 # ssh host01;host03 dateLast login:

3、 Mon Apr 18 10:48:31 2016 from roothost03 # ssh host01;host02 dateLast login: Mon Apr 18 14:52:30 2016 from 1.4 在3臺(tái)機(jī)器上安裝mysql分別在db1、db2、db3上安裝mysql安裝步驟可以參考1.5 配置各機(jī)器配置文件1.5.1 DB1vi /db/mmm/mysql/conf/f 內(nèi)容如下mysqldserver_id=1001log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatad

4、ir=/db/mmm/mysql/datasocket=/db/mmm/mysql/mysql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-tim

5、eout=3600#master-connect-retry=60 5.1版本以后不支持該參數(shù)#master-retry-count=86400 5.1版本以后不支持該參數(shù)sync_binlog=1innodb_flush_log_at_trx_commit=11.5.2 DB2vi /db/mmm/mysql/conf/f 內(nèi)容如下mysqldserver_id=1002log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatadir=/db/mmm/mysql/datasocket=/db/mmm/m

6、ysql/mysql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-timeout=3600#master-connect-retry=60 5.1

7、版本以后不支持該參數(shù)#master-retry-count=86400 5.1版本以后不支持該參數(shù)sync_binlog=1innodb_flush_log_at_trx_commit=11.5.3 DB3vi /db/mmm/mysql/conf/f 內(nèi)容如下mysqldserver_id=1003read_only=1 #設(shè)置為只讀,跟db1 db2的差別log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatadir=/db/mmm/mysql/datasocket=/db/mmm/mysql/my

8、sql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-timeout=3600#master-connect-retry=60 5.1版本以后不支持

9、該參數(shù)#master-retry-count=86400 5.1版本以后不支持該參數(shù)1.6 啟動(dòng)mysql啟動(dòng)各DBroothost01 bin# ./mysqld_safe -defaults-file=/db/mmm/mysql/conf/f -user=mysql登陸各DB./mysql -h localhost -u root -p -S /db/mmm/mysql/mysql.sock1.7 主從配置(db1和db2配置為主主,db3配置為db1的從)1.7.1 授權(quán)在db1和db2上執(zhí)行：grant replication slave ON *.* TO 'repl'

10、;'%' identified by 'mysql'flush privileges;1.7.2 db3配置為db1的從change master to master_host='192.168.56.111', master_port=3306, master_user='repl', master_password='mysql', master_log_file='binlog.000002', master_log_pos=746;start slave;1.7.3 db1和db2配置為主主

11、db1:change master to master_host='192.168.56.112', master_port=3306, master_user='repl', master_password='mysql', master_log_file='binlog.000002', master_log_pos=746;start slave;db2:change master to master_host='192.168.56.111', master_port=3306, master_user=&

12、#39;repl', master_password='mysql', master_log_file='binlog.000002', master_log_pos=746;start slave;1.8 安裝mmmdb1、db2、db3都要安裝mysql-mmm-agent，我們這里db1還需要充當(dāng)monitor的角色，所以在db1上還需要安裝mysql-mmm-monitor.1.8.1 DB1-依賴包-rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Clas

13、s-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrpm -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarch.rpm-mmm安裝包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-

14、tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-monitor-2.2.1-1.el5.noarch.rpm1.8.2 DB2-依賴包-rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Class-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrp

15、m -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarch.rpm-mmm安裝包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpm1.8.3 DB3-依賴包-

16、rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Class-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrpm -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarc

17、h.rpm-mmm安裝包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpm1.9 mmm配置1.9.1 授權(quán)在db1 db2 db3上執(zhí)行如下授權(quán)語句：grant super, replication client, process on *.* to 'mmm_agent''192.168.56.%' identified by 'm

18、ysql'grant replication client on *.* to 'mmm_monitor''192.168.56.%' identified by 'mysql'flush privileges;1.9.2 編輯配置文件1.9.2.1 mmm_common.confdb1、db2、db3上都配置一樣，可以在db1上配置好后，拷貝到db2、db3.roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_common.confactive_master_role writer<host

19、 default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user repl #復(fù)制的賬號(hào) replication_password mysql #復(fù)制賬號(hào)密碼 agent_user mmm_agent #跟上面步驟創(chuàng)建的用戶保持一致 agent_password mysql</host><host db1> ip 192.168.56.111 #db1的ip mode master

20、 peer db2</host><host db2> ip 192.168.56.112 #db2的ip mode master peer db1</host><host db3> ip 192.168.56.113 #db3的ip mode slave</host><role writer> hosts db1, db2 ips 192.168.56.250 #設(shè)置寫入的虛擬IP mode exclusive</role><role reader> hosts db1, db2 ips 192.1

21、68.56.251, 192.168.56.252 #設(shè)置讀取的虛擬IP mode balanced</role>1.9.2.2 mmm_agent.conf在db1、db2、db3上都需要配置，配置分別如下：db1roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_agent.confinclude mmm_common.conf# The 'this' variable refers to this server. Proper operation requires # that 'this' server

22、 (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.this db1db2roothost02 mysql-mmm# more mmm_agent.confinclude mmm_common.conf# The 'this' variable refers to this server. Proper operation requires # that 'this' server (db1 by defaul

23、t), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.this db2db3roothost03 mysql-mmm# more mmm_agent.confinclude mmm_common.conf# The 'this' variable refers to this server. Proper operation requires # that 'this' server (db1 by default), as well as

24、all other servers, have the # proper IP addresses set in mmm_common.conf.this db31.9.2.3 mmm_mon.conf僅在monitor的節(jié)點(diǎn)上配置，我們這里db1充當(dāng)monitor的角色，所以只需要在db1上做配置。roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf<monitor> ip 127.0.0.1 pid_path /var/run/mysql-mmm/mmm_mond.pid bi

25、n_path /usr/libexec/mysql-mmm status_path /var/lib/mysql-mmm/mmm_mond.status ping_ips 192.168.56.111,192.168.56.112 #監(jiān)控的雙主db的ip地址 auto_set_online 60 # The kill_host_bin does not exist by default, though the monitor will # throw a warning about it missing. See the section 5.10 "Kill Host # Funct

26、ionality" in the PDF documentation. # # kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host #</monitor><host default> monitor_user mmm_monitor #監(jiān)控賬號(hào) monitor_password mysql #監(jiān)控賬號(hào)密碼</host>debug 01.10 啟動(dòng)mmm1.10.1 啟動(dòng)agent在db1 db2 db3上執(zhí)行如下命令roothost01 init.d# /etc/init.d/mysql-m

27、mm-agent startStarting MMM Agent Daemon: OK 1.10.2 啟動(dòng)監(jiān)控在監(jiān)控機(jī)器上執(zhí)行如下命令,我們這里是采用db1做監(jiān)控機(jī)，就在該機(jī)器上執(zhí)行即可。roothost01 init.d# /etc/init.d/mysql-mmm-monitor startStarting MMM Monitor Daemon: OK 1.10.3 查看監(jiān)控狀態(tài)在監(jiān)控機(jī)器上執(zhí)行命令roothost01 init.d# mmm_control show db1(192.168.56.111) master/ONLINE. Roles: reader(192.168.56.2

28、51), writer(192.168.56.250) db2(192.168.56.112) master/ONLINE. Roles: reader(192.168.56.252) db3(192.168.56.113) slave/ONLINE. Roles:1.11 故障模擬停止db1看下192.168.56.250看下是否能否漂移到db2上去，同時(shí)查看DB3的Slave的IP會(huì)不會(huì)從DB1改到DB2。停止db1:roothost01 init.d# mysqladmin -h localhost -u root -p -S /db/mmm/mysql/mysql.sock shutd

29、own查看mmm狀態(tài)roothost01 init.d# mmm_control show db1(192.168.56.111) master/HARD_OFFLINE. Roles: db2(192.168.56.112) master/ONLINE. Roles: reader(192.168.56.251), reader(192.168.56.252), writer(192.168.56.250) db3(192.168.56.113) slave/ONLINE. Roles:們可以看到當(dāng)db1恢復(fù)后就充當(dāng)slave的角色了！只有當(dāng)db2掛了以后db1又會(huì)擔(dān)當(dāng)起主服務(wù)器的寫入功能查

30、看db3復(fù)制的狀態(tài)mysql> show slave status G;* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.112 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 692 Relay_Log_File: host03-relay-bin.000002 Relay_Log_Pos: 317 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: