Solved

MySQL backup progam

Posted on 2013-11-22
6
469 Views
Last Modified: 2013-12-05
I would need a program for backuping MySQL databases, preferably free.

I have a number of websites, but the backing them manually using mysqldump is a bore, and scripts are a bit unreliable.

The program should connect to the DB host, and back the DB up.


Thanks! :)
0
Comment
Question by:mrmut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 167 total points
ID: 39669430
This is what I'm using and works great so far,

http://sourceforge.net/projects/automysqlbackup/

Other option:

http://mysqlbackupftp.com/


Or use MySQL own mysqldump.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 167 total points
ID: 39671411
This uses PHP with the obsolete MySQL extension, but it works correctly and could be easily brought up-to-date with the MySQLi extension.  You could add an FTP step, perhaps.

Dump:
<?php // RAY_mysql_backup_dump.php
error_reporting(E_ALL);
date_default_timezone_set("America/Chicago");


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB LIST: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}


// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
    // NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
    echo "<form method=\"post\">\n";
    echo "BACK UP A DATA BASE:<br/>";
    foreach ($db_names as $db_name)
    {
        echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
    }
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];


// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}


// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp   = fopen($bkup, "w");


// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
    $tables[] = $s[0];
}


// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{

    // WRITE THE DROP TABLE STATEMENT
    fwrite($fp,"DROP TABLE `$table`;\n");

    // GET THE CREATE TABLE STATEMENT
    $res = mysql_query("SHOW CREATE TABLE `$table`");
    if (!$res) die( mysql_error() );
    $cre = mysql_fetch_array($res);
    $cre[1] .= ";";
    $txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
    fwrite($fp, $txt . "\n");

    // GET THE TABLE DATA
    $data = mysql_query("SELECT * FROM `$table`");
    $num  = mysql_num_fields($data);
    while ($row = mysql_fetch_array($data))
    {
        // MAKE INSERT STATEMENTS FOR ALL THE VALUES
        $txt = "INSERT INTO `$table` VALUES(";
        for ($i=0; $i < $num; $i++)
        {
            $txt .= "'".mysql_real_escape_string($row[$i])."', ";
        }
        $txt = substr($txt, 0, -2);
        fwrite($fp, $txt . ");\n");
    }
}

// ALL DONE
fclose($fp);

// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";

Open in new window

Restore:
<?php // RAY_mysql_backup_restore.php
error_reporting(E_ALL);


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'RAY_backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');

// IF NOTHING POSTED YET
if (empty($_POST))
{

// ASK CLIENT TO CHOOSE ONE FOR RESTORE
    $x = FALSE;
    echo "<form method=\"post\">\n";
    echo "RESTORE A DATA BASE:<br/>";
    foreach ($my_dir as $my_file)
    {
        if (!ereg('^mysql', $my_file)) continue;
        echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
        $x = TRUE;
    }
    if (!$x) die('NO BACKUP FILES FOUND');
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");

// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];

// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$",         '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// LOAD THE BACKUP DATA QUERIES
$sqls  = file('RAY_backups/' . $fname);

// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
    if (!$res = mysql_query($sql))
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }
}

Open in new window

HTH, ~Ray
0
 

Author Comment

by:mrmut
ID: 39671426
Thanks to both,

   have to test a bit!
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 166 total points
ID: 39686467
I use sqlyog to take schedule backup.
It takes backup in zip format so issue of size gets solved secondly since i can adjust the schedule time more easily.
0
 

Author Comment

by:mrmut
ID: 39697828
Guys, sorry for late response, I got flu. :(

I will share points, as I haven't yet fully decided which path to take.


Thanks!
0
 

Author Closing Comment

by:mrmut
ID: 39697830
Tahanks all!
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question