Export MySQL Database using Command line Interface

First check if your command line recognizes mysql command. If not go to command and type in

set path=c:\wamp\bin\mysql\mysql5.1.17\bin

Then use this command to export your database

mysqldump -u YourUserName -p YourDatabaseName > backup_database_file_name.sql

This exports the database to the path you are currently in, while executing this command

More commands fro export/Backup database or databases.

# Backup a single database
# NOTE: you can also use the shortcuts -u and -p to specify username and password
mysqldump –user=… –password=… your_database_name > your_database_name.sql

# Backup multiple databases:
mysqldump –user=… –password=… –databases db1 db2 > backup.sql

# or all the databases in one shot:
mysqldump –user=… –password=… –all-databases > backup.sql

# Optionally, it’s easy to compress the backup, for example with gzip:
mysqldump –user=… –password=… your_database_name \
| gzip > your_database_name.sql

# And, if your database also has other objects (apart from tables) such as functions,
# views, and stored procedures, you can back them up too with –routines:

mysqldump –user=… –password=… –routines \
your_database_name > your_database_name.sql

# It may also be useful to include a timestamp in the target file name, so to know
# right away when a backup was taken:

mysqldump –user=… –password=… \
your_database_name > “your_database_name-$(date +%Y-%m-%d-%H.%M.%S).sql”

# If you are backing up data with the purpose of restoring it to an instance you want
# to use as replication slave, then the option –master-data is handy as it adds to
# the dump the information needed to configure the connection with the
# replication master:

mysqldump –user=… –password=… –all-databases –master-data > backup.sql

Database using simple PHP file without the help of shell access or phpmyadmin



/* backup the db OR just a table */

function backup_tables($host,$user,$pass,$name,$tables = ‘*’)



$link = mysql_connect($host,$user,$pass);

if (!$link) {

    die(‘Could not connect: ’ . mysql_error());



//get all of the tables

if($tables == ‘*’)


$tables = array();

$result = mysql_query(‘SHOW TABLES’);

while($row = mysql_fetch_row($result))


$tables[] = $row[0];






$tables = is_array($tables) ? $tables : explode(‘,’,$tables);


//cycle through

foreach($tables as $table)


$result = mysql_query(‘SELECT * FROM ‘.$table);

$num_fields = mysql_num_fields($result);

//$return.= ‘DROP TABLE ‘.$table.’;’;

$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));

$return.= “nn”.$row2[1].”;nn”;

for ($i = 0; $i < $num_fields; $i++) 


while($row = mysql_fetch_row($result))


$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;

for($j=0; $j<$num_fields; $j++) 


$row[$j] = addslashes($row[$j]);

$row[$j] = ereg_replace(“n”,”\n”,$row[$j]);

if (isset($row[$j])) { $return.= ‘”’.$row[$j].’”’ ; } else { $return.= ‘”“’; }

if ($j<($num_fields-1)) { $return.= ‘,’; }


$return.= “);n”;





//save file

$handle = fopen(‘mmmm/db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);





Export to CSV file and download – PHP

function CSVExport() {
$query=”SELECT id,email FROM `newsletterusers` WHERE `status` = 1”;
$sql_csv = mysql_query($query) or die(“Error: ” . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer

while($row = mysql_fetch_row($sql_csv)) {
print ‘”’ . stripslashes(implode(‘”,”’,$row)) . “"n”;