Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Easy way to export a SQL table without access to the server or phpMyADMIN

I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP scripts on the server.

How do I get at the data?

I ask this question purely to record my way to do it

like image 959
Jrgns Avatar asked Sep 17 '08 10:09

Jrgns


People also ask

How can I access MySQL database without phpMyAdmin?

you can access using command prompt, command is depend on which server for ex window, linux, etc Linux : mysql -u {mysql-user} -p {mysql-password} -h {mysql-server} Enter password: you can view databases using this command SHOW databases; you can used database using this command use databasedname; and other command is ...

How do I export an entire database from the command line?

First of all open command prompt then open bin directory in cmd (i hope you're aware with cmd commands) go to bin directory of your MySql folder in WAMP program files. press enter system will export particular database and create sql file to the given location.


1 Answers

You could use SQL for this:

$file = 'backups/mytable.sql'; $result = mysql_query("SELECT * INTO OUTFILE '$file' FROM `##table##`"); 

Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example.

To get it back in to your DataBase from that file you can use:

$file = 'backups/mytable.sql'; $result = mysql_query("LOAD DATA INFILE '$file' INTO TABLE `##table##`"); 

The other option is to use PHP to invoke a system command on the server and run 'mysqldump':

$file = 'backups/mytable.sql'; system("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database | gzip > ".$file); 
like image 57
lewis Avatar answered Oct 13 '22 19:10

lewis