How to get database backup from online server to offline server using PHP with MySQL

Question

//Assume Server 1
$conn = mysql_connect("127.0.0.1","root","");

//Assume Server 2
$conn1 = mysql_connect("127.0.0.1","root","");

//Server 1 database
mysql_select_db("db1",$conn);

//Server 2 database
mysql_select_db("db2",$conn1);

//Count number of rows from server 1 -> database -> table (tbl1)
$cnt_rw=mysql_query("select count(*) from db1.tbl1");
$cnt_n=mysql_fetch_array($cnt_rw);  

//Fetch and update row one by one
for($i=0;$i<($cnt_n['count(*)']);$i++)
{
    $one_row=mysql_query("select * from db1.tbl1 limit $i,1");
    while($one_val=mysql_fetch_array($one_row))
    {
        $one=$one_val['one'];
        $two=$one_val['two'];
        $three=$one_val['three'];
    }

    //Already exist means update else insert so am using replace query
    mysql_query("REPLACE INTO db2.tbl2(one,two,three)values('".$one."','".$two."','".$three."')");
    $one=$two=$three='';
}   

This PHP code is working properly but take a long loading time. So I want a simple query or PHP/MySQL code or any idea to get backups from the online server to the offline server.

Online and offline database fields are same.

Answer 1

Since using PHP might not be the best idea if you have large amount of data you could also use command line tools like mysqldump to get a copy of the data and put it on your local machine with the mysql cli command.

Another option might be special tools like sqlyog which help you migrating data from one server to another.

Answer 2

Common admin solution is to have cron script which dumps whole db to file (ideally compressed to bz2) and then upload it via ftp, sftp, rsync to backup server.