mysql负载均衡-读写分离-ProxySQL

实验：读写分离
四台服务器
两台跑数据库node1写node2读
ygl搭建proxysql
最后一台当客户端

首先，同步所有时间
这里实验使用的proxysql包是

proxysql-1.4.2-1-centos7.x86_64.rpm

首先这是接着上次的半同步复制继续做的。
node1

MariaDB [(none)]> grant all on *.* to 'dbadmin'@'172.18.25.%' identified by 'dbpass';MariaDB [(none)]> flush privileges;

修改配置ygl上面的配置

[ root@YGL /etc ]# cat proxysql.cnfdatadir="/var/lib/proxysql"admin_variables={    admin_credentials="admin:admin"    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"#   mysql_ifaces="0.0.0.0:6032"#   refresh_interval=2000#   debug=true}mysql_variables={    threads=4    max_connections=2048    default_query_delay=0    default_query_timeout=36000000    have_compress=true    poll_timeout=2000#   interfaces="0.0.0.0:6033;/tmp/proxysql.sock"#   interfaces="0.0.0.0:6033"    interfaces="0.0.0.0:3306;/tmp/mysql.sock"    default_schema="information_schema"    stacksize=1048576    server_version="5.5.30"    connect_timeout_server=3000# make sure to configure monitor username and password# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password    monitor_username="monitor"    monitor_password="monitor"    monitor_history=600000    monitor_connect_interval=60000    monitor_ping_interval=10000    monitor_read_only_interval=1500    monitor_read_only_timeout=500    ping_interval_server_msec=120000    ping_timeout_server=500    commands_stats=true    sessions_sort=true    connect_retries_on_failure=10}# defines all the MySQL serversmysql_servers =(    {        address = "172.18.25.51" # 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.18.25.52" # 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 = 1           # 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 = "/var/lib/mysql/mysql.sock"#       port = 0#       hostgroup = 0#   },#   {#       address="127.0.0.1"#       port=21891#       hostgroup=0#       max_connections=200#   },#   { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },#   { address="127.0.0.1" , port=21892 , hostgroup=1 },#   { address="127.0.0.1" , port=21893 , hostgroup=1 }#   { address="127.0.0.2" , port=3306 , hostgroup=1 },#   { address="127.0.0.3" , port=3306 , hostgroup=1 },#   { address="127.0.0.4" , port=3306 , hostgroup=1 },#   { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 })# defines all the MySQL usersmysql_users:(    {        username = "dbadmin" # no default , required        password = "dbpass" # default: ''        default_hostgroup = 0 # default: 0        active = 1            # default: 1    }#   {#       username = "root"#       password = ""#       default_hostgroup = 0#       max_connections=1000#       default_schema="test"#       active = 1#   },#   { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 })#defines MySQL Query Rulesmysql_query_rules:(    {        rule_id=1        active=1        match_pattern="^SELECT .* FOR UPDATE$"        destination_hostgroup=0        apply=1    },    {        rule_id=2        active=1        match_pattern="^SELECT"        destination_hostgroup=1        apply=1    })scheduler=(#  {#    id=1#    active=0#    interval_ms=10000#    filename="/var/lib/proxysql/proxysql_galera_checker.sh"#    arg1="0"#    arg2="0"#    arg3="0"#    arg4="1"#    arg5="/var/lib/proxysql/proxysql_galera_checker.log"#  })mysql_replication_hostgroups=(        {                writer_hostgroup=0                reader_hostgroup=1                comment="production repl cluster"       }#       {#                writer_hostgroup=50#                reader_hostgroup=60#                comment="test repl 2"#        })

然后尝试启动服务

[ root@YGL /etc ]# service proxysql startStarting ProxySQL: DONE!        #正常启动方式

到这里读写分离就成功了