MySql 5.6 error (Table ‘thesql.user’ doesn’t exist) when started as a daemon

MySQL

We can't seem to get the MySql daemon to start when using the included init script…

A clean install on linux (ubuntu 14.04) was done using the generic MySql binary tarball. The following post-install steps were successful: (NOTE: installation locations and owner account were customized)

$ mysql_install_db –user=mysql-owner –basedir=/mysql-path –datadir=/mysql.data-path

$ mysqld_safe –user=mysql-owner –basedir=/mysql-path –datadir=/mysql.data-path

$ mysql_secure_installation

After those steps were completed (the last resulting in a changed 'root' password and some additional security locks), the mysqld service was stopped.

Subsequently, the 'mysql.server' init script was modified accordingly:

basedir='/mysql-path'
datadir='/mysql.data-path'

Along with my.cnf:

user=the_mysql_owner
basedir='/mysql-path'
datadir='/mysql.data-path'

After manually executing the init script:

./mysql.server start

The service fails to start and produces the following output in the hostname.err file:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
mysqld_safe mysqld from pid file /mysql-path/run/mysql.pid ended

All of the references to this error seem to be based upon incorrect ownership/privileges and/or corruption due to an upgrade routine. Well, this is a clean install (on a new server), and the service seemingly runs fine when manually started from the command line, so I don't see it being an ownership issue. It "feels" like the init script is dropping a configuration setting, but which one…?

Best Answer

I've conquered such issue before, pay attention that there's an original data dir existed in x:/your_mysql_5.6_install_dir/data.

if you have defined datadir=xxx in my.ini, and the path is not the one x:/your_mysql_5.6_install_dir/data, please do the following things:

  1. copy x:/your_mysql_5.6_install_dir/data to the destination folder.
  2. start MySQL

e.g. if you have defined: [mysqld] basedir=C:/mysql-5.6.43-winx64 datadir=C:/ProgramData/MySQL/MySQL 5.6 server/data

you need to copy C:/mysql-5.6.43-winx64/data to C:/ProgramData/MySQL/MySQL 5.6 server/data

then start your MySQL.

this has been described in https://dev.mysql.com/doc/refman/5.6/en/windows-create-option-file.html:

If you would like to use a data directory in a different location, you should copy the entire contents of the data directory to the new location. For example, if you want to use E:\mydata as the data directory instead, you must do two things:

1) Move the entire data directory and all of its contents from the default location (for example C:\Program Files\MySQL\MySQL Server 5.6\data) to E:\mydata.

2) Use a --datadir option to specify the new data directory location each time you start the server.

Related Question