Discussion on how to build Mysql proxy read-write separation under UBUNTU10 _mysql

One, Mysql-proxy Base

MySQL Proxy is a simple program between your client side and the MySQL server side that can monitor, analyze, or change their communications. It uses flexible, unrestricted, common uses including load balancing, failure, query analysis, query filtering and modification, and so on.

(Figure1:mysql Proxy)

Mysql-proxy, announced in June, are a binary application that sits between your MySQL client and server, and supports the E mbedded scripting language Lua. The proxy can is 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 are 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 and the queries in the slaves.

Mysql-proxy is a program between your MySQL database client and server, and it also supports the embedded scripting language Lua. This agent can be used to analyze, monitor, and transform (transform) communication data, which supports a very wide range of usage scenarios:

Load balancing and failover processing query analysis and log SQL macros (SQL macros) query rewriting (query rewriting) executing shell commands

One of the more powerful features of MySQL Proxy is the realization of "read-write separation (Read/write splitting)". The basic principle is to have the primary database handle transactional queries and select queries from the database. Database replication is used to synchronize changes caused by transactional queries to the database from the cluster.

Second, actual combat Process

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

192.168.1.147 Proxy proxy Entrance

192.168.1.126 Master hosts write only

192.168.1.145 Slaver read-only from machine

The program only needs to link to 192.168.1.147, and 192.168.1.126 and 192.168.1.145 are transparent to the program, you don't have to ignore it, you don't need to know 192.168.1.126 and 192.168.1.145, all you do to the database is 192. .168.1.147 the operation.

1. Install script Lua

#apt-get Install lua5.1

Mysql-proxy's read-write separation is primarily done through Rw-splitting.lua scripts, so you need to install LUA.

2. installation Configuration Mysql-proxy

#apt-get Mysql-proxy

The currently acquired version is: Mysql-proxy 0.8.0 (View 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 detach script, the script directory is/usr/share/mysql-proxy, Modify the read-write detach script Rw-splitting.lua, modify the default number of connections, for quick testing, if you do not modify the number of connections to reach the number of connections to 4 o'clock to enable read-write separation.

--Connection pool

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1,// default is 4

Max_idle_connections = 1,// default is 8

Is_debug = False

}

End

This is because Mysql-proxy detects client connections, and when the connection does not exceed the min_idle_connections preset, read-write separation is not performed, meaning that the query operation occurs on master.

4. new Folder/var/log/mysql-proxy/and file Mysql-proxy.log

#mkdir/var/log/mysql-proxy

#vi Mysql-proxy.log

5. Perform read-write separation

#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 proxy Entrance

192.168.1.126 Master hosts write only

192.168.1.145 Slaver read-only from machine

When you run sudo mysql-proxy above, the query process does not have 4040, you need to restart MySQL (sudo/etc/init.d/mysql restart) and then enter the proxy settings.

6. View Process ports

#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 has been created. If you want to kill a link, you can use Mysql>help kill to view the kill's help information, kill 36 process command: Mysql>kill 36;

8. test Read and write separation

1 in the Mysql-proxy machine into MySQL

#mysql-u gaizai-p-p4040-h 192.168.1.147

The-h parameter must be specified or the following error should be reported:

(Figure4: Error)

2 Display list of databases:

mysql> show databases;

If you are building mysql-proxy success, the list of databases you see above should be the list of databases on the 192.168.1.145 server. (You can create different databases for testing in 145 and 126, respectively)

3 Enter the test database:

Mysql> use Weibo;

4) query table record:

Mysql>select * from blog;

5) Insert a record:

mysql> INSERT into ' blogs ' (' TaskID ', ' Content ', ' Quote ', ' Author ', ' time ', ' Url ', ' ImageUrl ', ' transmits ', ' Comments ', ' Hash ', ' AddOn '] VALUES (' Ten ', ' fefef ', ' fefef ', ' EFEF ', now (), ' http://www.cnblogs.com/zgx/archive/2011/09/13/ 2174823.html ', NULL, ' 0 ', ' 0 ', ' 33333333 ', now ());

6) query table record:

Mysql>select * from blog;

Comparing the records of the two query tables to see if the records have changed, we inserted the data (confirm the insert succeeded), but two times the data is unchanged, that's right, this is the reading and writing separation (we are reading 145 of the database, inserting 126 of the database, And our 145 and 126 have not set the replication; if it was previously set, please stop and test first.

Note: sometimes Mysql_proxy (38) library will display data, restart the system system, restart MySQL after the phenomenon is not.

7 Enter the main 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 view a record that has been written.

8 Enter from 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 to display, the description can only read, cannot write.

When you execute a query using tool SQLyog, the following information is automatically displayed on the proxy server:

(Figure5: Information)

9.mysql-proxy+replication

The above test only tests the insertion of data, without the master and slave replication settings, read master and slave data is different, if you want to achieve Figure1 effect, We also need to set the data replication between master and Slave (Replication), for details please refer to: Ubuntu10 under MySQL build Master Slave

Third, Mysql-proxy Command

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

View the version of the MySQL proxy: $ mysql-proxy-v

Compiling startup scripts: $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

Four, Attention Matters

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

It is recommended that you start using a configuration file, noting that the profile must be 660 permissions or it will not start. If there are multiple slave, the proxy-read-only-backend-addresses parameter can be configured with multiple comma-delimited ip:port from the library list.

Kill the Mysql-proxy process: # killall Mysql-proxy

Create a new file: # VI/ETC/MYSQL-PROXY.CNF

Enter the contents of the two separator 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 boot 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 18:16? 00:00:00/usr/local/mysql-proxy/libexec/mysql-proxy--defaults-file=/etc/mysql-proxy.cnf

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

2.mysql-proxy parameters

--ADMIN-ADDRESS=HOST:PORT Specifies a mysqo-proxy management port, the default is 4041;

-P, the--proxy-address=

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

-B,--proxy-backend-addresses=

--defaults-file=<file> configuration file, you can put the Mysql-proxy parameter information into a configuration file;

--daemon Mysql-proxy Run as Daemon

--keepalive try to restart the proxy if it crashed, keep the connection started process there will be 2, a process to monitor the second process, if the second process dies automatically restart proxy.

–log-level=debug defines log log levels, from high to low respectively

(Error|warning|info|message|debug)

--PROXY-LUA-SCRIPT=FILE specifies a LUA script to control the running and setting of Mysql-proxy, which is called again every time a new connection and script changes.

--max-open-files: Specifies that the maximum number of files Open is 1024, otherwise there will be "could not raise rlimit_nofile to 8192, Invalid argument (22)." The log message for current limit still 1024. "appears.

3. When the MySQL master copy in show slave status\g when the value of slave_io_running or slave_sql_running is not yes, you need to stop from the server by the stop slave First, And then test the read and write separation.

4.mysql-proxy's Rw-splitting.lua script has many versions on the Web, but the most accurate version is still included in the source package lib/ Rw-splitting.lua scripts, if you have the basics of Lua scripting, can be optimized on the basis of this script;

5.mysql-proxy is actually very unstable, in the case of high concurrency or faulty connections, the process is easy to automatically shut down, so it is a good idea to open the –keepalive parameter so that the process can be automatically restored, but still cannot fundamentally solve the problem, Therefore, it is usually most prudent to install a mysql-proxy on each server for its own use, although it is less efficient but can guarantee stability;

6. A master multiple from the architecture is not the best architecture, usually more excellent practice is through the program code and middleware, etc., to plan, such as single and double server-id number of separate write to achieve two or more master servers;

The stability of the 7.mysql-cluster is not too good;

8.Amoeba for MySQL is a good middleware software, can also achieve read and write separation, load balancing functions, and the stability of much more than mysql-proxy, we recommend to replace Mysql-proxy, or even mysql-cluster.

9.mysql Proxy does not support Old_password. It can also be judged by the way the length of the password is viewed: Select Length (password) from Mysql.user is old_password without doubt if the length is 16 digits.

10. Mysql-proxy is installed to achieve read/write separation, Master x 1, slave x 2. In order to test failover, stopped a slave, and then Mysql-proxy will always error, the hint can not connect. This situation is worse than the single point of MySQL, hang off a whole! The MySQL engineer gave a piece of code to replace:

SRC/NETWORK-MYSQLD-PROXY.C's Network_mysqld_plugin_proto function can solve this problem. NETWORK-MYSQLD-PROXY-FUNCTION.C files.

(After testing: I stopped the slave database, proxy query will be transferred to master, when the slave started, Proxy is still reading master, when a new link came in to read the slave data)

11. If MySQL is installed on the Mysql-proxy machine, the novice will be confused at this time, how to test and link? For example, the use of command: #mysql-u gaizai-p-p4040-h 192.168.1.147 is the landing of the local 4040 port, the use of Gaizai account, this account can not be local MySQL account, so it is easier to distinguish between.

12. In the above environment, Mysql-proxy, Mysql-master, mysql-slave three servers have a single point of failure. There are two ways to avoid mysql-proxy single point of vulnerability: One approach is to mysql-proxy with keepalived, another way is to install Mysql-proxy and application services on the same server In order to avoid mysql-master single point of failure can use Drbd+heartbear to do dual-machine, in order to avoid mysql-slave single point of failure can add more than one mysql-slave, Mysql-proxy will automatically shield the back-end failure of the mysql-slave.

13. Test MySQL performance with sysbench (or super-smack):

#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. Writing about Mysql-proxy startup and shutdown shell scripts:

15. One of the problems with read-write separation is latency, which can be considered for semisyncreplicationdesign patches provided by Google.

Mysql-proxy defaults to use 4040 port, if you want to transparently to the 3306-port request to 4040, then 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 remove this rule, you can replace-I in the example above with-D. Reference links

17. When using bigint, mysql_insert_id () There are problems, see the manual, but for most people, bigint will not encounter, so you can ignore the problem note: For these two issues, the official bug library has been given a corresponding patch.

Five, Error

The following error occurred while executing the command:

(FIGURE6: Error message)

Could not raise rlimit_nofile to 8192

This is a warning level error, meaning that MySQL proxy cannot elevate the open files limit to 8192 on your system, but it doesn't matter that MySQL proxy is still running well on your computer.

Can be resolved by setting the startup--max-open-files parameter.

MySQL Proxy Installation and use ( i)

MySQL proxy master and slave test

After joining--MAX-OPEN-FILES=8192, report the following error:

(Figure7: Error message)

Six, Questions and Answers

1. When slave down, how is the Mysql-proxy read? (After testing: I stopped the slave database, proxy query will be transferred to master, when the slave started, Proxy is still reading master, when a new link came in to read the slave data again. Sometimes you may need to reboot the Mysql-proxy)

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

3. When slave is down for a period of time, if you synchronize master's missing data again?

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

, 192.168.1.147:3306 similar to the two slave, if two slave data is not synchronized, then how to read the data? # tail-50f/var/tmp/mysql-proxy.log Test

5. In addition to debugging in the production environment, do not open the MySQL query log, because the query log records all the statements of the client, frequent IO operation will lead to the overall performance of MySQL decline. How do I set it up?

What is the use of the Administrator account and password in the 6.mysql-proxy.cnf file? Using commands to enter administration

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

Mysql> select * from Proxy_connections;

Mysql> select * from Proxy_config;

(Figure8: Information)

7. About Mysql-proxy startup and shutdown shell script writing? Test

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

Local min_idle_connections = 4 local max_idle_connections = 8 What should be understood? Min's word is to achieve this value when the read and write separation, then what does Max mean? Maximum can have 8 links?

What do you mean, 9.mysqld? It's the MySQL daemon!

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