发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

张元兵

3

主题

3

帖子

9

积分

新手上路

Rank: 1

积分
9
显示全部楼层
环境：
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供主从复制使用
  1. mysql> create user 'rep-manager'@'%' identified by 'your password';
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> create user 'repl'@'%' identified by 'your password';
  5. Query OK, 0 rows affected (0.02 sec)
  7. mysql> create user 'proxysql'@'%' identified by 'your password';
  8. Query OK, 0 rows affected (0.02 sec)
  10. mysql> grant process,replication slave,replication client on *.* to 'proxysql'@'%';
  11. Query OK, 0 rows affected (0.01 sec)
  13. mysql> grant select,replication slave,replication client,reload,super on *.* to 'repl'@'%';
  14. Query OK, 0 rows affected, 1 warning (0.01 sec)
  16. mysql> grant all privileges on *.* to 'rep-manager'@'%' with grant option;
  17. Query OK, 0 rows affected (0.01 sec)
  19. mysql> flush privileges;
  20. Query OK, 0 rows affected (0.01 sec)
复制代码
 
2、复制虚拟机
3、在server00上安装replication-manager
4、在server00上安装proxysql
5、启动mysql，创建主从半同步复制
半同步需要安装插件，在主库和从库上都安装
  1. mysql> install plugin rpl_semi_sync_source soname 'semisync_source.so';
  2. mysql> install plugin rpl_semi_sync_replica soname 'semisync_replica.so';
  3. mysql> show plugins;<br>+----------------------------------+----------+--------------------+---------------------+---------+<br>| Name                             | Status   | Type               | Library             | License |<br>+----------------------------------+----------+--------------------+---------------------+---------+<br>| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |<br>| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |<br>| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |<br>| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL                | GPL     |<br>| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL                | GPL     |<br>| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |<br>| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |<br>| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL                | GPL     |<br>| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL                | GPL     |<br>| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL                | GPL     |<br>| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL                | GPL     |<br>| ngram                            | ACTIVE   | FTPARSER           | NULL                | GPL     |<br>| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL                | GPL     |<br>| mysqlx                           | ACTIVE   | DAEMON             | NULL                | GPL     |<br>| rpl_semi_sync_source             | ACTIVE   | REPLICATION        | semisync_source.so  | GPL     |<br>| rpl_semi_sync_replica            | ACTIVE   | REPLICATION        | semisync_replica.so | GPL     |<br>+----------------------------------+----------+--------------------+---------------------+---------+<br>50 rows in set (0.00 sec)<br>要想永久启用半同步需将配置写入my.cnf看下面的配置即可。<br>在主库上查看是否启用了半同步<br> mysql> show variables like 'rpl_semi%';<br>+---------------------------------------------+------------+<br>| Variable_name                               | Value      |<br>+---------------------------------------------+------------+<br>| rpl_semi_sync_replica_enabled               | ON        |<br>| rpl_semi_sync_replica_trace_level           | 32         |<br>| rpl_semi_sync_source_enabled                | ON         |<br>| rpl_semi_sync_source_timeout                | 10000      |<br>| rpl_semi_sync_source_trace_level            | 32         |<br>| rpl_semi_sync_source_wait_for_replica_count | 1          |<br>| rpl_semi_sync_source_wait_no_replica        | ON         |<br>| rpl_semi_sync_source_wait_point             | AFTER_SYNC |<br>+---------------------------------------------+------------+<br>从库上<br>mysql> show variables like 'rpl_semi%';<br>+---------------------------------------------+------------+<br>| Variable_name                               | Value      |<br>+---------------------------------------------+------------+<br>| rpl_semi_sync_replica_enabled               | ON         |<br>| rpl_semi_sync_replica_trace_level           | 32         |<br>| rpl_semi_sync_source_enabled                | ON         |<br>| rpl_semi_sync_source_timeout                | 10000      |<br>| rpl_semi_sync_source_trace_level            | 32         |<br>| rpl_semi_sync_source_wait_for_replica_count | 1          |<br>| rpl_semi_sync_source_wait_no_replica        | ON         |<br>| rpl_semi_sync_source_wait_point             | AFTER_SYNC |<br>+---------------------------------------------+------------+<br>8 rows in set (0.01 sec)
复制代码
master主机mysql配置my.cnf如下
  1. # For advice on how to change settings please see<br># http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html<br><br>[mysqld]<br>#<br># Remove leading # and set to the amount of RAM for the most important data<br># cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.<br># innodb_buffer_pool_size = 128M<br>#<br># Remove the leading "# " to disable binary logging<br># Binary logging captures changes between backups and is enabled by<br># default. It's default setting is log_bin=binlog<br># disable_log_bin<br>#<br># Remove leading # to set options mainly useful for reporting servers.<br># The server defaults are faster for transactions and fast SELECTs.<br># Adjust sizes as needed, experiment to find the optimal values.<br># join_buffer_size = 128M<br># sort_buffer_size = 2M<br># read_rnd_buffer_size = 2M<br>#<br># Remove leading # to revert to previous value for default_authentication_plugin,<br># this will increase compatibility with older clients. For background, see:<br># https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin<br># default-authentication-plugin=mysql_native_password<br><br>datadir=/var/lib/mysql<br>socket=/var/lib/mysql/mysql.sock<br><br>log-error=/var/log/mysqld.log<br>pid-file=/var/run/mysqld/mysqld.pid<br><br>default-authentication-plugin=mysql_native_password<br><br>######replication settings######<br>server-id=1001<br>log-bin=mysql-bin<br>binlog_format=row<br><br>binlog-ignore-db=mysql<br>binlog-ignore-db=information_schema<br>binlog-ignore-db=performance_schema<br>binlog-ignore-db=sys<br><br>######gtid#######<br>gtid_mode=on<br>enforce_gtid_consistency=on<br>binlog_gtid_simple_recovery=on<br><br>relay_log_recovery = ON<br>relay-log-index=mysql-relay<br>relay-log=mysql-relay<br><br>loose_rpl_semi_sync_source_enabled = ON<br>loose_rpl_semi_sync_replica_enabled = ON<br><br>log_slow_replica_statements = 1
复制代码
slave主机myql的配置如下：
  1. # For advice on how to change settings please see<br># http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html<br><br>[mysqld]<br>#<br># Remove leading # and set to the amount of RAM for the most important data<br># cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.<br># innodb_buffer_pool_size = 128M<br>#<br># Remove the leading "# " to disable binary logging<br># Binary logging captures changes between backups and is enabled by<br># default. It's default setting is log_bin=binlog<br># disable_log_bin<br>#<br># Remove leading # to set options mainly useful for reporting servers.<br># The server defaults are faster for transactions and fast SELECTs.<br># Adjust sizes as needed, experiment to find the optimal values.<br># join_buffer_size = 128M<br># sort_buffer_size = 2M<br># read_rnd_buffer_size = 2M<br>#<br># Remove leading # to revert to previous value for default_authentication_plugin,<br># this will increase compatibility with older clients. For background, see:<br># https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin<br># default-authentication-plugin=mysql_native_password<br><br>datadir=/var/lib/mysql<br>socket=/var/lib/mysql/mysql.sock<br><br>log-error=/var/log/mysqld.log<br>pid-file=/var/run/mysqld/mysqld.pid<br><br>default-authentication-plugin=mysql_native_password<br><br>######replication settings######<br>server-id=1002<br>log-bin=mysql-bin<br>binlog_format=row<br>log_slave_updates=on<br>relay_log_recovery=1<br><br>binlog-ignore-db=mysql<br>binlog-ignore-db=information_schema<br>binlog-ignore-db=performance_schema<br>binlog-ignore-db=sys<br><br>######gtid#######<br>gtid_mode=on<br>enforce_gtid_consistency=on<br>binlog_gtid_simple_recovery=on<br><br>relay_log_recovery = ON<br>relay-log-index=mysql-relay<br>relay-log=mysql-relay<br><br>loose_rpl_semi_sync_source_enabled = ON<br>loose_rpl_semi_sync_replica_enabled = ON<br><br>log_slow_replica_statements = 1<br><br>read_only = on<br>
复制代码
启动主从
  1. mysql> CHANGE REPLICATION SOURCE to SOURCE_HOST='server01.mshome.net',SOURCE_USER='repl',SOURCE_PASSWORD='your password',SOURCE_PORT=3306;<br>Query OK, 0 rows affected, 3 warnings (0.03 sec)<br><br>mysql> start replica;<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql> show replica status\G;<br>*************************** 1. row ***************************<br>             Replica_IO_State: Waiting for source to send event<br>                  Source_Host: server01.mshome.net<br>                  Source_User: repl<br>                  Source_Port: 3306<br>                Connect_Retry: 60<br>              Source_Log_File: mysql-bin.000009<br>          Read_Source_Log_Pos: 157<br>               Relay_Log_File: mysql-relay.000010<br>                Relay_Log_Pos: 373<br>        Relay_Source_Log_File: mysql-bin.000009<br>           Replica_IO_Running: Yes<br>          Replica_SQL_Running: Yes<br>              Replicate_Do_DB: <br>          Replicate_Ignore_DB: <br>           Replicate_Do_Table: <br>       Replicate_Ignore_Table: <br>      Replicate_Wild_Do_Table: <br>  Replicate_Wild_Ignore_Table: <br>                   Last_Errno: 0<br>                   Last_Error: <br>                 Skip_Counter: 0<br>          Exec_Source_Log_Pos: 157<br>              Relay_Log_Space: 795<br>              Until_Condition: None<br>               Until_Log_File: <br>                Until_Log_Pos: 0<br>           Source_SSL_Allowed: No<br>           Source_SSL_CA_File: <br>           Source_SSL_CA_Path: <br>              Source_SSL_Cert: <br>            Source_SSL_Cipher: <br>               Source_SSL_Key: <br>        Seconds_Behind_Source: 0<br>Source_SSL_Verify_Server_Cert: No<br>                Last_IO_Errno: 0<br>                Last_IO_Error: <br>               Last_SQL_Errno: 0<br>               Last_SQL_Error: <br>  Replicate_Ignore_Server_Ids: <br>             Source_Server_Id: 1001<br>                  Source_UUID: 590af332-e04e-11ed-8935-00155d030202<br>             Source_Info_File: mysql.slave_master_info<br>                    SQL_Delay: 0<br>          SQL_Remaining_Delay: NULL<br>    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates<br>           Source_Retry_Count: 86400<br>                  Source_Bind: <br>      Last_IO_Error_Timestamp: <br>     Last_SQL_Error_Timestamp: <br>               Source_SSL_Crl: <br>           Source_SSL_Crlpath: <br>           Retrieved_Gtid_Set: <br>            Executed_Gtid_Set: <br>                Auto_Position: 0<br>         Replicate_Rewrite_DB: <br>                 Channel_Name: <br>           Source_TLS_Version: <br>       Source_public_key_path: <br>        Get_Source_public_key: 1<br>            Network_Namespace: <br>1 row in set (0.00 sec)<br><br>ERROR: <br>No query specified
复制代码
  1.  
复制代码
server00主机上安装了replication-manager和proxysql
replication-manager的配置如下
  1. 查看replication-manager配置文件cluster1.toml<br>cat /etc/replication-manager/cluster.d/cluster1.toml
复制代码
  1. [cluster1]
  2. title = "cluster1"
  3. prov-orchestrator = "onpremise"
  4. prov-db-tags = "innodb,noquerycache,slow,pfs,pkg,linux,smallredolog,logtotable"
  5. prov-db-memory = "256"
  6. prov-db-memory-shared-pct = "threads:16,innodb:60,myisam:10,aria:10,rocksdb:1,tokudb:1,s3:1,archive:1,querycache:0"
  7. prov-db-disk-size = "1"
  8. prov-db-cpu-cores = "1"
  9. prov-db-disk-iops = "300"
  11. db-servers-hosts = "server01.mshome.net:3306,server02.mshome.net:3306,server03.mshome.net:3306"
  12. db-servers-prefered-master = "server01.mshome.net:3306"
  13. db-servers-credential = "rep-manager:yourpassword"
  14. db-servers-connect-timeout = 5
  15. replication-credential = "rep-manager:yourpassword"
  18. verbose = false
  19. log-failed-election  = true
  20. log-level = 1
  21. log-rotate-max-age = 7
  22. log-rotate-max-backup = 7
  23. log-rotate-max-size = 5
  24. log-sql-in-monitoring   = true
  25. log-sst = true
  27. ##############
  28. ## TOPOLOGY ##
  29. ##############
  31. replication-multi-master = false
  32. replication-multi-tier-slave = false
  34. ############
  35. # BACKUPS ##
  36. ###########
  39. backup-streaming = false
  40. backup-streaming-aws-access-key-id = "admin"
  41. backup-streaming-aws-access-secret = "xxxx"
  42. backup-streaming-endpoint= "https://s3.signal18.io/"
  43. backup-streaming-region= "fr-1"
  44. backup-streaming-bucket= "repman"<br><br>
  45. #####这里可以设置备份工具restic，工具可以跨服务器，结合minio备份就很爽了，后续再介绍吧
  46. backup-restic = false
  47. backup-restic-aws =  false
  48. backup-physical-type = "mariabackup"
  49. backup-logical-type = "mysqldump"
  50. backup-restic-aws-access-secret = "xxxx"
  51. backup-restic-password = "xxxx"
  52. backup-restic-binary-path = "/usr/bin/restic"
  54. monitoring-scheduler = true
  55. scheduler-db-servers-logical-backup  = true
  56. scheduler-db-servers-logical-backup-cron= "0 30 23 * * 6"
  57. scheduler-db-servers-logs   =  false
  58. scheduler-db-servers-logs-cron = "0 0 * * * *"
  59. scheduler-db-servers-logs-table-keep = 4
  60. scheduler-db-servers-logs-table-rotate  = false
  61. scheduler-db-servers-logs-table-rotate-cron = "0 0 0/6 * * *"
  62. scheduler-db-servers-optimize  = false
  63. scheduler-db-servers-optimize-cron = "0 0 3 1 * 5"
  64. scheduler-db-servers-physical-backup = true
  65. scheduler-db-servers-physical-backup-cron = "0 30 23 * * *"
  67. ##############
  68. ## FAILOVER ##
  69. ##############
  71. failover-mode = "manual"
  72. failover-pre-script = ""
  73. failover-post-script = ""
  75. ## Slaves will re enter with read-only
  77. failover-readonly-state = true
  78. failover-event-scheduler = false
  79. failover-event-status = false
  81. ## Failover after N failures detection
  83. failover-falsepositive-ping-counter = 5
  85. ## Cancel failover if already N failover
  86. ## Cancel failover if last failover was N seconds before
  87. ## Cancel failover in semi-sync when one slave is not in sync
  88. ## Cancel failover if one slave receive master heartbeat
  89. ## Cancel failover when replication delay is more than N seconds
  91. failover-limit = 0
  92. failover-time-limit = 0
  93. failover-at-sync = false
  94. failover-max-slave-delay = 30
  95. failover-restart-unsafe = false
  97. # failover-falsepositive-heartbeat = true
  98. # failover-falsepositive-heartbeat-timeout = 3
  99. # failover-falsepositive-maxscale = false
  100. # failover-falsepositive-maxscale-timeout = 14
  101. # failover-falsepositive-external = false
  102. # failover-falsepositive-external-port = 80
  104. ################
  105. ## SWITCHOVER ##
  106. ################
  108. ## In switchover Wait N milliseconds before killing long running transactions
  109. ## Cancel switchover if transaction running more than N seconds
  110. ## Cancel switchover if write query running more than N seconds
  111. ## Cancel switchover if one of the slaves is not synced based on GTID equality
  113. switchover-wait-kill = 5000
  114. switchover-wait-trx = 10
  115. switchover-wait-write-query = 10
  116. switchover-at-equal-gtid = false
  117. switchover-at-sync = false
  118. switchover-max-slave-delay = 30
  120. ############
  121. ## REJOIN ##
  122. ############
  124. autorejoin = true
  125. autorejoin-script = ""
  126. autorejoin-semisync = true
  127. autorejoin-backup-binlog = true
  128. autorejoin-flashback = false
  129. autorejoin-mysqldump = false
  131. ####################
  132. ## CHECKS & FORCE ##
  133. ####################
  135. check-replication-filters = true
  136. check-binlog-filters = true
  137. check-replication-state = true
  139. force-slave-heartbeat= false
  140. force-slave-heartbeat-retry = 5
  141. force-slave-heartbeat-time = 3
  142. force-slave-gtid-mode = false
  143. force-slave-semisync = false
  144. force-slave-failover-readonly-state = false
  145. force-binlog-row = false
  146. force-binlog-annotate = false
  147. force-binlog-slowqueries = false
  148. force-binlog-compress = false
  149. force-binlog-checksum = false
  150. force-inmemory-binlog-cache-size = false
  151. force-disk-relaylog-size-limit = false
  152. force-disk-relaylog-size-limit-size = 1000000000
  153. force-sync-binlog = false
  154. force-sync-innodb = false
  156. ##############
  157. ## MAXSCALE ##
  158. ##############
  160. ## for 2 nodes cluster maxscale can be driven by replication manager
  162. maxscale = false
  163. maxscale-binlog = false
  164. maxscale-servers = "192.168.0.201"
  165. maxscale-port = 4003
  166. maxscale-user = "admin"
  167. maxscale-pass = "mariadb"
  169. ## When true replication manager drive maxscale server state
  170. ## Not required unless multiple maxscale or release does not support detect_stale_slave
  172. maxscale-disable-monitor = false
  174. ## maxinfo|maxadmin
  176. maxscale-get-info-method = "maxadmin"
  177. maxscale-maxinfo-port = 4002
  179. maxscale-write-port = 4007
  180. maxscale-read-port = 4008
  181. maxscale-read-write-port = 4006
  182. maxscale-binlog-port = 4000
  184. #############
  185. ## HAPROXY ##
  186. #############
  188. ## Wrapper mode unless maxscale or proxysql required to be located with replication-manager
  190. haproxy = false
  191. haproxy-binary-path = "/usr/sbin/haproxy"
  193. ## Read write traffic
  194. ## Read only load balance least connection traffic
  195. haproxy-write-port = 3306
  196. haproxy-read-port = 3307
  198. ####################
  199. ## SHARDING PROXY ##
  200. ####################
  202. mdbshardproxy = false
  203. mdbshardproxy-hosts = "127.0.0.1:3306"
  204. mdbshardproxy-user = "root:mariadb"
  206. #################################
  207. ###### proxysql settings ########
  208. #################################
  209. proxysql = true
  210. proxysql-servers = "127.0.0.1"
  211. proxysql-port = "6033"
  212. proxysql-admin-port = "6032"
  213. proxysql-writer-hostgroup = "1000"
  214. proxysql-reader-hostgroup = "1001"
  215. proxysql-user = "admin"
  216. proxysql-password = "admin"
  217. proxysql-bootstrap = false
  218. proxysql-bootstrap-users = false #不从master复制用户到proxysql，因为复制会出问题
复制代码
  1. 查看replication-manager配置文件config.toml<br>cat /etc/replication-manager/config.toml
复制代码
  1. [Default]
  3. include = "/etc/replication-manager/cluster.d"
  5. monitoring-save-config = false
  6. monitoring-datadir = "/var/lib/replication-manager"
  7. #monitoring-sharedir = "/usr/share/replication-manager"
  9. ## Timeout in seconds between consecutive monitoring
  11. monitoring-ticker = 2
  13. #########
  14. ## LOG ##
  15. #########
  17. log-file = "/var/log/replication-manager.log"
  18. log-heartbeat = false
  19. log-syslog = false
  23. #################
  24. ## ARBITRATION ##
  25. #################
  27. arbitration-external = false
  28. arbitration-external-secret = "13787932529099014144"
  29. arbitration-external-hosts = "88.191.151.84:80"
  30. arbitration-peer-hosts ="127.0.0.1:10002"
  32. ## Unique value on each replication-manager
  34. arbitration-external-unique-id = 0
  36. ##########
  37. ## HTTP ##
  38. ##########
  40. http-server = true
  41. http-bind-address = "0.0.0.0"
  42. http-port = "10001"
  43. http-auth = false
  44. http-session-lifetime =   3600
  45. http-bootstrap-button = false
  46. http-refresh-interval = 4000
  48. #########
  49. ## API ##
  50. #########
  52. api-credentials = "admin:repman"
  53. api-port = "10005"
  54. api-https-bind = false
  56. api-credentials-acl-allow =  "admin:cluster proxy db prov,dba:cluster proxy db,foo:"
  57. api-credentials-acl-discard = false
  58. api-credentials-external = "dba:repman,foo:bar"
  60. ############
  61. ## ALERTS ##
  62. ############
  64. mail-from = "replication-manager@localhost"
  65. mail-smtp-addr = "localhost:25"
  66. mail-to = "replication-manager@signal18.io"
  67. mail-smtp-password=""
  68. mail-smtp-user=""
  70. alert-slack-channel = "#support"
  71. alert-slack-url = ""
  72. alert-slack-user = "svar"
  74. ##########
  75. # STATS ##
  76. ##########
  78. graphite-metrics = false
  79. graphite-carbon-host = "127.0.0.1"
  80. graphite-carbon-port = 2003
  81. graphite-embedded = false
  82. graphite-carbon-api-port = 10002
  83. graphite-carbon-server-port = 10003
  84. graphite-carbon-link-port = 7002
  85. graphite-carbon-pickle-port = 2004
  86. graphite-carbon-pprof-port = 7007
  87. <br>####这里可以设置逻辑备份和物理备份的程序，结合上面的配置里的restic做备份，很不错，后续有时间了再继续配置吧
  88. backup-mydumper-path = "/usr/local/bin/mydumper"
  89. backup-myloader-path = "/usr/local/bin/myloader"
  90. backup-mysqlbinlog-path = "/usr/local/bin/mysqlbinlog"
  91. backup-mysqldump-path = "/usr/local/bin/mysqldump"
  94. ##############
  95. # BENCHMARK ##
  96. ##############
  98. sysbench-binary-path = "/usr/bin/sysbench"
  99. sysbench-threads = 4
  100. sysbench-time = 100
  101. sysbench-v1 = true
复制代码
  1. 查看proxysql的配置<br>cat /etc/proxysql.cnf
复制代码
  1. #file proxysql.cfg
  3. ########################################################################################
  4. # This config file is parsed using libconfig , and its grammar is described in:        
  5. # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
  6. # Grammar is also copied at the end of this file                                       
  7. ########################################################################################
  9. ########################################################################################
  10. # IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:                             
  11. ########################################################################################
  12. # On startup, ProxySQL reads its config file (if present) to determine its datadir.
  13. # What happens next depends on if the database file (disk) is present in the defined
  14. # datadir (i.e. "/var/lib/proxysql/proxysql.db").
  15. #
  16. # If the database file is found, ProxySQL initializes its in-memory configuration from
  17. # the persisted on-disk database. So, disk configuration gets loaded into memory and
  18. # then propagated towards the runtime configuration.
  19. #
  20. # If the database file is not found and a config file exists, the config file is parsed
  21. # and its content is loaded into the in-memory database, to then be both saved on-disk
  22. # database and loaded at runtime.
  23. #
  24. # IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
  25. #            ProxySQL initializes its in-memory configuration from the persisted on-disk
  26. #            database ONLY. In other words, the configuration found in the proxysql.cnf
  27. #            file is only used to initial the on-disk database read on the first startup.
  28. #
  29. # In order to FORCE a re-initialise of the on-disk database from the configuration file
  30. # the ProxySQL service should be started with "systemctl start proxysql-initial".
  31. #
  32. ########################################################################################
  34. datadir="/var/lib/proxysql"
  35. errorlog="/var/lib/proxysql/proxysql.log"
  37. admin_variables=
  38. {
  39.     admin_credentials="admin:admin"
  40. #    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
  41.     mysql_ifaces="0.0.0.0:6032"
  42. #    refresh_interval=2000
  43. #    debug=true
  44. }
  46. mysql_variables=
  47. {
  48.     threads=4
  49.     max_connections=2048
  50.     default_query_delay=0
  51.     default_query_timeout=36000000
  52.     have_compress=true
  53.     poll_timeout=2000
  54. #    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
  55.     interfaces="0.0.0.0:6033"
  56.     default_schema="information_schema"
  57.     stacksize=1048576
  58.     server_version="5.5.30"
  59.     connect_timeout_server=3000
  60. # make sure to configure monitor username and password
  61. # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
  62.     monitor_username="monitor"
  63.     monitor_password="monitor"
  64.     monitor_history=600000
  65.     monitor_connect_interval=60000
  66.     monitor_ping_interval=10000
  67.     monitor_read_only_interval=1500
  68.     monitor_read_only_timeout=500
  69.     ping_interval_server_msec=120000
  70.     ping_timeout_server=500
  71.     commands_stats=true
  72.     sessions_sort=true
  73.     connect_retries_on_failure=10
  74. }
  77. # defines all the MySQL servers
  78. mysql_servers =
  79. (
  80. #    {
  81. #        address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
  82. #        port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
  83. #        hostgroup = 0            # no default, required
  84. #        status = "ONLINE"     # default: ONLINE
  85. #        weight = 1            # default: 1
  86. #        compression = 0       # default: 0
  87. #   max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
  88. #    },
  89. #    {
  90. #        address = "/var/lib/mysql/mysql.sock"
  91. #        port = 0
  92. #        hostgroup = 0
  93. #    },
  94. #    {
  95. #        address="127.0.0.1"
  96. #        port=21891
  97. #        hostgroup=0
  98. #        max_connections=200
  99. #    },
  100. #    { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
  101. #    { address="127.0.0.1" , port=21892 , hostgroup=1 },
  102. #    { address="127.0.0.1" , port=21893 , hostgroup=1 }
  103. #    { address="127.0.0.2" , port=3306 , hostgroup=1 },
  104. #    { address="127.0.0.3" , port=3306 , hostgroup=1 },
  105. #    { address="127.0.0.4" , port=3306 , hostgroup=1 },
  106. #    { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
  107. )
  110. # defines all the MySQL users
  111. mysql_users:
  112. (
  113. #    {
  114. #        username = "username" # no default , required
  115. #        password = "password" # default: ''
  116. #        default_hostgroup = 0 # default: 0
  117. #        active = 1            # default: 1
  118. #    },
  119. #    {
  120. #        username = "root"
  121. #        password = ""
  122. #        default_hostgroup = 0
  123. #        max_connections=1000
  124. #        default_schema="test"
  125. #        active = 1
  126. #    },
  127. #    { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
  128. )
  132. #defines MySQL Query Rules
  133. mysql_query_rules:
  134. (
  135. #    {
  136. #        rule_id=1
  137. #        active=1
  138. #        match_pattern="^SELECT .* FOR UPDATE$"
  139. #        destination_hostgroup=0
  140. #        apply=1
  141. #    },
  142. #    {
  143. #        rule_id=2
  144. #        active=1
  145. #        match_pattern="^SELECT"
  146. #        destination_hostgroup=1
  147. #        apply=1
  148. #    }
  149. )
  151. scheduler=
  152. (
  153. #  {
  154. #    id=1
  155. #    active=0
  156. #    interval_ms=10000
  157. #    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
  158. #    arg1="0"
  159. #    arg2="0"
  160. #    arg3="0"
  161. #    arg4="1"
  162. #    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
  163. #  }
  164. )
  167. mysql_replication_hostgroups=
  168. (
  169. #        {
  170. #                writer_hostgroup=30
  171. #                reader_hostgroup=40
  172. #                comment="test repl 1"
  173. #       },
  174. #       {
  175. #                writer_hostgroup=50
  176. #                reader_hostgroup=60
  177. #                comment="test repl 2"
  178. #        }
  179. )
  184. # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
  185. #
  186. # Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
  187. #
  188. # configuration = setting-list | empty
  189. #
  190. # setting-list = setting | setting-list setting
  191. #     
  192. # setting = name (":" | "=") value (";" | "," | empty)
  193. #     
  194. # value = scalar-value | array | list | group
  195. #     
  196. # value-list = value | value-list "," value
  197. #     
  198. # scalar-value = boolean | integer | integer64 | hex | hex64 | float
  199. #                | string
  200. #     
  201. # scalar-value-list = scalar-value | scalar-value-list "," scalar-value
  202. #     
  203. # array = "[" (scalar-value-list | empty) "]"
  204. #     
  205. # list = "(" (value-list | empty) ")"
  206. #     
  207. # group = "{" (setting-list | empty) "}"
  208. #     
  209. # empty =
复制代码
其实proxysql配置基本上在启动了之后都是通过数据库来设置的配置文件
proxysql的配置如下，进入proxysql
  1. [root@server00 ~]# mysql -uadmin -p -P6032 -h127.0.0.1 --prompt='proxysql Admin> '
  2. Enter password:
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 9037
  5. Server version: 5.5.30 (ProxySQL Admin Module)
  7. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. proxysql Admin> <br>proxysql Admin> set mysql-monitor_username='proxysql';<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> set mysql-monitor_password='your password';<br>Query OK, 1 row affected (0.00 sec)<br>proxysql Admin> select * from global_variables;<br><br>| mysql-monitor_username                                               | proxysql                                   |<br>| mysql-monitor_password                                               | your password                              |<br>| mysql-monitor_history                                                | 600000                                     |<br>| mysql-monitor_connect_interval                                       | 60000                                      |<br>| mysql-monitor_ping_interval                                          | 10000                                      |<br>| mysql-monitor_read_only_interval                                     | 1500                                       |<br>| mysql-monitor_read_only_timeout                                      | 500                                        |<br>| mysql-ping_interval_server_msec                                      | 120000                                     |<br>| mysql-ping_timeout_server                                            | 500                                        |<br>| mysql-commands_stats                                                 | true                                       |<br>| mysql-sessions_sort                                                  | true                                       |<br>| mysql-connect_retries_on_failure                                     | 10                                         |<br>| mysql-server_capabilities                                            | 569899                                     |<br>+----------------------------------------------------------------------+--------------------------------------------+<br>proxysql Admin> load mysql variables to runtime;<br>Query OK, 0 rows affected (0.00 sec)<br><br>proxysql Admin> save mysql variables to disk;<br>Query OK, 155 rows affected (0.01 sec)<br>proxysql Admin> insert into mysql_replication_hostgroups values(1000,1001,'read_only','读1000写1001分离');<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1000,'server01.mshome.net',3306);<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1001,'server02.mshome.net',3306);<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(1001,'server03.mshome.net',3306);<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> load mysql servers to runtime;<br>Query OK, 0 rows affected (0.00 sec)<br><br>proxysql Admin> save mysql servers to disk;<br>Query OK, 0 rows affected (0.05 sec)<br>proxysql Admin> delete from mysql_users;<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> insert into mysql_users(username,password,default_hostgroup) values('appbox','Appbox@123',1000);<br>Query OK, 1 row affected (0.00 sec)<br><br>proxysql Admin> load mysql users to runtime;<br>Query OK, 0 rows affected (0.00 sec)<br><br>proxysql Admin> save mysql users to disk;<br>Query OK, 0 rows affected (0.02 sec)<br><br>
复制代码
 按照以上配置打开replication-manager的web管理端，默认账号admin密码repman


 切换主从后

 

 

切换主从后
 
 在server01的主库上创建appbox用户
  1. mysql> create user appbox@'%' identified by 'Appbox@123';
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> create database appboxdb character set utf8mb4;
  5. Query OK, 1 row affected (0.01 sec)
  7. mysql> grant all privileges on appboxdb.* to appbox@'%';
  8. Query OK, 0 rows affected (0.01 sec)
  10. mysql>
复制代码
 用Navicat连proxysql

在proxysql中插入读写规则
  1. insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',100,1);
  2. insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',1000,1);<br>load mysql query rules to runtime;<br>save mysql query rules to disk;
复制代码
bash下进行读写测试
  1. 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
复制代码
如果有用，欢迎打赏，不限金额

 

来源:https://www.cnblogs.com/24la/p/mysql-proxysql-replication-manager-read-write-split-mha.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: 读《mysql是怎样运行的》有感

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

发表于 2023-4-23 20:11:15

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4