Simple shell script needed to move database files

Question

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!

Answer 1

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.

Answer 2

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.