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

                        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