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 /var
partition is small, it might cause you problem at a later stage. In such
a scenario, it is better to move the MySQL's datadir to
another partition (like /home.
The steps are
- Stop your mysql server before starting this operation
- Create the directories that will be new
datadir chownthe directory to themysql:mysqluser- copy the files from the old
datadirto the new location. However, make sure that the files namedib_arch_log_0000000000, ib_logfile0etc. are not copied to the newer location. - Make sure that the files and directories are owned by
mysqluser - Make changes in the my.cnf to point the new
datadir. - 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
datadir - After the server is running for a few days properly, get rid of the old data.
- Sleep
Defined tags for this entry: geek stuff
Comments
10. Sleep
You mean if I follow these steps I don't get to sleep for a "few days"?
...before step 3
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
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
cd /var/lib/mysql2
sudo rm ibdata1
sudo rm ib_logfile0
sudo rm ib_logfile1
vi /etc/mysql/my.cnf
- corrected line to : datadir =/var/lib/mysql2
vi /etc/apparmor.d/usr.sbin/mysqld
- 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.
Once done I did a 'mysqlcheck -A -u root -p'
and everything was ok. GREAT & SIMPLE
datadir=/var/lib/mysql/databases
in /etc/my.cnf
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