20577

insert into a MySQL database on a different server

Using PHP and MySQL in a script run on mysite1.com I'm trying to copy all rows and columns from a table on mysite2.com into an identical table (that has already been created) on mysite1.com. First I connect to both the databases (I've already set up remote MySQL connections on mysite2.com).

$con1 = mysql_connect("mysite1.com", "username1", "password1"); if (!$con1) {die('Could not connect: ' . mysql_error());} mysql_select_db("database1", $con1); $con2 = mysql_connect("mysite2.com", "username2", "password2"); if (!$con2) {die('Could not connect: ' . mysql_error());} mysql_select_db("database2", $con2);

I can't figure out how to format the "INSERT INTO" sql string so it will get the data from mysite2.com and put it in mysite1.com. Does anyone know how to do this?

INSERT INTO my_table1 SELECT * FROM my_table2

Answer1:

Can't be done the way you're thinking. You could set up a federated table and copy from that. Probably simpler just to use mysqldump and then load the file it creates though.

Third option is to read the data through php and generate INSERT statements, but that'l be the slowest of the three options.

Answer2:

MySQL makes a migration assistant tool that will do this. This functionality might be folded into the Workbench now.

You might also consider doing the following:

from mysql1 connect to mysql1: SELECT * FROM table INTO OUTFILE filename

from mysql1 connect to mysql2: LOAD DATA LOCAL INFILE filename INTO TABLE table

This will be pretty fast, but it won't be a perfect copy if you're doing inserts into mysql1 while this is going on.

If you need these to stay synchronized, replication is definitely the answer.

Answer3:

A mysql connection handle can't be shared between different servers. It's specifically tied to the server you opened it on, and that's where it'll stay.

There's a couple options. bemace mentioned federated tables. There's also replication. Of the two, replication is probably the easiest to maintain, though it is somewhat of a pain to get up and running. Once it's up, it'll handle all the updates to the replicated tables/databases transparently, and you don't have to worry about it.

With federated tables, you have to run your queries at least twice. One for the "local" table, and one for each "remote" table. And you'll run into problems with transactions: they're not supported at all on federated tables.

Answer4:

$con1 = mysql_connect("mysite1.com", "username1", "password1"); if (!$con1) {die('Could not connect: ' . mysql_error());} mysql_select_db("database1", $con1); $result = mysql_query('SELECT * FROM `some_table`', $con1); $query = array(); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $query[] = '('.implode(',', $row).')'; } $con2 = mysql_connect("mysite2.com", "username2", "password2"); if (!$con2) {die('Could not connect: ' . mysql_error());} mysql_select_db("database2", $con2); mysql_query('INSERT INTO `some_table` VALUES '.implode(',', $query).';', $con2);

Answer5:

You could use a 3rd party database synchronizer such as http://www.quest.com/toad-for-mysql/ (free)

Answer6:

Try this:

// origin $dblink1=mysql_connect('mysite1.com', 'user1', 'password1'); mysql_select_db('db1', $dblink1); // destination $dblink2=mysql_connect('mysite2.com', 'user2', 'password2'); mysql_select_db('db2',$dblink2); $res_from = mysql_query("show tables", $dblink1); if($res_from === FALSE) { die(mysql_error()); // for error handling } while($table = mysql_fetch_array($res_from)) { echo($table[0] . "<br />"); // optional, only for show the name tables $table=$table[0]; $tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ",$dblink1)); // retrieve table structure from mysite1.com mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on mysite2.com $res_to = mysql_query("SELECT * FROM $table ",$dblink1); // select all content from table mysite1.com while ($row = mysql_fetch_array($res_to, MYSQL_ASSOC) ) { $sql_error = ""; mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table on mysite2.com`enter code here` $sql_error = mysql_error($dblink2); } if ($sql_error) { echo $sql_error; }else { echo "copy table ".$table." done.<br />"; } flush(); } mysql_close($dblink1); mysql_close($dblink2);

IMPORTANT! make sure you have a user with all privileges in the mysql of mysite1.com In any case, you can make one so: CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; GRANT ALL ON *.* to myuser;

Recommend

  • How to add a new sheet in excel using VBA?
  • Why do I keep getting only tha last object value in Java ArrayList?
  • Java: Javolution: How to use UTF8ByteBufferWriter and MappedByteBuffer?
  • Google assistant SDK custom commands
  • batch file combine text from multiple files into one csv
  • Append vbCrLF to UCS-2 Little Endian
  • How to develop google actions locally?
  • connect to mysql database from docker container
  • Linked List From Text File
  • Firing Tags when a Virtual Page View occured
  • Getting info from struct after calling system plugin
  • Getting info from struct after calling system plugin
  • gzipstream.copyto alternate and easy way in .net 3.5
  • Microsoft Graph api 403 access denied when reading other users
  • How to consume TypeScript module:system export with systemjs in browser
  • Using GIT to implement Dropbox type functionality
  • Customizing the specific output files for various Typescript input files
  • Microsoft.Build.Evaluation.Project add folder and file force refresh of project in VS
  • Django/Python: How to iterate through a list in a dictionary for migration/migrate data
  • Django implementation of default value in database
  • insert into a MySQL database on a different server
  • Git cherry-Pick a batch of commits from a file
  • Uploading file with WCF streaming, tiny reads from stream
  • Entity Framework Core 1.0.1 add-migration
  • Type safe keys with Entity Framework model
  • using html data-attributes as css-variable (i.e. text-shadow)
  • Click on button in another program - FindWindow, C#
  • Creating PDF from TIFF image using iText
  • VSCode change debug shell to bash on windows
  • Does Apple allow the usage of sysctl.h within iOS applications?
  • as3-flash: any way to access all the instances placed in different frames from document class?
  • How to use carriage return with multiple line?
  • Moving mysql files across servers
  • Cancel a live stream “fast motion” catch-up in Flash
  • QLineEdit password safety
  • Spring security and special characters
  • Deleting and Updating values from a cusrsor adapter
  • Modifying destination and filename of gulp-svg-sprite
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview