You should be very happy the mysqldump is two-thirds the size of the data. Why?
You should want to move data and indexes without copying unused space at the same time. What aspects would you have to consider by simply importing a mysqldump?
Aspect #1
InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.
Aspect #2
InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:
In light of this, you should keep in mind that a mysqldump is a logical representation of the data and indexes. There is no wasted space to be concerned with. You should not have to scp or rsync bloated InnoDB data or logs. Importing a mysqldump into a fresh installation of MySQL should be what you what want.
I have written about this many times:
Once you have loaded the test database with the mysqldump, you should run CHECKSUM TABLE
on random tables of various sizes. If all tables have the same checksum value, you could certify as good. Make a disklevel backup of the data on the prod server as well.
You can then do this in production
- shutdown mysql
- hose everything (including ibdata1, ib_logfile0, ib_logfile1) in /var/lib/mysql except for /var/lib/mysql/mysql
- Set
innodb_data_file_path=ibdata1:10M:autoextend
in /etc/my.cnf
- restart mysql (this recreates ibdata1, ib_logfile0, ib_logfile1)
- reload mysqldump
Your InnoDB architecture and data will then be lean and mean.
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 :
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
Did you have
innodb_file_per_table = ON
?Regardless of the above setting,
ibdata1
will have some stuff in it. Without more specifics (size of that file, the above setting, number of tables, number of.ibd
files, etc.), I cannot be more specific.In my opinion, "tiny" tables may as well be built with file_per_table = OFF.