Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what to pay attention to when using MySQL-Proxy read-write separation". In daily operation, I believe that many people have doubts about what they need to pay attention to when using MySQL-Proxy read-write separation. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what do you need to pay attention to when using MySQL-Proxy read-write separation"? Next, please follow the editor to study!

It is best to install MySQL-Proxy and configure MySQL master and slave server before operation. Add: the new version of MySQL has built-in support

Delay problem

One of the unavoidable issues of read-write separation is latency. Consider the SemiSyncReplicationDesign patch provided by Google.

Port issu

MySQL-Proxy defaults to port 4040. If you want to transparently forward the request from port 3306 to 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, you can replace-I with-D in the above example.

Reference link

Password encryption mode

MySQL-Proxy does not support old password encryption, so if you are using an older version of MySQL, or if you have enabled the old_passwords option, an error may occur:

ERROR 2013: Lost connection to MySQL server

The best fix at this point is to use the new password encryption. If your user table is old-fashioned, you may need to run the mysql_fix_privilege_tables script in the scripts directory in the MySQL source code to upgrade the table structure. Sometimes the objective situation may not allow immediate upgrade operation, at this time, you can specifically create a user with an empty password for MySQL-Proxy (restrict access through the host, or set up a very complex user name), because no matter whether it is the new password encryption method or the old password encryption method, the empty password is also an empty string, thus avoiding the problem of password encryption.

Www.2cto.com

Query garbled code

After connecting to the MySQL-Proxy, random garbled code occurs when the query is executed. The reason for this problem is that when we use MySQL-Proxy to separate reads and writes, there are usually multiple back-end servers. When the client issues a query request, it usually issues a statement similar to "SET NAME gbk" to declare the client code, and then issues the SQL statement of the actual query. However, MySQL-Proxy may distribute these two statements to different back-end servers, resulting in garbled.

The solution is to forcibly specify the character encoding of the backend server:

Init-connect='SET NAME gbk'

Default-character-set=gbk

Skip-character-set-client-handshake

If you use init-connect, you need to be aware that the operation user cannot have SUPER privileges, otherwise this option has no effect.

Even after making the above settings, there may still be garbled code, such as the database is gbk, when we use PHPMyAdmin to connect to MySQL-Proxy, the query will still appear garbled, but this is normal! Because PHPMyAdmin uses utf8 encoding, the "SET NAMES utf8" statement it issues is blocked by skip-character-set-client-handshake, so garbled code occurs.

Process crash

Www.2cto.com

MySQL-Proxy occasionally crashes processes for unknown reasons.

The new version of MySQL-Proxy adds a keepalive option (try to restart the proxy if it crashed) to deal with this problem. When using this option, two mysql-proxy processes will be started successively. The mysql-proxy process started first is used to monitor the mysql-proxy process started later. In fact, the mysql-proxy process that starts after the service is provided. Once the started mysql-proxy process dies (you can try to kill yourself) The mysql-proxy process that starts first restarts a mysql-proxy providing service.

However, many people still use the old version of MySQL-Proxy. At this time, you can use init to achieve an effect similar to keepalive:

Write a script / usr/local/sbin/mysql-proxy.sh and add the following (depending on the installation):

LUA_PATH= "/ usr/local/mysql-proxy/share/mysql-proxy/?.lua"\

/ usr/local/mysql-proxy/sbin/mysql-proxy\

-- proxy-backend-addresses=192.168.0.1:3306\

-- proxy-read-only-backend-addresses=192.168.0.2:3306\

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

Don't forget to add executable attributes:

Chmod axix / usr/local/sbin/mysql-proxy.sh

Version 0.7.0 has a new option:-- defaults-file, which can write all the relevant information into the configuration file:

# MySQL Proxy's configuration file (mysql-proxy.cnf)

[mysql-proxy]

Daemon = true

Keepalive = true

Proxy-backend-addresses = 192.168.0.1 purl 3306

Proxy-read-only-backend-addresses = 192.168.0.2purl 3306

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

You can use: mysql-proxy-- defaults-file=mysql-proxy.cnf at startup

Modify inittab:

Www.2cto.com

Vi / etc/inittab

Add the following:

Mp:12345:respawn:/usr/local/sbin/mysql-proxy.sh

Then ask init to reread the inittab content:

Kill-HUP 1

The system automatically detects whether / usr/local/sbin/mysql-proxy.sh is running, and if not, it runs automatically.

It is important to note that when writing mysql-proxy.sh scripts, do not add the-- daemon option, otherwise / usr/local/sbin/mysql-proxy.sh will end as soon as it is run, and the system will keep trying to run the script, leaving a lot of error messages in / var/log/message (init: Id "mp" respawning too fast: disabled for 5 minutes).

Init's approach may seem a little different and you can use other tools, such as svscan.

Stateful query

Some special stateful queries may fail, such as:

SELECT SQL_CALC_FOUND_ROWS..

SELECT FOUND_ROWS ()

This kind of query is stateful and should be processed on the same backend. If you look at the rw-splitting.lua script, you can see that MySQL-Proxy has actually judged such a query, but there are still problems in practical application. It is estimated that the script is not well written, in practical application, it is recommended that we do not use such a query, first, there is no portability, and the efficiency is not necessarily good.

Another query that can cause problems is:

Www.2cto.com

INSERT... (AUTO_INCREMENT)

SELECT LAST_INSERT_ID ()

When the system executes INSERT and then executes SELECT, it may have been distributed to different back-end servers. If you are using PHP, you should use mysql_insert_id () to get the newly inserted id. After each INSERT, the corresponding autoincrement value has already been calculated and returned to PHP. You do not need to issue a separate query, just use mysql_insert_id (). However, many PHP programs use SELECT LAST_INSERT_ID (), such as AdbDB,CakePHP, etc., so be careful if you are using them. (there is a problem with mysql_insert_id () when using bigint, as detailed in the manual, but for most people, bigint is rarely encountered, so you can ignore the problem.)

Note: for these two problems, someone in the official BUG library has given corresponding patches.

Script issu

The read-write separation of MySQL-Proxy is achieved through the lua script (rw-splitting.lua), but this script is in disrepair and has a lot of problems, such as when it is used:

ERROR 1105: can't change DB to on slave

The reason for this problem is that when the client issues a query, MySQL-Proxy will compare whether the current client database is consistent with the server database, and if it is inconsistent, it will try to perform a "USE database" operation on the server side. One possibility is that the database structure of the master-slave server is different, and errors will occur naturally when USE a database that does not exist. There is another reason why some query operations do not have the context of the database. For example, the SHOW DATABASES query does not require a "USE database" in advance, but can be executed as long as it is connected to the server. At this time, if you still try to synchronize the database between the client and the server, errors are inevitable.

Rw-splitting.lua just does not block the situation described by the latter. The fix method is as follows, adding bold code in the appropriate place.

276 if cmd.type ~ = proxy.COM_INIT_DB and

C.default_db and c.default_db ~ = "" and c.default_db ~ = s.default_db then

If is_debug

Print ("server default db:".. S.default_db)

Print ("client default db:".. C.default_db)

280 print ("syncronizing")

End

Proxy.queries:prepend (2, string.char (proxy.COM_INIT_DB).. C.default_db) www.2cto.com

282 end

In lua, ~ = is not equal to. In addition, the empty string "" in lua is considered to be true in if, so c.default_db alone is not enough.

Easily add is_debug 's judgment, otherwise even if it is not debug status, the server's command line will occasionally pop up some debugging information.

In addition, in the new version of MySQL-Proxy, although there is a rw-splitting.lua script in the source code package, it is not installed by default, you need to copy it manually, and the data structure has changed, and the script needs to make appropriate modifications according to the changes in the data structure, you can refer to the author's description of the operation, or refer to the official Bug management or directly. What is worth looking forward to is that there is a special MySQL-Proxy-Lua-Scripts project now, and I hope the development progress can keep up.

At this point, the study of "what should be paid attention to when using MySQL-Proxy to separate reading and writing" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.