Hi I have tried to move data folder from /var/lib/mysql

MySQL & MariaDB Other MySQL® Questions
1

I tried to move data from folder /var/lib/mysql
to /opt/san_data/

I moved the data as follows:

/opt/san_data/
and[/B]
/opt/san_data/
and[/B]
/var/lib/mysql
and

less /var/log/mysqld.log
gave me :[/B]

140911 11:24:44 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
140911 11:24:44 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Whats wrong with my procedure to move to larger partition ?

2

Hi,

It looks like ubuntu & apparmor restrictions or NFS issue. Can you check OS restrictions on mysql data directory (if selinux installed or apparmor profile loaded), for sure apparmor restricts mysql data directory on ubuntu (and maybe debian) by default.

3

I have the solution to my problem . To save others the hassle here goes:

Create the new partition and filesystem.
Mount it somewhere.
Stop mysql

Copy the entire contents of
/var/lib/mysql
to the new location.

sudo cp -R -p /var/lib/mysql /opt/san_data/

sudo chown -R mysql:mysql /opt/san_data/

Delete /var/lib/mysql

Create a symlink from /var/lib/mysql to the new directory. For example:
use a symbolic link, remove the old /var/lib/mysql directory and run the following.

Use the Percona Configuration Wizzard to get the values for your my.cnf file
https://tools.percona.com/wizard
Edit the my,cnf file
sudo nano /etc/my.cnf
Set the data storage datadir as follows:

DATA STORAGE

datadir = /opt/san_data/mysql

I then had to reset the root users password for server lxtusynx01 :
/usr/bin/mysqladmin -u root password Mypassword*******
/usr/bin/mysqladmin -u root -h lxtusynx01 password Mypassword*********

Now the database works

4

Dont remove the SymLink
I gave the command as I had it wrong and needed to delete the old symlink to redo the symlink

5

Thank you Nijoshi I did fix my problem and posted two comments hopefully to help others
Did however see the my.cnf does not exist in newer Percona so this procedure I put here will no longer apply