Considerations when using Mysql-proxy to read and write separations

It is best to install the Mysql-proxy first and configure the MySQL master and slave server before doing the operation. Added: New MySQL has built-in support

Latency Issues

one of the problems that can't be avoided with read-write separation is latency, and you can consider semisyncreplicationdesign patches provided by Google.

Port Issues

mysql-proxy Default is 4040 port, if you want to transparently transfer 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 delete this rule, you can change the-I in the example above to-D.

Reference Links

Password Encryption Method

Mysql-proxy does not support old password encryption, so if you are using an older version of MySQL, or if the Old_passwords option is enabled, an error may occur:

ERROR 2013:lost connection to MySQL server

at this point the best way to fix this is to use the new password encryption method, if your user table is old-fashioned, you may need to first run the MySQL source code in the scripts directory of the Mysql_fix_privilege_tables script upgrade table structure. Sometimes the objective situation may not allow the immediate upgrade operation, at this time can be dedicated to Mysql-proxy set up a password for the user (restricted access by the host, or a very complex user name), because whether it is the new password encryption method or the old password encryption method, The empty password is also an empty string, which avoids the problem of password encryption.

Query garbled

when the mysql-proxy is connected, random garbled characters appear when the query is executed. This problem occurs because when we use the Mysql-proxy read-write separation, there are usually multiple back-end servers, when the client makes a query request, it usually emits a statement similar to "SET NAME GBK" to declare the client-side encoding, and then issue the actual query SQL statement, However, Mysql-proxy may distribute these two statements to different backend servers, so there is a garbled message.

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

init-connect= ' SET NAME gbk '
Skip-character-set-client-handshake ignoring the client character set

If you use Init-connect, you need to be aware that the operation user cannot have super privileges, otherwise this option is not valid.

even if you do the above settings, there may be garbled, such as the database is GBK, when we use phpMyAdmin connection Mysql-proxy, the query will appear garbled, but this is normal! Because phpMyAdmin uses UTF8 encoding, it emits a "SET NAMES UTF8" statement that is Skip-character-set-client-handshake masked, so garbled.

Process crashes

Mysql-proxy occasionally occurs when a process crashes, specifically for unknown reasons.

The new version of Mysql-proxy added a keepalive option (try to restart the proxy if it crashed) in order to cope with this problem, and when this option is used, two mysql-proxy processes are started, The mysql-proxy process that starts is used to monitor the mysql-proxy process that is started after the actual service is started after the mysql-proxy process, once the mysql-proxy process starts to hang off (you can kill yourself), The mysql-proxy process that starts first will restart a Mysql-proxy service provider.

but now a lot of people use the old version of the Mysql-proxy, at this time can use the init to achieve similar keepalive effect:

write the script/usr/local/sbin/mysql-proxy.sh, adding the following (depending on the installation situation):

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 the executable attribute:

chmod a+x/usr/local/sbin/mysql-proxy.sh

0.7. Version 0 has a new option:--defaults-file, you can write the relevant information in the configuration file:

# MySQL Proxy ' s configuration file (MYSQL-PROXY.CNF)

[Mysql-proxy]
Daemon = True
KeepAlive = True
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

can be used at startup: Mysql-proxy--defaults-file=mysql-proxy.cnf

Modify Inittab:

Vi/etc/inittab

Add the following content:

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

then let Init re-read the Inittab content:

Kill-hup 1

the system automatically detects if the/usr/local/sbin/mysql-proxy.sh is running and runs automatically if it does not.

Note that when writing the mysql-proxy.sh script, do not add the--daemon option, otherwise the/usr/local/sbin/mysql-proxy.sh run will end, the system will not stop trying to run the script, so that the/var/ Log/message left a lot of error messages (Init:id "MP" respawning too fast:disabled for 5 minutes).

The Init method may seem a bit different, and other tools, such as Svscan, can be used.

stateful Queries

some special queries that are stateful may fail, such as:

SELECT sql_calc_found_rows .
SELECT found_rows ()

This query is stateful and should be guaranteed to be processed at the same backend, and viewing the Rw-splitting.lua script can see that Mysql-proxy has actually judged the query, but there are still problems in the actual application. It is estimated that the script is not well written, in practice, it is recommended that you do not use such a query, one without portability, and efficiency is not necessarily good.

another query that may cause problems is:

INSERT ... (auto_increment)
SELECT last_insert_id ()

when the system executes the INSERT, and then executes select, it may have been distributed to a different back-end server, if you are using a programming language that is PHP, you should use MYSQL_INSERT_ID () to get the latest inserted ID, each time the insert ends, In fact, the corresponding AutoIncrement value has been calculated to return to PHP, you do not have to issue a separate query, directly with the mysql_insert_id () on it. However, many PHP programs use a Select LAST_INSERT_ID () method, such as adbdb,cakephp, and so on, if you are using them, you need to be careful. (When using bigint, mysql_insert_id () has a problem, see the manual for details, but for most people, bigint is basically not met, so you can ignore this problem)

Note: For these two issues, the official bug library has been given a corresponding patch.

Scripting Issues

The mysql-proxy read-write separation function is implemented through LUA scripting (Rw-splitting.lua), but the script is in disrepair and has many problems, such as when used:

ERROR 1105:can ' t change DB to on slave

This problem occurs because when a client issues a query, Mysql-proxy compares the current client's database to the database in which the server is located and, if not, attempts to perform a "use database" operation on the service side. One possibility is that the database structure of the master-slave server is different, the use of a nonexistent database will naturally error, there is a reason some query operations do not have the context of the database, such as show databases this query, do not need to advance "use database", As long as the server can be executed, if you are also trying to synchronize the client and service side of the database, an error is unavoidable.

Rw-splitting.lua exactly does not block the situation described in the latter, fix the method as follows, add the bold code in the appropriate place,

276 if Cmd.type ~= proxy.com_init_db and
277 c.default_db and c.default_db ~= "" and c.default_db ~= s.default_db Then
if Is_debug
278 Print ("Server default DB:"). s.default_db)
279 Print ("Client default db:"). c.default_db)
280 Print ("syncronizing")
End
281 proxy.queries:prepend (2, String.char (proxy.com_init_db): c.default_db)
282 End

in Lua, ~= is not meant to be equal, and in addition, the empty string "" in Lua is considered true in the IF, so the c.default_db alone is not enough.

plus is_debug judgment, or even if it is not the debug state, the server command line will occasionally appear some debugging information.