• 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 
  • 相关阅读:
    洛谷P3165 [CQOI2014]排序机械臂
    splay详解(一)
    存储过程解题
    使用zxing生成二维码
    Cognos 图表用图片取代”没有数据显示”
    哪些响应式布局框架是设计师使用的?
    A shallow summary of oracle log miner
    雅虎UED--无障碍网页设计
    smarty 截取字符串,调用php中的方法,foreach循环
    一个页面重构工程师眼中的“用户体验”
  • 原文地址:https://www.cnblogs.com/wzhuo/p/7173595.html
Copyright © 2020-2023  润新知