Install mysql-proxy to achieve mysql read-write separation

one。 Environment description

Operating system version: centos 7.2

Master server: 192.168.0.72 node2

Slave server: 192.168.0.73 node3

Scheduling server MySQL-Proxy:192.168.0.71 node1

two。 Configure mysql master to install mysql from 2.1

Both node2 and node3 need to be installed

[lirou@node2 ~] $sudo yum-y install mariadb-server

2.2 modify the configuration file

Master profile:

Skip_name_resolve

Innodb_file_per_table

Server_id = 1

Log_bin = / var/lib/mysql/bin_log

Slave profile:

Skip_name_resolve

Innodb_file_per_table

Server_id = 2

Log_bin = / var/lib/mysql/bin_log

Read_only

Note: the master server must turn on binary logging because master-slave replication depends on master. And the server_id of the master and slave servers must be different.

2.3 start the service

Both node2 and node3 need to start the service

[lirou@node3 ~] $sudo systemctl start mariadb

2.4 configure master and slave servers

On the Master server

Assign the account number of permission for slave hosts to access master:

[lirou@node2 ~] $mysql-uroot

MariaDB [(none)] > GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *. * TO 'repl_user'@'192.168.0.%' IDENTIFIED BY' repl_pass'

View the binary log location on master

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | binlog.000001 | 425 |

+-+

On the slave server

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.72', MASTER_USER='repl_user', MASTER_PASSWORD='repl_pass', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=425

Mysql > slave start; # enable slave synchronization

Mysql > show slave status\ G; # check whether the synchronization is successful

three。 Configure mysql-proxy3.1 download installation package

~] # wget wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

~] # sudo tar xf https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz-C / usr/local

~] # mv / usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit / usr/local/mysql-proxy

3.2 configure mysql-proxy

Create a lua script directory

~] # cd / usr/local/mysql-proxy

Mysql-proxy] # mkdir lua

Create a log directory

Mysql-proxy] # mdkir logs

Copy read-write separation configuration files and administrative scripts to the lua script directory

Mysql-proxy] # cp share/doc/mysql-proxy/rw-splitting.lua. / lua

Mysql-proxy] # cp share/doc/mysqp-proxy/admin-sql.lua. / lua

Create a profile

~] # vim / etc/mysql-proxy.conf

[mysql-proxy]

User = root the user who runs the agent

Admin-username = mysql_proxy_user # mysql-proxy users who connect to the back-end mysql server

Admin-password = mysql_proxy_pass # password for mysql-proxy to connect to the backend mysql server

Proxy-address = 192.168.0.71 IP and port listened for by 3307 # mysql-proxy. Default is 4040.

Proxy-read-only-backend-addresses = 192.168.0.73 # read-only service

Proxy-backend-addresses = 192.168.0.72 # read-write server

Proxy-lua-script = / usr/local/mysql-proxy/lua/rw-splitting.lua # indicates read-write separation configuration file

Admin-lus-script = / usr/local/mysql-proxy/lua/admin-sql.lua # manage script path

Log-file = / usr/local/mysql-proxy/logs/mysql-proxy.log # Log file path

Log-level = debug # logging level

Daemon = whether the true # program is started on the backend

Keepalive = true # mysql-proxy after a crash, try to restart

Modify read-write configuration file

Vim / usr/local/mysql-proxy/lua/rw-splitting.lua

If not proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1, # when the number of connections exceeds 4 by default, read-write separation begins, instead of 1.

Max_idle_connections = 1, # default 8, changed to 1

Is_debug = false

}

Start mysql-proxy

~] # / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/etc/mysql-proxy.cnf

Users who create mysql-proxy connections on mysql

Mysql > GRANT ALL ON *. * TO 'mysql_proxy_user'@'192.168.0.%' IDENTIFIED BY' mysql_proxy_pass'

four。 Test read-write separation

Test writing

Mysql-umysql_proxy_user-pmysql_proxy_pass-h292.168.0.71-- port=3307

Mysql > create databases master

Mysql > create table test (id int)

Mysql > insert into master.test value (1)

View the slave server:

You can see the synchronization from the server.

Test reading

Number of query statements on the main server

Query the number of statements from server

Connect mysql-proxy to execute a read statement

Number of query statements on the main server

Query the number of statements from server

We can see that the query statement is executed on the slave server.