Mysql read write separation

The query request and write request of client accessing data are processed by different database servers respectively.

Why data read-write separation

Reduce the concurrent access pressure of single server and improve the utilization of machine hardware
The server that processes and writes data is the master, and the read data server is the slave.

Configure data read-write separation (Maxscale + master-slave synchronization)

1. Deploy one master (101) and two slaves (102103)

2. Install maxscale-2.1.2-1.rhel.7.x86_.rpm on 150
Modify the configuration file / etc/maxscale.cnf and add two server s

[maxscale]
threads=auto				#Number of threads running

[server1]					#Define database server
type=server
address=10.10.10.101
port=3306
protocol=MySQLBackend		#Back end database
[server2]
type=server
address=10.10.10.102
port=3306
protocol=MySQLBackend
[server3]
type=server
address=10.10.10.103
port=3306
protocol=MySQLBackend

[MySQL Monitor]				#Define the database server for monitoring
type=monitor
module=mysqlmon
servers=server1,server2,server3		#The monitored database list cannot write ip
user=scalemon				#The user name of the connection when monitoring the database server: scalemon
passwd=123456				#Password 123456
monitor_interval=10000		#The frequency of monitoring is in seconds

[Read-Write Service]		#Define read write separation service
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscaled				#User name verifies the existence of the user accessing the database server when connecting to the agent service
passwd=123456
max_slave_connections=100%

[MaxAdmin Service]			#Define management services
type=service
router=cli

[Read-Write Listener]		#Port number used by the read-write service
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]			#Port number used by management service
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4099					#Add manually. If not specified, the default port is used. After starting the service, you can know what the default port is.

3. Add monitoring users and routing users to the two servers according to the settings of the configuration file

replication slave 	#Monitoring master-slave synchronization		replication client	#Monitoring service status
grant replication slave,replication client on *.* to scalemon@"%" identified by "123456"
grant select on mysql.user to maxscaled@"%" identified by "123456"

4. Start service on 150

Verify authorized users
 Start service: maxscale -f /etc/maxscale.cnf
 Stop service: Kill - 9 maxscale	

5. Test configuration

View management information (150 hosts access themselves)
	maxadmin -uadmin -pmariadb -P4099
	list servers
 Client connection 57 access data

6. Test data read-write separation
Users authorized to access on the primary database
The client connects to the middleware server: mysql-h10.10.10.150-p4006-uyaya8-p123456