Solved

Problem after moving mysql database

Posted on 2014-10-27
4
410 Views
Last Modified: 2014-10-28
Hello,

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!
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:Stampel
Stampel earned 200 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.
0
 
LVL 7

Expert Comment

by:Stampel
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
0
 
LVL 13

Accepted Solution

by:
Sandy earned 300 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

TY/SA
0
 

Author Closing Comment

by:soozh
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 53
RPM creation 6 49
Why can I INSERT a DATETIME record into mysql but I can't UPDATE it? 15 68
umask commands 5 22
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

710 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