MySQL Backup and Recovery

If your site manages it's data with MySQL, then you obviously need to make sure the data is safe. In this blog post, I will show how to create a daily backup automatically. I will also show a continuous data protection plan for MySQL databases. This blog post uses the previous backup server configured in my Secure Backup & Recovery with rsnapshot, rssh and OpenSSH article.

Secure Backup & Recovery with rsnapshot, rssh and OpenSSH

Overview

Wee all need to backup our machines. But we also need to keep the data private and the backup procedure secured. In UNIX and Linux machines, we need to run the backup operation as root in order to read everything on the machines. But allowing remote connections as the root user is not exactly a good idea. So how to we proceed? We use rsnapshot(1) and rssh(1) together with OpenSSH to secure the whole process. Here's how to do it on CentOS 6.

In case you're running a heterogeneous network, please note that I've successfully configured this process on FreeBSD, PC-BSD, RedHat, Ubuntu, AIX and Solaris servers.

In this example, our backup server is called backup.company.com and is running CentOS 6 while the clients are :

1. The OpenLDAP server alice.company.com that we configured in several other blog posts and running CentOS 6.
2. A workstation machine called charlie.company.com running PC-BSD 9.0 (i.e. FreeBSD 9.0 :)