Jump to content

Exporting data to a .sql file


mikemike27

Recommended Posts

Hi,

 

I can't believe I haven't been able to find this on Google in about 5 seconds.... but I haven't  :(

 

I’m trying to export data on a mysql table from one server to another using a webpage (so it’s an automated process)

 

So in my .php file I need to;

1. create an SQL statement that sends everything from one table on server1 to a .sql file.

2. Then I need to switch servers - which I know how to do

3. Then I need to import that .sql file to a table on server2.

 

I tried “SELECT * INTO OUTFILE '111_20120613.sql' FROM TABLE1” but yeh, that did not work. A similar statement, that sends it all to a csv file DOES work, but sending it to a csv will really slow my servers down. I'm moving a lot of data here.

 

Please help!

 

Mike

 

 

Link to comment
Share on other sites

First of all the query syntax you have mentioned in your post is not correct.

Changing extension form csv to sql will not have any effect , it will be still written as a flat file.

 

If you want to dump table  as a sql file you need use mysqldump.

Link to comment
Share on other sites

Ok. Thanks. Using mysqldump I waded around Google until I found out about the exec() function in PHP. I can use mysqldump in that. It works very nicely...

 

Now, just one final part is getting from the .sql file on to a different server.

 

I've tried;

exec('mysql --user=... --password=... --host=... $database $tablename' < /location/$filename.sql');

and;

exec('mysqlimport --user=... --password=... --host=... $database $tablename' < /location/$filename.sql');

 

Neither worked. They were complete guesses. I reversed the '>' as you can see, and I took off the 'dump' part as it's not a dump. Do you know this one too??

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.