up vote 0 down vote favorite
share [g+] share [fb]

We have a backup system at the moment, but after a recent scare, we’d like another backup of just the MySQL databases, nightly, to a local server in our office.

The databases are pretty big as it stores a lot of different ecommerce stores. To make things easier for us to find them, rather than just get one giant SQL file, a file of each database would be ideal.

  • The web server is CentOS with PHP/MySQL, with access via FTP/SSH
  • The local server is Ubuntu with PHP/MySQL, with access via SSH
  • Both servers have dedicated IP addresses
  • Only the previous day’s database needs to be kept, so it can be overwritten upon success

Any suggestions on the best way to do this?

link|improve this question
I would suggest an rsync script and a naming convention for each of your databases which allows you to uniquely identify each dump'ed dbase and possibly backup into a hierarchical folder structure too. – Dark Star1 Jun 27 '11 at 11:05
(there are lots of dulpicates of this question - the link I provided has links to some of the others) – symcbean Jun 27 '11 at 12:36
feedback

migrated from stackoverflow.com Jun 27 '11 at 11:19

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote 
for db in `mysql -uUsername -pPassword -sN -e "show databases"`
do mysqldump -uUsername -pPassword $db |/usr/bin/bzip2>/backup/$db.sql.bz2
done

This command will dump every database to a separate file. You will only have to copy the resulting files to the local server.

link|improve this answer
I think you need a space between -u and Username. Alternatively, put user/pass in your .my.cnf – fsb Jun 27 '11 at 12:26
2  
No, the space is not required here (but possible). – minaev Jun 27 '11 at 12:35
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.