Generate system tables for mysql

Sometime when we run mysql_install_db command, if for some reason mysql server was not started, check the error log, most probably it will read following

2014-10-24 13:48:33 7257 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user‘ doesn’t exist

This is because when we ran mysql_install_db script, it was unable to create the system tables for newly installed db for some reason, most probably because of wrong configuration file. We can fix this problem in two ways

1. Copy the mysql data directory from existing installation. NB: it will copy all users and its privilege from existing database.
2. We can run the mysql server with --skip-grant-tables option and create the privilege tables manually.

Option 1 is trivial, so here we will try option2.

Lets start the mysql server with --skip-grant-tables option and check if we are able to connect to it.

$ ./bin/mysqld_safe –defaults-file=/opt/mysql56/my.cnf –skip-grant-tables

Above command should start mysql server for us, now lets try to connect with it

$ ./bin/mysql –defaults-file=/opt/mysql56/my.cnf

If it connects with the mysql server, we are good to proceed. Now we will run SQL query to generate system tables manually from MySQL command line. MySQL installation contains all SQL queries to generate inside “share” folder.

mysql> source share/mysql_system_tables.sql;

mysql> source share/fill_help_tables.sql;

mysql> source share/mysql_system_tables_data.sql;

Running above command will create system tables and fill predefined data. It will also create a user “root” with no password.

Reference: http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

Via — Generate system tables for mysql

• mysql
• mysql install db
• mysql system tables
• troubleshoot
• ubuntu