Content tagged with: backup

In this tutorial, I'll show how you can use awk, grep, and sed (my favorite command line tools) to backup and archive your MySQL databases. This can be useful to schedule a cron job, transfer your databases to another server, or any other type of scripting.

First, you'll have to get acquainted with connecting to and dumping your database on the command line. Depending on your user, credentials, and where the databases are located, your command might look something like this. Please note, there is no space between the password and the "-p" flag.

To simplify my example, I'm going to shorten the mysqldump command to the follow.

Now that we're MySQL command line pros, I'll break down each command. I'll start by showing all the databases.

Now, I'll "pipe" the output from the previous command into awk to show the first column data.

And use grep to remove the first line that says "Database".

And use sed to build the mysqldump command. This one is kinda tricky, sorry. As you can see, I also embedded the date command in there to generate today's date in the format: YYYYMMDD.

Lastly, if everything looks good, you can pipe the output back to the command line.

You could even take this one step further and pipe the output through gzip to compress the dumps :)

Recently, I had to work on a few Drupal sites and only had FTP access to the webservers. One thing is for certain: FTP is slow and painful. I prefer SSH access so I can interact with Subversion, compress files, dump mysql databases, and transfer files securely. I tried to copy the entire remote docroot to my local development environment (using my FTP client, CyberDuck), and the time estimate to copy all the files individually was ridiculous. There are over 500 files in Drupal core alone, not to mention all the 3rd party modules and uploaded files. I decided to upload a tiny PHP file to execute once to backup the filesystem outside the docroot, so I could copy a single compressed file. Before you attempt something like this, you MUST understand the security risk and vulnerability of exposing site archives and having PHP scripts like this on your server. For instance, if someone was able to get a hold of your settings.php file, they'll have access to your MySQL DNS (connection string). Hopefully, your webserver does not have MySQL and other important services exposed through your firewall, but that is a different topic altogether. I'm already having doubts sharing this PHP snippet. I uploaded the following code to a file in the docroot of Drupal, browsed to the web path once, then promptly removed it from existence. Afterward, I was able to copy the entire filesystem as one file (one transfer), maintain my sanity, and saved myself hours of slow FTP transfers.

After this code is executed, be sure to remove the PHP script and archive!

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

It's important to realize that making changes to Drupal (configuration changes, uploading a file, etc) affect both the file system and the database. That's why I feel it's important to integrate your MySQL database with your subversion file system. For instance, if you upgrade a Drupal module, you should commit a backup of your database with the file system changes to ensure you have a definitive snap snot of your project, and can revert to a previous revision as necessary.

All of my Drupal projects are subversion integrated for proper version control. A typical subversion tree resembles:

When I checkout projects to my Linux file system, I use the following command to ensure I checkout everything parallel to the httpdocs folder. I use the "archive" folder for items (like database dumps) that I'd like to keep outside of the httpdocs, so they are not public.

Now that my file system is integrated with subversion, I can backup my MySQL database and check it into subversion before I make a critical change to the Drupal configuration, such as upgrading a module:

NOTE: if you've already checked your database into subversion and you're creating another backup, your svn commands (and output from the svn stat command) will be slightly different. A question mark represents a new file, while a capital "M" represents a modified file:

Let's say you upgrade a module (or Drupal) and get an unexpected error, you can now revert your database:

Here's a quick procedure on how to upgrade a Drupal site that's integrated with subversion (a checked out local repository). NOTE: you'll have to update paths, users/group, etc. I wrote this from memory, hopefully there are no errors.