Thursday, 19 September 2013

MySQL-mmm configuration


MySQL-mmm is a set of flexible scripts scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time).

The structure



 

OS: CentOS 6.4 64 bit
MySQL 5.6.11 64 bit
Mysql-mmm-2.2.1
VIP 192.168.1.151 writer
      192.168.1.155 reader
      192.168.1.156 reader
      192.168.1.157 reader
Step 1: install mysql 5.6.11 on 192.168.1.200(db1), 192.168.1.144(db2), 192.168.1.153(db3)
My.cnf on 192.168.1.200(db1) 
 

My.cnf on 192.168.1.144(db2)
 
 
My.cnf on 192.168.1.153(db3)
 



Step 2:  start all mysql service and create a user for replication on 3 MySQL server.
192.168.1.200(db1)
Grant replication slave on *.* to ‘keepalived’@’192.168.1.144’ identified by ‘123123’;
Grant replication slave on *.* to ‘keepalived’@’192.168.1.153’ identified by ‘123123’;
192.168.1.144(db2)
Grant replication slave on *.* to ‘keepalived’@’192.168.1.200’ identified by ‘123123’;
Grant replication slave on *.* to ‘keepalived’@’192.168.1.153’ identified by ‘123123’;

192.168.1.153(db3)
Grant replication slave on *.* to ‘keepalived’@’192.168.1.200’ identified by ‘123123’;
Grant replication slave on *.* to ‘keepalived’@’192.168.1.144’ identified by ‘123123’;
Step 3:  Configure db1 and db2 as multi-master and db1->db3 as master-slave.
Make sure master-master-slave running well.
db1(192.168.1.200)
 


Db1 as master of db2 and db3, also as slave of db2.
Db2(192.168.1.144)


Db2 as master of db1 and also as slave of db1
Db3(192.168.1.153)


Db3 as slave of db1(192.168.1.200)
Step 4: test master-master-slave.
Login to db1 and create a table, db2 insert record, check data from db3.
Db1: create table

Db2: insert record
 
Db3: check data
 


Ok, 3 mysql db mms working fine.

Step 5: install packages for mysql-mmm.
Yum install gcc –y
Yum install gcc* -y
Yum install perl* -y
Yum install bison –y
Yum install ncurses-devel -y
Yum install make –y
 Db1,db2 and db3 install some perl packages.
#perl –MCPAN –e shell
cpan> install Algorithm::Diff
cpan> install DBI
cpan>install Log::Dispatch
cpan> install Log::Log4perl
cpan> install Mail::Send
cpan> install Net::ARP
cpan> install Proc::Daemon
cpan> install Time::HiRes
cpan>install DBD::mysql
cpan>install File::stat
cpan>install File:basename
Monitor server(192.168.1.193) install below per packages
# perl -MCPAN -e shell
cpan> install Algorithm::Diff
cpan> install Class::Singleton
cpan> install Log::Dispatch
cpan> install Log::Log4perl
cpan> install Mail::Send
cpan> install Proc::Daemon
cpan> install Thread::Queue
cpan> install Time::HiRes
cpan> install DBI
cpan>install DBD::MySQL

Step 6: install mysql-mmm-2.2.1
cd /usr/local/
wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
mv :mmm2:mysql-mmm-2.2.1.tar.gz mysql-mmm-2.2.1.tar.gz
tar -zxvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make;make install

mysql-mmm location
/usr/lib/perl5/vendor_perl/5.8.8/MMM                 Perl packages using for MMM
/usr/lib/mysql-mmm                                   MMM script component
/usr/sbin                                            MMM command path
/var/log/mysql-mmm                                   MMM log path
/etc/mysql-mmm                                       MMM configure file path
/etc/init.d/                                         Agent and monitor startup script.
Create a mysql user for mysql-mmm on db1, db2 and db3.
MySQL>grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.200' identified by '123123';
MySQL>grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.144' identified by '123123';
MySQL>grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.153' identified by '123123';
MySQL>grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.193' identified by '123123';
Configure mysql_agent.cnf
Db1:
[root@centos mysql-mmm]# cat  mmm_agent.conf
include mmm_common.conf
this db1
[root@centos mysql-mmm]#
Db2:
[root@CENTOS2 mysql-mmm]# cat  mmm_agent.conf
include mmm_common.conf
this db2
[root@CENTOS2 mysql-mmm]#
Db3:
[root@CENTOS3 mysql-mmm]# cat mmm_agent.conf
include mmm_common.conf
this db3
[root@CENTOS3 mysql-mmm]#
Configure /etc/mysql-mmm/mmm_common.cnf on db1 and copy from db1 to db2, db3 and monitor server (192.168.1.193)

Configure mmm_mon.conf on monitor server (192.168.1.193)

Start mysql-mmm agent on db1,db2 and db3
[root@centos mysql-mmm]# service mysql-mmm-agent start;
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
Start mysql-mmm-monitor on monitor server 192.168.1.193
[root@CENTOS4 mysql-mmm]# service mysql-mmm-monitor start;
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok
[root@CENTOS4 mysql-mmm]# mmm_control show;
  db1(192.168.1.200) master/ONLINE. Roles: reader(192.168.1.156), writer(192.168.1.151)
  db2(192.168.1.144) master/ONLINE. Roles: reader(192.168.1.155)
  db3(192.168.1.153) slave/ONLINE. Roles: reader(192.168.1.157)
[root@CENTOS4 mysql-mmm]# mmm_control checks all
db2  ping         [last change: 2013/09/19 15:08:17]  OK
db2  mysql        [last change: 2013/09/19 15:08:17]  OK
db2  rep_threads  [last change: 2013/09/19 15:08:17]  OK
db2  rep_backlog  [last change: 2013/09/19 15:08:17]  OK: Backlog is null
db3  ping         [last change: 2013/09/19 15:08:17]  OK
db3  mysql        [last change: 2013/09/19 15:08:17]  OK
db3  rep_threads  [last change: 2013/09/19 15:08:17]  OK
db3  rep_backlog  [last change: 2013/09/19 15:08:17]  OK: Backlog is null
db1  ping         [last change: 2013/09/19 15:08:17]  OK
db1  mysql        [last change: 2013/09/19 15:08:17]  OK
db1  rep_threads  [last change: 2013/09/19 15:08:17]  OK
db1  rep_backlog  [last change: 2013/09/19 15:08:17]  OK: Backlog is null
Set read_only=on on db2 and db3
set global read_only=on;


Use another client try to  connect mysql with those 4 VIPs.


Step 7: MySQL-mmm testing
[root@CENTOS4 mysql-mmm]# mmm_control show
  db1(192.168.1.200) master/ONLINE. Roles: reader(192.168.1.156), writer(192.168.1.151)
  db2(192.168.1.144) master/ONLINE. Roles: reader(192.168.1.155)
  db3(192.168.1.153) slave/ONLINE. Roles: reader(192.168.1.157)
Stop mysql service on db1
  [root@CENTOS4 mysql-mmm]# mmm_control show
  db1(192.168.1.200) master/HARD_OFFLINE. Roles:
  db2(192.168.1.144) master/ONLINE. Roles: reader(192.168.1.155), writer(192.168.1.151)
  db3(192.168.1.153) slave/ONLINE. Roles: reader(192.168.1.156), reader(192.168.1.157)
Connect with VIP and insert a record
 
Start mysql service on db1 and show mysql-mmm status.
  [root@CENTOS4 mysql-mmm]# mmm_control show
  db1(192.168.1.200) master/AWAITING_RECOVERY. Roles:
  db2(192.168.1.144) master/ONLINE. Roles: reader(192.168.1.155), writer(192.168.1.151)
  db3(192.168.1.153) slave/ONLINE. Roles: reader(192.168.1.156), reader(192.168.1.157)
db1 status awaiting_recovery, it’s because during the db1 stopping, we insert a new record to table t.
try mmm_control set_online db1
[root@CENTOS4 mysql-mmm]# mmm_control set_online db1
OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@CENTOS4 mysql-mmm]# mmm_control show
  db1(192.168.1.200) master/ONLINE. Roles: reader(192.168.1.156)
  db2(192.168.1.144) master/ONLINE. Roles: reader(192.168.1.155), writer(192.168.1.151)
  db3(192.168.1.153) slave/ONLINE. Roles: reader(192.168.1.157)

---------------------------------The End------------------------------------------------
 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)