PDA

View Full Version : mysqldump options



alitvak
April 14th, 2007, 08:54 AM
Is there a way to enable mysqldump to use --compress or -C ?

kkg
April 15th, 2007, 09:15 PM
mysqldump does not do compression.

ZRM does compression for you if you turn on the compression option else you can just pipe the output of mysqldump to gzip.

--kkg

alitvak
April 16th, 2007, 06:10 AM
from the man mysqldump

--compress, -C

Compress all information sent between the client and the server if
both support compression.

To support compression zlib needs to be mentioned in the build. MySQL community rpms for Redhat do have it.

It actually gives me 3-4 times compression when dumping regular table (non blob)

In any case I found where mysqldump options were defined and added it. Which brings me to a new question. What is the reason for using --single-transaction explicitly? Reading mysqldump manual, I see

--single-transaction

This option issues a BEGIN SQL statement before dumping data from the server. It is useful only
with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the
database at the time when BEGIN was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent
state. For example, any MyISAM or MEMORY tables dumped while using this option may still change
state.

The --single-transaction option and the --lock-tables option are mutually exclusive, because LOCK
TABLES causes any pending transactions to be committed implicitly.

I also see option --opt enabled everywhere which enables bunch of options including --lock-tables, in this case having --opt and --single-transaction together doesn't make any sense.

I love the software, just want to make it better.

kkg
April 16th, 2007, 06:37 AM
Oh I misunderstood you. I thought you were looking for getting the output in comressed form.

The simple way to enable the client server compression would probably be to just add the option to /etc/my.cnf or ~/.my.cnf

Check out
http://dev.mysql.com/doc/refman/5.0/en/option-files.html
for more details

kkg
April 16th, 2007, 07:01 AM
from the man mysqldump

Which brings me to a new question. What is the reason for using --single-transaction explicitly? Reading mysqldump manual, I see

--single-transaction

This option issues a BEGIN SQL statement before dumping data from the server. It is useful only
with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the
database at the time when BEGIN was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent
state. For example, any MyISAM or MEMORY tables dumped while using this option may still change
state.

The --single-transaction option and the --lock-tables option are mutually exclusive, because LOCK
TABLES causes any pending transactions to be committed implicitly.

I also see option --opt enabled everywhere which enables bunch of options including --lock-tables, in this case having --opt and --single-transaction together doesn't make any sense.

I love the software, just want to make it better.


The reason it is there is so that InnoDB tables are protected.

As regards --opt, even if it is not specifed, that is assumed as the default and hence even if it is not specified, --opt is assumed. Also note that --opt is a short cut for specifying several options at one go and my understanding is that if other parameters are specified which is not compatable with the options in --opt, then mysqldump probably turns off that particular option that is part of --opt.

--kkg