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

In the actual production environment, if the read and write of the database are operated in the same database server, no matter the security, high availability, or high concurrency and other aspects can not meet the actual needs, generally speaking, it is through master-slave replication to synchronize poems, and then through read-write separation to improve the concurrent load capacity of the database to implement and deploy.

The principle of MYSQL master-slave replication

There is a close relationship between the master-slave replication of MYSQL and the read-write separation of MYSQL. First of all, master-slave replication must be deployed. Only when the master-slave replication is completed, can the data read-write separation be carried out.

MYSQL principle of separation of reading and writing

Read-write separation is written only on the master server and can only be read on the slave server. The basic principle is to let the main database handle transactional queries, so that the database handles select queries. Database replication is used to synchronize changes caused by transactional queries to the cluster minute slave database.

Implementation based on the intermediate proxy layer: the agent is generally located between the client and the server, and the proxy server establishes the request to the client and forwards it to the back-end database after judgment. We use Amoeba here.

Links to the packages and scripts needed for the experiment: https://pan.baidu.com/s/1papoUzo9sIVWs2QaIpdOWg password: qm2g

Master server IP:192.168.175.128

IP1:192.168.175.141 from the server

IP2:192.168.175.142 from the server

Proxy server address: 192.168.175.133

The experiments are as follows:

Turn off the firewalls of all servers before the experiment begins.

First of all, we need to synchronize the time of the master and slave server.

Insert the address field of the server at the end of the ntp.conf file under etc on the primary server, but use 127to indicate that the real address field is not used. Start the ntp service after exiting the file

Specify the address of the master server on the two slave servers for synchronization.

The next thing to do is master-slave replication.

First of all, go back to the main server and modify it under the my.cnf file under etc. If you don't have it in the file, add it yourself.

The following operations are performed on the database that resides on the server

First, create a user to give permission, refresh the database to take effect immediately, and display the master-slave synchronization status.

Let's go back to operating from the server.

Make changes under the my.cnf file under the etc on the two slave servers. Add them if they are not in the file.

Enter the database to specify the primary server address and binary logs and nodes. Here, it is important to note that the nodes of the primary server often change, so confirm the nodes and binary logs of the primary server before performing operations. Enable master-slave synchronization to display the master-slave synchronization status. You need to see two yes here.

To verify the success of master-slave synchronization, you can create a database on the master server to verify

The following actions on the proxy server

Install the java environment

At the bottom of the profile file under etc, when you refresh the file with source, the file will change the environment variables of the system.

Here we need to open permissions to amoeba, so go back to the master server and open permissions on the slave server

Create an amoeba file to hold the file we extracted for a while

Modify two amoeba configuration files

First set up a user so that the client can access the server

Specify whether the writer is the main reader or subordinate.

Edit the next file

Set up a user for proxy access to the server

Specify the address of the primary server

Specify two slave server addresses

Because the name of the server slave has been modified before, so the following needs to be changed.

Use / usr/local/amoeba/bin/amoeba start& to open the amoeba proxy service

You can use netstat-ntap | grep java to view the listening address and port

Even if all the services here are completed, the database on the client side can use mysql-u amoeba-p123456-h 192.168.175.133-P 8066

Here you specify that the login user asks for the amoeba password as 123456 address as the proxy server address-P specifies the port

To verify the separation of reads and writes, you can now create a table on the third generation server, write a different piece of data in each table, and then use stop slave; on the slave server to close the master and slave.

After that, the client can view the contents of the table if it can only display the information from the server. Insert a message on the client that only the master server can access. In this way, the experiment is finished.

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.