I am using MySQL on Ubuntu machine.

I need to physically move around the database files under /var/lib/mysql/ (this is where all the databases directories are located).

Say I have two databases, db_1 and db_2, so I can see two directories(db_1/ and db_2/) under the path /var/lib/mysql/ . Under each of these two database directory, there are many .frm .MYD .MYI files which holds all the tables' informations of the database.

For example, under db_1/ directory there are following files:

/var/lib/mysql/db_1# ls





which holds cars, customers, departments tables' info.

I need a shell script to do the following simple task:

  1. make a new direcotry named db_1_temp under /var/lib/mysql/

  2. move files related with cars and customers tables in db_1 directory to db_1_temp direcotry

  3. move files in db_2 directory to db_1 direcotry

NOTE, user need root privilege to access /var/lib/mysql/

I need these things in a .sh file so I can run it in another application.

Sorry that I do not know shell script, but need this thing. Appreciate if anyone can give a help, this shell script should not be hard I think. I try to extract some time to learn shell, as now I am quite busy with other software development. Thank you!


Don't rename the databases directories directly. Instead, dump the database out, create a new database name (which, in turn, will create the new directory name) and then dump the database back into the new database name:

mysqldump -u username -p  olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql

Once you verified that the database is functional, you can drop the datase:

mysqladmin -u username -p drop oldatabase.

See here for reference.

In your case, you probably want to create a tmp database, dump the db_1 database into that temp database, drop and recreate the db_2 database, then dump the db_2 database into the newly empty db_1 database.

  • But I only want to have part of the tables in the database, how to dump out part of the database ? – Mellon Nov 27 '11 at 10:15
  • Then use mysqldump -u username -p olddatabase tablename > oldtablename.sql to export an individual table, then mysql -u username -p newdatabase < oldtablename.sql to import. – Rilindo Nov 27 '11 at 21:07

Well, it's just moving files, so nothing complicated -- just 3 mv command


mkdir /var/lib/mysql/db_1_temp
mv /var/lib/mysql/db_1/cars* /var/lib/mysql/db_1_temp
mv /var/lib/mysql/db_1/customers* /var/lib/mysql/db_1_temp
mv /var/lib/mysql/db_2/* /var/lib/mysql/db_1_temp

But it if you plan to use the new database, you're better off with creating a new empty db and then use mysqldump to import tables you need.

  • Hi, savamane, I tried your command, but I got the following error: mv: cannot stat `/var/lib/mysql/db_1/*': No such file or directory. But I have that directory db_1/ – Mellon Nov 28 '11 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.