HostOnNet Blog

MySQL Can’t open and lock privilege tables

Looking for Linux Server Admin or WordPress Expert? We can help.

Recently on of the server had its HDD failed. After moving MySQL data (/var/lib/mysql) to another server MySQL (MariaDB) would not start.

> /var/log/mariadb/mariadb.log
systemctl start mariadb
cat /var/log/mariadb/mariadb.log

On checking log file, i got following errors

170314 14:48:09 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 681545349663
170314 14:48:09 [Note] Plugin 'FEEDBACK' is disabled.
170314 14:48:09 [ERROR] mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
170314 14:48:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
170314 14:48:09 [Note] Recovering after a crash using mysql-bin
170314 14:48:09 [Note] Starting crash recovery...
170314 14:48:09 [Note] Crash recovery finished.
170314 14:48:10 [Note] Server socket created on IP: '0.0.0.0'.
170314 14:48:10 [ERROR] mysqld: Can't find file: './mysql/host.frm' (errno: 13)
170314 14:48:10 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
170314 14:48:10 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

The problem is caused by corrupt tables in “mysql” database, that is used to store MySQL login details and database information.

Since this server had only 1 MySQL user and 2 databases, it was not hard to delete the MySQL database and recreate it.

Problem fixed by running

rm -rf /var/lib/mysql/mysql/*
mysql_install_db --user=mysql --ldata=/var/lib/mysql

Example

[root@localhost mysql]# rm -rf /var/lib/mysql/mysql/*
[root@localhost mysql]# mysql_install_db --user=mysql --ldata=/var/lib/mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
170314 14:49:38 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
170314 14:49:38 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 5210 ...
170314 14:49:39 [ERROR] mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_digest.frm'
170314 14:49:39 [ERROR] mysqld: Incorrect information in file: './performance_schema/host_cache.frm'
170314 14:49:39 [ERROR] mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_digest.frm'
170314 14:49:39 [ERROR] mysqld: Incorrect information in file: './performance_schema/host_cache.frm'
OK
Filling help tables...
170314 14:49:40 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
170314 14:49:40 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 5218 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from MariaDB
Corporation Ab. You can contact us about this at [email protected].
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/

[root@localhost mysql]# 

Now start MariaDB

systemctl start mariadb

After moving MySQL data folder from one server to another, it is better run

mysql_upgrade --force

So all tables get upgraded to current version of MySQL if required.

Posted in MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.