SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Automatically making mysql backup on other server

    Hi all
    I have a mysql database that I want to automatically backup. I was told by my hosts that I can use a program called mysqldump but it doesn't seem to be able to update another database on another server. How can I do this? thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what does "doesn't seem to be able to update another database on another server" mean?

    what are you trying to do?

    i use mysqldump to take backups of my live database on my web host, and then create and populate an identical database on my desktop computer

    needless to say it's not a huuuuuge database if it'll fit on my desktop

    but the process works just fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just want to have a copy of my database automatically stored on another server I have not on my desk top.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so did you test it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy
    You mean test if I can use mysqldump to automatically update another database on another server at a specific time? Well I haven't yet unfortunately as I just want to see if it is worth even going down that route if it doesn't do what I want. Like the ad for the roast chicken machine says I just want to "set it and forget it". So sorry to labour the point but will mysqldump do what I want? Cheers!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes it will
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  8. #8
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    mysqldump doesn't have any built-in scheduling, you'll have to use cron or some other scheduler to fire off mysqldump at the desired time. Then just have mysqldump save it's output as a temporary file then use the mysql program on the target server to load the dump file to the desired database on the backup server.

    You can do the dump and reload in one step by piping the dump output if you wish.

    But why do you want to have the backup actually reloaded in another mysql anyway? If you need some kind of real-time access to the backup data maybe you want to use replication instead (I've never used mysql replication but it's available. http://dev.mysql.com/doc/refman/5.0/en/replication.html)
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  9. #9
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    If you want, you can create a php file on the source machine that runs mysqldump and outputs the result:

    PHP Code:
    <?php
    echo `mysqldump blah parameters blah blah`;
    ?>
    Notice I'm using back ticks. That's how you run a shell command from php.

    You can then use wget, curl, or whatever from the destination server ... run via cron to take that data and update the database with that info.

    Example from PHP
    PHP Code:
    <?php
    $dbCreateCode 
    = `wget http://www.yoursite.com/dbbackup.php`;

    // code to set up db connection

    $db->execute($dbCreateCode);
    That's a VERY basic example. Sorry for my lack of detail. I'm bored out, and I'm on a tablet. I actually have the working code at home for something similar.

  10. #10
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies, I just want a back up in case my host loses everything, or they go down for a while, it is just information that I have now moved from excel to mysql so I don't have any copies of it.

  11. #11
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I'm trying to set up a 'dump and download' procedure as discussed above.

    I've not used 'mysqldump' before, and I'm having trouble working out which options I need. At present I've got something along these lines:
    PHP Code:
        for ( $i=0$i<count($tb_names); $i++ ) {
            
    $do $dump_path "mysqldump -h" $host " -u" $user " -p" $pass " --opt " $tb_names[$i] . " > " $save_path "/" $tb_names[$i] . ".sql";
            
    passthru($do,$result);
            echo 
    '<br />Do = ' $do;
            echo 
    '<br />' $result;
            echo 
    '<br />';
        } 
    which I've copied from another thread in Sitepoint Forums (and added the 'echo' statements). There's other code before that which sets up the variables, gets the table names from the DB, etc. That part seems to work OK, so I've not quoted it here.
    If I've accessed the database earlier in the script, do I need to do so again here with all the -h, -u, -p stuff ?

    The code as shown is creating the table files in the target folder, but they're all empty. The echo for the first iteration is:
    Do = c:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump -hlocalhost -uroot -ppassword --opt avail_2012 > d:/websites/mullbed2/backup/avail_2012.sql
    1
    'avail_2012' is the DB table name (one of several tables)
    I hope that as $result is '1' that indicates a successful run of 'mysqldump', but if so why are the files empty ?

    If I run:
    PHP Code:
            $do $dump_path "mysqldump --add-drop-database " $data_loc "bb_guests > " $save_path "/" $today "/dump.sql";
            
    passthru($do,$result);
            echo 
    '<br />Do = ' $do;
            echo 
    '<br />' $result;
            echo 
    '<br />'
    I duly get the 'dump.sql' file (which should contain all the tables) but it's still empty. The echo is:
    Do = c:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump --add-drop-database d:/SERVER/MySQL/data/bb_guests > d:/websites/mullbed2/backup/2012-07-12/dump.sql
    1
    I suspect the fact that all files are empty (no matter which version I run) indicates that neither form of $do is accessing the database properly. However it IS being accessed properly earlier in the script.
    I've been reading the MySQL Manual (5.5), but I'm still confused about which parameters I need.

    Can anyone help to set me straight, please ?
    Tim Dawson
    Isle of Mull, Scotland

  12. #12
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Following my last posting, I've now got this, which works to the point of creating the dump in a file accessible to the server:-
    PHP Code:
    <?php

    $dbuser 
    "myusername";
    $dbpass "mypassword";
    $dbhost "localhost";
    $dbname "mydatabase";
    $backupfile 'd:/websites/mullbed2/backup/' $dbname date("Y-m-d") . '.sql';

    system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname --single-transaction > $backupfile");

    ?>
    Having something simple that works makes it much easier to learn more and experiment with the syntax !
    Tim Dawson
    Isle of Mull, Scotland

  13. #13
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes,you can take backup automatically on anther server by using the simple code.
    <?php
    $user = "username";
    $pswd = "password";
    $backupfile = '//websites/mullbed2/backup/' . $dbname . date("Y-m-d") . '.sql';
    system("mysqldump -h $dbhost -u $user -p$pswd $dbname --single-transaction > $backupfile");

    ?>

  14. #14
    SitePoint Evangelist
    Join Date
    Mar 2008
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I have this script that works to make a backup of the db on the same server as my files. No I want to save the db copy on another. Where do I change the code to add the other server? Thank you


    <?php
    backup_tables('MYSERVERIP','MYUSERNAME','MYPASSWORD','MYDATABASENAME');


    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {

    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);

    //get all of the tables
    if($tables == '*')
    {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
    $tables[] = $row[0];
    }
    }
    else
    {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    //cycle through
    foreach($tables as $table)
    {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);

    $return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++)
    {
    while($row = mysql_fetch_row($result))
    {
    $return.= 'INSERT INTO '.$table.' VALUES(';
    for($j=0; $j<$num_fields; $j++)
    {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
    if ($j<($num_fields-1)) { $return.= ','; }
    }
    $return.= ");\n";
    }
    }
    $return.="\n\n\n";
    }

    //save file
    $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    }
    ?>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •