MySQL database master-slave copy read-write separation--proxysql

This experiment uses a master one from one agent, three devices, the specific steps are as follows:

1, complete the configuration of master-slave replication, please refer to this article http://panpangao.blog.51cto.com/10624093/1981418

2. Install the Proxysql package on the proxy server proxysql-1.4.2-1-centos7.x86_64.rpm

RPM-QL proxysql #软件安装后生成的文件/etc/init.d/proxysql/etc/proxysql.cnf #代理的所有配置全在此文件下修改, it is important to understand the meaning of each of these fields/usr/bin/proxysql #单独的一个服务/usr/share/proxysql/tools/proxysql_galera_checker.sh/usr/share/proxysql/tools/proxysql_galera_ writer.pl

3. Modify the Proxysql configuration file on the proxy server

Vim /etc/proxysql.cnfdatadir= "/var/lib/proxysql" #代理服务器的数据目录, this directory contains proxysql log files, process files, database files Admin_variables ={        admin_credentials= "Admin:admin"          mysql_ifaces= "127.0.0.1:6032;/tmp/proxysql_admin.sock"} #此配置段定义proxysql的管理信息, Only the Admin user can manage and only operate natively, the listening port is 6032 and also listens for sockets mysql_variables={         threads=4          max_connections=2048           default_query_delay=0         default_query_timeout=36000000        have_compress=true         poll_timeout=2000         Interfaces= "0.0.0.0:3306;/tmp/mysql.sock"   //listen to 3306 ports of all IP addresses on this machine while listening to native sockets          default_schema= "InformAtion_schema "        stacksize=1048576         server_version= "5.5.30"         connect_timeout_ server=3000        monitor_history=600000         monitor_connect_interval=60000        monitor_ ping_interval=10000        monitor_read_only_interval=1500         monitor_read_only_timeout=500         ping_interval_server_msec=120000        ping_timeout_server=500         commands_stats=true         sessions_sort=true        connect_retries_on_failure=10}# This configuration segment is a variable definition of the data and is generally kept to a default value Mysql_serverS = (        {                 address =  "192.168.1.101"   #后端主服务器地址                  port = 3306                #  back-end server ports                  hostgroup =  0             #  Host Server group, not the same as from the server                 status =   "ONLINE"          # default: ONLINE                 weight = 1                 # default: 1                 compression = 0            # default: 0         },                  {                 address= "192.168.1.106"                  port=3306                 hostgroup=1                 status =  "ONLINE"                  weight = 1                 compression = 0        }) #此配置段定义后端MySQL服务器信息, Each server is configured in a curly brace, and the different server curly brackets are separated by commas mysql_users: (        {                 username =  "Dbadmin"    # no default , required                 password =  "CentOS"     #  default:  '                  default_hostgroup = 0  # default: 0, the main mysql                 active = 1         # default: 1        }) #此配置段指明访问代理时连接数据库的账号信息, to authorize this user on the MySQL server in advance, Do not repeat how to authorize the Operation Mysql_query_rules: (        {                 rule_id=1                 active=1                 match_pattern= "^SELECT .* FOR  update$ "  #当查询任何条目且以UPDATE结尾的语句, the agent will leave this operation to the primary MySQL server for processing                  destination_hostgroup=0                 apply=1         },        {                 rule_id=2                 active=1                 Match_pattern= "^select"      #当以SELECT语句开始进行查询操作, the agent will take this action from MySQL processing                  destination_hostgroup=1                 apply=1         }) #此配置段指明对数据库查询的规则mysql_replication_hostgroups = (         {                 writer_hostgroup=0                 reader_hostgroup=1                 comment= "Test repl 1"    #仅是提示信息而已        }) #本配置段实现读写分离, The read operation is given to the master MySQL processing from the MySQL processing, the write operation

To complete the Proxysql configuration, start the service

Service Proxysql startss-ntlp# At this time can see 3306 ports and 6032 ports is open, 3306 is used to connect the database processing data, 6032 in the local management database Mysql-s/tmp/proxysql_ admin.sock-uadmin-padmin# This operation to manage the database

4. Testing

In order to visually display the Proxysql agent to master MySQL with the effect from MySQL, we follow the steps below to test:

1) Configure the copy filter from the MySQL server, please refer to this article for details http://panpangao.blog.51cto.com/10624093/1981556

SET @ @global. replicate_ignore_db=mydb; #从服务器上在执行SQL线程时就不会从中继日志中重放此数据库, that is, the database is not on the server # The purpose of this step is to fail when the Select command is executed from the server so that the Proxysql dispatch can see the effect

2) Create the MyDB database on the primary server and generate the appropriate tables

Create DATABASE MyDB;  Use MyDB;   CREATE TABLE s1 (id int, name varchar); INSERT into S1 values (1, ' Tom '); select * from S1; #创建数据库, table, insert field, view table contents # on the primary server The SELECT statement is able to execute

3) in the client test

mysql -udbadmin -pcentos -h192.168.1.107# This account password is the Proxysql configuration segment ' mysql_users ' defined mysql [(none)] > use mydb;database changedmysql [mydb]> show tables;+----------------+|  tables_in_mydb |+----------------+| s1              |+----------------+1 row in set  (0.00 sec) # Enter the database we created to find the S1 table mysql [mydb]> select id,name from s1; error 1049  (42000): unknown database  ' MyDB ' #但是在执行SELECT查询时却显示无mydb数据库, This means that Proxysql has dispatched the request to MySQL, and the server is copied to filter out the mysql [mydb]> select id,name from s1;+---- --+------+| id   | name |+------+------+|    1 |  tom  |+------+------+1 row in set  (0.00 sec) #但是我们将指令换成小写就可以查看 because the configuration segment Mysql_ Query_rules is defined in the start of a select dispatch to slave server note is uppercase # that is, when we use a lowercase select query statement, PROXYSQL does not recognize but dispatches toOn the primary server, so you can query to 

Through the above test, we realized the read and write separation function of master-slave replication.

This article from "A_pan" blog, declined reprint!

MySQL database master-slave copy read-write separation--proxysql