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

This article is to share with you about maxscale + mariadb5.5 how to achieve the separation of reading and writing, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Installation

[root@localhost] # yum-y localinstall maxscale-2.3.5-1.centos.7.x86_64.rpm

2. Add access users

Mysql > create user xingzhenxiang identified by '111111'

Query OK, 0 rows affected (0.04 sec)

Mysql > grant all privileges on. To xingzhenxiang

3. Format the password. When the password is not configured in this way, there seems to be a problem with the connection.

Maxkeys / var/lib/maxscale/

Maxpasswd / var/lib/maxscale/ 111111

The format strings obtained by different machines are different.

44F2F27B48309A316C0B02046796B505 a machine

28C52C82EA2328A7853B450734B5CEB3 b editor

Chown maxscale.maxscale / var/lib/maxscale/.secrets

Systemctl restart maxscale

4. Edit the configuration file

[root@localhost maxscale] # cat / etc/maxscale.cnf

[maxscale]

Threads=auto

Log_info=1

Log_warning=1

[server1]

Type=server

Address=192.168.204.130

Port=3306

Protocol=MariaDBBackend

[server2]

Type=server

Address=192.168.204.129

Port=3306

Protocol=MariaDBBackend

[MariaDB-Monitor]

Type=monitor

Module=mariadbmon

Servers=server1,server2

User=xingzhenxiang

Passwd=28C52C82EA2328A7853B450734B5CEB3

Monitor_interval=2000

Detect_replication_lag=true

Detect_stale_master=true

[Read-Write-Service]

Type=service

Router=readwritesplit

Servers=server1,server2

User=xingzhenxiang

Passwd=28C52C82EA2328A7853B450734B5CEB3

Max_slave_connections=100%

Max_slave_replication_lag=5

Use_sql_variables_in=all

Enable_root_user=1

Log_auth_warnings=1

[MaxAdmin-Service]

Type=service

Router=cli

[Read-Write-Listener]

Type=listener

Service=Read-Write-Service

# address=192.168.204.129

Protocol=MariaDBClient

Port=4006

[MaxAdmin-Listener]

Type=listener

Service=MaxAdmin-Service

Protocol=maxscaled

Socket=default

[root@localhost maxscale] #

5. Install sysbench

Curl-s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

6. Pressure test

[root@localhost] # sysbench/ usr/share/sysbench/tests/include/oltp_legacy/oltp.lua-mysql-host=192.168.204.129-- mysql-port=4006-- mysql-user=xingzhenxiang-- mysql-password=111111-- oltp-test-mode=complex-- oltp-tables-count=10-- oltp-table-size=100000-- threads=10-- time=120-- report-interval=10 run

7. My stress test

[root@localhost] # sysbench/ usr/share/sysbench/tests/include/oltp_legacy/oltp.lua-mysql-host=192.168.204.129-- mysql-port=4006-- mysql-user=xingzhenxiang-- mysql-password=111111-- oltp-test-mode=complex-- oltp-tables-count=10-- oltp-table-size=100000-- threads=10-- time=120-- report-interval=10 run

Sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 10

Report intermediate results every 10 second (s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[10s] thds: 10 tps: 163.15 qps: 3281.87 (r/w/o: 2298.15 tps) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00

[20s] thds: 10 tps: 139.10 qps: 2780.82 (r/w/o: 1947.42 tps 555.20 ms,95%) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00

[30s] thds: 10 tps: 141.09 qps: 2813.63 (r/w/o: 561.77 reconn/s: 2813.63) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00

[40s] thds: 10 tps: 115.85 qps: 2326.65 (r/w/o: 1627.53 Universe 467.41 reconn/s 231.71) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00

[50s] thds: 10 tps: 113.95 qps: 2268.95 (r/w/o: 1589.37) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00

[60s] thds: 10 tps: 115.60 qps: 2317.01 (r/w/o: 1621.81) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00

[70s] thds: 10 tps: 113.60 qps: 2270.16 (r/w/o: 1589.84) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00

[80s] thds: 10 tps: 117.40 qps: 2349.56 (r/w/o: 1644.67 reconn/s 470.09 + 234.80) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00

[90s] thds: 10 tps: 119.30 qps: 2385.28 (r/w/o: 1669.66 reconn/s 477.02) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00

[100s] thds: 10 tps: 115.29 qps: 2305.23 (r/w/o: 1613.78) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00

[110s] thds: 10 tps: 109.61 qps: 2190.48 (r/w/o: 1533.02 tps) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00

[120s] thds: 10 tps: 111.70 qps: 2242.33 (r/w/o: 1568.15 tps 450.79 reconn/s 223.39) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00

SQL statistics:

Queries performed:

Read: 206738

Write: 59068

Other: 29534

Total: 295340

Transactions: 14767 (122.93 per sec.)

Queries: 295340 (2458.60 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 120.1241s

Total number of events: 14767

Latency (ms):

Min: 14.39

Avg: 81.34

Max: 1064.40

95th percentile: 227.40

Sum: 1201151.18

Threads fairness:

Events (avg/stddev): 1476.7000Universe 7.23

Execution time (avg/stddev): 120.1151ax 0.00

8. Master-slave state, the internal mechanism automatically identifies the master-slave, not server1-based

MaxScale > list servers

Servers.

-+-

Server | Address | Port | Connections | Status

-+-

Server1 | 192.168.204.130 | 3306 | 0 | Master, Running

Server2 | 192.168.204.129 | 3306 | 0 | Slave, Running

-+-

MaxScale >

MaxScale > list servers

Servers.

-+-

Server | Address | Port | Connections | Status

-+-

Server1 | 192.168.204.129 | 3306 | 0 | Slave, Running

Server2 | 192.168.204.130 | 3306 | 0 | Master, Running

-+-

MaxScale >

This is how maxscale + mariadb5.5 achieves the separation of reading and writing. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.