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

Introduction to OneProxy (civilian software)

OneProxy for MySQL can reuse different connections applied to the back-end database and effectively reduce the number of concurrent connections to the database. It can instantly kick out unavailable nodes and forward application requests to other available nodes to ensure the stability of business services. Query statements can be transparently distributed to multiple MySQL standby libraries for execution, and tens of millions of concurrent accesses can be supported by read-write separation scheme. It can also be routed according to the values in SQL statements and evenly distributed to multiple MySQL main libraries to support the concurrent execution of millions of small transactions per second. It can analyze traffic data in real time and transparently, count the running time of SQL and transaction, analyze the structure of transaction, and get real-time performance reports of different dimensions; it can also carry out flow QoS control, which can be used as a database firewall to resist SQL injection *; according to the parallel execution of fragmented SQL, the performance problem of summary statistics under large amount of data is solved. Merging result sets across multiple fragments greatly simplifies the development workload of the application.

Official website: http://www.onexsoft.com/zh/ (absolute conscience)

Main function

1. Vertical sub-library 2. Horizontal table 3. Proxy cluster 4. Reading high is available 5. Read-write separation (master does not participate in reading) 6. Read-write separation (master participates in reading) 7. Write height available 8. Random reading and writing

Brief introduction of environment

192.168.242.5 master1

192.168.242.10 slave1

192.168.242.15 slave2

192.168.242.20 oneproxy

The name can be defined by yourself, and changing the name is easy to remember because it is tested in a virtual machine.

A total of 4 hosts are required, of which three are used for master-slave replication of MYSQL (one master and two slaves), and the other is used as OneProxy. If there are not enough hosts, OneProxy and MYSQL can be deployed to the same host.

Using mysqlreplicate to build Master and Slave

Change the hosts file for redirection to make it easy to find

Turn off the firewall

Master server configuration

Configure from the server

Create an authorized user on the primary server

The first is used to create an account for data replication, which is used for synchronization.

Article 2 root permission is granted to make remote connections

Article 3 reload the permission table

Create a user from the server

You only need to grant root permission.

Install mysql-utilities

Configure master-slave replication

-- master=root:123456 (here is the login MySQL account and password) @ the IP address of the host

-- IP address of slave=root:123456@ from

-- rpl-user=mysync:123456-b enter the previously configured account password for replication here

View statu

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Both threads are yes, indicating success

Also in both from the same, do not do the demonstration.

Configure OneProxy

Download the latest OneProxy for decompression configuration (you can also download other versions. The configuration file is probably the same and backward compatible, but some parameters will be different. For more information, please see the official website for configuration)

Change the configuration path of OneProxy in demo.sh (that is, / root/oneproxy, modify it according to the directory)

Change the proxy.conf file under conf

Proxy-smart-master = 1 intelligent is the main, this function needs to upgrade OneProxy to version 6.2.0

Parameter proxy-auto-readonly = 1 automatically adjusts the node type

Master does not participate in read traffic, so it is suitable for situations with more read nodes.

Proxy-group-policy= default:read_slave

Master nodes participate in read traffic, such as one master and one slave scenario

Proxy-group-policy= default:read_balance

Proxy-user-list = root/ key / database fill in the login user name and password here (password needs to be encrypted) and finally fill in the database

How to encrypt it? Just execute mysqlpwd under the bin of oneproxy

Proxy-master proxy-slave literally means default after master server slave server IP port @ is a default cluster node, which can be customized.

Start OneProxy

Demo lacks permission, so it gives execution permission.

Finally, it is tested by the browser.

Http://OneProxy_IP:8080 port can be modified in conf/proxy.conf, as long as the port does not conflict by default

Account and password is required to log in. Default user=admin password=OneProxy

The building is complete.

Period reference

Https://blog.51cto.com/suifu/1884673

Https://blog.51cto.com/suifu/1878443

I'm much obliged

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.