mysql+proxysql+replication-manager的主从半同步复制+高可用+读写分离

环境:

AlmaLinux release 9.1

MySQL Community Server Ver 8.0.33

Replication Manager v2.2.40 for MariaDB 10.x and MySQL 5.7 Series

ProxySQL version 2.5.1-90-gbedaa6c

主机分配情况:

采用hyper-v创建虚拟机的方式进行的,创建1台模板之后另外3台导入虚拟机复制。

1、安装mysql

mysql8的默认加密插件变为了caching_sha2_password需要修改成mysql_native_password,因为proxysql不支持caching_sha2_password

安装完成后在server01、02、03上创建以下用户

rep-manager供replication-manager使用,repl供主从复制使用

mysql> create user 'rep-manager'@'%' identified by 'your password';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'repl'@'%' identified by 'your password';
Query OK, 0 rows affected (0.02 sec)

mysql> create user 'proxysql'@'%' identified by 'your password';
Query OK, 0 rows affected (0.02 sec)

mysql> grant process,replication slave,replication client on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,replication slave,replication client,reload,super on *.* to 'repl'@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'rep-manager'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 

2、复制虚拟机

3、在server00上安装replication-manager

4、在server00上安装proxysql

5、启动mysql,创建主从半同步复制

半同步需要安装插件,在主库和从库上都安装

mysql> install plugin rpl_semi_sync_source soname 'semisync_source.so';
mysql> install plugin rpl_semi_sync_replica soname 'semisync_replica.so';
mysql> show plugins;
+----------------------------------+----------+--------------------+---------------------+---------+
| Name                             | Status   | Type               | Library             | License |
+----------------------------------+----------+--------------------+---------------------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL                | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL                | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL                | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL                | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL                | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL                | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL                | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL                | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL                | GPL     |
| rpl_semi_sync_source             | ACTIVE   | REPLICATION        | semisync_source.so  | GPL     |
| rpl_semi_sync_replica            | ACTIVE   | REPLICATION        | semisync_replica.so | GPL     |
+----------------------------------+----------+--------------------+---------------------+---------+
50 rows in set (0.00 sec)
要想永久启用半同步需将配置写入my.cnf看下面的配置即可。
在主库上查看是否启用了半同步
 mysql> show variables like 'rpl_semi%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_replica_enabled               | OFF        |
| rpl_semi_sync_replica_trace_level           | 32         |
| rpl_semi_sync_source_enabled                | ON         |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+
从库上
mysql> show variables like 'rpl_semi%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_replica_enabled               | ON         |
| rpl_semi_sync_replica_trace_level           | 32         |
| rpl_semi_sync_source_enabled                | ON         |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+
8 rows in set (0.01 sec)

master主机mysql配置my.cnf如下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_authentication_plugin=mysql_native_password
######replication settings######
server-id=1001
log-bin=mysql-bin
binlog_format=row

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

######gtid#######
gtid_mode=on
enforce_gtid_consistency=on
binlog_gtid_simple_recovery=on

relay_log_recovery = ON
relay-log-index=mysql-relay
relay-log=mysql-relay

rpl_semi_sync_source_enabled = ON
rpl_semi_sync_replica_enabled = ON log_slow_replica_statements
= 1

slave主机myql的配置如下:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_authentication_plugin=mysql_native_password
######replication settings######
server-id=1002
log-bin=mysql-bin
binlog_format=row
log_slave_updates=on
relay_log_recovery=1

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

######gtid#######
gtid_mode=on
enforce_gtid_consistency=on
binlog_gtid_simple_recovery=on

relay_log_recovery = ON
relay-log-index=mysql-relay
relay-log=mysql-relay

rpl_semi_sync_source_enabled = ON
rpl_semi_sync_replica_enabled = ON
log_slow_replica_statements = 1

read_only = on

启动主从

mysql> CHANGE REPLICATION SOURCE to SOURCE_HOST='server01.mshome.net',SOURCE_USER='repl',SOURCE_PASSWORD='your password',SOURCE_PORT=3306;
Query OK, 0 rows affected, 3 warnings (0.03 sec)

mysql> start replica;
Query OK, 0 rows affected (0.02 sec)

mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: server01.mshome.net
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000009
          Read_Source_Log_Pos: 157
               Relay_Log_File: mysql-relay.000010
                Relay_Log_Pos: 373
        Relay_Source_Log_File: mysql-bin.000009
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 157
              Relay_Log_Space: 795
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1001
                  Source_UUID: 590af332-e04e-11ed-8935-00155d030202
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 1
            Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified
 

server00主机上安装了replication-manager和proxysql

replication-manager的配置如下

查看replication-manager配置文件cluster1.toml
cat
/etc/replication-manager/cluster.d/cluster1.toml
[cluster1]
title = "cluster1"
prov-orchestrator = "onpremise"
prov-db-tags = "innodb,noquerycache,slow,pfs,pkg,linux,smallredolog,logtotable"
prov-db-memory = "256"
prov-db-memory-shared-pct = "threads:16,innodb:60,myisam:10,aria:10,rocksdb:1,tokudb:1,s3:1,archive:1,querycache:0"
prov-db-disk-size = "1"
prov-db-cpu-cores = "1"
prov-db-disk-iops = "300"

db-servers-hosts = "server01.mshome.net:3306,server02.mshome.net:3306,server03.mshome.net:3306"
db-servers-prefered-master = "server01.mshome.net:3306"
db-servers-credential = "rep-manager:yourpassword"
db-servers-connect-timeout = 5
replication-credential = "rep-manager:yourpassword"


verbose = false
log-failed-election  = true
log-level = 1
log-rotate-max-age = 7
log-rotate-max-backup = 7
log-rotate-max-size = 5
log-sql-in-monitoring   = true
log-sst = true

##############
## TOPOLOGY ##
##############

replication-multi-master = false
replication-multi-tier-slave = false

############
# BACKUPS ##
###########


backup-streaming = false
backup-streaming-aws-access-key-id = "admin"
backup-streaming-aws-access-secret = "xxxx"
backup-streaming-endpoint= "https://s3.signal18.io/"
backup-streaming-region= "fr-1"
backup-streaming-bucket= "repman"

#####这里可以设置备份工具restic,工具可以跨服务器,结合minio备份就很爽了,后续再介绍吧 backup-restic = false backup-restic-aws = false backup-physical-type = "mariabackup" backup-logical-type = "mysqldump" backup-restic-aws-access-secret = "xxxx" backup-restic-password = "xxxx" backup-restic-binary-path = "/usr/bin/restic" monitoring-scheduler = true scheduler-db-servers-logical-backup = true scheduler-db-servers-logical-backup-cron= "0 30 23 * * 6" scheduler-db-servers-logs = false scheduler-db-servers-logs-cron = "0 0 * * * *" scheduler-db-servers-logs-table-keep = 4 scheduler-db-servers-logs-table-rotate = false scheduler-db-servers-logs-table-rotate-cron = "0 0 0/6 * * *" scheduler-db-servers-optimize = false scheduler-db-servers-optimize-cron = "0 0 3 1 * 5" scheduler-db-servers-physical-backup = true scheduler-db-servers-physical-backup-cron = "0 30 23 * * *" ############## ## FAILOVER ## ############## failover-mode = "manual" failover-pre-script = "" failover-post-script = "" ## Slaves will re enter with read-only failover-readonly-state = true failover-event-scheduler = false failover-event-status = false ## Failover after N failures detection failover-falsepositive-ping-counter = 5 ## Cancel failover if already N failover ## Cancel failover if last failover was N seconds before ## Cancel failover in semi-sync when one slave is not in sync ## Cancel failover if one slave receive master heartbeat ## Cancel failover when replication delay is more than N seconds failover-limit = 0 failover-time-limit = 0 failover-at-sync = false failover-max-slave-delay = 30 failover-restart-unsafe = false # failover-falsepositive-heartbeat = true # failover-falsepositive-heartbeat-timeout = 3 # failover-falsepositive-maxscale = false # failover-falsepositive-maxscale-timeout = 14 # failover-falsepositive-external = false # failover-falsepositive-external-port = 80 ################ ## SWITCHOVER ## ################ ## In switchover Wait N milliseconds before killing long running transactions ## Cancel switchover if transaction running more than N seconds ## Cancel switchover if write query running more than N seconds ## Cancel switchover if one of the slaves is not synced based on GTID equality switchover-wait-kill = 5000 switchover-wait-trx = 10 switchover-wait-write-query = 10 switchover-at-equal-gtid = false switchover-at-sync = false switchover-max-slave-delay = 30 ############ ## REJOIN ## ############ autorejoin = true autorejoin-script = "" autorejoin-semisync = true autorejoin-backup-binlog = true autorejoin-flashback = false autorejoin-mysqldump = false #################### ## CHECKS & FORCE ## #################### check-replication-filters = true check-binlog-filters = true check-replication-state = true force-slave-heartbeat= false force-slave-heartbeat-retry = 5 force-slave-heartbeat-time = 3 force-slave-gtid-mode = false force-slave-semisync = false force-slave-failover-readonly-state = false force-binlog-row = false force-binlog-annotate = false force-binlog-slowqueries = false force-binlog-compress = false force-binlog-checksum = false force-inmemory-binlog-cache-size = false force-disk-relaylog-size-limit = false force-disk-relaylog-size-limit-size = 1000000000 force-sync-binlog = false force-sync-innodb = false ############## ## MAXSCALE ## ############## ## for 2 nodes cluster maxscale can be driven by replication manager maxscale = false maxscale-binlog = false maxscale-servers = "192.168.0.201" maxscale-port = 4003 maxscale-user = "admin" maxscale-pass = "mariadb" ## When true replication manager drive maxscale server state ## Not required unless multiple maxscale or release does not support detect_stale_slave maxscale-disable-monitor = false ## maxinfo|maxadmin maxscale-get-info-method = "maxadmin" maxscale-maxinfo-port = 4002 maxscale-write-port = 4007 maxscale-read-port = 4008 maxscale-read-write-port = 4006 maxscale-binlog-port = 4000 ############# ## HAPROXY ## ############# ## Wrapper mode unless maxscale or proxysql required to be located with replication-manager haproxy = false haproxy-binary-path = "/usr/sbin/haproxy" ## Read write traffic ## Read only load balance least connection traffic haproxy-write-port = 3306 haproxy-read-port = 3307 #################### ## SHARDING PROXY ## #################### mdbshardproxy = false mdbshardproxy-hosts = "127.0.0.1:3306" mdbshardproxy-user = "root:mariadb" ################################# ###### proxysql settings ######## ################################# proxysql = true proxysql-servers = "127.0.0.1" proxysql-port = "6033" proxysql-admin-port = "6032" proxysql-writer-hostgroup = "1000" proxysql-reader-hostgroup = "1001" proxysql-user = "admin" proxysql-password = "admin" proxysql-bootstrap = false proxysql-bootstrap-users = false #不从master复制用户到proxysql,因为复制会出问题
proxysql-bootstrap-hostgroups = false
proxysql-bootstrap-variables = false
查看replication-manager配置文件config.toml
cat
/etc/replication-manager/config.toml
[Default]

include = "/etc/replication-manager/cluster.d"

monitoring-save-config = false
monitoring-datadir = "/var/lib/replication-manager"
#monitoring-sharedir = "/usr/share/replication-manager"

## Timeout in seconds between consecutive monitoring

monitoring-ticker = 2

#########
## LOG ##
#########

log-file = "/var/log/replication-manager.log"
log-heartbeat = false
log-syslog = false



#################
## ARBITRATION ##
#################

arbitration-external = false
arbitration-external-secret = "13787932529099014144"
arbitration-external-hosts = "88.191.151.84:80"
arbitration-peer-hosts ="127.0.0.1:10002"

## Unique value on each replication-manager

arbitration-external-unique-id = 0

##########
## HTTP ##
##########

http-server = true
http-bind-address = "0.0.0.0"
http-port = "10001"
http-auth = false
http-session-lifetime =   3600
http-bootstrap-button = false
http-refresh-interval = 4000

#########
## API ##
#########

api-credentials = "admin:repman"
api-port = "10005"
api-https-bind = false

api-credentials-acl-allow =  "admin:cluster proxy db prov,dba:cluster proxy db,foo:"
api-credentials-acl-discard = false
api-credentials-external = "dba:repman,foo:bar"

############
## ALERTS ##
############

mail-from = "replication-manager@localhost"
mail-smtp-addr = "localhost:25"
mail-to = "replication-manager@signal18.io"
mail-smtp-password=""
mail-smtp-user=""

alert-slack-channel = "#support"
alert-slack-url = ""
alert-slack-user = "svar"

##########
# STATS ##
##########

graphite-metrics = false
graphite-carbon-host = "127.0.0.1"
graphite-carbon-port = 2003
graphite-embedded = false
graphite-carbon-api-port = 10002
graphite-carbon-server-port = 10003
graphite-carbon-link-port = 7002
graphite-carbon-pickle-port = 2004
graphite-carbon-pprof-port = 7007

####这里可以设置逻辑备份和物理备份的程序,结合上面的配置里的restic做备份,很不错,后续有时间了再继续配置吧 backup-mydumper-path
= "/usr/local/bin/mydumper" backup-myloader-path = "/usr/local/bin/myloader" backup-mysqlbinlog-path = "/usr/local/bin/mysqlbinlog" backup-mysqldump-path = "/usr/local/bin/mysqldump" ############## # BENCHMARK ## ############## sysbench-binary-path = "/usr/bin/sysbench" sysbench-threads = 4 sysbench-time = 100 sysbench-v1 = true
查看proxysql的配置
cat
/etc/proxysql.cnf
#file proxysql.cfg

########################################################################################
# This config file is parsed using libconfig , and its grammar is described in:        
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar 
# Grammar is also copied at the end of this file                                       
########################################################################################

########################################################################################
# IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:                             
########################################################################################
# On startup, ProxySQL reads its config file (if present) to determine its datadir. 
# What happens next depends on if the database file (disk) is present in the defined
# datadir (i.e. "/var/lib/proxysql/proxysql.db").
#
# If the database file is found, ProxySQL initializes its in-memory configuration from 
# the persisted on-disk database. So, disk configuration gets loaded into memory and 
# then propagated towards the runtime configuration. 
#
# If the database file is not found and a config file exists, the config file is parsed 
# and its content is loaded into the in-memory database, to then be both saved on-disk 
# database and loaded at runtime.
#
# IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
#            ProxySQL initializes its in-memory configuration from the persisted on-disk
#            database ONLY. In other words, the configuration found in the proxysql.cnf
#            file is only used to initial the on-disk database read on the first startup.
#
# In order to FORCE a re-initialise of the on-disk database from the configuration file 
# the ProxySQL service should be started with "systemctl start proxysql-initial".
#
########################################################################################

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

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"
    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 servers
mysql_servers =
(
#    {
#        address = "127.0.0.1" # 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 = "/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 users
mysql_users:
(
#    {
#        username = "username" # no default , required
#        password = "password" # 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 Rules
mysql_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=30
#                reader_hostgroup=40
#                comment="test repl 1"
#       },
#       {
#                writer_hostgroup=50
#                reader_hostgroup=60
#                comment="test repl 2"
#        }
)




# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here. 
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#     
# setting = name (":" | "=") value (";" | "," | empty)
#     
# value = scalar-value | array | list | group
#     
# value-list = value | value-list "," value
#     
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
#                | string
#     
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#     
# array = "[" (scalar-value-list | empty) "]"
#     
# list = "(" (value-list | empty) ")"
#     
# group = "{" (setting-list | empty) "}"
#     
# empty =

其实proxysql配置基本上在启动了之后都是通过数据库来设置的配置文件

proxysql的配置如下,进入proxysql

[root@server00 ~]# mysql -uadmin -p -P6032 -h127.0.0.1 --prompt='proxysql Admin> '
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9037
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

proxysql Admin> 
proxysql Admin> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)

proxysql Admin> set mysql-monitor_password='your password';
Query OK, 1 row affected (0.00 sec)
proxysql Admin> select * from global_variables;

| mysql-monitor_username                                               | proxysql                                   |
| mysql-monitor_password                                               | your password                              |
| mysql-monitor_history                                                | 600000                                     |
| mysql-monitor_connect_interval                                       | 60000                                      |
| mysql-monitor_ping_interval                                          | 10000                                      |
| mysql-monitor_read_only_interval                                     | 1500                                       |
| mysql-monitor_read_only_timeout                                      | 500                                        |
| mysql-ping_interval_server_msec                                      | 120000                                     |
| mysql-ping_timeout_server                                            | 500                                        |
| mysql-commands_stats                                                 | true                                       |
| mysql-sessions_sort                                                  | true                                       |
| mysql-connect_retries_on_failure                                     | 10                                         |
| mysql-server_capabilities                                            | 569899                                     |
+----------------------------------------------------------------------+--------------------------------------------+
proxysql Admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

proxysql Admin> save mysql variables to disk;
Query OK, 155 rows affected (0.01 sec)
proxysql Admin> insert into mysql_replication_hostgroups values(1000,1001,'read_only','读1000写1001分离');
Query OK, 1 row affected (0.00 sec)

proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1000,'server01.mshome.net',3306);
Query OK, 1 row affected (0.00 sec)

proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1001,'server02.mshome.net',3306);
Query OK, 1 row affected (0.00 sec)

proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1001,'server03.mshome.net',3306);
Query OK, 1 row affected (0.00 sec)

proxysql Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

proxysql Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.05 sec)
proxysql Admin> delete from mysql_users;
Query OK, 1 row affected (0.00 sec)

proxysql Admin> insert into mysql_users(username,password,default_hostgroup) values('appbox','Appbox@123',1000);
Query OK, 1 row affected (0.00 sec)

proxysql Admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

proxysql Admin> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)

 按照以上配置打开replication-manager的web管理端,默认账号admin密码repman

 

 

 在server01的主库上创建appbox用户

mysql> create user appbox@'%' identified by 'Appbox@123';
Query OK, 0 rows affected (0.01 sec)

mysql> create database appboxdb character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> grant all privileges on appboxdb.* to appbox@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> 

 用Navicat连proxysql

在proxysql中插入读写规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',100,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',1000,1);
load mysql query rules to runtime;
save mysql query rules to disk;

bash下进行读写测试

for i in {1..100}; do mysql -uappbox -pAppbox@123 -h server00.mshome.net  -P6033  -e 'select * from mysql.user LIMIT 2;' ; sleep 0.5; done

如果有用,欢迎打赏,不限金额

 

热门相关:仙城纪   寂静王冠   豪门闪婚:帝少的神秘冷妻   无限杀路   夫人你马甲又掉了