Search code examples
phpmysqlmultiple-databases

MySQL Insert into multiple databases - issue

I have two DB real and backDB. I would like to copy everything from one to a other with SQL... it seems not that easy. If anyone could help me out. Here is my code:

$newdbsql="CREATE TABLE $newdb LIKE $actdbname.`$acttable`";
$newresult = mysql_query($newdbsql, $bckconn) or die(mysql_error());

// copy all the data
$query = "INSERT INTO $newdb SELECT * FROM $actdbname.$acttable WHERE $acttable.azon < $upto";
$result = mysql_query($query, $bckconn) or die(mysql_error());

I've been fighting with it but I can't get it right... thanks

Update: I can connect both DB and I can do simple selects as well. But where I want to read from A and copy to BackupDb it always connect just to one DB.

Error message: SELECT command denied to "backup"user on the real DB...

Important info: I am on shared host so it is not that easy:)

Solution

  • You just need to grant SELECT permissions to the backup database user to the regular database:

    GRANT SELECT ON `$actdbname`.* TO `backup`@`localhost`;

    For best security, it isn't recommended to grant anything other than SELECT, since no other permissions should be needed.