MySQL master-slave replication, read-write separation, middleware usage record

--This article will introduce how to configure Mysql master-slave synchronization and read-write separation, and use the middleware ProxySQL to achieve more efficient database access.

Server Information#

Mysql

Master Server (A): 192.168.1.1
Backup Server (B): 192.168.1.2
Backup Server (C): 192.168.1.3
Middleware

Middleware Server (D): 192.168.1.4

Mysql Master-Slave Synchronization and Read-Write Separation Configuration#

Master Database Configuration#

Execute the following commands on the master server (A):
`# Create slave access account
grant replication slave on . to 'slave'@'%' identified by '123456';

Check the master server status
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 438 | | | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
`

Slave Server Configuration#

Execute the following commands on the slave server:

Stop the slave server#

stop slave;

Configure the master server information#

change master to master_host='192.168.1.1', master_user='slave', master_password='123456', master_log_file='mysql-bin.000006', master_log_pos=438;

Start the slave server

start slave;

Check the slave server status

show slave status\G;

If the slave server status displays the following content, it means the startup was successful:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

ProxySQL Configuration#

Installation
Go to [https://github.com/sysown/proxysql/releases] to download the ProxySQL installation package.
Upload the installation package to the server and execute the following command to install:

yum -y localinstall proxysql-2.5.5-1-centos7.x86_64.rpm

Verify the Installation Status#

Execute the following command to verify the ProxySQL installation status:

proxysql -V
ProxySQL version 2.5.5-10-g195bd70, codename Truls

Start the Service#

Execute the following command to start the ProxySQL service:

systemctl start proxysql.service

Enter the ProxySQL Local Server#

Execute the following command to enter the ProxySQL local server:

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

Add Master and Slave Servers#

Execute the following command in the ProxySQL local server to add master and slave servers:

insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(1, '192.168.1.1', 3306, 1, ' Master ');
insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(2, '192.168.1.2', 3306, 5, ' Slave 1 ');
insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(2, '192.168.1.3', 3306, 5, ' Slave 2 ');

Add Master and Slave Accounts (ProxySQL)#

Execute the following command in the ProxySQL local server to add master and slave accounts:

insert into mysql_users(username, password, default_hostgroup, transaction_persistent) values('proxysql', '123456', 1, 1);

Add Rule Routes#

Execute the following command in the ProxySQL local server to add rule routes:

insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, log, apply) values(1, 1, '^UPDATE', 1, 1, 1);
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, log, apply) values(2, 1, '^SELECT', 2, 1, 1);

Check Connection Status and Hit Rate#

Execute the following command in the ProxySQL local server to check the connection status and hit rate:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
select * from stats_mysql_query_rules;
select * from stats_mysql_query_digest;

With the above configuration and commands, you can achieve Mysql master-slave synchronization and read-write separation, and use the ProxySQL middleware to optimize database access. Other users can learn and understand how to configure and use these features from this article.