MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

Moving the whole datamysql base to a new location

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


§  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