one mysql server to another

[The Little Guy]

What is the fastest way to transfer database info from my home computer using command line php to a remote mysql server?

 

I would like to do this with as few mysql_query() functions as possible, this includes looping

[neel_basu]

Download MySQL GUI

Its free

Back your home db and restore that backuped sql file on Your Server's DB.

[The Little Guy]

Ummm... I don't understand how to transfer from my computer to my remote database using this software.

[gtk]

First Open MySQL Administrator

Then see on your left side there is a Option called back Up.

With it back up your DB First

and then Post.

[The Little Guy]

does that just make a sql file?

[boo_lolly]

use a valid XML document. it's the best way to transfer data from any medium to any other medium. even if it's the same medium. plus, it'd be a good opportunity to learn the wonders of XML and what makes it so friggin awesome!

[The Little Guy]

so... there is no way to do this using PHP mysqli functions?

[Wildbug]

Can you use the mysql command line tools?

 

($ = command prompt)

$ mysqldump your-database > your-db.sql
$ bzip2 your-db.sql
$ sftp remote.computer.net
> cd /whatever/dir
> put your-db.sql.bz2
> quit
$ ssh remote.computer.net
$ cd /whatever/dir
$ bzip2 -d your-db.sql.bz2
$ mysql > your-db.sql
$ rm your-db.sql
$ ^D

[The Little Guy]

I don't want to dump it into a sql file.

 

I just want to take my dead database and send certain rows from that database, if it meets the requirements, over to a live database.

 

My dead database has thousands of URLs, and each URL has a page title. Each page also has context.

 

I currently have 3 spiders,

- one retrieves URLS

- one retrieves titles

- one retrieves body content and file size

 

my 4th spider will crawl my database looking for rows that don't have NULL values for:

- URL

- title

- body content

 

I need to find these, then send a mass amount of rows to the remote database with as few queries as possible.

[Wildbug]

If you're only doing this once or a great once in a while, dumping and uploading is likely the most efficient solution.  You can still use a WHERE clause when dumping:

 

mysqldump database --where="URL IS NOT NULL AND title IS NOT NULL AND content IS NOT NULL" > outfile.sql
...etc

 

If you have your heart set on doing this through PHP, create two database connections, write a query that select the matching rows from the source database and assemble a query with the results for inclusion into the target database.  You can use a loop as usual.  This method, however, will be more taxing on both systems; I don't know if it will be significant, though.

 

$source = mysql_connect('hostname','user','pass');
$target = mysql_connect('hostname2','user','pass');

mysql_select_db('database1',$source);
mysql_select_db('database2',$target);

$result = mysql_query('SELECT * FROM table WHERE URL IS NOT NULL AND title IS NOT NULL AND content IS NOT NULL',$source);
while ($row = mysql_fetch_assoc($result)) mysql_query(sprintf('INSERT INTO table (URL,title,content) VALUES(%s,%s,%s,%s,etc)',$row['URL'],$row['title'],$row['content']),$target);

mysql_close($source);
mysql_close($target);

 

I left out error checking for simplicity's sake.