MySQL read-write separation

MySQL read-write separation

Today we present two tools for MySQL read-write separation:

1. Proxysql

2. MySQL Router

Premise: Implement Master-slave replication

172.16.75.1 host as primary MySQL server (master);

172.16.75.2 host as slave MySQL server (Slave1);

172.16.1.12 host as slave MySQL server (Slave2);

Master configuration file for master:

[Email protected] ~]# vim/etc/my.cnf [mysqld] .... Innodb_file_per_table=on skip_name_resolve = on expire_logs_days=15 log_bin=binlog server_id=199 sync_binlog=1 Inn Odb_flush_log_at_trx_commit=1

Slave1 Main configuration file:

[Email protected] ~]# vim/etc/my.cnf [mysqld] .... Innodb_file_per_table = on skip_name_resolve = on expire_logs_days=15 server_id=200 read_only=on Relay_log=slave Log

Slave2 Main configuration file:

[Email protected] ~]# vim/etc/my.cnf [mysqld] .... Innodb_file_per_table = on skip_name_resolve = on expire_logs_days=15 server_id=201 read_only=on Relay_log=slave Log

When you are finished configuring the three host services to open:

[Email protected] ~]# systemctl start Mariadb.service

Back up all databases from the primary server to two slave servers:

   [[email protected] ~]# mysqldump --all-databases --lock-all-tables  > alldb.sql   [[email protected] ~]# scp alldb.sql [ Email protected]:/root/    [email protected] ' s password:      alldb.sql                                                      100% 1689kb  10.6mb/s   00:00       [[ email protected] ~]# scp alldb.sql [email protected]:/root/     [email protected] ' s password:     alldb.sql                                                      100% 1689kb  35.3mb/s   00:00

Save all data from the primary server from the server:

[[email protected] ~]# MySQL MariaDB [(none)]> \. Alldb.sql

To see if the data is consistent:

Primary server:

Two slave servers:

After the data is consistent, perform the following actions:

1. The primary server assigns replication permissions to a user and sets the password:

2. Specify your own master from the server

3. The primary server creates a database to see if it is synchronized from the server;

1. Master:

To view the current status of master:

Authorized:

MariaDB [(None)]> grant replication Slave on * * to ' James ' @ ' percent ' identified by ' YTC '; Query OK, 0 rows affected (0.01 sec)

2. Slave: (two Slave perform the same operation)

Specify Master:

MariaDB [(none)]> change master to master_host= ' 172.16.75.1 ', master_user= ' James ', master_password= ' YTC ', master_ Port=3306,master_log_file= ' binlog.000042 ', master_log_pos=387;

View slave Status:

3. Create a database named Wade on the primary server and view it from the server side:

1) Master:

MariaDB [(None)]> create Database Wade; Query OK, 1 row Affected (0.00 sec)

2) Slave View:

Synchronous implementation of master-slave database;

Next, read-write separation is achieved:

1. Proxysql

1) installation (downloaded from the official website): Installed on the 172.16.1.14 host;

[Email protected] ~]# RPM-IVH proxysql-1.4.9-1-centos7.x86_64.rpm

2) Open service:

[[Email protected] ~]# service Proxysql start starting proxysql:done!

3) Connect the MySQL client tool to the Proxysql management interface:

The default administrator account and password are "admin" and the port is 6032:

[Email protected] ~]# mysql-uadmin-padmin-h127.0.0.1-p6032

4) Insert the Monitoring node into the proxysql (MySQL server):

That is, the 172.16.75.1,172.16.75.2,172.16.1.12 host, the group ID is "23":

MySQL [(None)]> insert into mysql_servers (Hostgroup_id,hostname,port) VALUES (23, ' 172,16.75.1 ', 3306), (23, ' 172.16.75.2 ', 3306), (23, ' 172.16.1.12 ', 3306);

5) Create a monitoring user and set the appropriate password:

1. Master-Side authorized users:

MariaDB [(None)]> grant replication client,replication slave on * * to ' monitor ' @ ' 172.16.%.% ' identified by ' YTC '; Query OK, 0 rows affected (1.32 sec)

2. Proxysql write the master-side authorized monitoring user and password to the Global_variables table:

MySQL [(None)]> set mysql-monitor_username= ' monitor ';  Query OK, 1 row Affected (0.00 sec) MySQL [(None)]> set mysql-monitor_password= ' YTC '; Query OK, 1 row Affected (0.00 sec)

6) Let the previously added backend MySQL server node and the user monitoring each node take effect:

MySQL [(none)]> load MySQL servers to runtime;  Query OK, 0 rows Affected (0.00 sec) MySQL [(none)]> save MySQL servers to disk;  Query OK, 0 rows affected (0.12 sec) MySQL [(none)]> load MySQL variables to runtime;  Query OK, 0 rows Affected (0.00 sec) MySQL [(none)]> save MySQL variables to disk; Query OK, affected rows (0.01 sec)

7) Set the Write group ID to "23" and the ID of the read group to "24";

and load the modified data into runtime and persist storage to disk, and let the configuration take effect:

MySQL [(None)]> insert into mysql_replication_hostgroups (Writer_hostgroup,reader_hostgroup) values (23,24); Query OK, 1 row Affected (0.00 sec) MySQL [(none)]> load MySQL servers to runtime; Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> save MySQL servers to disk; Query OK, 0 rows affected (0.02 sec)

8) View the READ_ONLY properties of the packet and back-end MySQL server:

Can be seen:

172.16.75.1 server is automatically divided into the write group (hostgroup_id:23), read_only=0, writable;

172.16.75.2 and 172.16.1.12 were divided into the reading Group (hostgroup_id:24), Read_only=1, read only;

So read and write separation is realized, slave only readable, master can write;

You can also send requests from different users to the specified back-end MySQL server:

For example: send a request named "VIP" user sent to the write group, and the master server;

Send a request called "other" user to the Read group, and the slave server;

1. Create the appropriate user on the master server:

MariaDB [(None)]> grant all on * * to ' VIP ' @ ' 172.16.%.% ' identified by ' 123 '; Query OK, 0 rows affected (0.84 sec) MariaDB [(none)]> grant all on * * to ' other ' @ ' 172.16.%.% ' identified by ' 123 '; Query OK, 0 rows affected (0.01 sec)

2. Add the user information created by master on Proxysql to the Mysql_users table,

and set the default group:

Send the VIP user's request settings to the write group,

Send the other user's request settings to the Read group,

MySQL [(None)]> insert into mysql_users (Username,password,default_hostgroup) VALUES (' VIP ', ' 123 ', 23), (' Other ', ' 123 ', 24); Query OK, 2 rows Affected (0.00 sec)

3. After adding the user, the modified data needs to be loaded into runtime and persisted to disk;

MySQL [(none)]> load MySQL users to runtime; Query OK, 0 rows Affected (0.00 sec) MySQL [(none)]> save MySQL users to disk; Query OK, 0 rows affected (0.02 sec)

Available MySQL [(none)]> select * from mysql_users\g; View user-related situation;

Test:

The VIP user's query request is sent to the master write group;

The other user's query request is sent to the slave read-only group;

Example:

Send the modification request to the write group and send the query request to the Read group:

 mysql [(None)]> insert into mysql_query_rules  (Rule_id,active,match_digest, destination_hostgroup,apply)                    values  (^select.*for update$ ', 23,1),  (2,1, ' ^select ', 24, 1);                     // rule_id : Rule number, auto-growing integer, can not specify whether the;  // active:  rule is valid, default value is 0, indicates invalid , it needs to be set to the specific match of the 1;  // match_digest:  definition rule when the rule is defined, and consists of regular expression metacharacters to match the SQL statement;  //  DESTINATION_HOSTGROUP&NBSP: For a rule-compliant request, set the target host group to implement route forwarding;   // apply: valid commit; The default value is 0, which means that when you define a rule, Set its value to 1;   mysql [(none)]> load mysql query rules to  runtime; query ok, 0 rows affected  (0.00 sec)  MySQL [(none)]>  save mysql query rules to disk; query ok, 0 rows affected  (0.16 SEC) 

Test:

Such as:

The query request is sent to the read group (Slave);

The modification request is sent to the write group (Master);

2. MySQL Router

1) Modify the master configuration file:

[Email protected] ~]# vim/etc/mysqlrouter/mysqlrouter.conf [DEFAULT] Logging_folder =/var/log/mysqlrouter/plugin_ folder =/usr/lib64/mysqlrouter Runtime_folder =/var/run/mysqlrouter Config_folder =/etc/mysqlrouter [logger] level = in fo [keepalive] interval = [Routing:masters] bind_address = 172.16.1.14:40081 Destinations = 172.16.75.1:3306 mode = RE Ad-write connect_timeout = 2 [routing:slaves] bind_address = 172.16.1.14:40082 Destinations = 172.16.75.2:3306,172.16.1.12:3306 mode = read-only Connect_timeout = 1

2) Start the service:

[Email protected] ~]# systemctl start Mysqlrouter

3) Test:

The graph shows:

Requests that come in from Port 40081 are sent to master;

Requests coming in from Port 40082 are sent to slave;

This realizes the reading and writing separation;

MySQL read-write separation