# MySQL中间件选择

### MyCAT(1.6.5)

1. XXL-JOB官方文档有说明：如果mysql做主从,调度中心集群节点务必强制走主库
2. flyway也不支持！

### Sharding-JDBC 3.0.0（Proxy也应该是这样，但未测试）

SQL限制很多，不支持冗余括号、CASE WHEN、DISTINCT、HAVING、UNION (ALL)，有限支持子查询。http://shardingsphere.io/document/current/cn/features/sharding/usage-standard/sql/

### Sharding-Proxy 3.1.0/4.0.0-RC1（2019-08-06测试）

SQL限制未详细研究，但是发现致命问题，数据表中有is_active/is_start这样的字段，其值为0或1，很明显这是保存的状态，语义类型为布尔类型，但是查出来结果却让人惊讶。

• 151/152分别为start.sh和docker run运行的Sharding-Proxy 3.1.0/4.0.0-RC1
• 153/154/155为MySQL主从，未发现有延迟
• 153 -> master
• 154/155 -> slave

 1  SELECT is_active,is_start FROM WHERE id_ = ''; 

sharding-proxy（151、152） mysql客户端返回 true、false

navcate返回 1、1

### ProxySQL

  1 2 3 4 5 6 7 8 9 10 11 12 13 14  [root@copl-srv013-152 ~]# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled) Active: inactive (dead) since Wed 2019-08-07 16:26:45 CST; 16s ago Docs: man:systemd-sysv-generator(8) Process: 6022 ExecStop=/etc/rc.d/init.d/proxysql stop (code=exited, status=0/SUCCESS) Process: 2744 ExecStart=/etc/rc.d/init.d/proxysql start (code=exited, status=0/SUCCESS) Aug 07 16:09:37 copl-srv013-152 systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL... Aug 07 16:09:37 copl-srv013-152 su[2747]: (to proxysql) root on none Aug 07 16:09:37 copl-srv013-152 proxysql[2744]: Starting ProxySQL: 2019-08-07 16:09:37 main.cpp:720:ProxySQL_Main_process_global_variables(): [WARNING] Unable to open config file /etc/proxysql.cnf Aug 07 16:09:37 copl-srv013-152 proxysql[2744]: 2019-08-07 16:09:37 main.cpp:722:ProxySQL_Main_process_global_variables(): [ERROR] Unable to open config file /etc/proxysql.cnf specified in the command line. Aborting! Aug 07 16:09:37 copl-srv013-152 proxysql[2744]: DONE! Aug 07 16:09:37 copl-srv013-152 systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL. 

 1  Usage: ProxySQL {start|stop|status|reload|restart|initial} 

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37  [root@copl-srv013-152 ~]# /etc/rc.d/init.d/proxysql initial Starting ProxySQL: 2019-08-07 17:12:28 [INFO] Using config file /etc/proxysql.cnf Renaming database file /var/lib/proxysql/proxysql.db 2019-08-07 17:12:28 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates. DONE! [root@copl-srv013-152 ~]# /etc/rc.d/init.d/proxysql restart Shutting down ProxySQL: DONE! Starting ProxySQL: 2019-08-07 17:12:39 [INFO] Using config file /etc/proxysql.cnf 2019-08-07 17:12:39 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. DONE! [root@copl-srv013-152 ~]# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled) Active: active (exited) since Wed 2019-08-07 16:27:05 CST; 45min ago Docs: man:systemd-sysv-generator(8) Aug 07 16:27:04 copl-srv013-152 systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL... Aug 07 16:27:04 copl-srv013-152 su[6065]: (to proxysql) root on none Aug 07 16:27:05 copl-srv013-152 proxysql[6062]: Starting ProxySQL: 2019-08-07 16:27:05 main.cpp:720:ProxySQL_Main_process_global_variables(): [WARNING] Unable to open config file /etc/proxysql.cnf Aug 07 16:27:05 copl-srv013-152 proxysql[6062]: 2019-08-07 16:27:05 main.cpp:722:ProxySQL_Main_process_global_variables(): [ERROR] Unable to open config file /etc/proxysql.cnf specified in the command line. Aborting! Aug 07 16:27:05 copl-srv013-152 proxysql[6062]: DONE! Aug 07 16:27:05 copl-srv013-152 systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL. [root@copl-srv013-152 ~]# systemctl restart proxysql [root@copl-srv013-152 ~]# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled) Active: active (exited) since Wed 2019-08-07 17:13:00 CST; 1s ago Docs: man:systemd-sysv-generator(8) Process: 18958 ExecStop=/etc/rc.d/init.d/proxysql stop (code=exited, status=0/SUCCESS) Process: 19151 ExecStart=/etc/rc.d/init.d/proxysql start (code=exited, status=0/SUCCESS) Aug 07 17:12:59 copl-srv013-152 systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL... Aug 07 17:12:59 copl-srv013-152 su[19153]: (to proxysql) root on none Aug 07 17:12:59 copl-srv013-152 proxysql[19151]: Starting ProxySQL: 2019-08-07 17:12:59 [INFO] Using config file /etc/proxysql.cnf Aug 07 17:12:59 copl-srv013-152 proxysql[19151]: 2019-08-07 17:12:59 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. Aug 07 17:12:59 copl-srv013-152 proxysql[19151]: DONE! Aug 07 17:13:00 copl-srv013-152 systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL. 

/etc/proxysql.cnf配置文件示例：

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196  #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 "service 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=32 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.7.23" 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 = "server1.ip", port = 3306, hostgroup = 0, max_connections = 2000 }, { address = "server2.ip", port = 3306, hostgroup = 1, max_connections = 2000 }, { address = "server3.ip", port = 3306, hostgroup = 1, max_connections = 2000 } ) # defines all the MySQL users mysql_users: ( { username = "", password = "", default_hostgroup = 0, max_connections = 2000, default_schema = "", active = 1 }, { username = "", password = "", default_hostgroup = 1, max_connections = 2000, default_schema = "", active = 1 }, ) #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 = 10, reader_hostgroup = 20, comment = "proxy" } ) # 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 = 

### MaxScale

Maridb是MySQL的开源分支，也是MySQL作者弄的，特殊的官方吧 待测试评估的