During daily work with MySQL databases, specific challenges such as missing tables or errors related to the unrecognized database performance_schema can arise. These problems can significantly affect the performance and stability of database systems, and their diagnosis and resolution can often be complicated and time-consuming. To facilitate this task, I have created this guide, which is the result of my experiences and proven practices. I provide a comprehensive approach to identifying and fixing issues related to performance_schema. It’s quite simple to import the schema from a newly built database.
Of course, it’s best to perform a backup of the database before the entire operation.
Initial Diagnosis in MySQL
Start by identifying the problem in the MySQL shell:
|
1 2 3 4 |
SHOW VARIABLES LIKE 'default_storage_engine'; ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist USE performance_schema; ERROR 1049 (42000): Unknown database 'performance_schema'; |
Creating New Performance Schema in MySQL
Create a new performance schema from the MySQL command line:
|
1 2 3 |
CREATE DATABASE performance_schema; shutdown; \q |
Server Operations to Establish New MySQL Environment
Return to the system to configure a clean MySQL environment:
|
1 2 3 4 5 |
systemctl stop mysql.service mkdir /var/lib/mysql_new mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql_new chown -R mysql:mysql /var/lib/mysql_new cd /var/lib/mysql_new |
Then, start MySQL in safe mode to configure it – without external network interruptions:
|
1 |
sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-grant-tables --skip-networking |
Configuration and Verification in MySQL
Return to the MySQL command line to verify the configuration and configure the new database:
|
1 2 3 4 |
mysql -u root SHOW DATABASES; USE performance_schema; SHOW TABLES; |
As you can see, here performance_schema is working correctly.
Update the root password in the MySQL environment to secure the new installation:
|
1 2 3 4 5 |
USE mysql; UPDATE user SET authentication_string=PASSWORD('NewSecurePassword'); FLUSH PRIVILEGES; shutdown; \q |
Restart MySQL in safe mode to ensure all configurations are applied:
|
1 |
sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-networking |
Configure MySQL Server to Run New Database
After securing the new MySQL configuration, update the server configuration to use the new data directory:
|
1 2 3 |
vi /etc/mysql/my.cnf # Change datadir to: datadir=/var/lib/mysql_new |
Restart the MySQL service to load the my.cnf file during mysql startup:
|
1 |
systemctl start mysql.service |
Export and Restore Performance Schema in MySQL
With the new configuration, export performance_schema using MySQL commands and prepare for its restoration:
|
1 |
mysqldump -u root -p --no-data --skip-lock-tables performance_schema > /root/performance_schema.sql |
It’s necessary to shut down MySQL:
|
1 2 |
shutdown; \q |
Restore the original MySQL data directory on the server, restart the MySQL service, and import the exported schema:
|
1 2 3 4 5 6 |
vi /etc/mysql/my.cnf # Change datadir back to: datadir=/var/lib/mysql_old systemctl start mysql.service mysql -u root -p performance_schema < /root/performance_schema.sql systemctl restart mysql.service |
Make sure the default storage engine is running, e.g:
|
1 2 3 4 5 6 7 |
SHOW VARIABLES LIKE 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) |
Don’t forget to remove the trash:
|
1 |
rm -rf /var/lib/mysql_new/ /root/performance_schema.sql |
If you’ve reached this point, remember to delete the database backup – as long as everything is working. You did make one, right? 🙂