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 installationyum install -y mysql
Login test test for normal accessmysql -u amoeba -p123456 -h 192.168.100.130 -P8066 //IP地址为amoeba服务器地址show databases;
Test data is synchronizeduse 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 separationinsert 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 failuresystemctl stop mysqld //关闭master1
VIP migration occurs
Mmm+amoeba build MySQL high-availability load Balancing cluster