How to Set Up MySQL Proxy read/write separation in Ubuntu10

I,MySQL-ProxyBasic

MySQL Proxy is a simple program between your Client and MySQL server. It can monitor, analyze, or change their communication. It is flexible and has no restrictions. Its common uses include load balancing, faults, query analysis, query filtering and modification.

(Figure1: MySQL Proxy)

MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. the proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:

Load balancing and fail over handling query analysis and logging SQL macros query rewriting executing shell commands

One of the more powerful features of MySQL Proxy is the ability to do "Read/Write Splitting ". the basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.

MySQL-Proxy is a program between your MySQL database client and the server. It also supports the embedded scripting language Lua. This proxy can be used to analyze, monitor, and transform communication data. It supports a wide range of application scenarios:

Load Balancing and Failover processing query analysis and log SQL Macro (SQL macros) query rewriting) execute shell commands

One of the more powerful functions of MySQL Proxy is to implement Read/Write Splitting )". The basic principle is to allow the primary database to process transactional queries, but to process SELECT queries from the database. Database Replication is used to synchronize changes caused by transactional queries to the slave database in the cluster.

II,Practical Process

Test environment: Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12. 10-log

192.168.1.147 proxy entry

192.168.1.126 write-only for the master host

192.168.1.145 slaver slave read-only

The program only needs to link to 192.168.1.147, while 192.168.1.126 and 192.168.1.145 are transparent to the program. You do not need to pay attention to them or know 192.168.1.126 and 192.168.1.145, all your operations on the database are performed only on 192.168.1.147.

1.Installation script lua

# Apt-get install lua5.1

MySQL-Proxy read/write separation is mainly implemented through the rw-splitting.lua script, so you need to install lua.

2.Install and configure MySQL-Proxy

# Apt-get mysql-proxy

The current obtained version is: mysql-proxy 0.8.0 (view the version command: # mysql-proxy-V)

3.Modify rw-splitting.lua

# Vim/usr/share/mysql-proxy/rw-splitting.lua

Configure and use the rw-splitting.lua read/write splitting script, the script directory is/usr/share/mysql-proxy, modify the read/write splitting script rw-splitting.lua, modify the default connections, perform a quick test, if the number of connections is not modified, read/write splitting is enabled only when the number of connections reaches 4.

-- Connection pool

If not proxy. global. config. rwsplit then

Proxy. global. config. rwsplit = {

Min_idle_connections =1 ,//The default value is 4.

Max_idle_connections =1 ,//The default value is 8.

Is_debug = false

}

End

This is because mysql-proxy will detect the client connection, when the connectionNot exceedingWhen the default value is min_idle_connections, read/write splitting is not performed, that is, the query operation will occur to the Master.

4.New Folder/var/log/mysql-proxy/and file mysql-proxy.log

# Mkdir/var/log/mysql-proxy

# Vi mysql-proxy.log

5.Execute read/write splitting

# Sudo mysql-proxy -- proxy-read-only-backend-addresses = 192.168.1.145: 3306 -- proxy-backend-addresses = 192.168.1.126: 3306 -- proxy-lua-script =/usr/share/mysql-proxy/rw-splitting.lua>/var/log/mysql-proxy/mysql-proxy.log &

Parameter description:

192.168.1.147 proxy entry

192.168.1.126 write-only for the master host

192.168.1.145 slaver slave read-only

After running the above statement of sudo mysql-proxy and the query process does not have 4040, restart mysql (sudo/etc/init. d/mysql restart) and then enter proxy settings.

6.View process Port

# Netstat-ant

# Netstat-ntl

(Figure2: Port)

Tcp 0 0 0.0.0.0: 4040 0.0.0.0: * LISTEN

Tcp 0 0 0.0.0.0: 4041 0.0.0.0: * LISTEN

7.View database links

Mysql> show processlist \ G;

(Figure3: Process)

As you can see, a new connection is generated. If you want to kill a link, you can use mysql> help kill to view the kill help information and kill the 36 process command: mysql> kill 36;

8.Test read/write splitting

1) Go to mysql on the MySQL-proxy host

# Mysql-u gaizai-p-P4040-h 192.168.1.147

The-h parameter must be specified. Otherwise, the following error is reported:

(Figure4: Error)

2) display the Database List:

Mysql> show databases;

If you have successfully set up MySQL-Proxy, the Database List shown above should be the Database List on the 192.168.1.145 server. (You can create different databases in 145 and 126 for testing)

3) enter the test database:

Mysql> use weibo;

4) query table records:

Mysql> select * from blog;

5) insert a record:

Mysql> insert into 'blog '('taskid', 'content', 'quote', 'author', 'time', 'url', 'imageurl', 'transmits ', 'comments', 'hash', 'addon ') VALUES ('10', 'fefefef', 'fefef', 'efef', NOW (), 'HTTP: // configure ());

6) query table records:

Mysql> select * from blog;

Compare the two query table records to see if there are any changes in the records. We inserted the data (confirm that the insertion was successful), but the data for the two queries was not changed. That's right, this is the read/write splitting (we read 145 of the database and inserted 126 of the database, and we didn't set Replication for 145 and 126; if we set it before, please stop before testing)

Note: Sometimes data is displayed in the mysql_proxy (38) database. restart the system and restart mysql.

7) Go to the master write server (192.168.1.126) to view the data

# Mysql-u gaizai-p-h 192.168.1.126

Mysql> use weibo;

Mysql> select * from blog;

You can check that a record has been written.

8) access the slave read server (192.168.1.145)

# Mysql-u gaizai-p-h 192.168.1.145

Mysql> use weibo;

Mysql> select * from blog;

Because there is no data display, it means it can only be read and cannot be written.

When you use the SQLyog tool to perform a query, the following information is automatically displayed on the Proxy server:

(Figure5: Information)

9.MySQL-Proxy + Replication

The above test only tests the data insertion. When the Master and Slave Replication settings are not performed, the data read from the Master and Slave is different. If you want to achieve the effect of Figure1, we also need to set Replication between the Master and Slave. For details, refer to: setting up a Master Slave for MySQL in Ubuntu10.

III,MySQL-ProxyCommand

Help Command: $ mysql-proxy -- help-all

View the MySQL Proxy version: $ mysql-proxy-V

Compile the Startup Script: $ vi/etc/init. d/mysql-proxy

Start command: $/etc/init. d/mysql-proxy start

Stop command: $/etc/init. d/mysql-proxy stop

Restart command: $/etc/init. d/mysql-proxy restart

IV,Notes

1. When starting mysql-proxy, you can save the startup command as a file:

We recommend that you start the service in the form of a configuration file. Note that the configuration file must have 660 permissions; otherwise, the service cannot be started. if there are multiple Slave instances, the proxy-read-only-backend-addresses parameter can be configured with multiple IP: Port Slave database lists separated by commas.

Killall mysql-proxy process: # killall mysql-proxy

Create a file: # vi/etc/mysql-proxy.cnf

Enter the content in the middle of two separators in the file:

------------------------------------------------------

[Mysql-proxy]

Admin-username = viajarchen

Admin-password = 123123

Admin-lua-script =/usr/share/mysql-proxy // admin-sql.lua

Proxy-backend-addresses = 192.168.1.126: 3306

Proxy-read-only-backend-addresses = 192.168.1.145: 3306

Proxy-lua-script =/usr/share/mysql-proxy/rw-splitting.lua

Log-file =/var/tmp/mysql-proxy.log

Log-level = debug

Daemon = true

Keepalive = true

Max-open-files = 1024

------------------------------------------------------

Set permissions: # chmod 660/etc/mysql-proxy.cnf

Or # chmod + x/etc/init. d/mysql-proxy

Set the Startup file: # mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf

View information: # ps-ef | grep mysql-proxy | grep-v grep

Root 1869 1 0 18:16? 00:00:00/usr/local/mysql-proxy/libexec/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf

Root 1870 1869 0? 00:00:00/usr/local/mysql-proxy/libexec/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf

View the log: # tail-50f/var/tmp/mysql-proxy.log

2. mysql-proxy Parameters

-- Admin-address = host: port specifies a Management port of mysqo-proxy. The default value is 4041;

-P, -- proxy-address =

-R, -- proxy-read-only-backend-addresses =

-B, -- proxy-backend-addresses =

-- Defaults-file = <file> configuration file. You can put the parameter information of mysql-proxy into a configuration file;

-- Daemon mysql-proxy run as a daemprocess

-- Keepalive try to restart the proxy if it crashed, there will be two processes to keep the Connection Starting, process 1 is used to monitor process 2, if process 2 is dead, the proxy will be automatically restarted.

-Log-level = debug defines the log level, from high to low.

(Error | warning | info | message | debug)

-- Proxy-lua-script = file specifies a Lua script program to control the running and setting of mysql-proxy. This script will be called again every time a new connection is created and the script is modified.

-- Max-open-files: specifies that the maximum number of files opened is 1024. Otherwise, [cocould not raise RLIMIT_NOFILE to 8192, Invalid argument (22 ). current limit still 1024..

3. when the Slave_IO_Running or Slave_ SQL _Running value of MySQL master-slave replication in show slave status \ G is not YES, you must stop the slave server by stopping slave and then test read/write splitting.

4. mySQL-Proxy's rw-splitting.lua script has many versions online, but the most accurate version is still the lib/rw-splitting.lua script included in the source package, if there is lua Script Programming basics, optimization can be performed on the basis of this script;

5. mySQL-Proxy is actually very unstable. In the case of high concurrency or incorrect connections, the process is easily closed automatically. Therefore, it is better to enable the-keepalive parameter to make the process automatically recover, but it still cannot solve the problem fundamentally. Therefore, the most secure practice is to install a MySQL-Proxy on each slave server for your own use. Although inefficient, it ensures stability;

6. the one-master, multi-slave architecture is not the best architecture. Generally, the best practice is to plan through the program code and middleware, for example, two or more Master servers can be implemented through separate Writing of single and double server-ID numbers;

7. The stability of MySQL-Cluster is not very good;

8. amoeba for MySQL is an excellent middleware software that can also implement read/write splitting, load balancing, and other functions. The stability is much higher than that of MySQL-Proxy. We recommend that you replace MySQL-Proxy, or even MySQL-Cluster.

9. mysql proxy does not support old_password. In addition, you can view the password length to determine whether the select length (password) from mysql. user is old_password if the length is 16 characters.

10. mysql-proxy is installed for read/write splitting, including master x 1 and slave x 2. In order to test the failover, a slave is stopped, and mysql-proxy always reports an error, prompting that the connection fails. This is worse than single-point mysql. If one is dropped, it will all be dropped! The mysql engineer provided a piece of code and replaced it:

Src/The NETWORK_MYSQLD_PLUGIN_PROTO function of the network-mysqld-proxy.c can solve this problem. A network-mysqld-proxy-function.c file.

(After testing: if I stop the slave database, proxy queries will be transferred to the master. After the slave is started, the proxy is still reading the master, when a new link comes in, the slave data will be read)

11. If mysql is installed on the mysql-proxy machine, the newbie will be confused at this time. How can I test and link it? For example, run the command # mysql-u gaizai-p-P4040-h 192.168.1.147, which indicates that port 4040 is used to log on to the local machine. The gaizai account may not be the local mysql account, this makes it easier to differentiate.

12. In the above environment, the three servers, mysql-proxy, mysql-master, and mysql-slave, all have single point of failure. There are two ways to avoid the single point of failure (spof) of mysql-proxy: mysql-proxy and keepalived, another method is to install mysql-proxy and Application Service on the same server. To avoid single point of failure of mysql-master, you can use DRBD + heartbear as the dual-host; to avoid single point of failure (spof) in mysql-slave, you can add multiple mysql-slave instances. mysql-proxy automatically blocks the mysql-slave instances with backend faults.

13. Use sysbench (or super-smack) to test mysql performance:

# Sysbench -- test = oltp -- mysql-table-engine = innodb -- oltp-table-size = 1000 -- mysql-socket =/tmp/mysql. sock -- mysql-password = 123456 -- mysql-user = gaizai -- mysql-host = 192.168.1.126 -- mysql-db = weibo -- num-threads = 15 prepare

# Sysbench -- test = oltp -- mysql-table-engine = innodb -- oltp-table-size = 1000 -- mysql-socket =/tmp/mysql. sock -- mysql-password = 123456 -- mysql-user = gaizai -- mysql-host = 192.168.1.126 -- mysql-db = weibo -- oltp-test-mode = complex run

14. Compile the shell script for mysql-proxy startup and shutdown:

15. One of the unavoidable issues of read/write splitting is latency. You can consider the SemiSyncReplicationDesign patch provided by Google.

16. MySQL-Proxy uses port 4040 by default. If you want to transparently forward requests from Port 3306 to port 4040, you can: iptables-t nat-I PREROUTING-s! 127.0.0.1-p tcp -- dport 3306-j REDIRECT -- to-ports 4040 if you want to delete this rule, replace-I in the preceding example with-D. Reference

17. when using bigint, mysql_insert_id () has a problem. For details, see the manual. However, for most people, bigint is basically not encountered, so you can ignore this problem.) Note: for these two problems, someone in the official BUG library has provided corresponding patches.

V,Error

The following error occurs during command execution:

(Figure6: error message)

Cocould not raise RLIMIT_NOFILE to 8192

This warning-level error means that MySQL Proxy cannot upgrade open files limit to 8192 on your system, but it doesn't matter, mySQL Proxy still works well on your computer.

You can solve this problem by setting the -- max-open-files parameter.

MySQL ProxyInstallation and Use (1)

Mysql proxy master and slave test

The following error is reported after -- max-open-files = 8192 is added:

(Figure7: error message)

VI,Questions and answers

1. When slave goes down, how does mysql-proxy read it?(After testing: if I stop the slave database, proxy queries will be transferred to the master. After the slave is started, the proxy is still reading the master, when a new link comes in, the slave data will be read again. Sometimes you may need to restart mysql-proxy)

2. How do I know on which machine mysql-proxy currently runs the select statement?

3. When slave goes down for a while, what if the missing data of the master is synchronized again?

4. When proxy-read-only-backend-addresses = 192.168.1.145: 3306 is set in the configuration

, 192.168.1.147: 3306. If the data of two slave instances is not synchronized, how does one read the data?# Tail-50f/var/tmp/mysql-proxy.logTest

5. In the production environment, do not enable mysql query logs except for program debugging. Because the query logs record all the statements on the client, frequent IO operations will cause the overall performance of mysql to decline. How to Set it?

6. What is the administrator account and password in the mysql-proxy.cnf file? Use commands to enter management

Mysql-u viajarchen-p 4041-h 192.168.1.147 password is 123123

Mysql> select * from proxy_connections;

Mysql> select * from proxy_config;

(Figure8: Information)

7. How to compile the mysql-proxy startup and shutdown shell scripts? Test

8. For the/usr/share/mysql-proxy/rw-splitting.lua script

Local min_idle_connections = 4 local max_idle_connections = 8 what should I do? Min means read/write splitting only when the value is reached. What does max mean? Can there be a maximum of 8 links?

9. What does mysqld mean? Is the mysql daemon!

10. How to build and use HAProxy and keepalived together? What problems can be solved?