Home | About Chris Hope | Contact Chris Hope | Search:

Command line PHP script to make a backup copy of a MySQL table

Posted February 19th, 2009 in PHP

On Sunday I looked at how to create a copy of a MySQL table with a PHP script and this post looks at how to do the same, but running the PHP script from the command line and passing in the table to copy from and to on the command line.

Passing command like arguments to a PHP script

The idea of the script provided below is to be able to call it like so from the command line, where we are making a copy of the table "products" as "products_bak"

php copy-table.php products products_bak

This can be done with the $argv array which is created by PHP and filled with the script's name as the first value and then subsequent parameters as the second and subsequent parameters.

Doing print_r($argv) from the above example would output this:

Array
(
    [0] => copy-table.php
    [1] => products
    [2] => products_bak
)

So the values we want are in indexes 1 and 2. If they are not specified then the script should warn the user and exit without attempting to do the copy.

To do this we can check if the array is of a certain length, or use isset() against the appropriate array indexes. The latter is the approach I have taken with the script below. Here's an example of getting the from and to tables from parameters 1 and 2:

$from = isset($argv[1]) ? $argv[1] : false;
$to = isset($argv[2]) ? $argv[2] : false;

The PHP script

The code from mysql_connect and down, including the copy_table and function_exists functions have already been covered in the previous post so I won't bother to explain them again, and I've explained the rest above, so here it is:

<?php

$from = isset($argv[1]) ? $argv[1] : false;
$to = isset($argv[2]) ? $argv[2] : false;

if(!$from || !$to) {
    echo "Please specify the table to copy from and to\n";
    echo "Example usage: php $argv[0] tablename_from tablename_to\n";
    exit;
}

mysql_connect('localhost', 'dev', 'singapore');
mysql_select_db('test');

echo "copying $from to $to: ";

if(copy_table($from, $to)) {
    echo "success\n";
}
else {
    echo "failure\n";
}
   
function copy_table($from, $to) {

    if(table_exists($to)) {
        $success = false;
    }
    else {
        mysql_query("CREATE TABLE $to LIKE $from");
        mysql_query("INSERT INTO $to SELECT * FROM $from");
        $success = true;
    }
   
    return $success;
   
}

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }
   
    $res = mysql_query("
        SELECT COUNT(*) AS count
        FROM information_schema.tables
        WHERE table_schema = '$database'
        AND table_name = '$tablename'
    ");
   
    return mysql_result($res, 0) == 1;

}

Save it as e.g. copy-table.php and then it can either be run like this:

php copy-table.php from to

or like this if you "chmod 0755" (or 0700 etc) the script and add a shebang line to the top of the script (this is covered in my running PHP scripts as shell scripts post):

./copy-table.php from to

As noted in my previous post, this script uses the native MySQL functions and has very little error checking so you might want to add more checking and/or change the mysql* functions to use your favourite database library.

Comments

blog comments powered by Disqus