Shulou(Shulou.com)06/01 Report--

Preface: the basic principle of the separation of reading and writing

The basic principle of mysql's read-write separation is to let master (master 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.

2. Preparatory work:

1. Prepare two servers. What I prepare here is 192.168.4.122 (master) and 192.168.4.123 (slave).

Prepare another server to install the middleware server (192.168.4.125)

2. First of all, synchronize the two servers.

3. Prepare the software for read-write separation: maxscale-2.1.2-1 (middleware)

III. Installation and configuration of maxscale (4.125)

1. Install: rpm-ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

2. Modify the configuration file: vim / etc/maxscale.cnf

Comment out lines 54-60, comment out 87-91

10 threads=auto

18 [server1]

19 type=server

20 address=192.168.4.122

21 port=3306

22 protocol=MySQLBackend

23 [server2]

24 type=server

25 address=192.168.4.123

26 port=3306

27 protocol=MySQLBackend

35 [MySQL Monitor] / / Monitoring the configuration of the database

36 type=monitor

37 module=mysqlmon

38 servers=server1, server2

39 user=scalemon / / Monitoring

40 passwd=123456

41 monitor_interval=10000

63 [Read-Write Service] / / configure the account to query read and write permissions

64 type=service

65 router=readwritesplit

66 servers=server1, server2

67 user=maxscale / / whether the user name and password of the connection exist on the database service when receiving the client connection request

68 passwd=123456

69 max_slave_connections=100%

104 port=4010

3. Add the above two authorized users to the main library (4.122)

Grant replication slave,replication client on *. * to scalemon@'%' identified by "123456"

Grant select on mysql.* to maxscale@'%' identified by "123456"

4. Start the service: maxscale-f / etc/maxscale.cnf

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

Check whether the service is started: netstat-natulp | grep maxscale

5. Add the user name used for the client to connect to the server on the main library (4.122)

Grant all on *. * to student@'%' identified by '123456'

6. On 4.125: maxadmin-P4010-uadmin-pmariadb / / access control backend

List servers / / shows all server hosts, and you can see the running information of the server.

7. The login method of using host as client connection middleware:

Mysql-h292.168.4.125-P4006-utest-p123456

8. Test method: when the slave is hung up, the client can write and read

But when the master hangs up, the client cannot be read or written

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.