Skip to content

Latest commit

 

History

History
173 lines (127 loc) · 8.14 KB

FAQ.md

File metadata and controls

173 lines (127 loc) · 8.14 KB
Raw

Changing username/password doesn't work

ProxySQL has a 3-tiered configuration system inspired from routers. The configuration file will only be taken into account the first time you start ProxySQL up, and when you manually force it to reset all settings, loading the ones from the configuration file (--initial flag).

Example on how to change the admin user/password

First, we'll connect to the default admin port with the default user/password combination, which will allow us to change these.

#
# Connecting to the default admin port, with the default user/password combination
#
$ mysql -u admin -padmin -h 127.0.0.1 -P6032
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 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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.

#
# Display & modify the current variable values from the in-memory layer 
# (see the [configuration system docs](https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md)
# to learn about the layers). It's only possible to change the values of the
# variables by first modifying them in the memory, and then in the runtime.
#

mysql> SELECT * FROM global_variables WHERE variable_name IN ('admin-admin_credentials','admin-mysql_ifaces');
+-------------------------+----------------+
| variable_name           | variable_value |
+-------------------------+----------------+
| admin-admin_credentials | admin:admin    |
| admin-mysql_ifaces      | 127.0.0.1:6032 |
+-------------------------+----------------+
2 rows in set (0.00 sec)

mysql> UPDATE global_variables SET variable_value='test:test' WHERE variable_name='admin-admin_credentials';
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE global_variables SET variable_value='127.0.0.1:6034;/tmp/proxysql_admin.sock' WHERE variable_name='admin-mysql_ifaces';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM global_variables WHERE variable_name IN ('admin-admin_credentials','admin-mysql_ifaces');
+-------------------------+-----------------------------------------+
| variable_name           | variable_value                          |
+-------------------------+-----------------------------------------+
| admin-admin_credentials | test:test                               |
| admin-mysql_ifaces      | 127.0.0.1:6034;/tmp/proxysql_admin.sock |
+-------------------------+-----------------------------------------+
2 rows in set (0.00 sec)

#
# This is where the settings finally start taking effect, by moving them
# from the in-memory layer to the runtime layer. This is useful to allow you
# to do transactional changes to the runtime configuration without downtime.
#

mysql> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Now, we can actually connect to the new port with the new password:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032
Warning: Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

$ mysql -u test -ptest -h 127.0.0.1 -P6034
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 5
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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.

mysql>

Example on how to save the username/password for connecting to ProxySQL and making queries to backend servers

Username/password combinations for connecting to the backend servers are stored in a different place (so not in admin variables, like we did for the admin interface, for simplicity purposes). There is one (user, password) combination allowed for each hostgroup (which is a set of backend servers grouped together).

So, in order to create a new user (note that this user will be created for the default hostgroup 0), you run a simple command against the admin interface for ProxySQL:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032
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 5
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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.

mysql> INSERT INTO mysql_users (username, password) VALUES ('user1', '123456');

#
# Because of the multi-layered configuration system, this user is right now only
# in memory (so not comitted to runtime).
#

Now you can connect with that user and run queries, if you like:

$ mysql -u user1 -p123456 -h 127.0.0.1 -P6033
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 6
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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.

mysql>

Please note that connecting to ProxySQL does not necessarily mean that this user has also been connected to a backend server. This may happen later on, when you run the first query, depending on the internal state of the proxy.

Configuration changes are not persisted after a restart of ProxySQL. Why?

First of all, by design, ProxySQL is not supposed to be restarted too often. A couple of the scenarios that currently force you to restart it:

  • modifying an admin variable that cannot be changed at runtime (such as the thread stack size). In theory, this is also possible to implement, but very complicated, so we chose to refrain from doing it.
  • updating the software to a newer version (we will prefer less frequent but more stable releases)
  • internal crash, in case you're running an unstable version of the proxy, such as a nightly build

Changes get lost on restart because they are not persisted to disk by default. Remember, ProxySQL has a multi-layer configuration system. In order to persist the configuration to disk, you must know which part of the configuration you want to persist and use one of the following commands:

  • SAVE MYSQL USERS TO DISK
  • SAVE MYSQL SERVERS TO DISK
  • SAVE MYSQL QUERY RULES TO DISK
  • SAVE MYSQL VARIABLES TO DISK
  • SAVE ADMIN VARIABLES TO DISK

There is currently no way to save all the changes to disk across al configuration items and no way to see the difference between what is stored on the disk and what is currently in memory. On the disk they are stored in the SQLite3 database binary format.

Can ProxySQL do automatic failover?

ProxySQL supports failover, but does not do the failover itself (by design, it's not a database manager). It can very easily complement existing solutions such as MMM and MHA.

How does it complement them?

  • monitors the value of the read_only variable and knows where to direct traffic in case the replication manager performs failover. In this way, the application does not know a failover has been performed and does not need to be reconfigured in any way
  • in case of multiple servers from which reading is done, ProxySQL is the only proxy on the market that can detect the failure of a query while it is executing and redirect the query to another backend server without the application ever knowing there was a glitch