MySQL system variables)

MySQL system variables)

MySQL system variables are actually some system parameters used to initialize or set the database's occupation of system resources and file storage location. These System variables can be modified at the global and session level, and some can also be modified dynamically. This article describes some concepts of system variables and how to set and view these system variables.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

1. What are system variables?
System variables are actually used to control some database behavior and method parameters. For example, when we start the database, we set the memory size, isolation level, log file size, storage location, and so on. Of course, after the database system is started, some system variables (parameters) can also be dynamically modified to timely adjust the database. In Oracle, this system variable is controlled by pfile or spfile. It is called a parameter.
System variable values: All values have default values, which can be modified at and after startup.
Set range: Global and session level. The global level requires the super permission. The session level only affects the session itself.
Setting Method: you can modify the settings by using the configuration file and startup options, and SET the settings by using the SET clause after startup.
Effective Period: The global variables are globally visible, but only the clients that connect after the change initialize the corresponding session variables from the global variables. The current session and logged-on session are not affected.

For size-related settings, you can use the suffix K, M, or G to indicate kilobytes, megabytes, or gigabytes, which are case insensitive.

2. set the usage of System Variables

-- Current version
Mysql> show variables like 'version % ';
+ ------------------------- + -------------------------------- +
| Variable_name | Value |
+ ------------------------- + -------------------------------- +
| Version | 5.5.37 |
| Version_comment | MySQL Community Server (GPL) |
| Version_compile_machine | x86_64 |
| Version_compile_ OS | Linux |
+ ------------------------- + -------------------------------- +

-- Get help about set
Mysql> help set
Name: 'set'
Description:
Syntax:
SET variable_assignment [, variable_assignment]...

Variable_assignment:
User_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| [@ Global. | @ session. | @] system_var_name = expr

-- View All System Variables
Root @ localhost [tempdb]> show variables; -- this command outputs all system variables of the current system.

-- View sort_buffer
Mysql> show variables like 'sort _ buffer % ';
+ ------------------ + --------- +
| Variable_name | Value |
+ ------------------ + --------- +
| Sorting _ buffer_size | 2097152 |
+ ------------------ + --------- +

-- The global and session keywords are omitted at the session level.
Mysql> set sort_buffer_size = 1024*1024*4; -- set to 4 M

Mysql> show variables like 'sort _ buffer % ';
+ ------------------ + --------- +
| Variable_name | Value |
+ ------------------ + --------- +
| Sorting _ buffer_size | 4194304 |
+ ------------------ + --------- +

-- Restore to the default value
Mysql> set sort_buffer_size = default;

Mysql> show variables like 'sort _ buffer % ';
+ ------------------ + --------- +
| Variable_name | Value |
+ ------------------ + --------- +
| Sorting _ buffer_size | 2097152 |
+ ------------------ + --------- +

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page