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!

2 Answers 2

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.

2
  • But I only want to have part of the tables in the database, how to dump out part of the database ?
    – Mellon
    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.
    – Rilindo
    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.

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/
    – Mellon
    Commented Nov 28, 2011 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .