Backing up with MySQL Dump

How reliable is the backup that is created using MySQL Dump? What are the other more safe alternatives?
LVL 8
rpkhareAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
If you are planning for desktop/single machine then it may sound bit new thing.. but coming to real world you need it...

For desktop - no need to worry just  take sqldump using sqlyog/mysqdump  or just binary copy..(data folder copy)
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
it is as reliable as the disk it is saved on.
0
 
artmsConnect With a Mentor Commented:
Dump files are quite reliable, but sometimes they have some quirks, especially with charsets. Another way how to backup your mysql data (takes more space) is to stop mysql and copy all data files (on linux it is often in /var/lib/mysql/) to some safe place. You can do this operation without shutting down mysql by using LVM snapshots.

More about mysql backup/recovery you can find here: http://dev.mysql.com/doc/refman/5.0/en/backup-and-recovery.html
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
UmeshMySQL Principle Technical Support EngineerCommented:
Tell me your dump command I'll tell you whether its reliable or not..

Also let me know  - engine details
                              - Any activity during the dump time?
0
 
rpkhareAuthor Commented:
Engine: InnoDB.

I take backup using MySQL Administrator.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
I have never used/taken dump using MySQL Administrator.

Any activity during the dump time?
0
 
rpkhareAuthor Commented:
Only a progress bar is shown. However, I've been using it from quite some time. It worked, but I want to know how reliable it is.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
That is what I wanted to tell you...so asked engine/usage details

When it comes to MySQL dump the thumb rule is...

InnoDB - make sure your mysqldump command has --single-transaction option enabled(Also make sure no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.).

MyISAM - Without locking you won't be getting any consistent state of the database.

0
 
rpkhareAuthor Commented:
If I take backup using SqlYog, what options to set. It shows following screen:
sqlyog-dump.JPG
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Lock all table for read should be fine...Pls check it.
0
 
rpkhareAuthor Commented:
What this feature means?
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
--lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.
#

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.

Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.
0
 
rpkhareAuthor Commented:
I think these options are not useful for a desktop single machine application.
0
 
rpkhareAuthor Commented:
Thanks all.
0
All Courses

From novice to tech pro — start learning today.