7

Our Db is about 2GB in size. We run a scheduled job at 3am every morning, when traffic is pretty low, where we export the whole db and sftp it off site.

Is it bad practice to do this on a production db? The slow query log always grows with 10 or so queries when doing this. Open/opened tables also grows a bit.

So, is this not a good backup strategy? What's a good/standard alternative to this? Is it even bad for the db somehow?

Thanks.

EDIT

We're on a Linode 4096 where the DB is the only thing run on that server. It has SSD's but not world class. We're using 2GB of the 4GB memory for the innodb_buffer_pool. The command is as follows:

mysqldump --opt --u USER -pPASSWORD DATABASE | gzip > /path/to/database.sql.gz
4
  • InnoDB or myisam-tables? Show your mysqldump command. Describe your hardware (fast disks? enough memory?)
    – da_didi
    Dec 8 '14 at 7:49
  • See edit in question above
    – Adergaard
    Dec 8 '14 at 7:55
  • Are all tables innodb?
    – da_didi
    Dec 8 '14 at 8:11
  • yes, all tables innodb
    – Adergaard
    Dec 8 '14 at 8:15
10

since the database size is large you should make the dump command like the following:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz

--quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out

--single-transaction: This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

--lock-tables=false : option stops MyISAM tables (if they exsit) being locked during the backup.

1
  • I know this is old, but I found out recently that piping mysqldump to gzip increases the time that the select queries are held open for. If you do the dump first, and then gzip the file(s) afterwards, you're less likely to end up with slow queries logged
    – MrCarrot
    Feb 8 at 18:31
1

You should modify the mysqldump command and use --single-transaction. All new changes to the database are stored in a transaction and you have valid point-in-time-database backup.

I have two steps: I export the database as a dump and the next step is to zip the dump, but i never tested if this make any differences.

You could use other backup software like the Percona xtrabackup

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .