Geek Answers Handbook

Can I use MySql Create Select on two different servers with PHP?

I am trying to use PHP and MySQL Create Table Choose between two different MySQL servers. I'm not sure if this can be done using SQL. I am not getting errors, but I am not doing anything:

<?php
$dbname = 'cms';
$dbmaster = 'cmsms';

$db1 = mysql_connect('localhost', 'root', 'secret');
if (!$db1) {
    echo 'Could not connect to mysql';
    exit;
}

$db2 = mysql_connect('server2', 'root', 'secret');
if (!$db2) {
    echo 'Could not connect to mysql';
    exit;
}

mysql_select_db("$dbname", $db1) or die ("Unable to select database");
mysql_select_db("$dbmaster", $db2) or die ("Unable to select database");

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql, $db1);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    $sql = "DROP TABLE `$row[0]`";
    mysql_query($sql, $db1);
    echo "Table rows: {$row[0]} Deleted <br/>";
    $sql = "CREATE TABLE $row[0] SELECT * FROM $db2.$dbmaster.$row[0] ";
    mysql_query($sql, $db1);
    echo "Table: {$row[0]} created <br/>";

}
echo "<br/>done...";

mysql_free_result($result);
?>

This line:

$sql = "CREATE TABLE $row[0] SELECT * FROM $db2.$dbmaster.$row[0] ";

just doesn't work. $ db2 does not transfer it to another server and does not select it.

After doing some reading, I found someone similar, and someone said that it was impossible to do this and look at the joined tables that would not work for me.

If this is not possible to do above, does anyone know a way to do what I do? I drop the tables onto the copies and recreate them based on the table in the main. Then I select the data in the main to insert the newly created tables. Thanks you

: . , , . , - create table select . SQL . , , - SQL.

+5
5

, PHP script , , .

$sql. mysql_query.

, :

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql, $db1);

. :

$sql = "SHOW TABLES";
$result = mysql_query($sql, $db1);

$db1 mysql_query, , .

, .

:

$sql = "CREATE TABLE $row[0] SELECT * FROM $db2.$dbmaster.$row[0] ";

$db2 $db1, :

  • $db2
  • $db1
  • Iterate $db2
  • $db1

.

+7

, . PHP- db1 db2, , db1 db2. db1 db2 , . PHP script .

select, db1, , insert db2. , , create table . , "create table select from", , create, db1. ?

http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

+3

$db2

SQL $db1,

mysql_query($sql, $db1);

SQL $db2,

mysql_query($sql, $db2);
0

, , , , . , MySQL. , , , , MySQL Proxy.

, , / / /. .

, .

0

, .

1) Implement asynchronous mysql replication. This will do the same thing you are trying to do in PHP, but it will be much more efficient. Mysql Documentation: Replication

2) Correct the script to transfer data from the main server to the backup server. This can be done by creating a table, and then loading the result in a row by row into the backup server table.

0
source

More articles:


All Articles