MySQL parameter introduction and setting

Classification of parameters

  • GLOBAL parameters: GLOBAL
    • Modifiable parameters
    • Parameters cannot be modified
  • SESSION parameter: SESSION
    • Modifiable parameters
    • Parameters cannot be modified

1: the user can modify the non read-only parameters online. The read-only parameters can only be set in the configuration file in advance, and can take effect after restarting the database instance.

2: all the parameters (GLOBAL/SESSION) that have been modified online will be lost after restart, and will not be written as my.cnf, so the modification cannot be persisted

3: some parameters exist in both GLOBAL and SESSION, such as autocommit (PS: MySQL is committed by default)

1. View parameters

mysql> show variables; # Display all parameters of the current mysql, and no hidden parameters
mysql> show variables like "max_%"; #Look up variables starting with max

2. Set parameters

  • Set global parameters

    mysql> set global slow_query_log = off; #Without global, error will be prompted
                                            #Slow query log is a global parameter
    
    mysql> set slow_query_log = off;  # The following error is reported. The default is the session parameter
    ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
    
  • Set session parameters

    mysql> set autocommit = 0;  # Current session takes effect
    # perhaps
    mysql> set session autocommit = 0;  # Current session takes effect
    

    autocommit also has the same parameters in GLOBAL

    mysql> set global autocommit = 1; #Current instance, global effective
    

    Note: if / etc/init.d/mysqld restart at this time, the global autocommit value will become the default value, or it depends on the setting value of my.cnf.

    The effect of implementation is as follows:

    mysql> show variables like "slow%"; # The original value is ON
    +---------------------+----------+
    | Variable_name       | Value    |
    +---------------------+----------+
    | slow_launch_time    | 2        |
    | slow_query_log      | OFF      |
    | slow_query_log_file | slow.log |
    +---------------------+----------+
    3 rows in set (0.00 sec)
    
    mysql> select @@session.autocommit; # Equivalent to slect @ autocomit;
    +----------------------+
    | @@session.autocommit |
    +----------------------+
    |                    0 |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> select @@global.autocommit;       
    +---------------------+
    | @@global.autocommit |
    +---------------------+
    |                   1 |
    +---------------------+
    1 row in set (0.00 sec)
    

Tags: MySQL Session Database

Posted on Sun, 01 Dec 2019 12:36:36 -0800 by ash4u