Customize MySQL data directory from default location to user defined location
MySQL is a widely used open source database. Generally, the data stored in the database is very huge. Depends on the information stored in the database, most of the times user faces an issue with the space allocated for the default partition. In the scenario of running out of space, need to move the data directory from default location to user specific location. This is a short little guide to show you how to move mysql to another partition on the system.
1. Collect complete backup of current database using
mysqldump -u root -p --all-databases > <$TEMP-DIR>/mysqldump.sql
2. Now, stop mysql service using '/etc/init.d/mysqld stop'.
3. By default, MySQL data directory is /var/lib/mysql. Hence, copy MySQL directory to custom location using 'cp -R /var/lib/mysql <$CUSTOM-DIR>'
4. Modify the owner to mysql using 'chown -R mysql:mysql <$CUSTOM-DIR>/mysql'(Installed directory owner should equal to the user specified in the my.cnf file. By default 'mysql' is the user specified in the file).
5. Remove ibdata1,ib_logfile0 and ib_logfile1 if exist under '<$CUSTOM-DIR>/mysql' directory.
6. Now, edit '/etc/my.cnf' file and modify the datadir value to custom directory as 'datadir = <$CUSTOM-DIR>/mysql'.
Sample my.cnf file for reference:
[mysqld]
#datadir=/var/lib/mysql
datadir=<$CUSTOM-DIR>/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysqld_safe]
log-error=<$CUSTOM-DIR>/mysql/logs/startmysql.log
pid-file=/var/run/mysqld/mysqld.pid
7. Start mysql service using ‘/etc/init.d/mysqld start‘.
Known issues:
1.
110215 11:30:11 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
110215 11:30:11 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
110215 11:30:11 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
Sol:
Check the permissions for configured directory. Owner for this directory should be a mysql instead of root.
2.
# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
During the access of Database :
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Sol:
If no exceptions are reported in the log file at this point of time, cross check the mysql.sock path in /etc/my.cnf file. It should point to /var/lib/mysql/mysql.sock. Should not modify this path and also verify the existance of mysql directory in the specified location.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment