Mmm+amoeba build MySQL high-availability load Balancing cluster

MySQL master-slave replication and MySQL read-write separation of the two have a close connection, first of all to deploy master-slave replication, only the master-slave replication is completed, the basis for the data read and write separation. MySQL read-write separation is only written on the primary server, only read from the server. The rationale is to have the primary database process transactional queries and to process select queries from the server. Database replication is used to synchronize changes that are caused by transactional queries to the from database in the cluster.
Amoeba primarily serves as an SQL route for application-level access to MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing-related to the target database, and multiple databases that can be requested concurrently. The amoeba is able to accomplish the functions of high availability, load balancing and data slicing of the data source.
MMM (MySQL master replication Manager) is a set of scripting programs that support dual-master failover and dual-master daily management. MMM is developed using the Perl language, which is mainly used to monitor and manage MySQL Master-master (dual master) replication, although it is called dual master replication, but only one master is allowed to write at the same time in the business, and the other master provides a partial read service to speed up the preheating of the alternate host during the main master switchover. Can say mmm this set of scripts to implement the function of failover, on the other hand, its internal additional tool script can also achieve multiple slave read load balancing.

Amooeba there is a single point of failure in the actual use of the problem, so after learning mmmm, I tried to use the Mmm+amoeba architecture to avoid a single point of failure of the main library, and then I will be on the virtual machine for you to simulate the building process of the service

Project Environment

• Using six servers to simulate the building, the topology diagram is as follows:
  
• Virtual Machine Planning

Host	Operating System	IP Address	VIP	main software
Master1	CentOS-7	192.168.100.101	192.168.100.200	mysql-5.7, Mysql-mmm
Master1	CentOS-7	192.168.100.102	192.168.100.200	mysql-5.7, Mysql-mmm
Slave1	CentOS-7	192.168.100.111	192.168.100.166	mysql-5.7, Mysql-mmm
Slave2	CentOS-7	192.168.100.112	192.168.100.188	mysql-5.7, Mysql-mmm
Amoeba	CentOS-7	192.168.100.130		JDK, Amoeba, MySQL
MMM	CentOS-7	192.168.100.120		Mysql-mmm

Project implementation install MySQL

In this experiment, I used the MySQL version of MySQL5.7, there is about mysql5.7 installation method, please refer to Centos7 in the compilation installation of MySQL (mysql-5.7)

Building Mysql-mmm Dual-master dual-slave architecture

Before I wrote a mariadb-mmm high-availability cluster, mariadb and MySQL belong to the same vein. MARIADB is fully compatible with MySQL, including API and command line, so that it can easily become a substitute for MySQL, so about the way to build MMM is the same, please refer to the previous article.
After I manually test, methods are common, about building mysql-mmm architecture and mariadb-mmm The only difference is/ETC/MY.CNF configuration file modification. [Mysqld] is modified to the following content.

[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1binlog-ignore-db=mysql,information_schemalog_bin=mysql_binlog_slave_updates=truesync_binlog=1auto_increment_increment=2auto_increment_offset=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

Build MySQL read-write separation

MySQL master-slave replication in the construction of the MMM structure has been completed, at this time we only need to use amoeba to build read and write separation.

Set up time synchronization set up time synchronization environment, build time synchronization server on Master1

yum -y install ntp

Configuring the NTP server

Modify the NTP configuration file to add the configuration.

vim /etc/ntp.confserver 127.127.100.0      //本地是时钟源，本地地址为192.168.100.101，此处地址则也为100段fudge 127.127.100.0 stratum 8   //stratum 8 时间层级为8(限制在15内)

Shutting down the firewall

systemctl disable firewalld.service systemctl stop firewalld.servicesetenforce 0

Start the service

systemctl start ntpd

Time synchronization on Master2, Slave1, slave2

yum -y install ntp ntpdate            //若是使用centos7系统，一般都已默认安装systemctl stop firewalld.servicesetenforce 0systemctl start ntpd                  //三台服务器的创建方式一致/usr/sbin/ntpdate 192.168.100.101     //与主服务器进行时间同步，IP地址为主服务器地址

Build amoeba server to shut down firewall and SELinux

systemctl stop firewalld.servicesetenforce 0

Installation Environment (jdk1.6)

cp /opt/jdk-6u14-linux-x64.bin /usr/local/cd /usr/local./jdk-6u14-linux-x64.binmv jdk1.6.0_14/ /usr/local/jdk1.6

Setting environment variables

#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       //刷新使环境变量立即生效

Installing Amoeba

tar zxf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba        chmod -R 755 /usr/local/amoeba/        //解压即完成安装/usr/local/amoeba/bin/amoeba           //测试amoeba是否安装成功

Authorized for Amoeba on MySQL

Here the authorized account and password is convenient amoeba connection background database Set the password, just authorize a master two from, the two are copied from each other, the data will be fully synchronized. User name and password customization.

mysql>grant all on *.* to [email protected]‘192.168.100.%‘ identified by ‘123123‘;

Configuring Amoeba Server Modification/usr/local/amoeba/conf/amoeba.xml

<property name="user">amoeba</property><property name="password">123456</property>     //访问amoeba的账户和密码<property name="defaultPool">master</property><property name="writePool">master</property><property name="readPool">slaves</property>

Modify/usr/local/amoeba/conf/dbservers.xml

• Modify the user name and password of the amoeba Access database, because the default user is test, when you do not use the default account, you need to comment on the default account, or change to "MySQL"

  <!-- mysql schema --><property name="schema">mysql</property>   //修改或者直接删除<!-- mysql user --><property name="user">chen</property><property name="password">123123</property>

• Setting the database server address

  <dbserver name= "Master" parent= "Abstractserver" >//Database name master<factoryconfig><!--mysql IP-->< Property Name= "IpAddress" >192.168.100.200</property>//Here is the VIP address, convenient for failover </factoryconfig></ Dbserver><dbserver name= "slave1" parent= "abstractserver" ><factoryConfig><!--mysql IP-->< Property Name= "IpAddress" >192.168.100.166</property>//slave1, address for VIP address </factoryconfig></ Dbserver><dbserver name= "Slave2" parent= "abstractserver" ><factoryConfig><!--mysql IP-->< Property Name= "IpAddress" >192.168.100.188</property>//slave2, address for VIP address </factoryconfig></ Dbserver><dbserver name= "Slaves" virtual= "true" >//Read database pool <poolconfig class= " Com.meidusa.amoeba.server.MultipleServerPool "><!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3 = Ha--><property name= "LoadBalance" >1</property>//1 Delegate Polling <!--separated by Commas,such As:server1,Server2,server1--><property name= "poolnames" >slave1,slave2</property>//Including slave1,slave2 

  Start Amoeba

  /usr/local/amoeba/bin/amoeba start&netstat -anpt | grep java

  Testing the availability of the MySQL client installation

  yum install -y mysql

  Login test test for normal access

  mysql -u amoeba -p123456 -h 192.168.100.130 -P8066  //IP地址为amoeba服务器地址show databases;

  Test data is synchronized

  use pythoncreate table java (id int,name varchar(10),sorce decimal(5,2));  //在python库中创建java表

  Log on to each server, the Java tables in the Python library exist, indicating that the dual-master synchronization is successful
  

  Test read/write separation

  insert into java (id,name,sorce) values (1,‘master‘,1);//在master1表中插入一条数据insert into java (id,name,sorce) values (2,‘slave1‘,2);//在slave1表中插入一条数据insert into java (id,name,sorce) values (3,‘slave2‘,3);//在slave2表中插入一条数据mysql -u amoeba -p123456 -h 192.168.100.130 -P8066use pythonselect * from java;     //登陆amoeba查看数据库中数据

  Test single point of failure

  systemctl stop mysqld    //关闭master1

  VIP migration occurs
  

Mmm+amoeba build MySQL high-availability load Balancing cluster