Thesqldump seems to copy excessively

backupMySQLmysqldump

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.

Best Answer

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

Related Solutions

Mysql – How should I migrate a large MySQL database to RDS

I just recently spent a lot of time trying to figure out a 15GB transition to RDS. Ended up finding a script on one of the amazon forums that I modified to my own uses and seems to work well. I'm not sure if you can do single transaction, but the dump itself is very quick compared to the actual transfer. I think 15GB only took me 12 minutes to dump, so even if it doesn't have single transaction option I don't think you'd have a very long span of time for inconsistencies to occur. I'm not sure if that's good enough for you, but I found the solution a lot more graceful than the flat file method.

#!/bin/bash

declare -a dbs=(dbname1 dbname2 dbname3 dbname4);

j=0
while [ $j -lt 4 ];
#4 is the number of dbs
do

echo "Dumping ${dbs[$j]} DB"
time mysqldump --order-by-primary --host=sourcehost --user=sourceuser --password=sourcepass `echo ${dbs[$j]}` > /tmp/`echo ${dbs[$j]}`.sql
echo "Adding optimizations to ${dbs[$j]}"
awk 'NR==1{$0="SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;\n"$0}1' /tmp/`echo ${dbs[$j]}`.sql >> /tmp/`echo ${dbs[$j]}`X.sql
mv /tmp/`echo ${dbs[$j]}`X.sql /tmp/`echo ${dbs[$j]}`.sql
echo "SET unique_checks=1; SET foreign_key_checks=1; COMMIT;" >> /tmp/`echo ${dbs[$j]}`.sql
echo "Copy ${dbs[$j]} into RDS"
time mysql --host=yourrds.rds.amazonaws.com --user=rdsuser --password=rdspassword `echo ${dbs[$j]}` < /tmp/`echo ${dbs[$j]}`.sql &

j=$(($j+1))
done

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Unfortunately, Yes you have to do every database. I have a step-by-step process I wrote up back on Oct 29, 2010 in StackOverflow as well as thorough explanation why there is nothing else you can do.

You also asked

Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database ?

You cannot reclaim half. It is an all-or-nothing decision. It is good to keep in mind what lives in ibdata1 when innodb_file_per_table is disabled :

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollback Segments
- Undo Space
Table Metadata (Data Dictionary)
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary indexes)
See the Pictorial Representation of ibdata1

Still, if you want to know which InnoDB tables are in ibdata1 and which are in their own tablespace file (.ibd), here is what you do:

STEP 01 : Run this query in the MySQL client

SELECT COUNT(1) InnoDBTableCount
FROM information_schema.tables
WHERE engine='InnoDB';

STEP 02 : Collect the number of .ibd files in the OS

ls -lR | grep -c ".ibd$"

STEP 03 : Subtract those values in the OS

Here is the shell script to compute these things

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

If you have partitioned tables, then do this:

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.partitions WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

Here is what you get:

IB_TOT : Number of InnoDB Tables in the MySQL Instance
IB_IBD : Number of InnoDB Tables Residing Outside ibdata1 as .ibd Files
IB_SYS : Number of InnoDB Tables Residing Inside ibdata1

Best Answer

Related Solutions

Mysql – How should I migrate a large MySQL database to RDS

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Related Question