Problem moving mySQL database to second hard drive

Asked by Brian Heup on 2008-11-20

I am developing a database in mySQL to handle the eventual development of a website. I have 2 hard drives in this box, an 80-GB system drive and a 250-GB drive to be used for data. I am running Ubuntu 8.04 with LAMP server installed. I have copied the files from /var/lib/mysql to /data, which is mounted as /dev/sdb. My issue is that when I try to point my.cnf to the new location, mysqld fails to start, meaning I have no access to the database. Please help!

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu mysql-dfsg-5.0 Edit question
Assignee:
No assignee Edit question
Solved by:
Brian Heup
Solved:
2008-12-08
Last query:
2008-12-08
Last reply:
2008-11-26

First change the owner of the directories to "mysql" user
Then change the permission of all the folders/files to 700

Then try to restart the server and report what happens. Please post the exact output you get when restarting.

Brian Heup (brianheup) said : #2

Ok, here's what I see after doing that in the command "tail /var/log/messages":
Nov 24 12:30:11 server1 kernel: [ 455.744095] audit(1227551411.771:12): type=1503 operation="inode_create" requested_mask="w::" denied_mask="w::" name="/var/lib/mysql/server1.lower-test" pid=6099 profile="/usr/sbin/mysqld" namespace="default"
Nov 24 12:30:11 server1 kernel: [ 455.745242] audit(1227551411.771:13): type=1503 operation="inode_create" requested_mask="w::" denied_mask="w::" name="/var/lib/mysql/server1.lower-test" pid=6099 profile="/usr/sbin/mysqld" namespace="default"
Nov 24 12:30:11 server1 kernel: [ 455.770554] audit(1227551411.791:14): type=1503 operation="inode_permission" requested_mask="rw::" denied_mask="rw::" name="/var/lib/mysql/ibdata1" pid=6099 profile="/usr/sbin/mysqld" namespace="default"

This log is totally geekish.... Its going over my head.

One simple question? After changing those permissions and users what problems are you facing?

Brian Heup (brianheup) said : #4

When I type 'mysql' in at the command prompt, I receive the following error: ERROR 2002: Can't connect to local MySQL server through socket /var/run/mysqld/mysqld.sock

ls -l /usr/bin/mysql
AND
ls -l /var/run/mysqld/mysqld.sock

Brian Heup (brianheup) said : #6

brian@server1:~$ ls -l /usr/bin/mysql
-rwxr-xr-x 1 root root 77808 2008-11-14 13:17 /usr/bin/mysql
brian@server1:~$ ls -l /var/run/mysqld/mysqld.sock
ls: cannot access /var/run/mysqld/mysqld.sock: No such file or directory

________________________________
From: Manish Sinha <email address hidden>
To: <email address hidden>
Sent: Monday, November 24, 2008 2:02:51 PM
Subject: Re: [Question #52047]: Problem moving mySQL database to second hard drive

Your question #52047 on mysql-dfsg-5.0 in ubuntu changed:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

    Status: Open => Answered

Manish Sinha proposed the following answer:
ls -l /usr/bin/mysql
AND
ls -l /var/run/mysqld/mysqld.sock

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047/+confirm?answer_id=4

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

You received this question notification because you are a direct
subscriber of the question.

Your system doesnt contain /var/run/mysqld/mysqld.sock

You need to create it. It should come up as
srwxrwxrwx 1 mysql mysql 0 2008-11-24 16:56 /var/run/mysqld/mysqld.sock

You can create it, change the file ownership to mysql, but I am unable to set that s bit at the starting

Brian Heup (brianheup) said : #8

Alright I have started over, as I am still having no luck.

________________________________
From: Manish Sinha <email address hidden>
To: <email address hidden>
Sent: Tuesday, November 25, 2008 8:41:56 AM
Subject: Re: [Question #52047]: Problem moving mySQL database to second hard drive

Your question #52047 on mysql-dfsg-5.0 in ubuntu changed:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

    Status: Open => Answered

Manish Sinha proposed the following answer:
Your system doesnt contain /var/run/mysqld/mysqld.sock

You need to create it. It should come up as
srwxrwxrwx 1 mysql mysql 0 2008-11-24 16:56 /var/run/mysqld/mysqld.sock

You can create it, change the file ownership to mysql, but I am unable
to set that s bit at the starting

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047/+confirm?answer_id=6

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

You received this question notification because you are a direct
subscriber of the question.

I have a workaround, open Nautilus in root mode.type in terminal
manish@Blackbeauty:~$ gksudo nautilus
and then you open in root permissions.
Then copy /var/run/synaptic.socket to /var/run/mysqld/ folder and rename
the synaptic.socket to mysqld.sock
And then try to restart the server

Or in command line method
sudo cp /var/run/synaptic.socket /var/run/mysqld/mysqld.sock

Best of Luck!

Brian Heup (brianheup) said : #10

I'm still scratching my head. Here's why: I am back to the base install, with my 80-GB hard drive hosting my system and 250-GB mounted as /data. I have not copied or moved anything as of yet. If I run a status command in mysql, it looks like things are running properly. I opened a second terminal to see if mysqld.sock is there, and it is there along with mysqld.pid under /var/run/mysqld. So the base install looks ok. Please guide me through step-by-step to set my data directory under /data. Thanks! At this time, I do not have a database running, I am starting from scratch once I get the setup proper.

Brian

________________________________
From: Manish Sinha <email address hidden>
To: <email address hidden>
Sent: Wednesday, November 26, 2008 2:47:19 AM
Subject: Re: [Question #52047]: Problem moving mySQL database to second hard drive

Your question #52047 on mysql-dfsg-5.0 in ubuntu changed:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

    Status: Open => Answered

Manish Sinha proposed the following answer:
I have a workaround, open Nautilus in root mode.type in terminal
manish@Blackbeauty:~$ gksudo nautilus
and then you open in root permissions.
Then copy /var/run/synaptic.socket to /var/run/mysqld/ folder and rename
the synaptic.socket to mysqld.sock
And then try to restart the server

Or in command line method
sudo cp /var/run/synaptic.socket /var/run/mysqld/mysqld.sock

Best of Luck!

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047/+confirm?answer_id=8

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

You received this question notification because you are a direct
subscriber of the question.

Brian Heup (brianheup) said : #11

By the way, I am running the LAMP server as well, if that helps. Thanks!

________________________________
From: Brian Heup <email address hidden>
To: <email address hidden>
Sent: Monday, December 1, 2008 7:24:38 PM
Subject: Re: [Question #52047]: Problem moving mySQL database to second hard drive

Your question #52047 on mysql-dfsg-5.0 in ubuntu changed:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

    Status: Answered => Open

You are still having a problem:
I'm still scratching my head. Here's why: I am back to the base
install, with my 80-GB hard drive hosting my system and 250-GB mounted
as /data. I have not copied or moved anything as of yet. If I run a
status command in mysql, it looks like things are running properly. I
opened a second terminal to see if mysqld.sock is there, and it is there
along with mysqld.pid under /var/run/mysqld. So the base install looks
ok. Please guide me through step-by-step to set my data directory under
/data. Thanks! At this time, I do not have a database running, I am
starting from scratch once I get the setup proper.

Brian

________________________________
From: Manish Sinha <email address hidden>
To: <email address hidden>
Sent: Wednesday, November 26, 2008 2:47:19 AM
Subject: Re: [Question #52047]: Problem moving mySQL database to second hard drive

Your question #52047 on mysql-dfsg-5.0 in ubuntu changed:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

    Status: Open => Answered

Manish Sinha proposed the following answer:
I have a workaround, open Nautilus in root mode.type in terminal
manish@Blackbeauty:~$ gksudo nautilus
and then you open in root permissions.
Then copy /var/run/synaptic.socket to /var/run/mysqld/ folder and rename
the synaptic.socket to mysqld.sock
And then try to restart the server

Or in command line method
sudo cp /var/run/synaptic.socket /var/run/mysqld/mysqld.sock

Best of Luck!

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047/+confirm?answer_id=8

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+question/52047

You received this question notification because you are a direct
subscriber of the question.

You received this question notification because you are a direct
subscriber of the question.

Brian Heup (brianheup) said : #12

I finally have it working! Here's what was missed: after the copy/move of the data directory, I had to update AppArmor as follows:

vi /etc/apparmor.d/usr.sbin.mysqld
add the new location:
/var/www/mysql_datadir/ r,
/var/www/mysql_datadir/** rwk,

restart apparmor:
/etc/init.d/apparmor restart

After performing this, I restarted mysql with the command /etc/init.d/mysql start, and it was successful. I then rebooted my server, was able to go into phpMyAdmin, create a table, then listed the tables in the original location and the new location and was able to see the new table in the new location.

Congratulations Brian!
I don't know much about Apparmor expect that its somewhat like SELinux. Its great you found out the way yourself.

Cheers!