Best way to back up MySQL

TarballZ

I need some suggestions about how to back up MySQL databases. As I understand mysqldump might not always be the best way to go about it when in production.

I am grateful for any suggestions. Thank you.

VPSRAIDSolutions

$db = array ( 'db_account' , 'db_game' , 'db_misc'); foreach($db as $name){ if(!file_exists('backup/'.date('Y_m_d').'/')) { $do = mkdir('backup/'.date('Y_m_d')); exec($do); } if(!file_exists('backup/'.date('Y_m_d').'/'.$name.'/')) { $do = mkdir('backup/'.date('Y_m_d').'/'.$name); exec($do); } $tmpDir = '/tmp/'; $sqlFile = $tmpDir.$name.date('d_m_Y_H_i').'.sql'; $backupFile = $name.'_'.date('d_m_Y_H_i').'.tgz'; $create = "mysqldump -u ".$mysql_user." --password='".$mysql_pass."' --default-character-set=latin1 --hex-blob ".$dbName." > ".$sqlFile; $createZip = "tar cvzf $backupFile $sqlFile"; exec($create); exec($createZip); unlink($sqlFile); }

mysqldump is a good way then taring the same and then lets put in a folder where rsync will later do it job.

This code can be executed by a cronjob to make it easier.

TarballZ

Thank you!

VPSRAIDSolutions

This is exactly what I use for the game servers. You can also get a dropbox account + use dropboxuploader api php

https://github.com/jakajancar/DropboxUploader

to send to dropbox.

socials

I have this in my backup script

[[ $MYSQL == "YES" ]] && {
    DATABASES=$(echo "SHOW DATABASES" | mysql -u$DBUSER -p"$DBPW" | grep -v Database)
    for db in $DATABASES; do
        mysqldump --single-transaction --skip-events --opt -u$DBUSER -p"$DBPW" $db > $TMP/$HOSTNAME.$db.$DATE.sql
    done
    tar -zcvf $TMP/$HOSTNAME.mysql.$DATE.tar.gz $TMP/*.sql
    rm -f $TMP/*.sql; }

As you can see, using mysqldump. Have no idea if it's the best option (probably not) but I'm not running any production databases anyway.

TarballZ

socials said: As you can see, using mysqldump. Have no idea if it's the best option (probably not) but I'm not running any production databases anyway.

What happens when mysqldump runs while multiple tables are being updated that have relations between each other?

Blanoz

@TarballZ said:
What happens when mysqldump runs while multiple tables are being updated that have relations between each other?

Lock tables, backup, unlock tables.

VPSRAIDSolutions

@Blanoz said:
Lock tables, backup, unlock tables.

Dont forget foriegn key checks ( if the DB's tables have any )

Oliver

This works well:

http://sourceforge.net/projects/automysqlbackup/

gbshouse

Try Percona's backup tools

rds100

I do master-slave replication and daily mysqldump on the slave.

MarkTurner

As @gbshouse says - use Percona's backup tools otherwise you end up with inconsistencies.

comXyz

+1 for Percona, if you have complex mySQL database.

SplitIce

+1 again for Percona.
Mysqldump is also reasonable for a secondary backup (i.e a partial backup for if the other backup fails).

Personally I have a Percona backup running daily, and a mysqldump partial backup of select tables run every 6 hours (in case of failure of backup, a backdated percona backup could be used + mysqldump for important tables).

On top of this full disk (e.g LVM) backups are also wise.

Falzo

@Oliver said:

http://sourceforge.net/projects/automysqlbackup/

+1

SpeedBus

Try this out, https://github.com/arjitc/DumpSQL

shyaminayesh

https://github.com/shyaminayesh/SQLBackup/blob/master/backup.sh

small bash for backup and upload to FTP if you are interesting.

linuxthefish

MySQLDump piped to gzip then FTP elsewhere.

datarealm

mysqldump is certainly acceptable. a better solution if you can is to setup a slave mysql server and run the dumps on slave.

geekalot

@datarealm said:
mysqldump is certainly acceptable. a better solution if you can is to setup a slave mysql server and run the dumps on slave.

^ This.

Assuming:

• Replication is not broken (and the slave has a full copy)
• You have ability to bring "backup" instance offline

You could then use any of the above suggestions on the slave without much impact on "production" instances IMHO

MarkTurner

You can also do filesystem snapshots, mount the snapshot and then dump that. That will give you a 100% point-in-time backup