By default, MySQL's
datadir is placed in the
/var/lib/mysql directory. However, if you are planning on
using MySQL tables to store a lot of data and your
partition is small, it might cause you problem at a later stage. In such
a scenario, it is better to move the MySQL's
another partition (like
The steps are
- Stop your mysql server before starting this operation
- Create the directories that will be new
chownthe directory to the
- copy the files from the old
datadirto the new location. However, make sure that the files named
ib_arch_log_0000000000, ib_logfile0etc. are not copied to the newer location.
- Make sure that the files and directories are owned by
- Make changes in the my.cnf to point the new
- Restart the MySQL database
- You might need to do varying degree of troubleshooting to get the server working if there is some problem
- Create a new database and verify that the files for this database
are getting created in the new
- After the server is running for a few days properly, get rid of the old data.
9. After the server is running for a few days properly, get rid of the old data.
You mean if I follow these steps I don't get to sleep for a "few days"?
Something's wrong I put in 3 > (angle brackets) followed by the text - "Meaning I don't get to sleep for days." But it didn't show up. Why?
I think the blog was stripping out the greater than angle brackets before displaying.
hehe -- I think you can do both of them concurrently
ID - you stop mysql server before doing any of these steps.
DELETING THE IB_* files when doing the move was BAD ADVICE in my case FYI !!! I couldn't figure out forever why I couldn't get everything working again, kept getting the error below. But when I finally copied the original ib_* files back into the data dir, it all worked again GREAT. Here's the error I got without them:
070516 13:13:52 InnoDB error:
Cannot find table / from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
070516 13:13:52 [ERROR] Slave: Error 'Got error 1 from storage engine' on query. Default database: ''. Query: 'replace into (groupid, ts, state) values (9,'2007-05-15 11:14:06',0)', Error_code: 1030
All my databases are INNODB and I keep getting "#2003 - Can't connect to MySQL server on 'localhost' (10061) " after following this procedure...
Check the mysqld.err log to see what the problem is. From my guess, it would be permission problem
I found this question on Yahoo answers, but the question has since been deleted and as far as I know it it was not answered. I figured out an answer for it which solved the same problem I was having.
How to change MySQL datadir
Okay, so I want to change the datadir of my MySQL databases... but something doesn't seem to be working correctly for me.
***FYI: Operating System = Ubuntu 8.04***
What I have done:
created arbitrary directory /var/lib/mysql2 (as opposed to /var/lib/mysql .. I am just trying to test the method to change datadir)
sudo chown -R mysql:mysql /var/lib/mysql2
sudo /etc/init.d/mysql stop
sudo cp -r /var/lib/mysql /var/lib/mysql2
sudo rm ibdata1
sudo rm ib_logfile0
sudo rm ib_logfile1
- corrected line to : datadir =/var/lib/mysql2
- added lines: /var/lib/mysql2/ r, and /var/lib/mysql2/** rwk,
Now, when I try:
sudo /etc/init.d/mysql start
it fails to start, If I edit the /etc/mysql/my.cnf file and change the datadir back to /var/lib/mysql, the service starts up fine.
The solution is to restart apparmor with:
sudo /etc/init.d/apparmor restart
then start mysql:
sudo /etc/init.d/mysql start
Do not delete the ibdata file, as that is where the innodb tables are stored.
Thanks -- at last somebody actually did this before me in hardy herron
worked great, just like that on debian 5 with MySQL 5. I did it right after I had installed the MySQL server with apt-get install.
Once done I did a 'mysqlcheck -A -u root -p'
and everything was ok. GREAT & SIMPLE
When moving datadir on mysql 5.5.21 by setting
it turned out to be necessary to move the ibdata1 ib_logfile0 ib_logfile1 files into the new directory
It worked for me. At first i tried only changing the data dir path
datadir = /mnt/sdb/mysql
After then, mysql server was not starting. Then i made a quick search on google and found your site and followed your guide.
Again mysql was started and worked fine.
Thank you very much