How to change mysql database directory

[tanyali]

I am using Linux RedHat , MySql Ver 14.12 Distrib 5.0.18

not enough disk space left on /dev/sda7, which I am working on,
I insert BLOBs into database and the table is as big as 5.54468 GB

I want to change the database to another directory which is on /dev/sda8 .

I think I should use mysqldump to backup the table , after change the directory for mysql , and then use the backup file to recover it.

But now I do not know how to change the directory of database. Who knows Please Help . Thanks a million.

[masdi2t]

I am using Linux RedHat , MySql Ver 14.12 Distrib 5.0.18

not enough disk space left on /dev/sda7, which I am working on,
I insert BLOBs into database and the table is as big as 5.54468 GB

I want to change the database to another directory which is on /dev/sda8 .

I think I should use mysqldump to backup the table , after change the directory for mysql , and then use the backup file to recover it.

But now I do not know how to change the directory of database. Who knows Please Help . Thanks a million.

this is the step:
1. shutdown your mysql server (clean shutdown).
2. copy your data directory (all your data including your ib_logfile, ibdata).
3. open your mysql configuration file (my.cnf) and make some update on it.
a. adjust your datadir to your new directory (for myisam).
b. if you use innodb storage engine, make this modification too:
i. modify innodb_data_home_dir to your new directory.
ii. modify innodb_data_file_path. adjust ibdata size by round it down to the closest multiple of 1024 × 1024 bytes. ex: you have 100MB of ibdata, this calculation will produce 100.000.000 / 1024 / 1024 = 95.xxx MB, then you sholud write ibdata1:95M:autoextend.
iii. this is also your chance to resize your log file. if you did a clean shutdown, then you can delete your current log file (you do not need this file to make instance recovery because of clean shutdown you already did). to resize your log file just specify your new size on innodb_log_file_size, for example 100M

4. start your mysql server
5. happy migrating

[tanyali]

Thank you very much for answering,

Sorry to say that I am new.... so Can you explain every step more detail with command,
eg. how to shutdown mysql server....
I tried : mysqladmin -u root -p shutdown..
but it said :
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

where is the directory of my.cnf ?

Thanks a million again

this is the step:
1. shutdown your mysql server (clean shutdown).
2. copy your data directory (all your data including your ib_logfile, ibdata).
3. open your mysql configuration file (my.cnf) and make some update on it.
a. adjust your datadir to your new directory (for myisam).
b. if you use innodb storage engine, make this modification too:
i. modify innodb_data_home_dir to your new directory.
ii. modify innodb_data_file_path. adjust ibdata size by round it down to the closest multiple of 1024 × 1024 bytes. ex: you have 100MB of ibdata, this calculation will produce 100.000.000 / 1024 / 1024 = 95.xxx MB, then you sholud write ibdata1:95M:autoextend.
iii. this is also your chance to resize your log file. if you did a clean shutdown, then you can delete your current log file (you do not need this file to make instance recovery because of clean shutdown you already did). to resize your log file just specify your new size on innodb_log_file_size, for example 100M

4. start your mysql server
5. happy migrating

[tanyali]

Thanks for your answer ,

I have a problem after I did this :
I changed the location of mysql in linux by following this :
change database location

I changed every old-directory in the file /etc/my.cnf to new-directory, when I copied all the files from the old-directory to the new-directory some files omitted copy.. which are database-file.... (eg. I have a database named 'test' and many tables in 'test', I can not copy the file named test.)

at last I created a file with the same name and copied everything inside the file across to the new-directory. .. I think properly this causes the problem ,
then I restarted mysql server,, it does not work.
it still goes to the old-directory to look for mysql.sock..

do you know how to deal with this.. ?? thanks first
Tanya

this is the step:
1. shutdown your mysql server (clean shutdown).
2. copy your data directory (all your data including your ib_logfile, ibdata).
3. open your mysql configuration file (my.cnf) and make some update on it.
a. adjust your datadir to your new directory (for myisam).
b. if you use innodb storage engine, make this modification too:
i. modify innodb_data_home_dir to your new directory.
ii. modify innodb_data_file_path. adjust ibdata size by round it down to the closest multiple of 1024 × 1024 bytes. ex: you have 100MB of ibdata, this calculation will produce 100.000.000 / 1024 / 1024 = 95.xxx MB, then you sholud write ibdata1:95M:autoextend.
iii. this is also your chance to resize your log file. if you did a clean shutdown, then you can delete your current log file (you do not need this file to make instance recovery because of clean shutdown you already did). to resize your log file just specify your new size on innodb_log_file_size, for example 100M

4. start your mysql server
5. happy migrating