PERSIST vs PERSIST_ONLY (MySQL)

Kai - Kazuya Ito 0
Viewed 28

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?
persist-only persist runtime mysql database
2 Answers

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).

Akina0

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:\Windows\System32>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)
Kai - Kazuya Ito0
Related