Shulou(Shulou.com)06/01 Report--

I. MySQL read-write separation

The master database handles transactional queries and select queries from the database. Database replication is used to synchronize changes caused by transactional queries to the slave database.

two。 There are two most common types of read-write separation:

1. Implementation based on program code

Classify routing according to select,insert process in code

Advantages: good performance, because it is implemented in code and no additional hardware is required

Disadvantages: it needs developers to implement, and the code changes are relatively large, so it is not suitable for large and complex applications.

two。 Implementation based on proxy layer

MySQL-Proxy: belongs to the MySQL open source project

Amoeba:Amoeba is a proxy which takes MySQL as the underlying data storage and provides MySQL protocol interface to the application. It responds to the application request centrally and sends the SQL request to a specific database for execution according to the rules set by the user in advance. Based on this, we can realize the requirements of load balancing, read-write separation, high availability and so on.

three。 Experimental environment

OS:CentOS 6.5 x64

Amoeba:192.168.0.128

Master:192.168.0.134

Slave:192.168.0.135

Client:192.168.0.137

three。 Configure master-slave replication

1.MySQL read-write separation is based on master-slave replication configuration. Master-slave replication is configured first, and then read-write separation is configured.

Master-slave copy blog link: http://guoxh.blog.51cto.com/10976315/1922643

two。 Add an authorized account for Amoeba access

Master and slave:

Mysql > grant all on *. * to 'proxy'@'192.168.0.128' identified by' 123456employees make query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)

IV. Amoeba installation configuration

1. Amoeba is developed based on JDK, so install JAVA environment first.

[root@amoeba ~] # chmod + x jdk-6u14-linux-x64.bin [root@amoeba ~] #. / jdk-6u14-linux-x64.bin [root@amoeba ~] # cat / etc/profile.d/java.sh export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/lib:$PATH:$HOME/ [root @ amoeba ~] # source / etc/profile.d/java.sh [root@amoeba] # java-versionjava version "1.6.0o14" Java (TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot (TM) 64-Bit Server VM (build 14.0-b16 Mixed mode) # JAVA environment configuration completed

two。 Install Amoeba

[root@amoeba ~] # mkdir / usr/local/amoeba [root@amoeba ~] # tar xf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba [root@amoeba ~] # ls / usr/local/amoeba/benchmark bin changelogs.txt conf lib LICENSE.txt README.html [root@amoeba ~] # cat / etc/profile.d/amoeba.sh export AMOEBA_HOME=/usr/local/amoeba/export PATH=$PATH:$AMOEBA_HOME/ [root @ amoeba ~] # source / etc/profile.d/amoeba.sh [root@amoeba ~] # / usr/local/amoeba/bin/amoebaamoeba start | stop # Amoeba installed successfully

3. Edit amoeba.xml profile

[root@amoeba conf] # cd / usr/local/amoeba/conf/ [root@amoeba conf] # cp amoeba.xml amoeba$ (date + "% Y_%m_%d"). Xml # back up a copy before modification [root@amoeba conf] # vim amoeba.xml27 28 29 30 amoeba # set client connection user 31 32 amoeba # set client connection password 33 34 35 36 ${amoeba.home} / conf/access_list.conf37 38 39 40 112113 ${amoeba.home} / conf/functionMap.xml114 1500115 master/property > # specified default is master116 117 # Delete comment 121 true

3. Edit dbServers.xml

25 26 proxy # fill in mysql authorized user 27 28 29 123456 # fill in MySQL authorized user password 30 45 46 47 48 192.168.0.134 # specify IP49 50 51 52 53 for master 54 55 192.168.0.135 # specify the IP56 of slave 57 58 59 60 61 162 63 64 slave65 66

4. Start the service

[root@amoeba ~] # amoeba start & [1] 2666 [root@amoeba ~] # log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2017-05-07 00 log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2017-05-07 00 root@amoeba 42root@amoeba 42root@amoeba 16692 INFO context.MysqlRuntimeContext-Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2017-05-07 00 42Switzerland 17064 INFO net.ServerableConnectionManager-Amoeba for Mysql listening On 0.0.0.0Charger 0.0.0Amoeba Monitor Server listening on 8066.2017-05-07 00 Amoeba Monitor Server listening on 42Amoeba Monitor Server listening on 17067 INFO net.ServerableConnectionManager-Amoeba Monitor Server listening on / 127.0.0.1 Amoeba Monitor Server listening on 56460. [root@amoeba ~] # netstat-anptl | grep javatcp 00:: ffff:127.0.0.1:56460:: * LISTEN 2666/java tcp 0 0:: 8066: * LISTEN 2666/java tcp 0 0:: ffff:192.168.0.128:37361:: ffff:192.168.0.135:3306 ESTABLISHED 2666/java tcp 0 0:: ffff:192.168.0.128:35241:: ffff:192.168.0.134 : 3306 ESTABLISHED 2666/java # Amoeba default port is 8066

five。 test

1. Client connection Amoeba

[root@client] # mysql-uamoeba-p123456-h 192.168.0.128-P8066

two。 Create a new database

Mysql > create database guoxh;Query OK, 1 row affected (0.01sec) mysql > show databases +-+ | Database | +-+ | information_schema | | aaa | | guoxh | | mysql | | test | +-+ 5 rows in set (0.01sec)

3.master and slave view the database

Mysql > show databases +-+ | Database | +-+ | information_schema | | aaa | | guoxh | # the newly built database is synchronized | mysql | | test | +-+ 5 rows in set (0.00 sec)

five。 Test read-write separation:

1. Create a new table in master, synchronize it to slave, then turn off the slave function and insert distinguishing statements on slave

Master: create a table named student

Mysql > show tables;Empty set (0.00 sec) mysql > create table student (id int (10), name varchar (10), info varchar (50)); Query OK, 0 rows affected (0.01 sec) mysql > show tables;+-+ | Tables_in_guoxh | +-+ | student | +-+ 1 row in set (0.00 sec)

Slave: stop synchronization

Mysql > show tables;+-+ | Tables_in_guoxh | +-+ | student | +-+ 1 row in set (0.00 sec) mysql > stop slave;Query OK, 0 rows affected (0.00 sec)

Master: inserting differentiated data

Mysql > insert into student values; Query OK, 1 row affected (0.01 sec) mysql > select * from student +-+ | id | name | info | +-+ | 1 | zhangsan | Mysql_master | +-+ 1 row in set (0.00 sec)

Slave: inserting differentiated data

Mysql > select * from student;Empty set (0.00 sec) mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+ | id | name | info | +-+ | 2 | lisi | Mysql_slave | +-+ 1 row in set (0.00 sec)

3. Test the read operation:

Client:

Mysql > show tables;+-+ | Tables_in_guoxh | +-+ | student | +-+ 1 row in set (0.00 sec) mysql > select * from student +-+ | id | name | info | +-+ | 2 | lisi | Mysql_slave | +-+ 1 row in set (0.00 sec) mysql > select * from student +-+ | id | name | info | +-+ | 2 | lisi | Mysql_slave | +-+ 1 row in set (0.00 sec)

# at this point, all read operations have been assigned to slave.

4. Test write operation

Client: insert a piece of data. You can't see the data here. Get master to view it.

Mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+ | id | name | info | +-+ | 2 | lisi | Mysql_slave | +-+ 1 row in set (0.00 sec)

Master:

Mysql > select * from student +-+ | id | name | info | +-+ | 1 | zhangsan | Mysql_master | | 3 | wangwu | Mysql_client | +-- -+ 2 rows in set (0.00 sec)

# at this point, all write operations are assigned to master.

So far, MySQL has achieved read-write separation!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.