3

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!

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
2
  • But I only want to have part of the tables in the database, how to dump out part of the database ? Commented Nov 27, 2011 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. Commented Nov 27, 2011 at 21:07
1

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.

Improve this answer
1
  • 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/ Commented Nov 28, 2011 at 15:56

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.