How to export and import mysql database with its data using php script?

I was asked to create an export and import mysql database with it's structure and data into a .sql file using php scripting and not phpmyadmin to make the user be able to backup his data?

Does anybody have an idea how to do that??

Thanks in advance

php
mysql

2012-04-04 22:13
by Mohamed Hassan

Does it have to be just PHP? Could always use mysqldump, and if it needed to be a PHP script, could use mysqldump through exec - Corbin 2012-04-04 22:17

Dup of Easy way to export a SQL table without access to the server or phpMyADMIN, php code to mysql database export, Best practice: Import mySQL file in PHP; split queries, and likely many others - outis 2012-04-04 22:22

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

either

$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip >     $backupFile";
system($command);

2012-04-04 22:19
by binarious

anybody knows how to import it to phpmyadmin through a php scrip - Mohamed Hassan 2012-04-05 00:31

Look at the tutorial. There is a "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";section - binarious 2012-04-05 00:32

SAFE and WORKING SOLUTION :

EXPORT_TABLES("localhost","user","pass","db_name"); 
// has 5th and 6th optional parameters too.

Latest version is available at github: Export.php + Import.php

2014-01-22 13:36
by T.Todua

Thank you! this is a best code. because other codes work with "exec" or "system" functions.this functions disabled on Linux hosts. but this script worked on Linux hosts - mghhgm 2014-04-19 22:56

exellent code worked for m - Nothing 2014-05-22 07:35

Awesome... Thanks for sharing this code Mr.tazo...It's working perfectly for me...Thanks - NoName 2016-10-02 12:08

fantastic and its very useful one - Karthi 2016-10-24 06:59

Here is a nice class that worked just fine for me. if it says fopen access denied, try to change the folder permissions.

https://github.com/clouddueling/mysqldump-php

2013-08-25 12:21
by Ayoub