How to separate read and write from maxscale + mariadb5.5
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.
