"Pure dry" amoeba realizes MySQL master-slave synchronization and read/write separation

"Pure dry" amoeba realize MySQL master-slave synchronization and read-write separation one, introduction

1. Amoeba Introduction

Amoeba (amoeba) project, the Open source framework launched a amoeba for MySQL software in 2008. This software is dedicated to MySQL's distributed database front-end agent layer, which mainly serves as the SQL routing function when the application layer accesses MySQL, focusing on the development of the distributed database Proxy layer. is located between the client and DB Server (s) and is transparent to clients. With load balancing, high availability, SQL filtering, read and write separation, routable related to the target database, multiple database merge results can be requested concurrently. With amoeba you can perform high-availability, load-balancing, and data slicing functions for multiple data sources, currently amoeba is used on many enterprise production lines

2. Advantages and disadvantages of amoeba

Advantages:

(1) Cost reduction, easy to use

(2) Improve overall system availability

(3) Easy to expand processing capacity and system size

(4) can directly achieve read and write separation and load balancing effect, without modifying the code

Disadvantages:

(1) Transaction and stored procedures are not supported

(2) Temporarily does not support the sub-database sub-table, amoeba currently only do sub-db instance

(3) Not suitable for amoeba data from the scene or query for large data queries is not appropriate (such as a request to return more than 10w or more data)

3. What is read-write separation

Read-Write separation (Read/write splitting), the basic principle is to let the main database processing transaction increment, change, delete operation (INSERT, UPDATE, delete), and from the database processing select query operation.

Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

Second, the experimental topological framework

Client Linux6-1 ip:192.168.234.186

Master MySQL centos7-1 ip:192.168.234.174

From MySQL01 centos7-2 ip:192.168.234.177

From MySQL02 centos7-5 ip:192.168.234.184

Amoeba Server centos7-3 ip:192.168.234.181

Read and write separation experiment Configuration source code compilation installation MySQL5.5

1. Compiling a MySQL5.5 dependent environment package

Yum install GCC, gcc-c++, make, CMake
Ncurses-devel, Bison, libaio-devel-y

2. Create a MySQL user

Groupadd-r MySQL
Useradd-g mysql-r-d/mydata/data MySQL

3. Source compiled MySQL

Tar zxvf mysql-5.5.24.tar.gz-c/opt
Cd/opt/mysql-5.5.24.tar.gz
Cmake-dcmake_install_prefix=/usr/local/mysql
-dmysql_unix_addr=/home/mysql/mysql.sock
-ddefault_charset=utf8
-ddefault_collation=utf8_general_ci
-dwith_extra_charsets=all
-dwith_myisam_storage_engine=1
-dwith_innobase_storage_engine=1
-dwith_memory_storage_engine=1
-dwith_readline=1
-denabled_local_infile=1
-dmysql_datadir=/home/mysql
-dmysql_user=mysql
-dmysql_tcp_port=3306
Make && make install

4. Change MySQL's home directory to MySQL Group

Chown-r Mysql.mysql/usr/local/mysql

5. Configure the MySQL environment variable

echo "Export path=\ $PATH:/usr/local/mysql/bin" >/etc/profile.d/mysql.sh

source/etc/profile.d/mysql.sh

6. Copy MySQL startup scripts and service profiles to/etc/my.cnf and/etc/init.d/mysqld respectively

CP SUPPORT-FILES/MY-MEDIUM.CNF/ETC/MY.CNF

CP Support-files/mysql.server/etc/init.d/mysqld

7. Grant the appropriate permissions and join the boot-up entry

chmod 755/etc/init.d/mysqld
Chkconfig--add/etc/init.d/mysqld
Chkconfig mysqld--level

8. Initializing the database

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--ldata=/var/lib/mysql \
--basedir=/usr/local/mysql \
--datadir=/home/mysql

9. Create a soft link

Ln-s/var/lib/mysql/mysql.sock/home/mysql/mysql.sock

10. Configure the startup script

Vim/etc/init.d/mysqld
Basedir=/usr/local/mysql
Datadir=/home/mysql

11. Start the MySQL service

Systemctl Start Mysqld.service

12. Set the password for the MySQL user

mysqladmin-u root password ' abc123 '

MySQL Primary server (master centos7-1) configuration

[[email protected] ~]# vim /etc/my.cnf...省略log-bin=master-bin          //这里原来的mysql修改为masterlog-slave-updates=true      //添加，表示从服务器更新二进制日志...省略server-id       = 11   

Restart the MySQL service and give access from the server

[[email protected] ~]# systemctl restart mysqld.service    //重启MySQL服务[[email protected] ~]# mysql -u root -pEnter password:mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.234.%‘ IDENTIFIED BY ‘123456‘;        //给予从服务器访问权限Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;    //刷新生成二进制文件Query OK, 0 rows affected (0.00 sec)mysql> show master status;     //查看pos偏移量  +-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000001 |      477 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.01 sec)

Configuration from server (slave centos7-2)

[[email protected] ~]# systemctl stop firewalld.service localhost ~]# setenforce 0[[email protected] ~]# yum install ntpdate -y[[email protected] ~]# systemctl start ntpd.service [[email protected] ~]# vim /etc/my.cnf...省略server-id       = 22   relay-log=relay-log-bin    //从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index   //定义relay-log的位置和名称

Restart the MySQL service and specify the object server to synchronize from the server

[[email protected] ~]# mysql-u root-penter password:mysql> change master to master_host= ' 192.168.234.174 ',    Master_user= ' Myslave ', master_password= ' 123456 ', master_log_file= ' master-bin.000001 ', master_log_pos=477;    Set the synchronization Object Server and user password query OK, 0 rows affected (0.01 sec) mysql> start slave;     Turn on master-slave sync query OK, 0 rows affected (0.01 sec) mysql> Show slave status\g; View slave status *************************** 1.                  Row *************************** slave_io_state:reconnecting After a failed master event read master_host:192.168.234.174 Master_user:myslave master_port:3306 Co Nnect_retry:60 master_log_file:master-bin.000001 read_master_log_pos:564 Relay_log_             file:localhost-relay-bin.001259 relay_log_pos:254 relay_master_log_file:master-bin.000001 Slave_io_running:yes//Must be yes here Slave_sql_running:yes//here must be yEs Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Tab le:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 Last_ error:skip_counter:0 exec_master_log_pos:564 relay_log_space:561 U           Ntil_condition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no                Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher: Master_ssl_key:seconds_behind_master:1360master_ssl_verify_server_cert:no Last_io_err no:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_serv er_ids:master_server_id:111 row in Set (0.00 sec)

Configuration from server 2 (Slave2 centos7-5) and above

Verifying master-Slave synchronization

Add a database school on the primary server to see whether two slave servers can synchronize to get to the school database

Primary server Add Database School

mysql> show databases;  +--------------------+| Database           |+--------------------+| information_schema || #mysql50#.mozilla  || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.01 sec)mysql> create database school;     //添加school数据库Query OK, 1 row affected (0.00 sec)

To see if a synchronization gets to the school database on both slave servers

Slave1 centos7-2

mysql> show databases;    //查看有哪些数据库+--------------------+| Database           |+--------------------+| information_schema || #mysql50#.mozilla  || mysql              || performance_schema || school             |      //这里就同步获取到school数据库| test               |+--------------------+6 rows in set (0.01 sec）

Slave2 centos7-5

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || #mysql50#.mozilla  || mysql              || performance_schema || school             |     //这里slave2也同步获取到一个school的数据库| test               |+--------------------+6 rows in set (0.00 sec)

If both servers can get to the school database, the master-slave synchronization is no longer a problem, then the next step is to deploy read-write separation on the amoeba server (centos7-3).

Deployment settings for the Amoeba server (centos7-3)

Because Amoeba is developed by Java programs, the JDK environment is first installed on the amoeba server

[[email protected] ~]# systemctl stop firewalld.service [[email protected] ~]# setenforce 0[[email protected] mysql]# cp jdk-6u14-linux-x64.bin /usr/local/[[email protected] mysql]# ./jdk-6u14-linux-x64.bin 。。。省略Do you agree to the above license terms? [yes or no]   y    //前面一直按Enter直到这里输入y（yes），然后就等jdk环境安装完成

Then install the Amoeba service

[[email protected] local]# mv jdk1.6.0_14/ jdk1.6[[email protected] local]# vim /etc/profile    //添加amoeba的环境变量   //G到行尾添加export 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[[email protected] local]# source /etc/profile    //刷星使环境变量立即生效[[email protected] local]# mkdir /usr/local/amoeba    //创建一个amoeba的工作目录[[email protected] local]# tar zxvf /abc/mysql/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/     //解压安装amoeba至工作目录[[email protected] local]# chmod -R 755 amoeba/     //递归给予amoeba及子文件执行权限[[email protected] local]# /usr/local/amoeba/bin/amoeba   //检查amoeba是否安装成功amoeba start|stop     //看到这样的提示就说明amoeba服务安装完成

Then add permissions on the three MySQL servers open to amoeba access, the user uses test, the password is 123123

Grant all on, to [e - Mail protected] ' 192.168.234.% ' identified by ' 123123 ';

Modify the configuration file for the amoeba server

[[email protected] amoeba]# vim conf/amoeba.xml ...    Omit <!---->//delete here, means to open the <property name= "user" >amoeba</proper Ty>//Here to modify the user for the previously set amoeba <!---->/delete here, which means to open the <property Name= "Password" &GT;123456&LT;/PR operty>//Add password for previously set password 123456 33 ...                 Omit <property name= "Defaultpool" >master</property>//Modify here to use Master master server by default 116 117 118 <property name= "Writepool" >master</property>//Allow primary server to write 119 <propert       Y name= "Readpool" >slaves</property>//allow read from server [[email protected] amoeba]# vim conf/dbservers.xml Modify the database configuration file <!--mysql user--<property name= "user" and Gt;test</property>//Modified here to test, that is, using the test user to read MySQL data <!--mysql Pseeword// Here is a <!--noteMeaning, so to delete <property name= "password" >123123</property>//password for the password set previously 123123 --//delete <dbserver name= "master" parent= "Abstractserver" >//Modify original server1 to mast                         ER <factoryConfig> $ <!--mysql IP--47 <property name= "ipAddress" >192.168.234.174</property>//The IP address of the primary server is changed to </factorycon fig> </dbServer> <dbserver name= "slave1" parent= "Abstractserver" >//server2                         Change to slave1 <factoryConfig> <!--mysql IP--54 <property name= "ipAddress" >192.168.234.177</property>//slave1 (ie centos7-2) IP address 55 & lt;/factoryconfig> </dbServer> #添加 <dbserver name= "slave2" parent= "Abstractserver" &G     T               59  <factoryConfig> <!--mysql IP--<property name= "I         Paddress ">192.168.234.184</property>//slave2 (i.e. centos7-5) IP address $ </factoryConfig> 64 <dbserver name= "Slaves" virtual= "true" >//server pool name slaves <poolconfig class= "Com.meidusa . Amoeba.server.MultipleServerP Ool "> <!--Load balancing Strategy:1=roundrobin, 2=we                         IGH tbased, 3=ha--> <property name= "LoadBalance" >1</property> 68 69 <!--separated by Commas,such as:server1,server2,se rver1---&LT;PR         Operty name= "Poolnames" >slave1,slave2</property>//name of the two servers in the server pool. </poolConfig> 72 </dbServer>

Turn on the amoeba service and check that the port is open

[[email protected] amoeba]#/usr/local/amoeba/bin/amoeba start &//Open amoeba server and put into background [2] 3996[[email  protected] amoeba]# log4j:warn log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2018-07-08 15:02:45,493 INFO context. Mysqlruntimecontext-amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:warn IP access config load Co mpleted from file:/usr/local/amoeba/conf/access_list.conf2018-07-08 15:02:46,450 INFO net.     Serverableconnectionmanager-amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066. Here you can see the start listening port 80662018-07-08 15:02:46,476 INFO net. Serverableconnectionmanager-amoeba Monitor Server listening on/127.0.0.1:38438.^c[[email protected] amoeba]# Netstat-ntap |             grep java//view Java program TCP6 0 0::: 8066:::* LISTEN 3996/java           And then you can see that there's a 8066 port already open. TCP6 0 0 127.0.0.1:38438:::* LISTEN 3996/java   Tcp6    0 0 192.168.234.181:37510 192.168.234.174:3306 established 3996/java tcp6 0 0 192.168.2 34.181:38208 192.168.234.177:3306 established 3996/java tcp6 0 0 192.168.234.181:47642 192.168  .234.184:3306 established 3996/java

Then the deployment is complete and the rest is to verify that read-write separation is possible.

Verify

Here is a brief explanation of the verification process: first create a table Zyc in the school library of the primary server, and then two shut down slave from the server, insert data 1 from the ZYC table in the primary server, and the Zyc table from server 1 (note: The synchronization is not closed when the table is created. So you insert data 2 from the server to generate a Zyc table synchronously, insert data 3 from the ZYC table in server 2, and then use the client linux6-1 to access the amoeba server, and you will see that the displayed data switches between the two 2 and 3 data from the server and does not display data written by the primary server 1 , write data on the client linux6-1, and then only in the main MySQL server can be queried, two from the server but not to see, and finally open two slave server synchronization, the client to view the data, you will see the data display as 214, 314 switch.

Primary server Add Table mysql> CREATE TABLE ZYC (ID int (5), name varchar (10)); Query OK, 0 rows affected (0.03 sec) mysql> show tables;+------------------+| Tables_in_school |+------------------+| Zyc |+------------------+1 row in Set (0.00 sec)//Two slave server will synchronize get table information # from server 1 centos7-2mysql> use School;data Base changedmysql> show tables;+------------------+| Tables_in_school |+------------------+| Zyc |+------------------+1 row in Set (0.00 sec) mysql> stop slave; Query OK, 0 rows Affected (0.00 sec) #从服务器2 centos7-5mysql> use school;database changedmysql> show tables;+------- -----------+| Tables_in_school |+------------------+| Zyc |+------------------+1 row in Set (0.01 sec) mysql> stop slave; Query OK, 0 rows affected (0.01 sec) #主服务器插入数据1mysql > INSERT into ZYC values (1, ' Zhangsan '); Query OK, 1 row affected (0.01 sec) mysql> select * from zyc;+------+----------+| ID |    Name |+------+----------+| 1 | Zhangsan |+------+----------+1 row In Set (0.00 sec) #从服务器1插入数据2mysql > INSERT INTO ZYC values (2, ' Lisi '); Query OK, 1 row affected (0.01 sec) mysql> select * FROM zyc;+------+------+| ID |    Name |+------+------+| 2 | Lisi |+------+------+1 row in Set (0.01 sec) #从服务器2插入数据3mysql > inserts into ZYC values (3, ' Wangwu '); Query OK, 1 row affected (0.01 sec) mysql> select * from zyc;+------+--------+| ID |    Name |+------+--------+| 3 | Wangwu |+------+--------+1 row in Set (0.00 sec) #此时使用客户机去访问amoeba服务器会看到数据只显示两个从服务器的mysql > select * FROM zyc;+------+- -------+| ID |    Name |+------+--------+| 2 | Lisi |+------+--------+1 row in Set (0.00 sec) mysql> SELECT * from zyc;+------+--------+| ID |    Name |+------+--------+| 3 | Wangwu |+------+--------+1 row in Set (0.00 sec) #然后打开两个从服务器的同步, insert data in client 4mysql> insert into ZYC values (4, ' Zhaoliu '); Query OK, 1 row affected (0.01 sec) SELECT * FROM zyc;+------+----------+| ID |    Name |+------+----------+| 2 |    Lisi |+------+----------+| 1 | ZhaNgsan |+------+----------+| 4 | Zhaoliu |+------+----------+select * from zyc;+------+----------+| ID |    Name |+------+----------+| 3 |    Wangwu |+------+----------+| 1 |    Zhangsan |+------+----------+| 4 | Zhaoliu |+------+----------+

The final experiment concludes that the Primary server is responsible for writing the data, the data is read from the server, and the two slave servers are synchronized with the primary server, while the two slave servers are not synchronized

If you get the results from your experiment, then congratulations on your success in reading and writing the separate experiment!

This realizes the MySQL read-write separation and master-slave synchronization.

"Pure dry" amoeba realizes MySQL master-slave synchronization and read/write separation