maxscale+mariadb5.5如何实现读写分离-创新互联

本篇文章给大家分享的是有关maxscale + mariadb5.5如何实现读写分离,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。

创新互联公司-专业网站定制、快速模板网站建设、高性价比汨罗网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式汨罗网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖汨罗地区。费用合理售后完善,十多年实体公司更值得信赖。

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

2、添加访问用户

mysql> create user xingzhenxiang identified by '111111';
Query OK, 0 rows affected (0.04 sec)

mysql> grant all privileges on . to xingzhenxiang;

3、格式化密码,没有这么配置密码的时候,好像连接有问题
maxkeys /var/lib/maxscale/
maxpasswd /var/lib/maxscale/ 111111
不同机器得到的格式串不一样
44F2F27B48309A316C0B02046796B505 a机器
28C52C82EA2328A7853B450734B5CEB3 b辑器

chown maxscale.maxscale /var/lib/maxscale/.secrets
systemctl restart maxscale
4、编辑配置文件
[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、安装sysbench
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
6、压测

[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、我的压测
[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/656.41/327.31) 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/555.20/278.20) 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: 1969.68/561.77/282.18) 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/467.41/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/451.79/227.80) 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/463.90/231.30) 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/453.11/227.21) 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/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/477.02/238.61) 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/460.87/230.58) 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/438.24/219.22) 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/450.79/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.7000/7.23
execution time (avg/stddev):  120.1151/0.00

8、主从状态,内部机制自动识别主从,并不是以server1为主
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>

以上就是maxscale + mariadb5.5如何实现读写分离,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。

另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。


本文标题:maxscale+mariadb5.5如何实现读写分离-创新互联
标题路径:http://myzitong.com/article/pssgd.html