Occasionally, mysql is installed in the wrong filesystem, and as the data build, the filesystem runs out of space. Moving the database is relatively straight forward
1. Ensure there is a good, recent backup. Take an extra backup to be on the safe side
2. Decide where the files will be moved to and create any subdirectories as required. Ensure the permissions are correct
cd /local/mysql
mkdir data
chown mysql:mysql data
root@compnode04[mysql] ls -ltr
total 8
drwxr-xr-x 5 mysql mysql 4096 Mar 17 11:25 data
3. Shutdown the database
4. Tar the existing files – this will preserve the permissions on the files
cd /var/lib/mysql
tar -cvf movefiles.tar *
5. Copy the tar file into the new location
cp movefiles.tar /local/mysql/data
6. Untar the files in the new location
cd /local/mysql/data
tar -xvf movefiles.tar
7. Edit my.cnf
cd /etc
cp my.cnf my.cnf.old
vi my.cnf
change
§ datadir=/var/lib/mysql to datadir=/local/mysql/data
§ log-error=/var/log/mysqld.log to log-error=/local/mysql/data/mysql-netmail.log
NOTE: DO NOT CHANGE LCOATION OF SOCKET or MySQL will not restart
8. Rename location of old files
Makes it easier to drop the old files, and ensures mysql uses the new location when it starts. However, this may be the location of the socket, so will need to recreate the directory so the socket file can be created when MySQL restarts
cd /var/lib
mv mysql mysql.old
9. Recreate the mysql directory, if this is the location of the socket file which was renamed in the above step
mkdir mysql
chown mysql:mysql mysql
10. Restart the database
11. Check that everything is running OK
· Check err file
· Check timestamps on data files
· Ask users to test connectivity
12. Take a backup
13. Delete the old files
cd /var/lib/mysql.old
ls –ltr
check nothing has been updated since mysql restarted
rm –r *
No comments:
Post a Comment