What BACKUP/RESTORE command you use?

[veenuisthebest]

For SSH users:-

I want to know the commands/scripts you use for a reliable backup and restore. I beg, please do not post theoretical replies, be practical and post commands lol.

Here's what I am using now and things are working perfect, but I need some experienced opinions on this:-

For Backup:

mysqldump --opt -u USERNAME -p DATABASENAME | gzip > /home/username/dbbackup.sql.gz

The above command works great. But I have three questions related to it-

a) Some people use -Q option in the above mysqldump command which is equal to quote-names i.e. it puts a backtick on table names. What is its significance? Do you use it too?

b) Some people use -9 option for gzip in the above command. In --help it shows that -9 compresses better. Do you use -9 too?

c) Do you even prefer to gzip OR just like to keep the bulky .sql files?

For Restore:

gunzip < /home/username/dbbackup.sql.gz | mysql -u USERNAME -p EMPTY_DATABASENAME

a) Does the above look good to you? Do you use exactly the same?

Thankss

[BSMedia]

Thats exactly what i've always used when manually making backups.

For automated backups there are a lot of free shell scripts that will do basically the same thing as well

[Dismounted]

Originally Posted by veenuisthebest

a) Some people use -Q option in the above mysqldump command which is equal to quote-names i.e. it puts a backtick on table names. What is its significance? Do you use it too?

That command is particularly useful when you have tables/databases/fields that contain MySQL keywords (which should be never!). However, when you can't really control what's being added into the database (such as vBulletin modifications), it would be a good idea to use this.

Originally Posted by veenuisthebest

b) Some people use -9 option for gzip in the above command. In --help it shows that -9 compresses better. Do you use -9 too?

I've never really seen that until now - my guess is that it uses additional CPU power to create a smaller archive (trade-off). I don't use it though.

Originally Posted by veenuisthebest

c) Do you even prefer to gzip OR just like to keep the bulky .sql files?

GZIP is the way to go.