Logo Questions Linux Laravel Mysql Ubuntu Git Menu

MySQL show all user defined variables

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?

like image 682
fidel150992 Avatar asked Mar 17 '23 15:03


2 Answers

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

See table performance_schema.user_variables_by_thread


like image 172
Marc Alff Avatar answered Mar 19 '23 05:03

Marc Alff

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:


| 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

like image 40
SherylHohman Avatar answered Mar 19 '23 05:03