mysqldump seems to copy excessively

Question

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.

Answer 1

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 !!!

Answer 2

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

What is the best way to reduce the size of ibdata in mysql?