Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Install mysql-proxy to achieve mysql read-write separation

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report