Database backup script using CLI and PHP Tutorial

Lets get started. First we have some global variables. You can change these are required for you.

$database = "wftutorials";
$db_user = "root";
$db_password = "";
$backupLocation = "C:\Users\wfranklin\Documents\GitHub\\test";
$alltables = null;
$backLimit =0;

Most are self explanatory. The allTables option is where we are place the tables to backup. The backLimit is the number of files to keep within our backup directory.

Next we have our connection functions and a clean_string function. This clean_string is used in case our database name as weird stuff in it.

function get_connection(){
  global $database;
  global $db_user;
  global $db_password;

  $dsn = "mysql:host=localhost;dbname=$database";
  $user = $db_user;
  $passwd = $db_password;

  $conn = new PDO($dsn, $user, $passwd);

  return $conn;
}

function cleanString($string) {
  $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.
  return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
}

Now lets go through the back_by_tables function. It should really be named backup.

function back_by_tables(){

}

Inside the back by tables function

So inside the back_by_tables function we call our globals.

global $database;
global $backupLocation;
global $alltables;
global $backLimit;
$tables = [];

The $tables is where our list of tables is going.
We check to see if our backup limit is reached in terms of how many files in the backup directory based on a pattern. I will go through the removeOldFiles function later.

 // remove old files
  if($backLimit > 0){
    echo "Old Files check \r\n";
    removeOldFiles();
  }

Next we get our connection and pull our tables.

 $conn = get_connection();
  if(empty($alltables)){
    $sql = "SELECT TABLE_NAME AS tablename
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '$database'";
    $query = $conn->prepare($sql);
    $query->execute([]);
    $results = $query->fetchAll();
    foreach ($results as $result){
      $tables[] = $result['tablename'];
    }
  }else{
    $tables = explode(',', $alltables);
  }

We either get the tables from the schema. Which is the first way or we check to see if any tables was added to alltables comma delimited we create an array and use those table names.

Next we create our output variable. Now we are going to add to it based on information in our tables.

  $output = "";

Now we loop our $tables and add to the output.

foreach ($tables as $table) {

}

We need to add the drop table if exists statement and we also fetch all the information from the table and place in the results variable.

$query = $conn->prepare('SELECT * FROM ' . $table);
$query->execute([]);
$result = $query->fetchAll(PDO::FETCH_ASSOC); // important
$output .= 'DROP TABLE IF EXISTS ' . $table . ';';
$query2 = $conn->prepare('SHOW CREATE TABLE ' . $table);
$query2->execute([]);
$q2result = $query2->fetchAll();
if(isset($q2result[0]['Create Table'])){
$output .= "\n\n" . $q2result[0]['Create Table'] . ";\n\n";
}

One of the issues I encouter was not using PDO::FETCH_ASSOC on fetch all.

We get how to create the table by call the Show Create SQL query.

Next we loop through the results and add it to our output.

    foreach ($result as $row) {
        $output .= 'INSERT INTO ' . $table . ' VALUES('; // adding insert statement
        foreach ($row as $data) { // looping throgh row to get column data
            $data = addslashes($data); // quote string with slashes

            // Updated to preg_replace to suit PHP5.3 +
            $data = preg_replace("/\n/", "\n", $data); // remove line break to suitable format
            if (isset($data)) {
                $output.= '"' . $data . '"'; // add the data within quotes
            } else {
                $output.= '""';
            }
            $output.= ',';
        }
        $output = substr($output, 0, strlen($output) - 1); // could be removing the trailing comma
        $output.= ");\n"; // close up
    }

This part just works. You can inspect it further if you like. Its not hard to understand though. I’ll add some comments that might help. But has I said it just works.

To understand full the above go through the lines with test data and see the results.

Finallly we add some more line breaks.

$output.="\n\n\n";

Writing to a file

Still inside the back_by_tables function we are now going to write the results to a file.

$filepath = $backupLocation . '\db_'.$database. '_' . strtotime(date("D M d, Y G:i:s")).'.sql';
echo "Writing to file: $filepath \r\n";

$handle = fopen($filepath, 'w+');
fwrite($handle, $output);
fclose($handle);
echo "Backup complete \r\n";

We create a filename using our backupLoaction and the format db_ and the database name. Then we use a unix timestamp to end our file with strtotime.
We call fopen and we create our file. That is it our backup is complete.

Initalization and Process flow

Now lets see how our code really works.
First we check to see if the code is running from command line. If it is not we run away.

// not running from cli
if(!defined('STDIN') ){
  echo("Not Running from CLI"); exit; // run away if not in cli
}

The we get our flags using the code below. Flags are for example below we have tree (lol three) flags. In our case we used on letter.

runcommand -flag 1 -flag2 2 -flag3 3

Lets look at the get_arguments function.

$flags = get_arguments();

function get_arguments(){
  $arguments = getopt("d:t:l:s:");
  return $arguments;
}

We cal the getopt PHP function. Which gets options from the command line argument list. Learn more here

Once we have our flags we check for them to overwrite our default globals

$database = !empty($flags['d']) ? $flags['d'] : $database; // set the database
$alltables = !empty($flags['t']) ? $flags['t'] : null; // set the tables to backup
$backupLocation = !empty($flags['l']) ? $flags['l'] : $backupLocation; // set backup location
$backLimit = !empty($flags['s']) ? $flags['s'] : $backLimit;

Finally we run the code in a try and catch statement.

try{
  echo "Starting backup...\r\n";
  back_by_tables();
}catch(Exception $e){
  echo $e->getMessage() . "\r\n";
}

Removing old files

If you only want to keep a certain number of backups we can do that with this script. Lets see how. The function removeOldFiles handles this. To be honest this one needs to be tested on a unix system first. It might not work that perfectly there but you get the idea.

function removeOldFiles(){
// get globals
  $fCount = 0;
  $firstFile = null;
  global $backLimit;
  global $backupLocation;
  global $database; 

// go through directory
  foreach (new DirectoryIterator($backupLocation) as $file) {
    if($file->isDot()) continue;
        // does the file name exists
    if (strpos($file->getFilename(), 'db_'.$database) !== false) {
      if(empty($firstFile)){
        $firstFile = $file->getFilename(); // save first filename
      }
      $fCount++;
    }
  }
    // if the limit is reached and a first file exists
  if($fCount >= $backLimit && !empty($firstFile)){
    echo "Removing file $firstFile \r\n";
        // remove the first file
    unlink($backupLocation . DIRECTORY_SEPARATOR . $firstFile);
  }
}

I added comments so you can understand what it does.

Conclusion

You can see this class in action using this video.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s