Thread: Backing Up MySQL

When I want to backup all of the MySQL databases on my server, I have been using this command:

Code:

mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`

The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.

Presence uses mysqlhotcopy to backup each database into it's own file:

Code:

#!/bin/bash
BACKUP_DIR=/tmp/backup
for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/local/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done

Hurray!
Now that's an awesome tip, specially when I have a few 200+ Mb DB's to backup everyday.

Thx Will!

This actually makes a hot copy of the data intact. You can just then tar the files up individually, or the whole directory.

Hooray you just made work a bit easier for me

Sir i am a newbie in PHP, how can i execute your backup script in .php? i dont know how to start it. hope you dont mind but i need this script.

thanks!

Originally Posted by Will.Spencer

When I want to backup all of the MySQL databases on my server, I have been using this command:

Code:

mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`

The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.

Presence uses mysqlhotcopy to backup each database into it's own file:

Code:

#!/bin/bash
BACKUP_DIR=/tmp/backup
for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/local/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done

Originally Posted by badtux

Sir i am a newbie in PHP, how can i execute your backup script in .php? i dont know how to start it. hope you dont mind but i need this script.

thanks!

It isn't in PHP, this is just something you'd run from shell.

i see.

So do you have a php scripts that i can backup my remote database into my local drive?

Originally Posted by badtux

So do you have a php scripts that i can backup my remote database into my local drive?

I believe that phpMyBackupPro does that.

Originally Posted by Will.Spencer

When I want to backup all of the MySQL databases on my server, I have been using this command:

Code:

mysqldump --all-databases -u MYSQL_USERNAME -h MYQL_SERVER -pMYSQL_PASSWORD   > mysql-backup-`date +'%Y%m%d-%H%M%S'`

The trouble is that this backs up the DB's all into one large file. That makes restoring an individual DB kind of annoying.

You could just dump one database at a time.

Code:

mysqldump -u username -p database-name > output_filename.sql

Originally Posted by NameStand

You could just dump one database at a time.

Code:

mysqldump -u username -p database-name > output_filename.sql

I could, but the list of DB's is large and growing rapidly.

I've actually asked Siva to look into Automatic MySql Backup Script · Dagon Design for us.