search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.06k stars 983 forks source link

config in table mysql_group_replication_hostgroups be deleted when the service is restarted #4562

Closed 804e closed 5 months ago

804e commented 5 months ago

i have added a row in mysql_group_replication_hostgroups 

ProxySQL Admin> select * from mysql_group_replication_hostgroups ;
Empty set (0.00 sec)

ProxySQL Admin> INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (1,2,3,4,1,1,1,0);
LOAD MYSQL SERVERS TO RUNTIME;Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Query OK, 0 rows affected (0.00 sec)

ProxySQL Admin> SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;Query OK, 0 rows affected (0.04 sec)

ProxySQL Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 162 rows affected (0.01 sec)

ProxySQL Admin> SELECT * FROM monitor.mysql_server_group_replication_log ORDER BY time_start_us LIMIT 10;
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname      | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 10.0.1.148    | 3306 | 1718238814264195 | 1215            | YES              | YES       | 0                   | NULL  |
| 10.0.1.147    | 3306 | 1718238814271009 | 1488            | YES              | NO        | 0                   | NULL  |
| 10.0.1.149    | 3306 | 1718238814273455 | 3413            | YES              | YES       | 0                   | NULL  |
| itk8s-mysql02 | 3306 | 1718238814284186 | 682             | YES              | YES       | 0                   | NULL  |
| itk8s-mysql01 | 3306 | 1718238814284404 | 728             | YES              | NO        | 0                   | NULL  |
| 10.0.1.147    | 3306 | 1718238814287524 | 737             | YES              | NO        | 0                   | NULL  |
| itk8s-mysql03 | 3306 | 1718238814289400 | 1903            | YES              | YES       | 0                   | NULL  |
| 10.0.1.149    | 3306 | 1718238814291609 | 3269            | YES              | YES       | 0                   | NULL  |
| 10.0.1.148    | 3306 | 1718238814293328 | 1076            | YES              | YES       | 0                   | NULL  |
| 10.0.1.149    | 3306 | 1718238819282120 | 3376            | YES              | YES       | 0                   | NULL  |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
10 rows in set (0.00 sec)

ProxySQL Admin> select * from runtime_mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 2                       | 3                | 4                 | 1      | 1           | 1                     | 0                       | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

ProxySQL Admin> exit

then restart proxysql, mysql_group_replication_hostgroups will be purge

root@itk8s-mysql01:~# systemctl restart proxysql
root@itk8s-mysql01:~# mysql -u jafron -pCaBvIXU9eHojYjWq -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2024, 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> select * from mysql_group_replication_hostgroups ;
Empty set (0.00 sec)

does anyone knows how store this config?

JavierJF commented 5 months ago

Hi @804e,

two questions regarding this behavior:

This matches (even if I don't want to jump to conclusions, with what can be seen in your provided command line output:

ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Query OK, 0 rows affected (0.00 sec)

Two statements, yet only one, response Query OK from ProxySQL, pointing that maybe only the LOAD MYSQL SERVERS TO RUNTIME have been processed.

Please confirm if this is the case. If it's this is expected behavior as the admin interface doesn't support multi-statement commands.

Regards, Javier.

804e commented 5 months ago

thanks for reply @JavierJF

Copyright (c) 2000, 2024, 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> select * from runtime_mysql_group_replication_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 1 | 2 | 3 | 4 | 1 | 1 | 1 | 0 | NULL | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ 1 row in set (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.03 sec)

ProxySQL Admin> SAVE MYSQL VARIABLES TO DISK; Query OK, 162 rows affected (0.01 sec)

ProxySQL Admin> exit Bye root@itk8s-mysql01:/data/proxysql# systemctl restart proxysql.service root@itk8s-mysql01:/data/proxysql# mysql -u jafron -pCaBvIXU9eHojYjWq -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2024, 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> select * from runtime_mysql_group_replication_hostgroups; Empty set (0.00 sec)

ProxySQL Admin> 


Can you look at it again for me?
JavierJF commented 5 months ago

Hi @804e,

yw. Are you sure to be using the same directory as datadir when you are starting ProxySQL as a service?

Please supply the full ProxySQL error log during this restarts. Looks like the database file you are checking, might not be the database file ProxySQL is picking when starting as a service (different datadirs).

804e commented 5 months ago

Thanks for the thought @JavierJF below is my server's configuration file and log service status

root@itk8s-mysql01:/data/proxysql# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
     Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; preset: enabled)
     Active: active (running) since Fri 2024-06-14 17:11:33 HKT; 5min ago
    Process: 31337 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
   Main PID: 31339 (proxysql)
      Tasks: 25 (limit: 19135)
     Memory: 82.3M
        CPU: 1.535s
     CGroup: /system.slice/proxysql.service
             ├─31339 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf --initial
             └─31340 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf --initial

Jun 14 17:11:33 itk8s-mysql01 systemd[1]: Starting proxysql.service - High Performance Advanced Proxy for MySQL...
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using config file /etc/proxysql.cnf
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: Renaming database file /data/proxysql/proxysql.db
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Current RLIMIT_NOFILE: 102400
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] SSL keys/certificates found in datadir (/data/proxysql): loading them.
Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: Process 30905 died: No such process; trying to remove PID file. (/data/proxysql/proxysql.pid)
Jun 14 17:11:33 itk8s-mysql01 systemd[1]: Started proxysql.service - High Performance Advanced Proxy for MySQL.

/lib/systemd/system/proxysql.service

[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target

[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS
#PIDFile=/data/proxysql/proxysql.pid
#StandardError=null  # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes

[Install]
WantedBy=multi-user.target

proxysql.cnf

datadir="/data/proxysql"
errorlog="/data/proxysql/proxysql.log"

admin_variables=
{
    admin_credentials="jafron:CaBvIXU9eHojYjWq"
    mysql_ifaces="0.0.0.0:6032"
}

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"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="proxysql_monitor"
    monitor_password="a8Y6bRKdricEQF8q"
    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 = "10.0.1.147"
        port = 3306
        hostgroup = 1
    },
    { address="10.0.1.148" , port=3306 , hostgroup=2 },
    { address="10.0.1.149" , port=3306 , hostgroup=2 }
)

# defines all the MySQL users
mysql_users:
(
    {
        username = "proxysql" # no default , required
        password = "nmpb1x0K3O57KMIa" # default: ''
        default_hostgroup = 1 # default: 0
        active = 1            # default: 1
    },
    {
        username = "proxysql" # no default , required
        password = "nmpb1x0K3O57KMIa" # default: ''
        default_hostgroup = 2 # default: 0
        active = 1            # default: 1
    },
)

mysql_query_rules:
(
)

scheduler=
(
)

mysql_replication_hostgroups=
(
)

service start log

2024-06-14 17:11:33 [INFO] ProxySQL version 2.6.3-107-gcdfcfdc
2024-06-14 17:11:33 [INFO] Detected OS: Linux itk8s-mysql01 6.1.0-18-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.76-1 (2024-02-01) x86_64
2024-06-14 17:11:33 [INFO] ProxySQL SHA1 checksum: cb0e7730bcc8161ab656012cf91be4b615831963
2024-06-14 17:11:33 [INFO] Starting ProxySQL
2024-06-14 17:11:33 [INFO] Successfully started
2024-06-14 17:11:33 [INFO] Angel process started ProxySQL process 31340
2024-06-14 17:11:33 [INFO] SSL keys/certificates found in datadir (/data/proxysql): loading them.
2024-06-14 17:11:33 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Mon May 20 08:43:02 2024
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Mon May 20 08:43:02 2024
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Mon May 20 08:43:02 2024
2024-06-14 17:11:33 [INFO] Using UUID: cc4bb48c-2e2c-48e9-9750-0586c6ae28c3 , randomly generated. Writing it to database
2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0x8EFB3C895E3D66EB', with epoch '1718356293'
2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0xCCBB4B178504D158', with epoch '1718356293'
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Mon May 20 08:43:02 2024
2024-06-14 17:11:33 [INFO] ProxySQL SHA1 checksum: cb0e7730bcc8161ab656012cf91be4b615831963
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Mon May 20 08:43:02 2024
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Mon May 20 08:43:02 2024
2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0xE8484D3294995481', with epoch '1718356293'
2024-06-14 17:11:33 [INFO] Generating runtime mysql servers and mysql servers v2 records.
2024-06-14 17:11:33 [INFO] Dumping mysql_servers_incoming
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.0.1.147 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.1.148 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.1.149 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-06-14 17:11:33 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-06-14 17:11:33 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 10.0.1.147 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.1.148 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 10.0.1.149 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-06-14 17:11:33 [INFO] Creating new server in HG 1 : 10.0.1.147:3306 , gtid_port=0, weight=1, status=0
2024-06-14 17:11:33 [INFO] Creating new server in HG 2 : 10.0.1.148:3306 , gtid_port=0, weight=1, status=0
2024-06-14 17:11:33 [INFO] Creating new server in HG 2 : 10.0.1.149:3306 , gtid_port=0, weight=1, status=0
2024-06-14 17:11:33 [INFO] New mysql_group_replication_hostgroups table
2024-06-14 17:11:33 [INFO] New mysql_galera_hostgroups table
2024-06-14 17:11:33 [INFO] New mysql_aws_aurora_hostgroups table
2024-06-14 17:11:33 [INFO] New mysql_hostgroup_attributes table
2024-06-14 17:11:33 [INFO] New mysql_servers_ssl_params table
2024-06-14 17:11:33 [INFO] Checksum for table mysql_servers_v2 is 0xCE6ED500FA5FFF6A
2024-06-14 17:11:33 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x4B4A5A9766E6C973'
2024-06-14 17:11:33 [INFO] Checksum for table mysql_servers is 0xCE6ED500FA5FFF6A
2024-06-14 17:11:33 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0xFA5FFF6ACE6ED500 }, mysql_replication_hostgroups { old:0x0, new:0x0 }
2024-06-14 17:11:33 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1718356293'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Mon May 20 08:43:02 2024
2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x0000000000000000', with epoch '1718356293'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon May 20 08:43:02 2024
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Mon May 20 08:43:02 2024
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Mon May 20 08:43:02 2024
2024-06-14 17:11:33 [INFO] For information about products and services visit: https://proxysql.com/
2024-06-14 17:11:33 [INFO] For online documentation visit: https://proxysql.com/documentation/
2024-06-14 17:11:33 [INFO] For support visit: https://proxysql.com/services/support/
2024-06-14 17:11:33 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
2024-06-14 17:11:34 main.cpp:170:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error

looks like table mysql_group_replication_hostgroups be recreated when start service

renecannao-phabricator commented 5 months ago

You have --initial in your proxysql arguments.

You have configured proxysql to always forget whatever it has saved to database file.

On Sat, 15 Jun 2024, 15:59 804e, @.***> wrote:

below is my server's configuration file and log service status

@.***:/data/proxysql# systemctl status proxysql ● proxysql.service - High Performance Advanced Proxy for MySQL Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; preset: enabled) Active: active (running) since Fri 2024-06-14 17:11:33 HKT; 5min ago Process: 31337 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS) Main PID: 31339 (proxysql) Tasks: 25 (limit: 19135) Memory: 82.3M CPU: 1.535s CGroup: /system.slice/proxysql.service ├─31339 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf --initial └─31340 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf --initial

Jun 14 17:11:33 itk8s-mysql01 systemd[1]: Starting proxysql.service - High Performance Advanced Proxy for MySQL... Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0 Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using config file /etc/proxysql.cnf Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: Renaming database file /data/proxysql/proxysql.db Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Current RLIMIT_NOFILE: 102400 Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024 Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: 2024-06-14 17:11:33 [INFO] SSL keys/certificates found in datadir (/data/proxysql): loading them. Jun 14 17:11:33 itk8s-mysql01 proxysql[31337]: Process 30905 died: No such process; trying to remove PID file. (/data/proxysql/proxysql.pid) Jun 14 17:11:33 itk8s-mysql01 systemd[1]: Started proxysql.service - High Performance Advanced Proxy for MySQL.

/lib/systemd/system/proxysql.service

[Unit] Description=High Performance Advanced Proxy for MySQL After=network.target

[Service] Type=forking RuntimeDirectory=proxysql

PermissionsStartOnly=true

ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql

ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/

ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS

PIDFile=/data/proxysql/proxysql.pid

StandardError=null # all output is in stderr

SyslogIdentifier=proxysql Restart=no User=proxysql Group=proxysql PermissionsStartOnly=true UMask=0007 LimitNOFILE=102400 LimitCORE=1073741824 ProtectHome=yes NoNewPrivileges=true CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG ProtectSystem=full PrivateDevices=yes

[Install] WantedBy=multi-user.target

proxysql.cnf

datadir="/data/proxysql" errorlog="/data/proxysql/proxysql.log"

admin_variables= { admin_credentials="jafron:CaBvIXU9eHojYjWq" mysql_ifaces="0.0.0.0:6032" }

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" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="proxysql_monitor" monitor_password="a8Y6bRKdricEQF8q" 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 = "10.0.1.147" port = 3306 hostgroup = 1 }, { address="10.0.1.148" , port=3306 , hostgroup=2 }, { address="10.0.1.149" , port=3306 , hostgroup=2 } )

defines all the MySQL users

mysql_users: ( { username = "proxysql" # no default , required password = "nmpb1x0K3O57KMIa" # default: '' default_hostgroup = 1 # default: 0 active = 1 # default: 1 }, { username = "proxysql" # no default , required password = "nmpb1x0K3O57KMIa" # default: '' default_hostgroup = 2 # default: 0 active = 1 # default: 1 }, )

mysql_query_rules: ( )

scheduler= ( )

mysql_replication_hostgroups= ( )

service start log

2024-06-14 17:11:33 [INFO] ProxySQL version 2.6.3-107-gcdfcfdc 2024-06-14 17:11:33 [INFO] Detected OS: Linux itk8s-mysql01 6.1.0-18-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.76-1 (2024-02-01) x86_64 2024-06-14 17:11:33 [INFO] ProxySQL SHA1 checksum: cb0e7730bcc8161ab656012cf91be4b615831963 2024-06-14 17:11:33 [INFO] Starting ProxySQL 2024-06-14 17:11:33 [INFO] Successfully started 2024-06-14 17:11:33 [INFO] Angel process started ProxySQL process 31340 2024-06-14 17:11:33 [INFO] SSL keys/certificates found in datadir (/data/proxysql): loading them. 2024-06-14 17:11:33 [INFO] Loaded built-in SQLite3 Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Mon May 20 08:43:02 2024 Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Mon May 20 08:43:02 2024 Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Mon May 20 08:43:02 2024 2024-06-14 17:11:33 [INFO] Using UUID: cc4bb48c-2e2c-48e9-9750-0586c6ae28c3 , randomly generated. Writing it to database 2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0x8EFB3C895E3D66EB', with epoch '1718356293' 2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0xCCBB4B178504D158', with epoch '1718356293' Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Mon May 20 08:43:02 2024 2024-06-14 17:11:33 [INFO] ProxySQL SHA1 checksum: cb0e7730bcc8161ab656012cf91be4b615831963 Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Mon May 20 08:43:02 2024 Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Mon May 20 08:43:02 2024 2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0xE8484D3294995481', with epoch '1718356293' 2024-06-14 17:11:33 [INFO] Generating runtime mysql servers and mysql servers v2 records. 2024-06-14 17:11:33 [INFO] Dumping mysql_servers_incoming +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.0.1.147 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 10.0.1.148 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 10.0.1.149 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2024-06-14 17:11:33 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming +-------------+--------------+----------+------+ | mem_pointer | hostgroup_id | hostname | port | +-------------+--------------+----------+------+ +-------------+--------------+----------+------+ 2024-06-14 17:11:33 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.0.1.147 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 10.0.1.148 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 10.0.1.149 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2024-06-14 17:11:33 [INFO] Creating new server in HG 1 : 10.0.1.147:3306 , gtid_port=0, weight=1, status=0 2024-06-14 17:11:33 [INFO] Creating new server in HG 2 : 10.0.1.148:3306 , gtid_port=0, weight=1, status=0 2024-06-14 17:11:33 [INFO] Creating new server in HG 2 : 10.0.1.149:3306 , gtid_port=0, weight=1, status=0 2024-06-14 17:11:33 [INFO] New mysql_group_replication_hostgroups table 2024-06-14 17:11:33 [INFO] New mysql_galera_hostgroups table 2024-06-14 17:11:33 [INFO] New mysql_aws_aurora_hostgroups table 2024-06-14 17:11:33 [INFO] New mysql_hostgroup_attributes table 2024-06-14 17:11:33 [INFO] New mysql_servers_ssl_params table 2024-06-14 17:11:33 [INFO] Checksum for table mysql_servers_v2 is 0xCE6ED500FA5FFF6A 2024-06-14 17:11:33 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x4B4A5A9766E6C973' 2024-06-14 17:11:33 [INFO] Checksum for table mysql_servers is 0xCE6ED500FA5FFF6A 2024-06-14 17:11:33 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0xFA5FFF6ACE6ED500 }, mysql_replication_hostgroups { old:0x0, new:0x0 } 2024-06-14 17:11:33 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms 2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1718356293' Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Mon May 20 08:43:02 2024 2024-06-14 17:11:33 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x0000000000000000', with epoch '1718356293' In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon May 20 08:43:02 2024 Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Mon May 20 08:43:02 2024 Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Mon May 20 08:43:02 2024 2024-06-14 17:11:33 [INFO] For information about products and services visit: https://proxysql.com/ 2024-06-14 https://proxysql.com/2024-06-14 17:11:33 [INFO] For online documentation visit: https://proxysql.com/documentation/ 2024-06-14 https://proxysql.com/documentation/2024-06-14 17:11:33 [INFO] For support visit: https://proxysql.com/services/support/ 2024-06-14 https://proxysql.com/services/support/2024-06-14 17:11:33 [INFO] For consultancy visit: https://proxysql.com/services/consulting/ 2024-06-14 https://proxysql.com/services/consulting/2024-06-14 17:11:34 main.cpp:170:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error

looks like table mysql_group_replication_hostgroups be recreated when start service

— Reply to this email directly, view it on GitHub https://github.com/sysown/proxysql/issues/4562#issuecomment-2169225608, or unsubscribe https://github.com/notifications/unsubscribe-auth/AD5Q55MPH3FIJGNI76JBHITZHP66XAVCNFSM6AAAAABJHPTCS2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNRZGIZDKNRQHA . You are receiving this because you are subscribed to this thread.Message ID: @.***>

804e commented 5 months ago

@renecannao-phabricator Thanks I can't find where to set $PROXYSQL_OPTS, so i remove it from /lib/systemd/system/proxysql.service now, proxysql can persistence rows in table runtime_mysql_group_replication_hostgroups when restart service