In this blog post, I’ll look at how to use the loose_ option prefix in my.cnf in MySQL.
mysqld throws errors at startup – and refuses to start up – if a non-existent options are defined in the my.cnf file.
For example:
2016-10-05 15:56:07 23864 [ERROR] /usr/sbin/mysqld: unknown variable 'bogus_option=1'
The MySQL manual has a solution: use the loose_ prefix option in my.cnf file.
In the prior example, if we specify loose_bogus_option=1 in the my.cnf file, instead of bogus_option=1, mysqld starts successfully and ignores that option.
This is useful in three situations:
- Using Percona Server-specific options in a config file that might be used for MySQL Community instances.
- Including options from a future version of MySQL.
- Setting options for plugins before they’re loaded.
Use Case 1:
We can use this feature to make a my.cnf file that will work on both MySQL Community and Percona Server, but contains options that aren’t implemented in MySQL Community. For example:
slow_query_log=ON long_query_time=0 loose_log_slow_rate_limit = 100
Without the loose_ prefix on log_slow_rate_limit, MySQL Community will throw a fatal error (as log_slow_rate_limit is not implemented in MySQL Community).
Use Case 2:
If you push out the same my.cnf file to multiple versions of MySQL, you can still use features that only exist on newer versions:
loose_super_read_only = ON
Use Case 3:
Another use case is installing the Percona audit log plugin. If you specify the
audit_log
options in my.cnf, and then restart the server before running INSTALL PLUGIN, mysqld will fail. If you use the loose_ prefix, mysqld will start up successfull, and the options will be read when you run INSTALL PLUGIN.
loose_audit_log_file = /audit/audit.log loose_audit_log_rotate_on_size = 1073741824 loose_audit_log_rotations = 5 loose_audit_log_format = JSON
This trick also works for options given on the command line, e.g. mysqld --loose-bogus-option.