MySQL master-slave (MySQL proxy lua read and write separation settings, a master multiple from the synchronization configuration, the sub-database sub-table scheme)

Mysql Proxy Lua Read and write separation settings

I. Read-Write separation instructions

1. Read-Write separation (Read/write splitting), the basic principle is to let the main database processing transaction increment, change, delete operation (INSERT, UPDATE, delete), and from the database processing select query operation. Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

1. Setting instructions

1. Master server: 192.168.41.196

2. Slave Server: 192.168.41.197

3. Proxy Server: 192.168.41.203

2. Install MySQL Proxy

Install on the proxy server. If the source mode installation, need to install PKG-CONFIG,LIBEVENT,GLIBC,Lua and other dependent packages, very troublesome, it is recommended to use the two-in-plate directly.

1. # Cd/u01/software/mysql

2. # TAR-ZXVF Mysql proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz-c/usr/local

3. # cd/usr/local

4. # ln-s MySQL proxy-0.8.1-linux-rhel5-x86-32bit mysql Proxy

5. # VI + ~/.bash_profile

6. Export path= $PATH:/usr/local/mysql proxy/bin/

7. # . ~/.bash_profile

3. Mysql Proxy Option Description

1. # Mysql Proxy Help-all

Management feature Options:

1. admin-address=host:port Specifies a mysqo-proxy management port, the default is 4041;

2. Admin-username=<string> Username to allow to log in

3. Admin-password=<string> Password to allow to log in

4. admin-lua-script=<filename> Script to execute by the admin plugin

Agent feature options:

2. -P, proxy-address=<host:port> is the listening port on MySQL proxy server, the default is 4040;

4. -R, proxy-read-only-backend-addresses=<host:port> read-only slave address and port, default is not set;

6. -B, proxy-backend-addresses=<host:port> Remote Master Address and port, can be set multiple do failover and load balance,  The default is 127.0.0.1:3306;

8. Proxy-skip-profiling Turn off the query analysis function, the default is open;

10. proxy-fix-bug-25371 fix MySQL Libmysql version greater than 5.1.12 of a #25371 bug;

12. -S, proxy-lua-script=<file> Specify a LUA script to control the running and setting of the MySQL proxy, which will be recalled each time a new connection is created and the script changes;

Other options:

1. defaults-file=<file> configuration files, you can put the parameter information of MySQL proxy into a configuration file;

2. Daemon Mysql proxy runs as daemon

3. Pid-file=file setting the path of the storage PID file for MySQL proxy

4. KeepAlive try to restart the proxy if it crashed, keep the connection start process will have 2, a process used to monitor process second, if the second process died automatically restart proxy.

4. Database Preparation Work

(1) Install the semi-synchronous patch (recommended)

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

(2) authorization to the user

Establish a test user in Master/slave, since the SQL sent by the client is forwarded via the MySQL proxy server, so make sure you can log in to the MySQL master/slave library from the MySQL proxy server.

1. MySQL> Grant all privileges on * * to ' u_test ' @ ' 192.168.41.203 ' identified by ' xxx ' with GRANT option;

(3) Set up a test table in master

1. MySQL> CREATE table db_test.t_test (col varchar (10));

2. MySQL> INSERT into db_test.t_test values (' TestA ');

3. MySQL> select * from Db_test.t_test;

4. +-+

5. | Col |

6. +-+

7. | TestA |

8. +-+

5, Mysql proxy start

(1) Modifying the read-write separation Lua script

The default minimum of 4 maximum of 8 client connections will be read and write separated, now to the minimum 1 maximum of 2:

2. # VI +40/usr/local/mysql proxy/share/doc/mysql Proxy/rw-splitting.lua

4. Connection pool

6. If not Proxy.global.config.rwsplit then

8. Proxy.global.config.rwsplit = {

10. min_idle_connections = 1,

12. max_idle_connections = 2,

14. Is_debug = true

16. }

18. End

This is because the MySQL proxy detects the client connection and does not read and write when the connection does not exceed the Min_idle_connections preset, that is, the query operation takes place on master.

(2) Start MySQL Proxy

It is recommended to start with a configuration file, note that the profile must be 660 permissions, or it will not start. If there are multiple slave, the proxy-read-only-backend-addresses parameter can be configured with multiple comma-separated ip:port from the list of libraries.

2. # Killall Mysql Proxy

4. # Vi/etc/mysql PROXY.CNF

6. [Mysql Proxy]

8. Admin-username=WANGNC

10. admin-password=IAMWANGNC

12. Admin-lua-script=/usr/local/mysql Proxy/lib/mysql Proxy/lua/admin.lua

14. proxy-backend-addresses=192.168.41.196:3351

16. proxy-read-only-backend-addresses=192.168.41.197:3351

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

20. Log-file=/var/tmp/mysql Proxy.log

22. log-level=Debug

24. Daemon=True

26. Keepalive=True

28. # chmod 660/etc/mysql proxy.cnf

30. # Mysql Proxy defaults-file=/etc/mysql proxy.cnf

32. # Ps-ef | grep Mysql Proxy | Grep-v grep

34. Root 1869 1 0 18:16? 00:00:00/usr/local/mysql proxy/libexec/mysql Proxy defaults-file=/etc/mysql proxy.cnf

36. Root 1870 1869 0 18:16? 00:00:00/usr/local/mysql proxy/libexec/mysql Proxy defaults-file=/etc/mysql proxy.cnf

38. # Tail-50f/var/tmp/mysql Proxy.log

6. Client Connection Test

(1) Stop the slave replication process first

1. MySQL> Stop slave;

(2) Connect the proxy port and insert the data

1. # Mysql-uu_test-pxxx-h192.168.41.203-p4040-ddb_test

2. MySQL> INSERT into db_test.t_test values (' Testb ');

3. MySQL> select * from Db_test.t_test;

4. +-+

5. | Col |

6. +-+

7. | TestA |

8. | Testb |

9. +-+

(3) Open more than a few clients, connect proxy port, query data

1. # Mysql-uu_test-pxxx-h192.168.41.203-p4040-ddb_test

2. MySQL> select * from Db_test.t_test;

3. +-+

4. | Col |

5. +-+

6. | TestA |

7. +-+

If the query is not up to the newly inserted data, it is connected to the slave, and the read-write separation succeeds. Insert the data again on the same thread and verify that:

1. MySQL> INSERT into db_test.t_test values (' TESTC ');

2. MySQL> select * from Db_test.t_test;

3. +-+

4. | Col |

5. +-+

6. | TestA |

7. +-+

The insert operation was found to be successful, but the select does not have the data just inserted, indicating that the same thread also read and write separated successfully. From the log you can verify:

2. # Tail-50f/var/tmp/mysql Proxy.log

4. ...

6. [Read_query] 192.168.41.203:45481

8. Current backend = 0

10. Client default db = db_test

12. Client username = u_test

14. query = SELECT * from Db_test.t_test

16. Sending to backend:192.168.41.197:3351

18. Is_slave:true

20. Server default Db:db_test

22. Server Username:u_test

24. In_trans:false

26. In_calc_found:false

28. Com_query:true

30. [Read_query] 192.168.41.203:45481

32. Current backend = 0

34. Client default db = db_test

36. Client username = u_test

38. query = INSERT INTO db_test.t_test values (' TESTC ')

40. Sending to backend:192.168.41.196:3351

42. Is_slave:false

44. Server default Db:db_test

46. Server Username:u_test

48. In_trans:false

50. In_calc_found:false

52. Com_query:true

(4) After the test is complete, start the slave replication process

1. MySQL> start slave;

7. Formal Environmental Statement

1, MySQL proxy is still a beta version, MySQL official is not recommended to use the production environment;

2, Mysql proxy Rw-splitting.lua script on the Internet has many versions, but the most accurate version is still the source package included in the rw-splitting. Lua scripts, if there is a Lua script programming basis, can be optimized on the basis of this script;

3, Mysql Proxy is actually very unstable, in the case of high concurrency or faulty connection, the process is easy to automatically shut down, so open the KeepAlive parameter so that the process of automatic recovery is a better way, but still can not fundamentally solve the problem, Therefore, it is usually the most prudent to install a MySQL proxy on each slave server for its own use, although relatively inefficient but can guarantee stability;

4, Amoeba for MySQL is a good middleware software, the same can be read and write separation, load balancing and other functions, and stability to much more than the MySQL proxy, we recommend that you use to replace the MySQL proxy, or even mysql-cluster.

Second, MySQL one master many from the synchronization configuration

1. Configuration (same as on Platform 3)

There may be no my.cnf files in/etc directory, copy my-medium.cnf to/etc from the/user/share/mysql directory and modify to MY.CNF

[Email protected] etc]# cp/usr/share/mysql/my-medium.cnf my.cnf

[email protected] etc]# ll |grep my

-rwxr-xr-x 1 root root 5204 Feb 22:52 My_bak

-rwxr-xr-x 1 root root 4765 Jul 23:07 my.cnf

on master;

[Email protected] ~]# VI/ETC/MY.CNF

1. Modify the configuration file my.cnf on master.

Under [Mysqld], add the following fields:

Server-id = 1

Log-bin=mysql-bin

BINLOG-DO-DB=YYY//databases that need to be synchronized

Binlog-ignore-db=mysql//Ignored database

Binlog-ignore-db=information-schema//Ignored database

Add a sync account to slave on master

mysql> grant replication Slave on * * to ' affairlog ' @ ' 192.168.2.182 ' identified by ' pwd123 ';

Successful landing on the slave1

mysql> grant replication Slave on * * to ' affairlog ' @ ' 192.168.2.111 ' identified by ' pwd123 ';

Successful landing on the Slave2

After saving, restart Master's MySQL service:

service MySQL restart;

View log conditions with the show Master Status command

Mysql> Show Master Status\g;

1. Row ***************************

file:mysql-bin.000087

position:106

Binlog_do_db:yyy

Binlog_ignore_db:mysql,information-schema

1 row in Set (0.00 sec)

2. Modify the configuration file my.cnf on the slave1.

Add the following fields under [Mysqld]

[Email protected] ~]# VI/ETC/MY.CNF

server-id=182

Master-host=192.168.3.101

Master-user= Affairlog

Master-password=pwd123

master-port=3306

Master-connect-retry=60

REPLICATE-DO-DB=YYY//Synchronized database

Replicate-ignore-db=mysql//Ignored database

Replicate-ignore-db=information-schema//Ignored database

View the file name and Master_log_pos for Master_log_file on master.

Mysql>change Master to master_host= ' 192.168.3.101 ' master_user= ' affairlog ' master_password= ' pwd123 ' Master_log_ File= ' mysql-bin.000087 ' master_log_pos=1845;

After saving, restart the slave MySQL service:

service MySQL restart;

Modify the configuration file on the Slave2 my.cnf, similar to the above, just to change the Server-id, in order to facilitate, I have used the corresponding IP a bit,

The Server-id I set on So,slave2 is 111.

Go to MySQL in two slave machines.

Mysql>start slave;

Mysql>show slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:192.168.3.101

Master_user:affairlog

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000087

read_master_log_pos:106

relay_log_file:vm111-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000087

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:yyy

Replicate_ignore_db:mysql,information-schema

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:106

relay_log_space:406

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

If the slave_io_running and slave_sql_running states in the two Slave are yes, the setting is successful.

Slave_io_running: Connect to the main library and read logs from the main library to local, generate local log files

Slave_sql_running: Reads the local log file and executes the SQL command in the log.

Third, MySQL sub-database sub-table scheme: https://my.oschina.net/ydsakyclguozi/blog/199498

MySQL master-slave (MySQL proxy lua read and write separation settings, a master multiple from the synchronization configuration, the sub-database sub-table scheme)