MySQL Global Variables

Summary: in this tutorial, you’ll explore MySQL global variables, discover how to display all of them, filter specific ones, and set new values.

Introduction to MySQL global variables

MySQL global variables are parameters that control the behavior of the MySQL server. You can use the global variables to configure many aspects of the server such as memory allocation, logging, and performance tuning.

Displaying all global variables

To show all global variables, you use the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES;Code language: SQL (Structured Query Language) (sql)

This command returns an output that consists of two columns Variable_name and Value. Here is the partial output:

Filtering global variables

To filter the global variable, you add the LIKE clause to the SHOW GLOBAL VARIABLE statement as follows:

SHOW GLOBAL VARIABLES LIKE 'variable_name';Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the current value of the max_connections variable:

SHOW GLOBAL VARIABLES LIKE 'max_connections';Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that MySQL Server can accept up to 151 concurrent connections.

If you don’t remember the variable name, you can use the wildcard character % and _ to find the variable.

For example, the following statement finds the variable that starts with max_con:

 SHOW GLOBAL VARIABLES LIKE 'max_con%';Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

It returns two global variables max_connect_errors and max_connections.

Another way to return the current value of a variable is to use the SELECT statement:

SELECT @@variable_name;Code language: SQL (Structured Query Language) (sql)

For example:

SELECT @@max_connections;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Setting global variables

To set the value of a global variable, you can use the following statement:

SET GLOBAL variable_name = value;Code language: SQL (Structured Query Language) (sql)

In this statement, you replace the variable_name with the name of the global variable you want to modify and value with the new value you want to set.

For example, the following statement changes the value of the global variable max_connections to 300:

SET GLOBAL max_connections = 301;Code language: SQL (Structured Query Language) (sql)

After changing the variable, you can check its current value using the SHOW GLOBAL VARIABLE statement:

SHOW GLOBAL VARIABLES LIKE 'max_connections';Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 301   |
+-----------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Upon disconnecting and reconnecting from the MySQL server, you’ll notice that the change to the global variable remains in effect.

Summary

• Use the SHOW GLOBAL VARIABLES statement to show all global variables.
• Use the SHOW GLOBAL VARIABLES LIKE variable_name or SELECT @@variable_name to display the current value of the variable_name;
• Use the SET GLOBAL variable_name = value to set a new value for the variable_name.