Piwigo.org

executive · 2021-01-26 03:29:10

This is outside of Piwigo, but maybe somebody knows an easy answer.

Is there a such thing as a PHP script that will connect to a MySQL DB on a remote server, and duplicate the contents to a local DB?

Please and thanks.

nicolas · 2021-01-26 07:37:53

executive wrote:
Is there a such thing as a PHP script that will connect to a MySQL DB on a remote server, and duplicate the contents to a local DB?

I supposed you are on a shared hosting provider. For security reason, you can not connect remotly on a database server on a shared hosting provider. But your provider probably give you a way to make dump of your database and download them.

executive · 2021-01-26 08:18:34

The remote access is already established. (and by the way, not a problem on shared hosting)

All I'm looking for is a script that can duplicate a database.

Adminer is able to copy remote-to-remote but not remote-to-local.

erAck · 2021-01-26 11:01:44

If it doesn't have to be PHP, how about this

Code:

mysqldump -u remoteuser -p remotepass -h remote.example.com databasename | mysql -u localuser -p localpass databasename

Note that it exposes passwords on the command line, which is visible in a process listing.

executive · 2021-01-26 19:15:18

no shell unfortunately

jnashpiwigo · 2021-01-26 19:32:27

Code:

<?php

exec("mysqldump -u remoteuser -p remotepass -h remote.example.com databasename | mysql -u localuser -p localpass databasename");

?>

;-)

Last edited by jnashpiwigo (2021-01-26 19:33:38)

executive · 2021-01-26 21:17:17

exec is disabled

executive · 2021-01-26 21:19:49

I found one on the net that might do it.

Code:

mysqli_select_db($dblink1,'db1');  // select database 1

$dblink2=mysqli_connect('127.0.0.1', 'root', ''); // connect server 2   

mysqli_select_db($dblink2,'db2'); // select database 2

$result = (mysqli_query($dblink1,"SHOW TABLES  "));
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}
echo "Searching Table: ".$tables[0] ."<br>";
foreach ($tables as $i => $table){

    $val = mysqli_query($dblink2,"select 1 from $table  ");

    if($val !== FALSE) /***** table exists */
    {
        $structure1= get_colums($dblink1, $table);
        $structure2= get_colums($dblink2, $table);
        compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );


        $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
        echo $table." Updating table.... "."<br>";
        $rowcount=mysqli_num_rows($result); 
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
            $update_string = update_query_format($row );

           mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."') 
           ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
        }
        echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>"; 
    }
    else
    {
        /**** table not exists */
       echo $table." Table not found "."<br>";
       echo $table." Creating table... "."<br>";
       $tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table  ")); // get structure from table on server 1
       mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2

       $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
       echo $table." Copying table.... "."<br><br><br>"; 
       while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
          mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table
       }
    }    


}
 mysqli_close($dblink1); 
 mysqli_close($dblink2);


 function compare_structure($table, $dblink1 , $dblink2){
            /***  Check if table exists */
            $val = mysqli_query($dblink2,"select 1 from $table  ");

            if($val !== FALSE) /***** table exists */
            {
                $structure1= get_colums($dblink1, $table);
                $structure2= get_colums($dblink2, $table);
                compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );


                $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
                echo $table." Updating table.... "."<br>";
                $rowcount=mysqli_num_rows($result); 
                while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
                    $update_string = update_query_format($row );

                   mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."') 
                   ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
                }
                echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>"; 
            }
            else
            {
                /**** table not exists */
               echo $table." Table not found "."<br>";
               echo $table." Creating table... "."<br>";
               $tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table  ")); // get structure from table on server 1
               mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2

               $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
               echo $table." Copying table.... "."<br><br><br>"; 
               while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
                  mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table
               }
            }

}

function get_colums($dblink, $table){
    $result = mysqli_query($dblink,"SHOW COLUMNS FROM $table ");
    $columns = [];           
    if (!$result) {
        echo 'Could not run query: ' . mysql_error();
        return [];
    }
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $columns[$row['Field']] = $row['Type'];
        }
    }

    return $columns;

}

function compare_colums( $dblink1, $dblink2,  $structure1, $structure2, $table ){

    foreach ($structure2 as $field => $type){
        if (!array_key_exists($field,$structure2)){

            $result = mysqli_query($dblink2,"ALTER TABLE cus_tbl  ADD $field $type NOT NULL ");  
            if ($result){
                echo "Table: ". $table." New Field created ".$field. " ". $type. " .... "."<br>"; 
            }
        }

    }

}

function update_query_format($row){

    $query_format = "";
    foreach ($row as $k => $v){
        $query_format .= " ".$k. " = '". $v ."',"; 
    }
   // echo strlen($query_format);exit;
   // echo substr($query_format, 0,strlen($query_format)-1); exit;
    return substr($query_format, 0, strlen($query_format)-1);

}

Piwigo.org

Announcement

#1 2021-01-26 03:29:10

I need to clone a remote DB

#2 2021-01-26 07:37:53

Re: I need to clone a remote DB

executive wrote:

#3 2021-01-26 08:18:34

Re: I need to clone a remote DB

#4 2021-01-26 11:01:44

Re: I need to clone a remote DB

Code:

#5 2021-01-26 19:15:18

Re: I need to clone a remote DB

#6 2021-01-26 19:32:27

Re: I need to clone a remote DB

Code:

#7 2021-01-26 21:17:17

Re: I need to clone a remote DB

#8 2021-01-26 21:19:49

Re: I need to clone a remote DB

Code:

Board footer