MySQL show all user defined variables

Question

I set two user-defined variables as shown below but after some time, I forgot the names:

SET @a = 2, @b = 3;

So, does MySQL have the command that displays all user-defined variables?

Answer 1

Starting with MySQL 5.7, the performance schema exposes user variables.

See table performance_schema.user_variables_by_thread

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-user-variable-tables.html

Answer 2

If you have MariaDB (a binary "drop-in" equivalent of MySQL) there is a plugin available, provided by MariaDB itself.

MariaDB 10.2 (equivalent to MySQL 5.7) and above has a plugin that creates a "USER_VARIABLES" table.
Here is how to install the plugin.

Here is an example of its use:

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;

+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| var           | 0              | INT           | utf8               |
| var2          | abc            | VARCHAR       | utf8               |
+---------------+----------------+---------------+--------------------+

MariaDB installs the plugin by default after version MariaDB 10.2.6.
The link above shows how to install it for prior versions.

Double check what version of "mysql" you're running, because sometimes people will refer to a MariaDB as MySQL, due to its use as a "binary drop in replacement" for MySQL. So it's possible that you are running a MariaDB database.

I am not aware of MySQL providing anything similar.

How to check which version of mysql you're running (the prompt is in bold)
From the command line:
$ mysql -v

From the mysql command client:
mysql> SHOW VARIABLES LIKE "%version%";

It is also shown when you first log into the mysql command client, which you can do via:
$ mysql -u your_mysql_username --password=your_mysql_password