0

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?

Improve this question
3

1 Answer 1

Reset to default
1 
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.

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

You must log in to answer this question.

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