Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use proxysql 1.4.14 middleware to achieve the read-write separation of mysql 5.7.26 master and slave. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Preparation condition

Operating system redhat 6.9

Database mysql 5.7.26

Middleware proxysql 1.4.14

Mysql is configured to have one master and one slave. For more information, please see below.

The overall architecture of database read-write separation

Numbered server role ip address port

1 proxysql 10.0.0.13 6032 033 (Note: 6032 is the management port of proxysql, 6033 is the port of proxysql external service)

2 mysql main library 10.0.0.11 3306

3 mysql slave 10.0.0.12 3306

Configuration database read-write separation

1. Log in to the middleware proxysql

[root@mysqlclient] # mysql-uadmin-padmin-h227.0.0.1-P6032

2, configure different host groups for read-write separation, 10 for mysql master library, 20 for mysql slave library

Mysql > insert into mysql_replication_hostgroups values (10, 10, 20, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 10, 20, 10, 10, 10, 10, 20, 10, 10, 10, 10, 20, for msyql primary replication', for msyql primary replication').

Query OK, 1 row affected (0.00 sec)

Mysql > select * from mysql_replication_hostgroups

+-+

| | writer_hostgroup | reader_hostgroup | comment | |

+-+

| | 10 | 20 | use for msyql primary replication |

+-+

1 row in set (0.00 sec)

3. Log in to the mysql master library to create a database user monitor that is used to monitor whether the mysql master-slave library read_only is read-only

Mysql > grant replication client on *. * to 'monitor'@'10.0.0.13' identified by' monitor'

Query OK, 0 rows affected (0.02 sec)

4. Log in to mysql to confirm that read_only=on is read-only.

Mysql > show global variables like 'read_only'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_only | ON |

+-+ +

1 row in set (0.00 sec)

5. Login middleware proxysql to configure database users and passwords for monitoring mysql

(note: corresponding to the database user configured in step 3 above)

Mysql > select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password')

+-+ +

| | variable_name | variable_value |

+-+ +

| | mysql-monitor_password | monitor |

| | mysql-monitor_username | monitor |

+-+ +

2 rows in set (0.00 sec)

-- if the configured database user password is incorrect, adjust it according to the actual situation

Mysql > set mysql-monitor_username='monitor'

Query OK, 1 row affected (0.00 sec)

Mysql > set mysql-monitor_password='monitor'

Query OK, 1 row affected (0.00 sec)

-- persistence

Mysql > load mysql variables to runtime

Query OK, 0 rows affected (0.00 sec)

Mysql > save mysql variables to disk

Query OK, 97 rows affected (0.02 sec)

6. Configure the corresponding relationship between the mysql master-slave node and the host group

(note: different mysql nodes correspond to different host groups to achieve read-write separation)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 10.0.0.11pr 3306)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (20meme 10.0.0.12pr 3306)

Query OK, 1 row affected (0.00 sec)

Persistence

Mysql > load mysql servers to runtime

Query OK, 0 rows affected (0.01 sec)

Mysql > save mysql servers to disk

Query OK, 0 rows affected (0.03 sec)

Mysql > select * from mysql_servers

+ -+

| | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+ -+

| | 10 | 10.0.0.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| | 20 | 10.0.0.12 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+ -+

2 rows in set (0.00 sec)

7. Log in to the mysql main library to create a database user for read-write separation

-- read and write database users

Mysql > grant all on *. * to rwuser@'10.0.0.11' identified by 'system'

Query OK, 0 rows affected (0.02 sec)

-- read-only database users

Mysql > grant all on *. * to rouser@'10.0.0.12' identified by 'system'

Query OK, 0 rows affected (0.01 sec)

8. Login middleware proxysql configures the corresponding relationship between database users and host groups, that is, different database users can be directed to different mysql master and slave nodes

Mysql > insert into mysql_users (username,password,default_hostgroup) values ('rwuser','system',10)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_users (username,password,default_hostgroup) values ('rouser','system',20)

Query OK, 1 row affected (0.00 sec)

-- persistence

Mysql > load mysql users to runtime

Query OK, 0 rows affected (0.00 sec)

Mysql > save mysql users to disk

Query OK, 0 rows affected (0.02 sec)

Mysql > select * from mysql_users

+-+ -+

| | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |

+-+ -+

| | rwuser | system | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |

| | rouser | system | 1 | 0 | 20 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |

+-+ -+

2 rows in set (0.00 sec)

9. The client uses different database user login middleware to realize the read-write separation of mysql master and slave libraries.

(note: rwuser database users access mysql master library, rouser database users access mysql slave library)

[root@mysqlclient proxydir] # mysql-urwuser-psystem-h227.0.0.1-P6033-e'select @ @ server_id'

Warning: Using a password on the command line interface can be insecure.

+-+

| | @ @ server_id |

+-+

| | 1 |

+-+

[root@mysqlclient proxydir] # mysql-urouser-psystem-h227.0.0.1-P6033-e'select @ @ server_id'

Warning: Using a password on the command line interface can be insecure.

+-+

| | @ @ server_id |

+-+

| | 2 |

+-+

[root@mysqlclient proxydir] #

On "how to use proxysql 1.4.14 middleware to achieve mysql 5.7.26 master-slave reading and writing separation" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.