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?


1 Answer 1

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

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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