MySQL Series 8 MySQL Server Variables

Tutorial Series

MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 3 Basics
MySQL Series 4 SQL Syntax
MySQL series five views, stored functions, stored procedures, triggers
MySQL series 6 users and authorization
MySQL Series 7 MySQL Storage Engine
MySQL series 9 MySQL query cache and index
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test

Note: Some of these parameters can be modified at runtime and will take effect immediately; some parameters cannot be modified and can only take effect by modifying the configuration file and restarting the server program; some parameters are global and cannot be changed; some can provide separate (session) settings for each user

1. Server Options

# mysqld --help -verbose #Get all possible options

# mysqld --print-defaults #Get the default settings

#mysqld_safe –-skip-name-resolve=1: Add an option parameter when starting the service to disable reverse IP resolution and improve login efficiency; you can also add the skip_name_resolve=1 parameter to the my.cnf configuration file

2. Server system variables

Server variables are divided into dynamic variables and non-dynamic variables. Dynamic variables can be modified without restarting the server.

Server variables are divided into global variables and session variables. Modification of global variables affects users who log in later; session variables only affect the current session and become invalid after exiting.

MariaDB [(none)]> SHOW GLOBAL VARIABLES;: View all global variables

MariaDB [(none)]> SHOW VARIABLES;: View all current session variables

Modify global variables: only valid for newly created sessions after modification; invalid for already established sessions

mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;

Modify the session variables:

mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;

3. Server Status Variables

View status variables (read-only): variables used to save mysqld running statistics, cannot be changed

MariaDB [(none)]> SHOW GLOBAL STATUS; #Global status variables
MariaDB [(none)]> SHOW STATUS; #Session status variables

Refer to the official documentation:

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables

SQL_MODE

​ sql_mode is both an option and a variable. Setting it can perform some constraint checking tasks. It can be set globally or for the current session.

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_mode'; #View the sql_mode variable, which is empty by default

• NO_AUTO_CREATE_USER prohibits GRANT from creating users with empty passwords
• NO_AUTO_VALUE_ON_ZERO Inserting 0 or NULL into an auto-increment column will not be the next auto-increment value
• NO_BACKSLASH_ESCAPES Backslash "\" is treated as a normal character instead of an escape character
• When PAD_CHAR_TO_FULL_LENGTH is enabled, CHAR data will not be truncated for empty data.
• PIPES_AS_CONCAT Treat "||" as a concatenation operator instead of an "or" operator
• TRADITIONAL: includes STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, which is a collection of parameters

MariaDB [(none)]> SET sql_mode='TRADITIONAL';

Refer to the official documentation: https://mariadb.com/kb/en/library/sql-mode

This concludes the article on MySQL server variables in the eighth series. For more information on MySQL server variables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!