• mariadb的读写分离


    实验环境: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 
  • 相关阅读:
    Eureka相关相关接口和代码位置
    Zookeeper(4)---ZK集群部署和选举
    Zookeeper(3)---java客户端的使用
    Zookeeper(2)---节点属性、监听和权限
    玩转百度地图API(地图,坐标,标记,添加控件,2D图,混合图,智能搜索,地址解析器,信息窗口)
    HTML+CSS系列:CSS选择器(标签、ID、类、通配符、后代、子元素、并集、伪类)
    Git系列:常用命令
    Linux系列:快捷键、目录结构、用户目录
    mybatis-plus系统化学习之更新-AR-主键-service
    mybatis-plus系统化学习之查询专题
  • 原文地址:https://www.cnblogs.com/wzhuo/p/7173595.html
Copyright © 2020-2023  润新知