Read/write separation of MySQL master-slave copy of database

1 Overview

In general, there is only one primary node, there are multiple from the node, only read operations are supported from the node, and the master node supports write operations.

The realization of read and write separation has two solutions, one in the terminal implementation, the second is to add the middle tier implementation, generally to the client transparent, so it is advisable to add the middle layer to achieve.

There are several software layers in the middle tier:

Mysql-proxy: The subsequent Atlas is based on an improved version of Mysql-proxy, and the Atlas Open source project is still in maintenance.

Amoeba for MySQL: read-write separation, sharding;

Cobar: Shard Framework, based on the amoeba version of the upgrade, based on Java development, so to install the JDK

Mycat: The subsequent version is Oneproxy

Oneproxy: Dual License. There are commercial companies in the maintenance, support to the mysql5.7 version. is a good choice to have open source version

Maxscale: MARIADB is responsible for maintenance, but also dual licensing, but the configuration interface is inconvenient to use.

Proxysql: Is the DBA team developed, high-performance open source MySQL proxy server middle layer, this is a better choice, 8 performance introduction See Official Website: http://www.proxysql.com/. Code hosted on GitHub:

Https://github.com/sysown/proxysql/releases

Alisql: Developed by Ali Corporation. Already open source.

If you do not use the above SQL Agent to achieve read and write separation, because the read-write separation by delay, data inconsistency and other issues, it is recommended to use a dual-master or multi-master model is not required to achieve read-write separation, only need load balancing, using Haproxy, Nginx, LVS, ... and other tools for scheduling, the backend MySQL cluster has the following two scenarios:

Using the PXC tool: Percona XtraDB Cluster

Or use mariadb Cluster, but the cluster may cause deadlocks due to resource contention

Demo Proxysql will be shown here

2 Introduction to Proxysql configuration files

All configurations of proxysql are defined in an admin table. You can change the configuration using SQL statements to change configuration information directly at run time. Authentication and authorization can be implemented by the backend, or in the front-end proxy do check, a limited number of specific accounts placed on the proxy server to achieve authentication, proxy server access back-end server also need to verify that the agent's permissions need to have all account access to the backend permissions

Configuration file/etc/proxysql.cnf is described below

Proxysql: It is recommended to configure Proxysql high availability via keepalive, as long as the Proxysql configuration is the same, because Proxysql has no status

Example configuration:

Datadir= "/var/lib/proxysql" #放置数据

admin_variables= #管理变量

{

admin_credentials= "Admin:admin" #类似varnish的6082端口, here is the listener on port 6032, this interface does not develop to remote access, because the interface is the management interface

Mysql_ifaces= "127.0.0.1:6032;/tmp/proxysql_admin.sock"

}

Mysql_variables= #连接后端服务器的变量, generally only need to change the interfaces parameter, others with default values can be

{

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" #建议监听标准端口

Default_schema= "Information_schema"

stacksize=1048576

server_version= "5.5.30"

connect_timeout_server=3000 #连接后端的超时时长

Monitor_username= "Monitor" #后端主机需要拥有该账号才能监控

monitor_password= "Monitor"

monitor_history=600000 #每个多长时间重连一次

monitor_connect_interval=60000

monitor_ping_interval=10000

monitor_read_only_interval=1500

monitor_read_only_timeout=500

ping_interval_server=120000

ping_timeout_server=500

Commands_stats=true

Sessions_sort=true

connect_retries_on_failure=10 #后端主机错误重试的次数

}

Mysql_servers = #定义mysql服务器主机, each host separated by curly braces

(

{

Address = "172.18.0.67" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3306 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

HostGroup = 0 # no default, required

Status = "ONLINE" # Default:online

Weight = 1 # default:1

Compression = 0 # default:0

}, #注意, the last data item cannot be followed by a comma

{

Address = "172.18.0.68"

Port = 3306

HostGroup = 1

Status = "ONLINE" # Default:online

Weight = 1 # default:1

Compression = 0 # default:0

},

{

Address = "172.18.0.69" #这里架构不一样不用写69, because 69 is the agent at this time, not the backend

Port = 3306

HostGroup = 1

Status = "ONLINE" # Default:online

Weight = 1 # default:1

Compression = 0 # default:0

}

)

Mysql_users: #支持的用户账号, the account defined below is authorized on the back-end MySQL server

(

{

Username = "root"

Password = "mageedu"

Default_hostgroup = 0 #0表示默认发给主节点

max_connections=1000

Default_schema= "MyDB"

Active = 1 #表示用户处于激活状态

}

)

Mysql_query_rules: #定义查询的规则, the order of the rules is critical, the regular expression format is supported,

Note that the following is a general rule that enables

(

{

Rule_id=1

Active=1

Match_pattern= "^select. * for update$"

Destination_hostgroup=0

Apply=1

},

{

rule_id=2

Active=1

Match_pattern= "^select"

Destination_hostgroup=1

Apply=1

}

)

Scheduler= #定义调度器

(

)

Mysql_replication_hostgroups= #真正定义组的用途, such as defining a read-write group

(

{

Writer_hostgroup=0 #定义主组, write function

Reader_hostgroup=1 #定义从组, read function

comment= "Test repl 1" #注释信息

}

)

3 Example Proxysql for read-write separation

192.168.1.75 is a proxy server, in general, production environment agent needs to have two network segment IP, reduce IO pressure, public IP provided to client access, private IP to internal MySQL cluster, but experiment, here configure only one network segment IP

192.168.1.71 primary server, 192.168.1.73 for slave server

75 Installing the Proxysql

[[Email protected] ~] #yum-y install/root/proxysql-1.4.5-1-centos7.x86_64.rpm

Modifying a configuration file

[[Email protected] ~] #vim/etc/proxysql.cnf

Datadir= "/var/lib/proxysql"

admin_variables=

{

Admin_credentials= "Admin:admin"

Mysql_ifaces= "127.0.0.1:6032;/tmp/proxysql_admin.sock"

}

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"

Default_schema= "Information_schema"

stacksize=1048576

server_version= "5.5.30"

connect_timeout_server=3000

Monitor_username= "Monitor"

monitor_password= "Monitor"

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

}

Mysql_servers =

(

{

Address = "192.168.1.71" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3306 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

HostGroup = 0 # no default, required

Status = "ONLINE" # Default:online

Weight = 1 # default:1

Compression = 0 # default:0

},

{

Address = "192.168.1.73" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3306 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

HostGroup = 1 # no default, required

Status = "ONLINE" # Default:online

Weight = 1 # default:1

Compression = 0 # default:0

}

)

Mysql_users:

(

{

Username = "Proxyadm" # no default, required

Password = "pass1234" # Default: '

Default_hostgroup = 0 # default:0

active = 1 # default:1

}

)

Mysql_query_rules:

(

{

Rule_id=1

Active=1

Match_pattern= "^select. * for update$"

Destination_hostgroup=0

Apply=1

},

{

rule_id=2

Active=1

Match_pattern= "^select"

Destination_hostgroup=1

Apply=1

}

)

Scheduler=

(

)

mysql_replication_hostgroups=

(

{

Writer_hostgroup=0

Reader_hostgroup=1

comment= "Test Repl 1"

}

)

On all servers on the backend to authorize the account can be managed, the recommended password does not contain capital letters, possible problems, because the management interface to see the Mysql_users, when the profile is written to the account password is uppercase, this table looked at the lower case letter, so the recommended authorization when the password is lowercase

MariaDB [sunnydb]> Grant All on * * to ' proxyadm ' @ ' 192.168.1.% ' identified by ' pass1234 ';

Start the Proxysql service

[[Email protected] ~] #systemctl start Proxysql

View status

[[Email protected] ~] #service proxysql status;

Connecting the Service Interface 3306

View Proxysql's data

[[Email protected] ~] #ls/var/lib/proxysql/

Test, connect MySQL interface, Server version:5.5.30 (Proxysql)

[[Email protected] ~] #mysql-uproxyadm-ppass1234-h 192.168.1.75

Or, open Management interface Server version:5.5.30 (proxysql admin Module)

MySQL [sunny]> show databases;

The primary server node of the connection, this is the default group definition in the configuration, at which point the write is dispatched to the master node, and the read operation is on the slave server

Note that due to read and write separation, if the table in MySQL read server (from) can be read out, it must be in MySQL from the server has corresponding libraries and tables, if there is a corresponding content from the server, the master server can only be seen table, but cannot be select out of the content

Connection Management Interface 6302

[[Email protected] ~] #mysql-S/tmp/proxysql_admin.sock-uadmin-padmin;

After connecting the Proxysql, you can see the related tables and libraries with show databases and show tables, but the data is simulated

such as running MySQL [(none)]> select * from Mysql_servers;

You can see the Mysql_servers configuration section configured in the configuration file can see the data converted to table, so directly modify the tables, the equivalent is to modify the configuration file, directly the runtime takes effect

Read/write separation of MySQL master-slave copy of database