Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an old_server with 50 databases. I want to create new_server with just 3 databases.

When I try using mysqldump, it seems like all the database directories (although they are empty on new_server) and many unnecessary support files for the other 47 tables are being copied.

Is there any way to avoid this (or clean up afterwards, if unavoidable)? I would like new_server to be as clean as possible, with no traces of those other 47 databases, if at all possible.

I am tempted to delete those files and directories directly from the file system (or at least temporarily move them to /tmp), but I'd like to avoid this if possible.

share|improve this question
    
@AbdulManaf fixed the OP - meant databases, not tables. I even get MYD, MYI, frm files from databases I deleted in new_server years and years ago. –  kfmfe04 Dec 16 '11 at 10:13
    
:sorry i misunderstand the question.I am giving answer. –  Abdul Manaf Dec 16 '11 at 10:15
add comment

2 Answers 2

up vote 1 down vote accepted

If you would like to detect which databases actually contain MySQL data, you can run the following query:

SELECT DISTINCT table_schema FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql');

Any database with no tables in it simply do not appear in this query's result set.

How do you mysqldump these nonempty databases? Using the aforementioned query, like this:

mysql -uuser -ppassword -AN -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')" > /tmp/NonEmptyDatabases.txt
DBLIST=""
SPC=""
for DB in `cat /tmp/NonEmptyDatabases.txt`
do
    DBLIST="${DBLIST}${SPC}${DB}"
    SPC=" "
done
mysqldump -uuser -ppassword --routines --triggers --databases ${DBLIST} > /root/RealMySQLData.sql

Give it a Try !!!

share|improve this answer
add comment

I think you are backing up all the Databases.You only need to backup those three databases.use mysqldump like:

mysqldump -uuser -ppassword --databases db_name1 db_name2 db_name3 > Three_db_dump.sql

you can see various options of the mysqldump using mysqldump --help.

In Case If you want to drop the 47 databases which you didn't want on new server If they all are having only MyISAM tables you can easily drop them.It will also reclaim the space.

If the databases have the InnoDB tables then you have to go with another process because only dropping the database doesn't recliam the InnoDB table space.For this follow the link

Is there any best way to reduce the size of ibdata in mysql.?

share|improve this answer
    
+1 tyvm - I will try this out –  kfmfe04 Dec 16 '11 at 10:41
    
I think your InnoDB hint may be the reason why all the dead/non-existent db files are being copied over –  kfmfe04 Dec 16 '11 at 15:27
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.