Thread: Here is a script to move mysql datafiles to new location

please don't flame me, not script writer, thought script might useful somebody. feel free edit , improve it.
developed ubuntu , therefore assumes my.cnf @ /etc/mysql/my.cnf, can overidden on command line.
requires mysql down minimum of time syncing directories(twice) before stopping mysql , doing another(quick) sync after mysql has been stopped.
usage:
have "chmod 777 move-mysql-database.txt" make executable , possibly rename suit needs.

move-mysql-database.txt <from dir> <to dir> <mysql conf file>
<from dir> must not include ending slashes e.g. /var/lib/mysql
<to dir> must not include ending slashes e.g. /mysql_data
<mysql conf dir> full filename of my.cnf e.g. /etc/mysql/my.cnf
move standard location /mysql_data
move-mysql-database.txt /var/lib/mysql /mysql_data
or
move-mysql-database.txt where
find out running mysqld looks my.cnf

if source directory not exist abort.
if destination directory not exist create it(after confirm should) , set owner/group mysql , permissions 711. if exists ask whether should remove files in - beware remove data files. remove remove ibdata* files(mysql default names innodb data files).

uses rsync copy files (found example on cpanel forum).
uses sed replace old directories new location - note innodb_home_directory updated new location(or inserted if not exist). syncs ibdata* files, not complete directory.

advised not move innodb datafiles mysql ('datadir' entry in my.cnf) directory.

can run while mysql up. stop @ appropriate moment , restart after change complete. if mysql server not running when script run, leave not running, have execute hand.

if fails restart mysql server, copy backup of my.cnf , restart old configuration (if mysql server running when script started).

new version lot more checking try , prevent accidental corruption of mysql database or installation.

main intention of script move innodb datafiles if run (potential) space problem , need move files directory more space or move innodb files different location mysql files(datadir directive in my.cnf) after having set database(what needed for).
general flow is:

1. checks current running mysql uses entered source directory , further basic checks
2. check target dir exists , deletes ibdata* files it, or creates target directory.
3. rsync (twice) ibdata* source target, leaving source intact
4. change my.cnf reflect new target dir 'innodb_data_home_dir'
5. stop mysql
6. rsyncs ibdata* source target, leaving source ibdata* files intact. 1 should quick.
7. restarts mysql (if running)
8. checks if mysqld running , if not, revert saved my.cnf , try starting again.
9. check running mysqld using new locations.
10. checks innodb-file-per-table not set mysql version < 5.6 older versions not support location other 'datadir' setting.
11. innodb-file-per-table not yet implemented don't have 5.6 installed yet - script terminate message.

hope useful somebody.

attached files

• move-mysql-database.sh (12.0 kb, 92 views)

found serious flaw in script:
if my.cnf not have entry "innodb_data_home_dir" or current directory setting not match 'from directory' entered, not updated correctly , old configuration still used, though new directory has been created.

working on fixing this. on using linux util insert entry if not there appreciated, otherwise have write program update[solved using sed]. has follow [mysqld] setting, prefered after innodb comment:

code:

[mysqld] ... # * innodb innodb_data_home_dir = /var/lib/mysql ... ...

new version uploaded - see first post

Forum The Ubuntu Forum Community Ubuntu Specialised Support Development & Programming Programming Talk Here is a script to move mysql datafiles to new location

Ubuntu