Automatic Local, FTP and Email Backups of MySQL Databases with Cron

I’ll start by saying this is not all my own code, it is based on dagon design’s original release in 2007 (Automatic MySql Backup Script) but this version builds on their version 2.1 to add FTP support.

What this script does:

  • Backup all of your MySQL databases on a server individually, then package them into a single tar.
  • Save that tar locally, on a FTP server or even email it to you

What you need:

  • PHP
  • MySQL
  • An account with relevant mysql access (it needs access to the databases you wish to backup)

Download:

Zip containing dbbackup.php & dbbackupconfig.php – dbbackup.zip

What I’ve added:

To upload to a remote FTP server, I added this to the config file:

######################################################################
## FTP Options
######################################################################

// Use FTP Option?
$useftp = true;

// Use passive mode?
$usepassive = true;

// FTP Server Address
$ftp_server = 'host';

// FTP Username & Password
$ftp_user_name = 'username';
$ftp_user_pass = 'password';

and this to the main file below email sending:

// do we ftp the file?
if ($useftp == true) {
$file = $BACKUP_DEST.'/'.$BACKUP_NAME;
$remote_file = $BACKUP_NAME;

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// turn passive mode on?
ftp_pasv($conn_id, $usepassive);

// upload a file
if (ftp_put($conn_id, $remote_file, $file, FTP_BINARY)) {
 echo "successfully uploaded to ftp: $remotefile\n";
} else {
 echo "There was a problem while uploading $remotefile\n";
}

// close the connection
ftp_close($conn_id);
}

That’s all!

Stripping everything but letters and numbers from a string in PHP with preg_replace

Useful for a number of things including username and anything else you don’t want ANY special chars in, leaving only alphanumeric digits.

<?php
$string = 'us$$er*&^[email protected]@e';
$string = cleanabc123($string);
function cleanabc123($data)
{
$data = preg_replace("/[^a-zA-Z0-9\s]/", "", $data);
return $data;
}
// This will be 'username' now
echo $string;
?>

And if you wanted it to also remove whitespace, you could change the function by removing the \s whitespace character.

$data = preg_replace("/[^a-zA-Z0-9]/", "", $data);

Remove whitespace (spaces) from a string with PHP and preg_replace

Seeing as ereg_replace and that whole family has been depreciated in the newer PHP releases it’s surprising how many sites still show and use it.

This simple function strips out whitespace from a string. Could be useful in taking spaces out of usernames, id’s, etc.

<?php
$string = 'us er na  me';
$string = nowhitespace($string);
function nowhitespace($data) {
return preg_replace('/\s/', '', $data);
}
// This will be 'username' now
echo $string;
?>

You could use this to make all whitespace only 1 space wide by changing the ” in the following line.

return preg_replace('/\s/', ' ', $data);

Last example modified from the PHP manual.

Using PHP to strip a user IP address into subnets and filter out characters

Found it very difficult to find a simple solution to this problem – you want to display the submittor’s IP address on a webpage where it will be publicly available, but obfuscate some characters.

You want 255.255.255.255 to become 255.255.255.x or something, right?

Preg_replace() an IP

So, assuming we have the user’s IP address, for this we could use the following server variable:

// Displays user IP address
$ip =  $_SERVER['REMOTE_ADDR'];

We can manipulate this with preg_replace.

// Displays user IP address with last subnet replaced by 'x'
$ip = preg_replace('~(\d+)\.(\d+)\.(\d+)\.(\d+)~', "$1.$2.$3.x", $ip);

So the code to get and change this IP would be:

// Sets user IP address
$ip =  $_SERVER['REMOTE_ADDR'];
// Sets user IP address with last subnet replaced by 'x'
$ip = preg_replace('~(\d+)\.(\d+)\.(\d+)\.(\d+)~', "$1.$2.$3.x", $ip);
// Displays user IP address as altered.
echo $ip;

Of course, if you’re happy to just cut off the end then you can use the substr function to trim a number of characters off.

I’ll revise this to validate and deal with proxies another time.

Integrating your existing site into phpBB3

One way of making an existing site dynamic is to integrate it with a phpBB forum. This is actually very easy, and allows you to quickly pull data about your users; control page access by groups and more.

Page type

The page you’re integrating phpBB with needs to be a php page! (pretty obvious really seeing as phpBB is a PHP forum)

Connect to phpBB to get variables, etc.

You will need to include a file containing the phpBB connection information (essentially plugs the page into the rest of phpBB). This file should contain the following

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : 'change_this_to_phpbb_dir';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();
?>

Save this file as phpbb.php, or put this code in some other file you will include in every page you want to integrate. All this code does is define where phpbb can be found, and include the common.php file from within phpbb. It also starts a user session, which we can use on our page.

Get our page to interact with phpBB

So now we go to the page we want to integrate with phpBB. In this case I’m going to use a blank file as an example but obviously you can use any file. This bit of code checks to see if the user is logged in or not and displays an appropriate message

<?php include_once("phpbb.php"); ?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpBB conn test</title>
</head>
<body>
<?php
// PHPBB LOGIN AUTH
if ($user->data['user_id'] == ANONYMOUS) {
?>
Welcome, anomalous!
<?php
} else {
?>
Welcome back, <?php echo $user->data['username_clean']; ?> | You have <?php echo $user->data['user_unread_privmsg']; ?> new messages
<?php } ?>
</body>
</html>

This will display “Welcome, anomalous!” if you’re not logged into phpBB or “Welcome back, username | You have 0 new messages” if you are logged in. Note the placement of the include_once ABOVE the <head> tag – this is required if you don’t want any errors.

This is a very simple example and doesn’t tell our user how to log in or log out…which are pretty critical activities.

How about a login form?

The simple way to login is to push all logins through the phpBB system. This form does just that.

<form method="POST" action="forum/ucp.php?mode=login">
Username: <input type="text" name="username" size="20"><br />
Password: <input type="password" name="password" size="20"><br />
Remember Me?: <input type="checkbox" name="autologin"><br />
<input type="submit" value="Login" name="login">
<input type="hidden" name="redirect" value="">
</form>

This collect the login data and posts it to the phpBB ucp.php. We can make phpBB redirect the user back to any page by changing the value of the redirect input field.

<input type="hidden" name="redirect" value="../somefile.php">

Once this form has been posted the user will be logged in. But how about logging out?

Logging out of phpBB

You could just navigate to the forum and click the link there, but hey – might as well do this in as few clicks as possible. So we need a link to log out…

<a href="somefile.php?cp=logout">Log Out</a>

and in the same file we could put the following at the top of the document:

<?php
// check for logout request
$cp = $_GET['cp'];
// is it a logout? then kill the session!
if ($cp == "logout") {
$user->session_kill();
$user->session_begin();
echo "Successfully Logged Out.";
}
?>

So when cp is set to logout (when the user visits somefile.php?cp=logout) the session containing the userdata is destroyed and reset. ‘Succssfully Logged Out’ is also shown for the user’s benefit.

Combining conditional, login & logout

As a summary, I’ve combined what we looked at above.

In phpbb.php:

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH :  'change_this_to_phpbb_dir';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();
?>

In somefile.php:

<?php include_once("phpbb.php");
// check for logout request
$cp = $_GET['cp'];
// is it a logout? then kill the session!
if ($cp == "logout") {
$user->session_kill();
$user->session_begin();
echo "Successfully Logged Out.";
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpBB conn test</title>
</head>
<body>
<?php
// Page login notice
if ($user->data['user_id'] == ANONYMOUS)
{
?>
<center>Welcome! Please login below or click <a href="forum/ucp.php?mode=register">here</a> to register.<br />
<form method="POST" action="forum/ucp.php?mode=login">
Username: <input type="text" name="username" size="20"><br />
Password: <input type="password" name="password" size="20"><br />
Remember Me?: <input type="checkbox" name="autologin"><br />
<input type="submit" value="Login" name="login">
<input type="hidden" name="redirect" value="../somefile.php">
</form>
<?php
} else { ?>
Welcome back, <?php echo $user->data['username_clean']; ?> | You have <?php echo $user->data['user_unread_privmsg']; ?> new messages | <a href="somefile.php?cp=logout">Log Out</a>
<?php } ?>
</body>
</html>

This will give you basic integration with an existing phpBB forum, we’ll look at further integration including private messenging in contact boxes soon.