Keep the show_compatibility_56 always ON in MySQL

Question

I needed to set up the system variable "show_compatibility_56" to ON in MySQL. So, I run the command set global show_compatibility_56 = on;, and it worked However, I noticed that whenever I stop and then start the MySQL server, this variable goes back to OFF. Any hints on how to keep it always ON even if I restart the server?

Answer 1

I'm using a Laravel Homestead (Vagrant) box (MySql Ver 14.14 Distrib 5.7.17).

I needed to SSH into Homestead and then run:

echo "[mysqld]
show_compatibility_56 = ON
performance_schema" | sudo tee -a /etc/mysql/conf.d/mysql.cnf  >/dev/null    
sudo service mysql restart

(Thanks to Mark Reed for showing how to skip opening vim.)

Older version:

sudo vim /etc/mysql/conf.d/mysql.cnf

Then I added this section:

[mysqld]
show_compatibility_56 = ON
performance_schema

I was surprised that other answers here and elsewhere on the web didn't specify that it needed to be under [mysqld] instead of [mysql] and also that you must restart the MySql service:

sudo service mysql restart

Answer 2

you need to save this variable setting in your configuration file my.cnf for linux and my.ini for windows.

Answer 3

To make it permanent, you need to add this variable in configuration file of MySQL like we did for all other variables as:

show_compatibility_56 = ON

For Linux based system: File name is my.cnf and default location is /etc/my.cnf

For Windows based system: File name is my.ini and default location is your windows mysql data directory that you can check via below command:

show variables like 'datadir';