You should not use mysqldump for this big database. You should take RAW
backup of this database and it will be much faster than mysqldump and your
restoration will be very very fast. There are different ways to take raw
1) Stop mysql and copy database directory. ( Require Downtime)
2) Use mysqlhotcopy to take raw backup. ( It will take read lock on that
database so no write/delete/update will happen on that database,
mysqlhotcopy can only take backup of MyISAM engine).
please read this for mysqlhotcopy details.
3) Use LVM snapshot to take raw backup. ( In this you do not need down time
nor db will be locked for write/delete/update but this require yous system
should be on linux and mysql data directory should be on lvm partition and
you should have enough space in volume group for lvm snapshot , LVM snapshot
is independent of mysql engines.)
4) Use MySQL ZRM tool.
5) You can use ibbackup tool from innodb.com but this is commercial tool.
Hope this info will help you.
On Wed, Sep 29, 2010 at 12:59 AM, asmith varma <asmith.varma@stripped>wrote:
> we are having a mysql database <http://www.dbforums.com/#> which is around
> 200gb.iam using mysqldump for backup this database.
> the restoration is taking so much of time.
> Could anyone suggest me to speed up backup using mysqldump and also how can
> i speed up the restoration process.
> Do i need to set any parameters in my.cnf to speed up backup as well as
> restoration or need to make any changes to the config
> Please give me your thoughts
> asmith varma
Abhishek Kumar Singh