MySQL Insert into multiple databases - issue

Question

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:)

What isn't working corerctly? What is the error you recieve? — Michael Berkowski
– Michael Berkowski, Commented Aug 5, 2012 at 15:13
thanks I have updated it says that the backup user can't have access to the real servers DB. — TryHarder
– TryHarder, Commented Aug 5, 2012 at 15:15
Are you aware of MySQL replication? This is not a problem you should try to solve in code. — Asaph
– Asaph, Commented Aug 5, 2012 at 15:15
What you search for is called database replication. Possible related issue: stackoverflow.com/questions/7278163/… — Whisperity
– Whisperity, Commented Aug 5, 2012 at 15:16
@Asaph Both have their place though. Replication is usually live and on all the time. It does not protect you from messing up one database with an errant statement, since the same error gets replicated to the other as well (unless you switch replication on and off as a backup system).This is more like a live SQL dump that only happens intermittently. — Michael Berkowski
– Michael Berkowski, Commented Aug 5, 2012 at 15:17

Michael Berkowski · Accepted Answer · 2012-08-05 15:16:19Z

2

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.

answered Aug 5, 2012 at 15:16

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TryHarder Over a year ago

I think this is would be the solution but I am not sure if I can do on a share host so I can't give access... I think but I will double check that. Thanks

TryHarder Over a year ago

GRANT SELECT ON realuser.* TO 'bckuser'@'%' WITH GRANT OPTION; this is how it worked can you tell me what is the different between yours and my (came from my host provider) thx

Michael Berkowski Over a year ago

@AndrasSebestyen WITH GRANT OPTION would permit the bckuser user account to use GRANT statements and grant privs to other users. That may not be needed in your situation.

Whisperity · Accepted Answer · 2012-08-05 15:33:38Z

1

Warning:

Please, don't use mysql_* functions to write new code. They are no longer maintained and the community has begun the deprecation process. See the red box?

Instead, you should learn about prepared statements and use either PDO or MySQLi. This article should give some details about deciding which API to use. For PDO, here is a good tutorial.

To replicate the database without using MySQL's internal replication feature, you can use the CREATE TABLE foo LIKE bar; and INSERT INTO foo SELECT * FROM bar; statements.

The first line will create a new table named foo using the exact same structure what bar had originally, and the second line will help you copy the whole contents of bar into foo.

Edit:

The table names can be said as foo, it will mean the foo table in your current database. But you can say baz.foo, which will mean the foo table in your baz database. Of course, your users needs the required privileges to have on both databases. The following code works:

mysql_connect("localhost", "username", "password");
mysql_select_db("original");

mysql_query("CREATE TABLE backup.foo LIKE original.bar");
mysql_query("INSERT INTO backup.foo SELECT * FROM original.bar");

mysql_close();

edited Aug 5, 2012 at 15:33

answered Aug 5, 2012 at 15:27

Whisperity

3,0421 gold badge21 silver badges36 bronze badges

3 Comments

TryHarder Over a year ago

thanks the problem is I am using two Database so access is an issue $result = mysql_query($query, $bckconn) as the Insert run on the bckserver I cant access to the real access???

TryHarder Over a year ago

thanks I am struggling with the permission the rest of it worked when I used only one DB-t

Whisperity Over a year ago

@AndrasSebestyen You will need SELECT, INSERT and CREATE privileges on both databases. The two databases needs to be on the same host.

Collectives™ on Stack Overflow

MySQL Insert into multiple databases - issue

2 Answers 2

3 Comments

Edit:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Edit:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related