Mysql+amoeba+mysqlmmm High-availability clusters

Source: Internet
Author: User
Tags chmod db2 switches aliyun

One, mysql-mmm (master-master Replication Manager fo MySQL) MMM overview
    • Dual-master failover and routine management procedures
    • Consists of multiple MySQL master servers and multiple MySQL slave servers
    • MMM is developed using the Perl language and is primarily used to monitor and manage MySQL Master-master (dual master) replication
    • Its internal additional tool scripts can also implement read load balancing for multiple slave.

Note:

    • Only one master is allowed to write at the same time, and the additional master provides a partial read service

    • Not suitable for situations where data consistency is required (replaceable product: heartbeat+drbd+mysql high-availability scenario)
MMM composition
    • Mmm_mond: monitoring process, responsible for all monitoring, decision and processing of all nodes
    • MMM_AGENTD: Agent process running on each MySQL database, complete monitoring of local state and communication on the monitor side
    • Mmm_control: A script that provides commands for the Mmm_mond process
Second, the case

Lab Environment:
Six Centos6, two master MySQL (Master01, Master02), two from MySQL (SLAVE01, SLAVE02), one monitor (Mmm_mond), one read/write scheduler (Amoeba)

Deploy primary master (Master01 and MASTER02) replication first

Master01

1. Preparatory work
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.10    NETMASK=255.255.255.0
vim /etc/sysconfig/network-scripts/ifcfg-eth1    DEVICE=eth1    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=dhcp
vim /etc/hosts    192.168.1.10    db1    192.168.1.20    db2    192.168.1.30    db3    192.168.1.40    db4
vim /etc/sysconfig/network    HOSTNAME=db1
reboot
2.YUM source configuration and installation of MMM
rm -rf /etc/yum.repos.d/*
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
yum -y install epel-release
yum -y install mysql-mmm* mysql mysql-server mysql-devel
3.MySQL Configuration
/etc/init.d/mysqld start && chkconfig --level 35 mysqld on
mysqladmin -uroot password "123"
cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
vim /etc/my.cnf    [mysqld]    50 log-slave-updates
/etc/init.d/mysqld restart
4. Authorization and Master synchronization
mysql -u root -pmysql> grant replication slave on *.* to ‘slave‘@‘192.168.1.%‘ identified by ‘123‘;mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      647 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql> change master to master_host=‘192.168.1.20‘,master_user=‘slave‘,master_password=‘123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=481;//所跟IP、log、pos等信息都为第二台主的信息mysql> start slave;mysql> show slave status\G;//查看同步状态,I/0和SQL线程状态为yes则正确

Master02

1. Preparatory work
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.20    NETMASK=255.255.255.0
vim /etc/sysconfig/network-scripts/ifcfg-eth1    DEVICE=eth1    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=dhcp
vim /etc/hosts    192.168.1.10    db1    192.168.1.20    db2    192.168.1.30    db3    192.168.1.40    db4
vim /etc/sysconfig/networkHOSTNAME=db2
reboot
2.YUM source configuration and installation of MMM
rm -rf /etc/yum.repos.d/*
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
yum -y install epel-release
yum -y install mysql-mmm* mysql mysql-server mysql-devel
3.MySQL Configuration
/etc/init.d/mysqld start && chkconfig --level 35 mysqld on
mysqladmin -uroot password "123"
cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
vim /etc/my.cnf    [mysqld]    50 log-slave-updates    58 server-id = 2
/etc/init.d/mysqld restart
4. Authorization and Master synchronization
mysql -u root -pmysql> grant replication slave on *.* to ‘slave‘@‘192.168.1.%‘ identified by ‘123‘;mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      481 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql> change master to master_host=‘192.168.1.10‘,master_user=‘slave‘,master_password=‘123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=647;//所跟IP、log、pos等信息都为第一台主的信息mysql> start slave;mysql> show slave status\G;
Deploy two more master-slave replications (M1 and s1,m2 and S2)

Slave01

1. Preparatory work
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.30    NETMASK=255.255.255.0
vim /etc/sysconfig/network-scripts/ifcfg-eth1    DEVICE=eth1    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=dhcp
vim /etc/hosts    192.168.1.10    db1    192.168.1.20    db2    192.168.1.30    db3    192.168.1.40    db4
vim /etc/sysconfig/network    HOSTNAME=db3
reboot
2.YUM source configuration and installation of MMM
rm -rf /etc/yum.repos.d/*
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
yum -y install epel-release
yum -y install mysql-mmm* mysql mysql-server mysql-devel
3.MySQL Configuration
/etc/init.d/mysqld start && chkconfig --level 35 mysqld on
mysqladmin -uroot password "123"
cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
vim /etc/my.cnf    [mysqld]    50 relay-log=relay-log-bin    51 relay-log-index=slave-relay-bin.index    59 server-id = 3
/etc/init.d/mysqld restart
4. Authorization and master-Slave synchronization (M1,S1)
mysql -u root -pmysql> change master to master_host=‘192.168.1.10‘,master_user=‘slave‘,master_password=‘123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=647;//所跟IP、log、pos等信息都为第一台主的信息mysql> start slave;mysql> show slave status\G;

Slave02

1. Preparatory work
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.40    NETMASK=255.255.255.0
vim /etc/sysconfig/network-scripts/ifcfg-eth1    DEVICE=eth1    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=dhcp
vim /etc/hosts    192.168.1.10    db1    192.168.1.20    db2    192.168.1.30    db3    192.168.1.40    db4
2.YUM source configuration and installation of MMM
rm -rf /etc/yum.repos.d/*
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
yum -y install epel-release
yum -y install mysql-mmm* mysql mysql-server mysql-devel
3.MySQL Configuration
/etc/init.d/mysqld start && chkconfig --level 35 mysqld on
mysqladmin -uroot password "123"
cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
vim /etc/my.cnf    [mysqld]    50 relay-log=relay-log-bin    51 relay-log-index=slave-relay-bin.index    59 server-id = 4
/etc/init.d/mysqld restart
4. Authorization and master-Slave synchronization (M2,S2)
mysql -u root -pmysql> change master to master_host=‘192.168.1.20‘,master_user=‘slave‘,master_password=‘123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=647;//所跟IP、log、pos等信息都为第二台主的信息mysql> start slave;mysql> show slave status\G;
Configuring High-Availability Mysql-mmm

Master01

1. Create a new authorized user
mysql> grant super,replication client,process on *.* to ‘mmm_agent‘@‘192.168.1.%‘ identified by ‘123‘;//主服务器授权,从服务器自动同步mysql> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.1.%‘ identified by ‘123‘;           //主服务器授权,从服务器自动同步mysql> grant all on *.* to ‘test‘@‘192.168.1.%‘ identified by ‘123‘;                    //新建测试用户
2. Configure Mmm_common
vim/etc/mysql-mmm/mmm_common.conf active_master_role writer 
3. Configure Mmm_agent
vim /etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db1
4. Copy files to the remaining hosts
scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/scp /etc/mysql-mmm/mmm_common.conf [email protected]:/etc/mysql-mmm/
/etc/init.d/mysql-mmm-agent restart && chkconfig --level 35 mysql-mmm-agent on

Master02

Configure Mmm_agent
vim /etc/mysql-mmm/mmm_agent.confthis db2
/etc/init.d/mysql-mmm-agent restart && chkconfig --level 35 mysql-mmm-agent on

Slave01

Configure Mmm_agent
vim /etc/mysql-mmm/mmm_agent.confthis db3
/etc/init.d/mysql-mmm-agent restart && chkconfig --level 35 mysql-mmm-agent on

Slave02

Configure Mmm_agent
vim /etc/mysql-mmm/mmm_agent.confthis db4
/etc/init.d/mysql-mmm-agent restart && chkconfig --level 35 mysql-mmm-agent on
Monitoring Configuration (Mmm_mon) 1. Preparatory work
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.50    NETMASK=255.255.255.0
vim /etc/sysconfig/network-scripts/ifcfg-eth1    DEVICE=eth1    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=dhcp
vim /etc/hosts    192.168.1.10    db1    192.168.1.20    db2    192.168.1.30    db3    192.168.1.40    db4
reboot
2.YUM source configuration and installation of MMM
rm -rf /etc/yum.repos.d/*
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
yum -y install epel-release
yum -y install mysql-mmm* mysql
3. Configure Mmm_common
scp 192.168.1.10:/etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/
4. Configure Mmm_mon
vim /etc/mysql-mmm/mmm_mon.conf    include mmm_common.conf    <monitor>            ip                  127.0.0.1            ping_ips            192.168.1.10,192.168.1.20,192.168.1.30,192.168.1.40    </monitor>    
5. Start the service and verify
/etc/init.d/mysql-mmm-monitor restart && chkconfig --level 35 mysql-mmm-monitor on  //监控端启动
mmm_control show                                    //查看节点状态
mysql -u test -p -h 192.168.1.250

Error Resolution:

[[email protected] ~]# mysql -u test -p -h 192.168.1.250Enter password:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘192.168.1.254‘ (113)

Primary server:

grant super,replication client,process on *.* to ‘mmm_agent‘@‘db1‘ identified by ‘123‘;grant super,replication client,process on *.* to ‘mmm_agent‘@‘db2‘ identified by ‘123‘;grant super,replication client,process on *.* to ‘mmm_agent‘@‘db3‘ identified by ‘123‘;grant super,replication client,process on *.* to ‘mmm_agent‘@‘db4‘ identified by ‘123‘;

Log view:

tail -f /var/log/mysql-mmm/mmm_agentd.log                       //MySQL端的Agent日志
tail -f /var/log/mysql-mmm/mmm_mond.log                     //监控机端的Monitor日志
Deploy read-Write scheduler AMOEBA1. Environment Preparation
vim /etc/sysconfig/network-scripts/ifcfg-eth0    DEVICE=eth0    TYPE=Ethernet    ONBOOT=yes    NM_CONTROLLED=no    BOOTPROTO=static    IPADDR=192.168.1.254    NETMASK=255.255.255.0
2. Installing JDK and Amoeba
yum -y erase java-*
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
mv jdk1.6.0_14/ /usr/local/jdk1.6
vim /etc/profileexport JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin
source /etc/profile && java -version
mkdir /usr/local/amoeba
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
3. Create a new authorized user in the main MySQL database
mysql -u root -p    mysql> grant all on *.* to [email protected]‘192.168.1.%‘ identified by ‘123‘;
4. Edit the Amoeba configuration file
  vim/usr/local/amoeba/conf/amoeba.xml <property name= "user" >hehe</property>                 Set connection Amoeba user <property name= "password" >123</property>//Setup Connection Amoeba user 115 <property name= "Defaultpool" >slaves</property>116117 <property name= "WritePo Ol ">master</property> Note Delete <!----> Comments 118 <property name=" Readpool ">slaves< /property>//define read server pool  
Vim/usr/local/amoeba/conf/dbservers.xml <!--mysql user--26 & Lt;property name= "User" >haha</property>//settings for users connected to MySQL <property n Ame= "Password" >123</property>//Set password to connect MySQL Note Delete <!----> Comments <dbserver name= "M Aster "parent=" Abstractserver "> <factoryConfig> $ <!--mysql IP-                 --<property name= "ipAddress" >192.168.1.254</property>//definition of Write server IP 47 </factoryConfig> <dbserver </dbServer> name= "slave1" parent= "Abstractse                         RVer > <factoryConfig>!--mysql IP--52 <property name= "ipAddress" >192.168.1.30</property>//definition Read server IP </factoryc Onfig> </dbserver> <dbserver name= "slave2" parent= "Abstractserver" > <factoryConfig> <!--mysql IP--<property name= "IpAddress" >192.168.1.         40</property>//define read server IP </factoryConfig> </dbServer> 62 <dbserver name= "Slaves" virtual= "true" > <property name= "poolnames" >slave1,slave2 </property>//define the host for the input slaves read server pool.
amoeba start &
netstat -utpln | grep 8066
5. Connection test
client:mysql -u hehe -p -h 192.168.1.254 -P 8066

Mysql+amoeba+mysqlmmm High-availability clusters

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.