Commavee

Today’s Tech Tip deals with performing fully automated (unattended) backups of a MySQL database from one system to another, even though there is no direct connectivity between the two systems.

Background

You must backup & restore a MySQL database from one server to another. These servers have a firewall between them; direct connectivity is not possible. Further, neither of the MySQL servers involved are directly accessible across the Internet. This technique assumes that each of the MySQL servers are visible to disparate segments on a Wide Area Network.

The work flow looks like this:

Remote MySQL Server -> Intermediate system -> Local MySQL Server

Details

The key to this technique is finding an Intermediate system to which each of your MySQL database servers has access. Ask your system administrator to create an account for you on the Intermediate server, and then use your existing normal user account on the Remote Server and a super-user account on the local server. The super-user account is necessary because you will be touching files owned by the MySQL user (usually mysql), and unless you *are* the MySQL user, you will not have file-system permissions to do this.

Setup a key exchange that you can seed automatically - inside your script - that then allows you to perform scp and ssh operations without the system prompting for a password. You may have seen some people solve problems like this with Expect; the solution I implement below has worked for me everywhere, as many (most?) systems don’t have Expect (or even Tcl) installed. ssh is installed almost everywhere. If ssh isn’t installed on the system, you probably have no business attempting to connect to it anyway.

Once the key exchange is setup, you then create a script like the one below, substituting the stubbed values for your real values. You can use variations of the script to do just about anything. Note how I have embedded an ssh connection to the Local system inside the ssh connection from the Remote DB server to the Intermediate system. In those embedded ssh sessions I run normal system commands like chown, tar and sleep. This is very powerful.

Example Script:

Notice that you must stop & restart the local MySQL server in order for it to read the newly copied database. Since it can take a few seconds to fully stop MySQL, I use the sleep command here to ensure that the subsequent start command doesn’t step on any mysqld PIDs that are still closing.

Add exection of this script to your cronjob and you will have a process that copies & restores the MySQL database between these servers, running automatically and without creating any new firewall rules.

Tags: freepository, automation, backups, tech-tip