Considerations for using MySQL Proxy Lua read/write splitting

UseMySQL ProxyRead/writeSeparationNote: This article will introduce the content. You 'd better install it before you start the operation.MySQL-ProxyAnd configure the MySQL master-slave server. Supplement: New VersionMySQLBuilt-in support

Latency problems

Read/writeSeparationOne of the unavoidable problems is latency. You can consider the SemiSyncReplicationDesign patch provided by Google.

Port Problems

MySQL-ProxyPort 4040 is used 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 
 
 

To delete this rule, replace-I in the preceding example with-D.

Password Encryption Method

MySQL-Proxy does not support the old password encryption method. Therefore, if you use the old MySQL version or enable the old_passwords option, an error may occur:

 
 

  
  
ERROR 2013: Lost connection to MySQL server 
 
 

In this case, the best solution is to use the new password encryption method. If your user table is old-fashioned, you may need to run the mysql_fix_privilege_tables script in the scripts directory of MySQL source code to upgrade the table structure. In some cases, you may not be allowed to upgrade immediately. In this case, you can create a user with an empty password for MySQL-Proxy to restrict access through the host, or create a complicated user name ), no matter the new or old password encryption method, the empty password is also an empty string, which avoids the password encryption problem.

Query garbled characters

After connecting to MySQL-Proxy, garbled characters are randomly displayed during query. This problem occurs because when we use MySQL-Proxy for read/write splitting, there are usually multiple backend Servers. When the client sends a query request, generally, a statement similar to "set name gbk" is issued to declare the client encoding, and then the actual query SQL statement is issued, however, MySQL-Proxy may distribute these two statements to different backend servers, so garbled characters are generated.

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 must note that the Operation user cannot have the SUPER permission. Otherwise, this option is invalid.

Even after the above settings are completed, garbled characters may occur. For example, if the database is gbk and PHPMyAdmin is used to connect to MySQL-Proxy, garbled characters may still occur during queries, but this is normal! Because PHPMyAdmin uses utf8 encoding, the "set names utf8" statement sent by PHPMyAdmin is blocked by skip-character-set-client-handshake, so garbled code occurs.

Process crash

MySQL-Proxy occasionally crashes. The specific cause is unknown.

The new version of MySQL-Proxy adds a keepalive option try to restart the proxy if it crashed to cope with this problem). When this option is used, two mysql-proxy processes are successively started, the mysql-proxy process that is started first is used to monitor the mysql-proxy process that is started later. What actually provides services is the mysql-proxy process that is started later, once the mysql-proxy process that is started later crashes, you can kill it by yourself.) the mysql-proxy process that is started first restarts a mysql-proxy to provide services.

However, many people use the old version of MySQL-Proxy. In this case, init can be used to implement keepalive-like effects:

Write the script/usr/local/sbin/mysql-proxy.sh, add the following content 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 the executable attributes:

 
 

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

In version 0.7.0, there is a new option: -- defaults-file, which can write the relevant information to 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 
 
 

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

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 whether/usr/local/sbin/mysql-proxy.sh is running and runs automatically if not.

It should be noted that when writing mysql-proxy.sh scripts, do not add the -- daemon option, otherwise/usr/local/sbin/mysql-proxy.sh will end with a run, the system will keep trying to run the script, therefore, a large number of error messages are left in/var/log/message init: Id "mp" respawning too fast: disabled for 5 minutes ).

The init method may seem a little different. You can use other tools, such as svscan.

Stateful Query

Some stateful special queries may fail, for example:

 
 

  
  
SELECT SQL_CALC_FOUND_ROWS ..  
  
  
SELECT FOUND_ROWS() 
 
 

This kind of query is stateful, it should be ensured that in the same backend processing, view the rw-splitting.lua script can see that MySQL-Proxy has actually been on such a query for the judgment, however, problems still exist in practical applications. It is estimated that the script is not well written. in actual application, we recommend that you do not use such a query. As a result, there is no portability and the efficiency is not good.

Another query that may cause problems is:

 
 

  
  
INSERT ... (AUTO_INCREMENT)  
  
  
SELECT LAST_INSERT_ID() 
 
 

When the system executes the INSERT statement and then runs the SELECT statement, it may have been distributed to different backend servers. If you use PHP, you should use mysql_insert_id () to get the latest inserted id. After each INSERT operation, the corresponding autoincrement value is calculated and returned to PHP. You do not need to issue an independent query and directly use mysql_insert_id () you can. However, many PHP programs use the SELECT LAST_INSERT_ID () method, such as AdbDB and CakePHP. Be careful if you are using them. When using bigint, mysql_insert_id () has a problem. For more information, 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.

LUA script problems

MySQL-Proxy read/write separation function is achieved through the lua script rw-splitting.lua), but this script is out of repair for a long time, a lot of problems, such as use may appear:

ERROR 1105: can't change DB to on slave

The cause of this problem is that when the client sends a query, MySQL-Proxy will compare whether the database of the current client is consistent with the database of the server, if they are inconsistent, a "USE Database" operation will be performed on the server. One possibility is that the database structure of the master and slave servers is different, and errors will naturally occur when you USE a database that does not exist, there is another reason that some query operations do not have the context of the database. For example, the show databases query does not require "USE Database" in advance and can be executed by connecting to the server, at this time, if you still try to synchronize the database on the client and server, errors are unavoidable.

Rw-splitting.lua does not mask the situation described by the latter, the solution is as follows, add the bold code in the appropriate position,

 
 

  
  
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 equal to, in addition,LuaThe empty string "" is considered to be true in if, so c. default_db alone is not enough.

With the judgment of is_debug, some debugging information may occasionally appear in the command line of the server even if it is not in the debug status.

In addition, the new versionMySQL-Proxy, Although the source code contains rw-splitting.LuaScript, but it is not installed by default. You need to manually copy it, and the data structure has changed. The script must be modified according to the changes in the data structure.

Summary: UsageMySQL Proxy LUARead/writeSeparationI hope this article will be helpful to you!