MySQL replication implementation, read-write separation and installation failure

Note: This is an installation failure version, so the Mysql-proxy installation process is not necessary to see.

I've already talked about MySQL master-slave synchronization.

Atlas

Official documents: http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html

mysql-proxy-0.8.1 Write host also provides read

mysql-proxy-0.8.2 Write host Simple write

Using Mysql-proxy to realize read-write separation

Http://www.infoq.com/cn/news/2007/10/mysqlproxyrwsplitting

In order to achieve read/write separation we need to connect the pool. We switch to the back end only if a certified connection to a backend has been opened. The MySQL protocol first shakes hands. It is too late to authenticate the new connection when entering into the query/return result phase. We have to make sure that we have enough open connections to stay on track.

--read-write separation--
  sends all non-transactional Select to a from the database
  if is_in_transaction = 0 and
     packet:byte () = = Proxy.com_query and
     packet:sub (2, 7) = = "Select" Then local
    Max_conns =-1 local
    max_conns_ndx = 0
    for i = 1, #proxy. servers Do local
      s = proxy.servers[i]
      --Select a from database
      if S.type = = Proxy with an idle connection. Backend_type_ro and
         s.idling_connections > 0 Then
        if Max_conns = = 1 or
           s.connected_clients < Max_ Conns then
          Max_conns = s.connected_clients
          max_conns_ndx = I end end
    -- We found a free connection from the database
    if max_conns_ndx > 0 then
      proxy.connection.backend_ndx = Max_conns_ndx End
  else
    -sends to the primary database end return
  proxy. Proxy_send_query

One, what is MySQL Proxy?

MySQL Proxy is a network protocol that leverages MySQL over the network and provides one or more MySQL servers with one or more MySQL clients to communicate with each other. In most basic configurations, Mysqlproxy simply passes queries from the client to the MySQL server and the MySQL server answers to the client.

The reason for this is that MySQL Proxy uses MySQL's network protocol, which is compatible with any MySQL client (including command-line clients, any client developed using the MySQL client library, any MySQL connector, support for MySQL's network protocol), Can be connected to a proxy server without modification. In addition to basic delivery, the MySQL Proxy has the ability to monitor and change the conversation between the client and the server. MySQL Proxy uses the scripting language of Lua to write the appropriate script and intercepts the corresponding query request based on the definition of the script.

By intercepting the corresponding query request from the client, MySQL Proxy can insert some query requests before the query queue is sent to the server, or delete those corresponding replies in the reply returned by the server. This feature allows you to keep track of each query and get reports. For example, monitoring its execution time or other debugging information, recording the results separately, and returning the answer to the client correctly. To sum up, MySQL Proxy allows you to perform additional monitoring, filtering, or manipulating queries, and you do not need to make any changes at the client, since all this is transparent to both the client and the user, and they will think they are connected to a true MySQL server.

Client--> Mysql-proxy-->db
Client-->db

MySQL: Command options and configuration file syntax

Http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-configuration.html

MySQL Proxy Scripting:

Http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-scripting.html

15.7.4.3. Capturing a Connection with Connect_server ()
15.7.4.4. Examining the handshake with read_handshake ()
15.7.4.5. Examining the authentication Credentials with Read_auth ()
15.7.4.6. Accessing authentication information with Read_auth_result ()
15.7.4.7. Manipulating Queries with Read_query ()
15.7.4.8. Manipulating Results with Read_query_result ()

Mysql Proxy Use:

Http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-using.html

Management interface:

Http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-using-admin.html

FAQ:

Http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-faq.ht