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

Article Contents 1. Mysql read-write separation principle 2. About Amoeba 3. Configuration example Step 1: Configure Amoeba server Step 2: Add permissions on three mysql servers and open them to amoeba (That is, three mysql allow amoeba to access them) Step 3: Configure the read-write separation function on the amoeba server Step 4: Client test IV. Summary I. Mysql read-write separation principle Read-write separation is to write only on the master server and read only on the slave server; the master database handles transactional queries and the select query is handled from the database; Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster. About Amoeba (1) Amoeba is a proxy that takes MySQL as the underlying data store and provides MySQL protocol interfaces to applications. It centrally responds to application requests and sends SQL requests to specific databases for execution according to rules set by users in advance. Based on this, requirements such as Load Balancer, Read-Write Separation, and High Availability can be realized. (2) Amoeba is equivalent to a SQL request router designed to provide mechanisms for Load Balancer, read/write separation, and high availability, rather than fully implementing them. It needs to be combined with mechanisms such as MySQL Replication to achieve replication synchronization and other functions. III. Configuration example environment description: prepare three Mysql servers (one for the master server and the other two for the slave server); one Amoeba, used to achieve read-write separation, write operations to the master server, read operations sent to two slave servers; experimental verification: write operations performed on the client, verified on the master and slave servers.

Description: Mysql master-slave replication is closely related to read and write functions. Data synchronization is achieved through master-slave replication, and then the concurrent load capacity of the database is improved through read and write separation. Application client 192.168.220.131Amoeba 192.168.220.129 master server 192.168.220.141 slave server 1 192.168.220.140 slave server 2 192.168.220.136 Step 1: Configure Amoeba server (1) Turn off firewall: systemctl stop firewalld.service setenforce 0 (2) Install admin jdk: The requested URL/usr/local/index.php was not found on this server. //copy./ jdk-6u14-linux-x64.bin //install jdk, pay attention to this step, press enter all the way to the end, prompt yes, wait for installation

2. To facilitate management, rename the jdk package: mv jdk1.6.0_14/ /usr/local/jdk1.63. Modify the profile file 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/amoebausr export PATH=$PATH:$AMOEBA_PATH/bin Make the environment variables effective: source /etc/PATH (3) Extract amoeba package: HOME 1. First create a file mkdir /usr/local/amoeba2. Extract tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/3. Add weight and verify whether the installation is successful chmod -R 755 /usr/local/amoeba/input: /usr/local/amoeba/bin/amoeba, press Enter to check whether it is successful

Step 2: Add permissions to the three mysql servers and open them to amoeba access (that is, three mysql servers allow amoeba access to them). Enter the following command: grant all on *.* to test@'192.168.220.% ' identified by ' 123.com';//Allow IP 192.168.220 to access from any terminal using the password of 123.comStep 3: Configure read-write separation on amoeba server1. cd/usr/local/amoebavimconf/amoeba.xml Line 30: amoeba Line 32:123456117 Remove comments: master slaves

2. vim conf/dbServers. xmlLine 26 -29: Remove comments, set login username and password test123.com Find master server and slave server modules, modify names and corresponding IP addresses: master server: 192.168.220.141 First slave server: 192.168.220.140 Second slave server: 192.168.220.136 Specify slave server pool: slave1,slave2

The requested URL/bin/was not found on this server. //& means to put the process in the background, it takes a long time, wait a little 1 Step 4: Client test 1. Client virtual machine, you can install a mysql service directly with yum yum install -y mysql2. Connect amoeba server (1) Specify password and port to connect amoebamysql -u amoeba -p 123456 -h 192.168.220.129-P8066 (2) Create a library named school, and then create a table called info MySQL [school]> create table info ( -> id int(4) not null primary key, //Specify id number, primary key, not empty -> name varchar(10) not null, //name -> score decimal(4,1) not null); Query OK, 0 rows affected (0.03 sec)

3. Three mysql servers verify the experimental results. First, verify whether the write operation is separated: (1) At this time, we directly enter the database on the main server, and we will find that there is also an additional table named info.

(2) However, the two slave servers do not have:

Therefore, the statement we write on the client side will be queried on the master server, but not on the two slave servers. At this time, the operation written will be directly handed over to the master server. Next is to verify the operation of reading: (1) in the two slave servers on each also create an info table, also write two pieces of information.

(2) The client queries the contents of the info table, because the read-write separation operation is done, so the contents of the query should be the contents of the info table on the two slave servers.

Therefore, users read data while passing through the slave server, thus reducing concurrency on the master server. 4. To sum up this blog, in order to show the experimental effect of read-write separation, there is no master-slave synchronization at the same time. In the enterprise network, master-slave synchronization and read-write separation are inseparable. In this way, when the client writes to the database, it is undertaken by the master server; when reading, querying, etc., it is undertaken by the slave server (due to master-slave synchronization, the data of each slave server is the same as that of the master server). In case of high concurrent requests, Load Balancer is realized to ensure the security of data and efficient operation of the server

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.