Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

cars.frm 
cars.MYD 
cars.MYI

customers.frm
customers.MYD
customers.MYI

departments.frm
departments.MYD
departments.MYI

... 

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!

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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
add comment

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

#!/bin/bash

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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