Category: Full Stack

There are all sorts of ways to backup MySQL to Amazon S3 floating around the interent, but the only problem is that many of them require all sorts of things. I wanted to have my MySQL database on my WHM/cPanel server backed up automatically to Amazon S3, in the easiest way possible. Here’s what I did:

Get Amazon S3

It’s dead cheap and way easy to signup. There is a button to the right of this page. Once you have it all ready to go, hover over the “Your Web Services Account” and click the “AWS Access Identifiers” link. This is where you can get the secret code and access key needed later on.

Install Net::Amazon::S3

This you may or may not have to do, but it’s way easy with WHM. Just login, on the left menu find “Install a Perl Module”. Then enter Net::Amazon::S3 and click search. It found 3 items for me, just click on the one that says “Net::Amazon::S3”. Hard wasn’t it?

Install BackupManager

Note that I’m using the Devel 0.7.9 version because it supports S3. The commands below were via SSH logged in as root.

1. # wget http://www.backup-manager.org/download/backup-manager-0.7.9.tar.gz
2. # gzip -d backup-manager-0.7.9.tar.gz
3. # tar -xvf backup-manager-0.7.9.tar
4. # cd backup-manager-0.7.9
5. # make install
6. # cp /usr/share/backup-manager/backup-manager.conf.tpl /etc/backup-manager.conf

Configure BackupManager

There is a whole bunch of configuration options available, but I’ll only go through the options to *only* backup a MySQL database and *only* send it to Amazon S3. This should be enough to get you started.

1. # pico -w /etc/backup-manager.conf
2. Use CTRL + W to find the text below, and change accordingly. Bold text is what you want to change to your specific settings.

• export BM_ARCHIVE_METHOD=”mysql”
• export BM_MYSQL_DATABASES=”dbname1 dbname2 dbname3“
• export BM_MYSQL_ADMINLOGIN=”mysqlusername“
• export BM_MYSQL_ADMINPASS=”mysqlpassword“
• export BM_UPLOAD_METHOD=”s3″
• export BM_UPLOAD_S3_DESTINATION=”s3bucketname“
• export BM_UPLOAD_S3_ACCESS_KEY=”s3accesskey“
• export BM_UPLOAD_S3_SECRET_KEY=”s3secretkey“

1. Press CTRL + X to close and “Y” to save

Sneaky Secret

This will only work if you have *all* InnoDB tables. If you do, and you want backups to not stall your server, let’s get your hands dirty:

1. # pico /usr/share/backup-manager/backup-methods.sh
2. CTRL+W (search for –opt)
3. Replace “–opt” with “–opt –single-transaction”
4. CTRL+X to close and “Y” to save

This will turn on snapshot backups. InnoDB is able to take a snapshot of the entire database and dump it without locking the tables. This is the difference between your server database holding up the entire server and things running as if no backup is running. Some day I’ll contact the backup-manager people to have them put it in..

Run Configure BackupManager

The first time you run it, you want to do it while you are there with verbose turned on. This way if any errors happen, you’ll know right away. To run it simply type:

1. # /usr/sbin/backup-manager –verbose

Setup the Cron Job

If that runs without any errors, then the next step is to setup your cron jobs. Type the following, substituting the bold 45 for the minute you want it to run, and the bold 1 for the hour you want it to run. Remove –verbose if you don’t want to get emails with all the details.

1. # crontab -e
2. press o
3. type “45 1 * * * /usr/sbin/backup-manager –verbose“
4. press esc
5. type ZZ to save or :q! to quit if you make a mistake
6. # crontab -l

The last command should list all your cron jobs. If it looks good, your golden. Just don’t forget that the quotes are not part of the command. You are typing what is inside the quotes.

Accessing your Files with Firefox

Well this is the kicker..  Accessing the files with S3 Firefox Organizer is way easy. It’s a plugin that sits in your bottom status bar of Firefox and you can just click it anytime and see your files. You can easily use it to synchronize folders on your computer too (so you can easily download those backups anytime!).

Program owners take note! If you’ve ever wanted to turn a splash page into a splash page your users can use, you’ll want to bookmark this page. It’s very simple but if you don’t know how to do it, where do you start?

1. Create the Splash Page in normal, dull, HTML

This is the easy part. Get a splash page made, make it yourself, whatever. Just have it in the normal HTML mode, and use your own referral link in all the links. Below is an example link:

http://www.startxchange.com/?referer=xxclixxx

2. Change the links

Next you need to open your html file with notepad. Only use notepad, unless you know the program is a PHP editor. Then find your userid or username from your link. In the example above, it’s xxclixxx. Search the html file and replace your username with:

<?php=$userid?>

The URL above now becomes what’s shown below. It’s important to make sure all links on the splash page are using referral links, and all links have been replaced with this code. Otherwise you’ll get angry affiliates thinking you are cheating them out of their referrals.

http://www.startxchange.com/?referer=<?php=$userid?>

3. Add this header

Simply put, add the following to the top of the page BEFORE the <html> tag. Ok, so it’s not crucial to be above that tag, but to keep things smooth and simple, put it before any other code.

<?php
$userid = ereg_replace("[\'\")(;|`,<>]", "",$_GET["userid"]);
?>

This is important for two reasons. You don’t have to have register globals turned on, and you don’t have to worry about XSS attacks. It cleans the user id for anything harmful, before letting you put it into the page.

4. Rename the file

Last, you’ll just rename that .html file to .php, and upload it to your website. Say you name it splash3.php, then your users would link to it as such:

http://www.yoursite.com/splash3.php?userid=xxclixxx

It’ll magically change all the links to have the ID in the userid field. Pretty sweet!

5. Bonus: Changing the variable name!

If you want it to be ?referer= rather than ?userid= all you have to do is change the header section. Where it says $_GET["userid"] change that text inside the quotes to something else. It could be anything. Just make sure you don’t change anything else, and keep the quotes!

If you don’t know what XSS is, and you are a web developer. Well, it’s time to wake up. XSS or Cross Site Scripting is basically injecting code onto someone else’s website. By doing it, you can do all sorts of nasty stuff.

The good thing is, it’s pretty simple to prevent this in PHP:

$string = ereg_replace("[\'\")(;|`,<>]", "", $string);

This piece of code will take out the characters needed to do the XSS exploits. There are also some in there that are useful to clean user input before say adding a string to a database query. It’s necessary to clean EVERY variable inputted by the end user, even ones you don’t put into a database or output to the user. Some time down the road you might use it, and not realize you forgot to clean it first.

Wow. It’s incredible the difference two words will make: “Connection: closed” with a PHP command fsockopen changed the website scanner’s speed dramatically. I won’t go into the details, I found them here. But the key thing is for me to post about it so later when I forget about it I can find it again on my blog. That’s what a blog is for afterall, right? =P

This tutorial goes out to Andre who wanted to use the URL Rotator for something similar but different. In this tutorial I am going to assume that you already have a database table “textlinks” set up with at least the following fields: title, url

The Code

<?
$global_dbh = mysql_connect("localhost","dbusername","dbpassword");
mysql_select_db("dbname", $global_dbh);

$query = "SELECT `title`, `url` FROM `textlinks` ORDER BY RAND() LIMIT 10";
$result = mysql_query($query, $global_dbh);
$cnt = mysql_num_rows($result);

for ($i=0; $i<$cnt; $i++) {
$row = mysql_fetch_row($result);
echo "<a href='$row[1]' target='_blank'>$row[0]<br />";
}
?>


This is fairly simple, however it is put in the “Advanced” section of the tutorials as the “Basic” will be just syntax and the very basics.

The first paragraph we connect to the database. Remember to change dbusername, dbpassword, and dbname to your username, password, and database name.

The second paragraph is the key MySQL command. ORDER BY RAND() tells it to randomly pick them. LIMIT 10 tells it to limit the results to 10 rows.

The third paragraph just goes through all the rows, and outputs the links. If you prefer to use $row[“title”] and $row[“url”] change mysql_fetch_row to mysql_fetch_array.

So there you have it! Any questions?