Considerations when using MySQL-Proxy read/write splitting

Precautions for using MySQL-Proxy read/write splitting before you start to install MySQL-Proxy and configure the MySQL Master/Slave server. Supplement: the new version of MySQL has built-in support for www.2cto.com. One of the issues that cannot be avoided in read/write splitting is latency. You can consider the SemiSyncReplicationDesign patch provided by Google. Port problems 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. The link 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 at this time, 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 in the 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 (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. After www.2cto.com is connected to MySQL-Proxy through garbled query, 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 = gbkskip-character-set-client-handshake, 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. A process crash occurs occasionally on www.2cto.com MySQL-Proxy. 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 yourself), the mysql-proxy process that is started first restarts a mysql-proxy to provide services. But now many people still use the old version of MySQL-Proxy, at this time you can use init to achieve similar keepalive effect: 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 executable attributes: chmod a + x/usr/local/sbin/mysql-proxy.sh 0.7.0 version has a new option: -- defaults-file, you can write the relevant information to the configuration file: # MySQL Proxy's configuration file (mysql-proxy.cnf) [mysql-proxy] daemon = tru Ekeepalive = trueproxy-backend-addresses = 192.168.0.1: hosts = 192.168.0.2: 3306proxy-lua-script =/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua can be used when it is started: mysql-proxy -- defaults-file = mysql-proxy.cnf modify inittab: www.2cto.com vi/etc/inittab Add the following content: mp: 12345: respawn: /usr/local/sbin/mysql-proxy.sh and then let init re-read inittab content: kill-HUP 1 the system will automatically detect/usr/local/sbin/mysql-p Whether roxy. sh is running. If not, it runs automatically. 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 (init: Id "mp" respawning too fast: disabled for 5 minutes) are left in/var/log/message ). The init method may seem a little different. You can use other tools, such as svscan. Some special stateful queries may fail, for example, SELECT SQL _CALC_FOUND_ROWS .. SELECT FOUND_ROWS () This query is stateful, should ensure that the processing at the same backend, view the rw-splitting.lua script can see that MySQL-Proxy has actually been on such a query to judge, 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: www.2cto.com INSERT... (AUTO_INCREMENT) SELECT LAST_INSERT_ID () when the system executes the INSERT statement and then executes the SELECT statement, it may have been distributed to different backend servers. If you use PHP as the programming language, in this case, you should use mysql_insert_id () to obtain the latest inserted id. After each INSERT operation, the corresponding autoincrement value is calculated and returned to PHP, you can directly use mysql_insert_id () without sending an independent query. 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 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. Script problem 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 the use may appear: ERROR 1105: can't change DB to on slave this problem occurs because 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 block 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 and277 c. default_db and c. default_db ~ = "" And c. default_db ~ = S. default_db thenif is_debug278 print ("server default db :".. s. default_db) 279 print ("client default db :".. c. default_db) 280 print ("syncronizing") end281 proxy. queries: prepend (2, string. char (proxy. COM_INIT_DB ).. c. default_db) www.2cto.com 282 end in lua ,~ = Is not equal. In addition, the Null String "" in lua 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 version of MySQL-Proxy, although the source code contains a rw-splitting.lua script, but the default is not installed, You need to manually copy, and the data structure has changed, the script needs to be modified according to the changes in the data structure. You can refer to the author's description, or refer to official Bug management or direct download. It is worth looking forward to seeing a special MySQL-Proxy-Lua-Scripts project, hoping that the development progress can be followed up. Author root_zhang