实验环境:CentOS7
设备:一台主数据库服务器,两台从数据库服务器,一台调度器
主从的数据库配置请查阅:http://www.cnblogs.com/wzhuo/p/7171757.html ;
[root@~ localhost]#yum install proxysql-1.3.6-1-centos7.x86_64.rpm mariadb-server #配置proxysql的配置文件 [root@~ localhost]# cat /etc/proxysql.cnf mysql_variables= { #数据库的端口 interfaces="0.0.0.0:3306;/tmp/proxysql.sock" #默认的数据库 default_schema="mydb" #定义需要调度的数据库 mysql_servers = ( #设置三台数据库系统:写的数据库hostgroup为0;读的数据库组为1 { address = "172.16.254.47" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain hostgroup = 0 # no default, required status = "ONLINE" # default: ONLINE weight = 1 # default: 1 compression = 0 # default: 0 # max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned }, { address = "172.16.253.177" port = 3306 hostgroup = 1 }, { address= "172.16.252.182" port= 3306 hostgroup= 1 max_connections=200 } #定义可登陆的用户(需要现在master数据库上进行授权 # 如 MariaDB [hellodb]> grant all on *.* to 'myadmin'@'172.16.%.%' identified by '123456'; #) mysql_users: ( { username = "myadmin" # no default , required password = "123456" # default: '' default_hostgroup = 0 # default: 0 active = 1 # default: 1 default_schema="mydb" } #定义读写的组: mysql_replication_hostgroups= ( { writer_hostgroup=0 reader_hostgroup=1 comment="test repl 1" } [root@~ localhost]#systemctl start proxysql.service [root@~ localhost]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:3306 *:* LISTEN 0 128 127.0.0.1:6032 *:* #测试:使用本机ip登陆: [root@~ localhost]#mysql -h172.16.252.142 -umyadmin -p MySQL [(none)]> create database testtest; MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | | testdb | | testtest #组0的数据库: MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | | testdb | | testtest #组1的数据库; MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | | testdb | | testtest