Problem after moving mysql database

Posted on 2014-10-27
Medium Priority
Last Modified: 2014-10-28

When the IT dept set up my Linux server with MySQL they set the data directory to /var/lib/mysql

Now the system disk is full.

So there is a disk with loads of space which i have copied of the database to using:

cp /var/lib/mysql /data

then i edited the my.cnf file and changed the row that Points to the database to:

datadir = /data

I deleted some old log files to create some space.

Then i restarted the server.

But i now get an error message in mysqld.log that says:

Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

How can i solve this?  I have seen examples that say you can use:

mysql_install_db –user=mysql –ldata=/newlocation

but i just want to be certain i am not doing a total new install by type in that command.

I know nothing about Linux/MySQL so keep you answers to beginner level!
Question by:soozh
  • 2

Assisted Solution

Stampel earned 800 total points
ID: 40406836
you could just move the data and symlink /var/lib/mysql to /whatever/you/want
without changing the configuration. I have done this a couple of times for production systems.

Expert Comment

ID: 40406842
i suspect someone could have upgraded mysql without upgrading data and you are hitting this cause you did restart mysql what nobody did before.

Usually when you it this error of missing table you would run "mysql_upgrade -p"
That would fix the problem with this table
LVL 13

Accepted Solution

Sandy earned 1200 total points
ID: 40407822
1. Stop MySQL using the following command:
sudo /etc/init.d/mysql stop

2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:
sudo cp -R -p /var/lib/mysql /newpath

3. you already modified the "datadir" parameter

4. Restart MySQL with the command:

/etc/init.d/mysql restart

Hope this helps ... Keep posted


Author Closing Comment

ID: 40407848
Thanks for the help.  I did want sandy suggested but i had copied the wrong directory level.  So now everything is working.  Thanks for the help.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Can you run Linux on a Windows system?  Yep.  Here's how.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question