Exporting data to a .sql file

[mikemike27]

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

 

 

[Illusion]

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.

[mikemike27]

Yes. mysqldump would be ideal. But can mysqldump be done from a php script? I thought it was only through the command line?

[Illusion]

You can run mysqldump from php script if you have dedicated server for your site or privileges to run the invoke commands (which will not be a case with shared hosting).

[mikemike27]

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??

 

[The Little Guy]

does the user on the database have enough permissions to import the dump file?

 

also remove the table name from the command your running it is already in the dump file. you also have too many quotes.

 

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

[mikemike27]

Ah, awesome. Removing the table name worked perfectly. Cheers for all the help guys! Much appreciated.