skip to Main Content

I read Syntax for Persisting System Variables in MySQL documentation about PERSIST and PERSIST_ONLY as shown below:

  • To persist a global system variable to the mysqld-auto.cnf option file in the data directory, precede the variable name by the PERSIST keyword or the @@PERSIST. qualifier:
SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;
  • To persist a global system variable to the mysqld-auto.cnf file without setting the global variable runtime value, precede the variable name by the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier:
SET PERSIST_ONLY back_log = 100;
SET @@PERSIST_ONLY.back_log = 100;

It seems like PERSIST sets a global variable runtime value but PERSIST_ONLY doesn’t set a global variable runtime value but I don’t understand what a global variable runtime value is, so I don’t really understand the difference between PERSIST and PERSIST_ONLY.

My questions:

  1. What is the global variable runtime value?
  2. What is the difference between PERSIST and PERSIST_ONLY?

2

Answers


  1. Chosen as BEST ANSWER

    To set a global variable runtime value, PERSIST doesn't need to restart MySQL while PERSIST_ONLY needs to restart MySQL.

    For example, a global variable runtime value is "max_connections" which is "151" by default as shown below:

    mysql> SELECT @@GLOBAL.max_connections;
    +--------------------------+
    | @@GLOBAL.max_connections |
    +--------------------------+
    |                      151 |
    +--------------------------+
    

    Now, with PERSIST, if setting "500" to "max_connections" as shown below:

    mysql> SET PERSIST max_connections = 500;
    

    Then, "max_connections" is now "500" as shown below:

    mysql> SELECT @@GLOBAL.max_connections;
    +--------------------------+
    | @@GLOBAL.max_connections |
    +--------------------------+
    |                      500 |
    +--------------------------+
    

    But with PERSIST_ONLY, if setting "500" to "max_connections" as shown below:

    mysql> SET PERSIST_ONLY max_connections = 500;
    

    Then, "max_connections" is still "151" as shown below:

    mysql> SELECT @@GLOBAL.max_connections;
    +--------------------------+
    | @@GLOBAL.max_connections |
    +--------------------------+
    |                      151 |
    +--------------------------+
    

    Then, stop, start and login MySQL again:

    C:WindowsSystem32>net stop MySQL80 && net start MySQL80 && mysql -u root -p
    

    Then finally, "max_connections" is now "500" as shown below:

    mysql> SELECT @@GLOBAL.max_connections;
    +--------------------------+
    | @@GLOBAL.max_connections |
    +--------------------------+
    |                      500 |
    +--------------------------+
    1 row in set (0.00 sec)
    

  2. what the global variable runtime value is .. what is the difference between PERSIST and PERSIST_ONLY?

    There exists a bunch of global option variables which effects the server. They effects the whole server. Changing the variable value changes the server behavior immediately.

    Option variables values are loaded from option files during server start. If some option have no according row in the file then hardcoded default value is used.

    Option variables are not reloaded when the server works. Changing the setting in the options file does not effect the server until it restarts.

    So when you use PERSIST then new value is written into the option file (and it will be applied during the next server start) and is set to current server settings (the server alters its work according this new setting).

    When you use PERSIST_ONLY then new value is written into the option file (and it will be applied during the next server start) but it is not set to current server settings (and current server behavior is not changed).

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search