Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

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

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

    
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

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
done

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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