[WNYLUG-Users] mysqldump question

Chris Hawkins chawkins at bplinux.com
Fri Aug 12 10:50:10 EDT 2011


Dennis that's not correct. Using file_per_table tells innodb to keep innodb data for each table in its own file instead of all together in ibdata. The data is one place or the other, not both, and space is reclaimed when tables are dropped. And dropping tables and databases also does not clear ibdata. You have to export, drop the databases, delete the ibdata file, and then import. 


----- Original Message -----
> Don't fall into that trap! file per table has it's advantages, but
> keeping a smaller ibdata1 file is not one of them! This file will
> still be large and cannot be deleted!
> 
> The only way to clear white space in your innodb tables is to
> unload/drop/reload.
> 
> That being said none of this answers why your dump file is 10 times
> larger than your data size...
> 
> 
> 
> On Fri, Aug 12, 2011 at 10:04 AM, Chris Hawkins <chawkins at bplinux.com>
> wrote:
> > He's right - using file_per_table avoids this issue entirely.
> > Otherwise
> > dropping a table or even an entire db does not free disk space,
> > which is the
> > situation you have presently.
> >
> >
> > ________________________________
> >
> > Darin,
> >
> > The ibdata1 file contains your innodb, or at least some of it, data
> > so that
> > swollen size is part of your database space. What you may want to do
> > when
> > you reload is use the innodb_file_per_table option in your
> > configuration
> > file so that each table has its own .ibd file within the database
> > sub-directory. Here is some more information about that option:
> > http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html.
> >
> > Hope this helps,
> > Tyler
> >
> >
> > On Fri, Aug 12, 2011 at 9:57 AM, Tyler Poland
> > <tyler.poland at gmail.com>
> > wrote:
> >>
> >> I've never seen an instance where the dump file was larger than the
> >> source
> >> database. MySQL has the ability to spread data across multiple
> >> partitions,
> >> are you sure when measuring the data directory size you are
> >> measuring
> >> everything? Give the following a try:
> >> -----
> >> select sum(index_length, data_length)/1024/1024/1024 as data_GB,
> >> table_schema
> >> from information_schema.tables
> >> group by table_name;
> >> -----
> >>
> >> To incorporate gzip with your command:
> >> -----
> >> mysqldump -uroot -p -A |gzip -c > /mytmp/full-mysqldump.sql.gz
> >> -----
> >>
> >> You mentioned the slave was offline, if you are using this to
> >> restore the
> >> data to the replica you may want to include the --master-data=[1|2]
> >> directive.
> >>
> >> Tyler
> >>
> >>
> >> On Fri, Aug 12, 2011 at 9:42 AM, Dennis Ruzeski
> >> <denniruz at gmail.com>
> >> wrote:
> >>>
> >>> So to be clear- The size of your mysql data directory is 2g and
> >>> the
> >>> mysqldump output is 23g?
> >>>
> >>>
> >>>
> >>> On Fri, Aug 12, 2011 at 9:37 AM, Darin Perusich <darin at darins.net>
> >>> wrote:
> >>> > This is a replicated server and the commands running on the
> >>> > master.
> >>> > The replication client/slave is currently off line.
> >>> >
> >>> > mysqldump -uroot -p -A > /mytmp/full-mysqldump.sql
> >>> >
> >>> > --
> >>> > Later,
> >>> > Darin
> >>> >
> >>> >
> >>> >
> >>> > On Fri, Aug 12, 2011 at 9:32 AM, Dennis Ruzeski
> >>> > <denniruz at gmail.com>
> >>> > wrote:
> >>> >> Can you post your mysqldump command line? Is this on a
> >>> >> replicated
> >>> >> server? If so, are you running it on the master or the slave?
> >>> >>
> >>> >> --Dennis
> >>> >>
> >>> >>
> >>> >>
> >>> >> On Fri, Aug 12, 2011 at 9:22 AM, Robert Wolfe
> >>> >> <rwolfe at fpsoft.net>
> >>> >> wrote:
> >>> >>> On 08/12/2011 08:37 AM, Darin Perusich wrote:
> >>> >>>>
> >>> >>>> I know this is a bit off topic but I'm hoping someone might
> >>> >>>> have
> >>> >>>> some
> >>> >>>> insight on this.
> >>> >>>>
> >>> >>>> I'm performing a mysqldump of a database which on disk is
> >>> >>>> about 2G,
> >>> >>>> nothing special. When I perform a mysqldump of this same
> >>> >>>> database
> >>> >>>> the
> >>> >>>> dump file swells to over 23G is size! I know there are
> >>> >>>> triggers and
> >>> >>>> stored proc's associated with this db, and what they do is
> >>> >>>> anyone's
> >>> >>>> guess, and I'm thinking they are what's causing this but I
> >>> >>>> really
> >>> >>>> don't know. I've never encountered this before.
> >>> >>>>
> >>> >>>> Any thoughts
> >>> >>>>
> >>> >>>> Thanks!
> >>> >>>>
> >>> >>>> --
> >>> >>>> Later,
> >>> >>>> Darin
> >>> >>>>
> >>> >>>> _______________________________________________
> >>> >>>> Users mailing list
> >>> >>>> Users at wnylug.org
> >>> >>>> http://wnylug.org/mailman/listinfo/users_wnylug.org
> >>> >>>
> >>> >>> Have you tried doing this with phpMyAdmin and see if you get
> >>> >>> the same
> >>> >>> results?
> >>> >>>
> >>> >>> _______________________________________________
> >>> >>> Users mailing list
> >>> >>> Users at wnylug.org
> >>> >>> http://wnylug.org/mailman/listinfo/users_wnylug.org
> >>> >>>
> >>> >>
> >>> >> _______________________________________________
> >>> >> Users mailing list
> >>> >> Users at wnylug.org
> >>> >> http://wnylug.org/mailman/listinfo/users_wnylug.org
> >>> >>
> >>> >
> >>> > _______________________________________________
> >>> > Users mailing list
> >>> > Users at wnylug.org
> >>> > http://wnylug.org/mailman/listinfo/users_wnylug.org
> >>> >
> >>>
> >>> _______________________________________________
> >>> Users mailing list
> >>> Users at wnylug.org
> >>> http://wnylug.org/mailman/listinfo/users_wnylug.org
> >>
> >
> >
> > _______________________________________________
> > Users mailing list
> > Users at wnylug.org
> > http://wnylug.org/mailman/listinfo/users_wnylug.org
> >
> >
> > _______________________________________________
> > Users mailing list
> > Users at wnylug.org
> > http://wnylug.org/mailman/listinfo/users_wnylug.org
> >
> >
> 
> _______________________________________________
> Users mailing list
> Users at wnylug.org
> http://wnylug.org/mailman/listinfo/users_wnylug.org



More information about the Users mailing list