1

My problem yesterday (MySQL - Alter Table Fails As Workbench Tries To Create Existing Table) was caused by /dev/sda2 (/root) running out of space.

The cause of this was that, by default, MySQL had installed the DataDirectory (datadir) on /dev/sda2(/root).
I therefore recursively copied its contents to /dev/sda3 (/home) after stopping the MySQL server.
I also editted /etc/init.d/mysql, /etc/mysql/my.cnf and /etc/apparmor.d/usr.bin.mysqld to reflect this change.

After both trying to restart the MySQL server and a reboot the server is still not running. The error.log in /var/log/mysql is also not being appended to. Obviously I have missed something but what? I am running Linux Mint 13.

Improve this question

1 Answer 1

Reset to default
0

I have done the same thing. Be absolutely sure that all rights and owners are intact. You can do this with cp -rp if I'm correct. Rsync the folder locally should also keep owners and rights intact. You don't have to change the config however. A symlink will do.

  • Rename the original datadir to datadir-old or something similar.
  • Then create a symlink to the new location.
  • If you want the symlink to have right owner, create a new folder (tmp), move the symlink inside that folder, then change the owner of the tmp folder and its contents, then move the symlink back.
  • Undo the changes to the config files.

On the commandline... (I haven't tried this so check if all is as it should be!)

sudo -i
cd /var/lib
mv mysql mysql-old
ln -s /home/mysql mysql
mkdir tempdir
mv mysql tempdir
chown -R mysql:mysql tempdir
mv tempdir/mysql .
rm -rf tempdir

In the directory listing I see the following on my system. Yours should be similar!

drwx------  7 mysql         mysql          4096 Feb  6 12:01 mysql  

-rw-rw----  1 mysql mysql 1671430144 Feb  6 17:37 ibdata1  
-rw-rw----  1 mysql mysql    5242880 Feb  6 17:37 ib_logfile0  
-rw-rw----  1 mysql mysql    5242880 Jan 28 14:35 ib_logfile1  
drwx------  2 mysql mysql      24576 Jan 28 14:34 testdatabase_local  
drwx------  2 mysql mysql       4096 Jan 28 13:38 mysql
-rw-rw----  1 mysql mysql          6 Jan 28 13:38 mysql_upgrade_info
drwx------  2 mysql mysql       4096 Jan 28 13:38 performance_schema

Try this and see if that fixes your problem.

Improve this answer
2
  • Just tried this. Had to change the last but one line to "mv tempdir mysql" as original caused response of "does not exist. The last line thus became irrelevant. On trying to restart mysql I got "2015-02-11 17:05:55 3311 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist" so not sure where next? Listing was similar...
    – MichaelJohn
    Commented Feb 11, 2015 at 17:21
  • The last five lines only have the purpose of changing the owner of the symlink to mysql:mysql. (1) create the tempdir, then (2) move the symlink into that tempdir, (3) change the owner of tempdir and all its contents (the symlink) to mysql:mysql, then (4) move the symlink back into its old position and finally (5) remove the tempdir. If my code doesn't work, then create it yourself. I just explained how it works, but my code may have an error in it. I didn't test it. Then do ls -al mysql, and see if that returns the same results as ls -al mysql-old. If not, it won't work.
    – SPRBRN
    Commented Feb 11, 2015 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.