I have a Database in Local server named 'empData' and i want that all the data from that database to be copied to another Database, which is in the another server(web server) with the name 'empDataBackup'.
I have tried this code mysql_query("INSERT INTO empData.backup_emp SELECT * FROM empData.emp");
But it did not work as both the databases are not in the same server...(as in our case one is in local and another is in web server)..
please help..
views:
43answers:
4
+1
A:
Dump your data out using mysqldump and then pipe that file into mysql to import the data somewhere else.
On server1:
mysqldump empData > empData.sql
On server2:
mysql < empData.sql
If you want to get fancy, you could use pipes and ssh to pipe the data directly from server1 to server2.
scompt.com
2010-06-04 08:19:40
@scompt.com but i have to keep the check that, the data is already present or not..so that to avoid duplication....above method will append the data in the backup database....
Harish Kurup
2010-06-04 08:22:21
Use can pass the `--insert-ignore` flag to `mysqldump` to tell it to write out `INSERT IGNORE` statements for all the data. That way, it won't overwrite a row if the key already exists. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_insert-ignore
scompt.com
2010-06-04 08:27:31
@scompt.com hey thanks..i will try it that way...
Harish Kurup
2010-06-04 08:30:04
thanks @scompt.com, it was a nice answer...
Harish Kurup
2010-06-05 07:21:50
A:
this isn't a PHP answer, but maybe it gets you started:
mysqldump --host=db1.example.com --user=db1user --password=db1pass --single-transaction myschema mytable | \
mysql --host=db2.example.com --user=db2user --password=db2pass myschema
If you can't access both hosts from one machine, you could use ssh, e.g.
ssh db1.example.com 'mysqldump --user=db1user --password=db1pass --single-transaction myschema mytable' | \
mysql --user=db2user --password=db2pass myschema
or
mysqldump --user=db1user --password=db1pass --single-transaction myschema mytable | \
ssh db2.example.com 'mysql --user=db2user --password=db2pass myschema'
sfussenegger
2010-06-04 08:21:50
A:
You have to dump the database on the original server, then copy it to the other server, and restore it.
For each of these actions, there is a "server side" way, and a "PHPside" way. If you can, use the first one. If you can't use the second, but you need to pay extra attention to security.
Server side: 1. and 3.: use mysqldump. 2. use scp to transfer the file.
PHP side: 1. use a script to dump databases, like this one. 2. Copy the file using CURL, or place it in an available directory, protecting it somehow. 3. Retrieve the file from the second server, and just run it through a mysql_exec to restore the DB.
Palantir
2010-06-04 08:23:26
+1
A:
There's always the option of straight MySQL Replication rathe rthan relying on your PHP script to keep the databases synchronised
Mark Baker
2010-06-04 08:38:52