The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

A script to move the location of innodb data files

Discussion in 'Database Discussions' started by shumifan50, Oct 4, 2012.

  1. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Please don't flame me, I am not a script writer, but I thought this script might be useful to somebody. Feel free to edit and improve it.
    It was developed for Ubuntu and therefore assumes that my.cnf is at /etc/mysql/my.cnf
    Usage:
    You will have to "chmod 777 move-mysql-database.txt" to make it executable and possible rename it to suit your needs.
    move-mysql-database.txt <from dir> <to dir>
    from dir must include slashes e.g. /var/lib/mysql/
    to dir must also include slashes e.g. /mysql_data/
    so to move from the standard location to /mysql_data
    move-mysql-database.txt /var/lib/mysql/ /mysql_data/

    If the source directory does not exist it will abort.
    If the destination directory does not exist it will create it(after you confirm it should) and set owner/group to mysql and permissions to 711

    It uses rsync to copy the directories (found example here on cPanel forum).
    It uses sed to replace the old directories with the new location - note that both innodb_home_directory and mysqld datadir will be updated to the new location.

    It can be run while mysql is up. It will stop it at the appropriate moment and restart it after the change is complete. If mysql server is not running when the script is run, it will leave it not running, you will have to execute it by hand.

    if it fails to restart mysql server, it will copy back the backup of my.cnf and restart with the old configuration (if mysql server was running when the script was started).

    I hope it is useful to somebody.
     

    Attached Files:

  2. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Found a serious flaw in the script:
    If my.cnf does not have an entry for "innodb_data_home_dir" or the current directory setting for it does not match the 'from directory' entered, then it will not be updated correctly and the old configuration will still be used, even though the new directory has been created.

    I am working on fixing this. Any help on using a Linux util to insert the entry if it is not there will be appreciated, otherwise I will have to write a program to do the update. It has to follow the [mysqld] setting, prefered after the InnoDB comment:
    Code:
    [mysqld]
    ...
    # * InnoDB
    innodb_data_home_dir = /var/lib/mysql
    ...
    ...
    
     
  3. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Here is a better version of the script to move mysql datafiles to new location
    Please don't flame me, I am not a script writer, but I thought this script might be useful to somebody. Feel free to edit and improve it.
    It was developed for Ubuntu and therefore assumes that my.cnf is at /etc/mysql/my.cnf, but it can be overidden on command line.
    It requires mysql to be down for a minimum of time by syncing the directories(twice) before stopping mysql and then doing a another(quick) sync after mysql has been stopped.
    Usage:
    You will have to "chmod 777 move-mysql-database.txt" to make it executable and possibly rename it to suit your needs.

    WARNING: THIS SCRIPT WILL NOT WORK IF YOUR INNODB DATAFILES HAVE BEEN RENAMED and are no longer called ibdata*.

    move-mysql-database.txt <from dir> <to dir> <mysql conf file>
    <from dir> must include slashes e.g. /var/lib/mysql/
    <to dir> must also include slashes e.g. /mysql_data/
    <mysql conf dir> is the full filename of my.cnf

    so to move from the standard location to /mysql_data
    move-mysql-database.txt /var/lib/mysql/ /mysql_data/

    If the source directory does not exist it will abort.
    If the destination directory does not exist it will create it(after you confirm it should) and set owner/group to mysql and permissions to 711. If it exists it will ask you whether it should remove the files in it - BEWARE THAT YOU ONLY REMOVE THE DATA FILES. The remove will remove all ibdata* files(mysql default names for innodb data files).

    It uses rsync to copy the directories (found example on cPanel forum).
    It uses sed to replace the old directories with the new location - note that both innodb_home_directory only will be updated to the new location(or inserted if it does not exist). It only syncs ibdata* files, not the complete directory.

    It is strongly advised not to move the innodb datafiles to the mysql ('datadir' entry in my.cnf) directory.

    It can be run while mysql is up. It will stop it at the appropriate moment and restart it after the change is complete. If mysql server is not running when the script is run, it will leave it not running, you will have to execute it by hand.

    if it fails to restart mysql server, it will copy back the backup of my.cnf and restart with the old configuration (if mysql server was running when the script was started).

    This new version does a lot more checking to try and prevent accidental corruption of the mysql database or installation.

    I hope it is useful to somebody.
     

    Attached Files:

    #3 shumifan50, Oct 7, 2012
    Last edited: Oct 8, 2012
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Do you have it performing a mysqldump before doing anything? Otherwise, I highly suggest that is added.
     
  5. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    @Tristan:
    No it does not do a mysqldump as in the case of large databases that can take hours, and the objective of this script is to keep the database unavailable for as short a time as possible; hence the many rsyncs that are run, keeping each one, after the first one, fairly quick.
    However,
    It does not physically remove the 'pre move ' datafiles, that has to be done manually, which is why it recovers by re-instating the 'pre move' configuration and restarting mysql.

    The main intention of this script is to move the innodb datafiles if you run into a (potential) space problem and need to move the files to a directory with more space or to move the innodb files to a different location from the mysql files after having set up the database(what I needed it for).
    The general flow is:

    1. Checks current running mysql uses the entered source directory and some further basic checks
    2. Check target dir exists and deletes ibdata* files from it, or creates the target directory.
    3. rsync (twice) ibdata* from source to target, leaving source intact
    4. Change my.cnf to reflect new target dir for 'innodb_data_home_dir'
    5. Stop mysql
    6. rsyncs ibdata* from source to target, leaving source ibdata* files intact. This one should be really quick.
    7. Restarts mysql (if it was running)
    8. Checks if mysqld is running and if not, revert to saved my.cnf and try starting again.
    9. Ckec that the running mysqld uses the new locations and output error if not.

    ADDED:
    Running
    Code:
               move-mysql-database.sh where
    
    will display the order in which the running mysqld reads the my.cnf files and which ones exist.
     

    Attached Files:

    #5 shumifan50, Oct 8, 2012
    Last edited: Oct 8, 2012
  6. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Added checks for innodb-file-per-table as mysql version < 5.6 does not support moving the datafiles to other than 'datadir' setting.
    innodb-file-per-table is not yet implemented as I dont have 5.6 installed yet.

    Sorry for the new posts, but I can't edit previous posts to update the attachment.
     

    Attached Files:

Loading...

Share This Page