Solved

how can I backup a mysql database on shared linux godaddy

Posted on 2014-02-27
2
1,013 Views
Last Modified: 2014-02-27
how can I backup a mysql database on shared linux godaddy

Thank you for contacting Professional Hosting Support. I understand that you would like to create daily backups of your MySQL databases.  Please note that in an effort to protect the integrity of your account, company policy restricts Customer Support Representatives from making changes to customer accounts or services on behalf of the customer.  As such, we cannot modify your account to create the backups on your behalf.

If you would like to allow for autonomous routine tasks to be executed on your shared linux hosting account, rather than manually backing up your MySQL databases every day, you will need to create a cron job to do so.  Please note, however, that because we do not directly support coding and scripting, we are unable to advise specifically as to how you should code the command of your cron job(s) to create the backup(s)--for more advice on this, we recommend that you consult your preferred search engine for more information.

Cron is a standard Linux feature that allows you to schedule tasks, called "Cron Jobs," to run unattended at a specified frequency. For example, you can set the frequency of a job to run twice an hour, Mondays at 8:00 a.m., or weekdays at 12:00 p.m. and 6:00 p.m.

Cron Job reports are sent to the email address specified in the Hosting Control Panel Cron Job Manager.

There are several ways to schedule commands to run. Typically, you create a shell script to run as a Cron Job. It runs a list of commands while checking for errors and valid return codes. To run a shell script, set its bit set permissions to "executable." Alternatively, compose a binary executable with a number of arguments. For example, "touch" a file:

/bin/touch $HOME/html/cron_test

The first part of this sample command, "/bin/touch" runs the executable. The second part, "$HOME/html/cron_test" is an argument for the "touch" command.

NOTE: In the example above, the "$HOME" variable is set to the default directory of the hosting account. The "html" directory is the document root of the hosting account.

In addition to shell scripts, your hosting environment supports other language scripts. For example, Perl is a commonly used scripting language that can use scripts as Cron Jobs. In most cases, you can perform this task by specifying to use the executable as the first line of the script. For example:

#!/usr/bin/perl

When a script begins with a line, as in the previous example, and the executable permissions are set, you can specify the line as the command to run for a Cron Job.

The PHP (versions 4 and 5) hosting installations do not support the use of the executable line in scripts. To run a PHP script with Cron, you must set the path to the PHP interpreter as the first element of the command, and then enter the full path of the script you want to run.

The full path to the PHP executables are:


    PHP version 4: /web/cgi-bin/php

    PHP version 5: /web/cgi-bin/php5

    PHP version 5.3: /web/cgi-bin/php5_3


For more information, see Accessing Your Cron Job Manager with Shared Hosting.

If we can help you in any other way, please let us know.

Sincerely,
James P.
Professional Hosting Support

As we value your feedback about the service you received, please take a moment to complete the short survey linked at the bottom of this email.
0
Comment
Question by:rgb192
2 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
You may want to experiment with this a little bit.  It works on my server, so it's a place to start.

<?php // demo/mysql_backup_dump.php
error_reporting(E_ALL);

// 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)) trigger_error("ERROR: DATABASE {$_POST["d"]} NOT FOUND", E_USER_ERROR);
$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

0
 

Author Closing Comment

by:rgb192
Comment Utility
many lines of
Warning: fwrite() expects parameter 1 to be resource, boolean given in C:\Users\Acer\Documents\NuSphere PhpED\Projects\noname295.php on line 122

and then message of file creation at /backup folder

but backup folder was not created

so I create backup folder and then sql file was created. Thanks


I will verify by creating a new database on local host

Thank you.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now