MySQL Series 8------read/write separation

First, Preface: The basic principle of reading and writing separation

The basic principle of MySQL's read-write separation is to have master (the primary database) respond to transactional operations,

Let slave (from the database) respond to select non-transactional operations,

Then, master-slave replication is used to synchronize transactional operations on master to the slave database. achieve simple load balancing.

Second, pre-preparatory work:

1, prepare two servers, I am prepared here is 192.168.4.122 (master), 192.168.4.123 (from)

Also prepare a server to install the middleware server (192.168.4.125)

2, the first two servers to do the master-slave synchronization.

3, ready to read and write the Separation software: maxscale-2.1.2-1 (middleware)

Three, Maxscale installation and configuration (4.125)

1. Installation: RPM-IVH maxscale-2.1.2-1.rhel.7.x86_64.rpm

2, modify the configuration file: vim/etc/maxscale.cnf

54-60 lines commented out, 87-91 commented out

Ten Threads=auto

[Server1]

Type=server

address=192.168.4.122

port=3306

Protocol=mysqlbackend

[Server2]

Type=server

Address=192.168.4.123

port=3306

Protocol=mysqlbackend

[MySQL Monitor]//monitoring Database configuration

Approx. type=monitor

Panax Notoginseng Module=mysqlmon

Servers=server1, Server2

User=scalemon//monitoring

passwd=123456

monitor_interval=10000

[read-write Service]//Configuration query read-write access to the account

Type=service

Router=readwritesplit

Servers=server1, Server2

User=maxscale//When receiving a client connection request, the connected user name and password are present on the database service

passwd=123456

max_slave_connections=100%

104 port=4010

3. Add the required two authorized users on the main library (4.122)

Grant replication Slave,replication Client on * * to [e-mail protected] '% ' identified by ' 123456 ';

Grant Select on mysql.* to [email protected] '% ' identified by "123456";

4. Starting Service: Maxscale-f/etc/maxscale.cnf

Stop service: ps-c maxscale (check process) kill-9 13109 (Kill process)

To see if the service started: Netstat-natulp | grep Maxscale

5. Add a user name for the client Connection server on the main library (4.122)

Grant all on * * to [e-mail protected] '% ' identified by ' 123456 ';

6, on 4.125: maxadmin-p4010-uadmin-pmariadb//access control background

List servers//display all server hosts, you can see the server running information

7, using the host to do client-side connection middleware Landing method:

mysql-h192.168.4.53-p4006-utest-p123456

8. test method: The client can be written and readable when it is removed from the wall;

But when the master hangs, the client is unreadable and not writable.

MySQL Series 8------read/write separation