MySQL database backups to a local server

Question

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?

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
possible duplicate of [Optimal way to make MySQL backups for fairly large databases (MyISAM / InnoDB)](http://serverfault.com/questions/139593/optimal-way-to-make-mysql-backups-for-fairly-large-databases-myisam-innodb) — symcbean, Jun 27 '11 at 12:35
(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

score 1 · Answer 1 · answered Jun 27 '11 at 11:51

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.

answered Jun 27 '11 at 11:51

minaev

1,617
1
13
13

I think you need a space between `-u` and `Username`. Alternatively, put user/pass in your `.my.cnf` – Jun 27 '11 at 12:26
2

No, the space is not required here (but possible). – minaev Jun 27 '11 at 12:35

MySQL database backups to a local server

1 Answers1