0

I need to take a backup of some selected tables in database.I have a form in my php page ,when clicking submit button on the form then the backup of selected table is stored to my computer as .sql or .sql zip file.

(eg: if db1 is my database name ,table1,table2,table3 are tables, then clicking on the submit button in the form take a backup of tabl1,table2 only and save this as .sql or .sqlzip file in my computer).

If any one know the solution please post here.

Raptor
  • 53,206
  • 45
  • 230
  • 366
  • 1
    I do not recommend to use PHP codes to perform the job, as you can use `mysqldump` to do so. But anyway, you can still use PHP `exec()` to execute `mysqldump` command, but it sounds redundant. A shell script (if Linux-based) is already sufficient. – Raptor Feb 04 '14 at 06:51
  • This is for specific need in my plugin work for wordpress site.So i need the php code.Please help if you know the code. I write if(isset($_REQUEST['submit'])){ mysql_query('mysqldump -usecret_user -psecret_pass gcertificate wp_wpgc_campaigns > ThreeTablesBackup4Feb.sql'); } .But the code is not working. –  Feb 04 '14 at 07:05
  • `mysqldump` is a command / executable, not a SQL statement. Also, `mysql_*` functions are deprecated; use PDO or MySQLi instead. There are a lot more issues than you imagine, such as network timeout. Last, do NOT re-invent the wheel, there are a lot of WP plugins doing the same task; You can reference their codes. – Raptor Feb 04 '14 at 07:08
  • This is a good suggestion . –  Feb 04 '14 at 07:24
  • 1
    please see the link http://stackoverflow.com/questions/21546786/how-to-create-a-plugin-for-take-a-backup-of-speific-table-in-wordpress-database –  Feb 04 '14 at 08:09

1 Answers1

0

If you are dumping tables t1, t2, and t3 from mydb

mysqldump -uYouruser -pYourPassword YourDBName Table1 Table2 Table3 > /PathWhereToStoreBackup/mydb_tables.sql

If you have a ton of tables in mydb and you want to dump everything except t1, t2, and t3, do this:

DBTODUMP=mydb
TBLIST=`mysql -u... -p... -AN -e"select group_concat(table_name separator ' ') from information_schema.tables where table_schema='${DBTODUMP}' and table_name not in ('t1','t2','t3')"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql
Abdul Manaf
  • 4,768
  • 3
  • 27
  • 34
  • Thank you for support .But the code is not working . i write if(isset($_REQUEST['submit'])){ mysql_query('mysqldump -usecret_user -psecret_pass gcertificate wp_wpgc_campaigns > ThreeTablesBackup4Feb.sql'); } i write this code as mention in the answer.But it is not working –  Feb 04 '14 at 07:01
  • Simplified my answer..Whats the `error/problem` you are facing – Abdul Manaf Feb 04 '14 at 07:03
  • There were no error/problem but i didn't see the .sql file in any location .Really i didn't know where the .sql file is saved.I look in download folder but no sql files are there. –  Feb 04 '14 at 07:14
  • if(isset($_REQUEST['submit'])){ $table_name = "wp_wpgc_campaigns"; $backup_file = "/wp_wpgc_campaigns.sql"; $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name"; mysql_select_db('gcertificate'); $retval = mysql_query( $sql); if(! $retval ) { die('Could not take data backup: ' . mysql_error()); } echo "Backedup data successfully\n"; } i write this code.It is working but sql file contain no data .The file size is 0kb –  Feb 04 '14 at 07:20
  • this also not working mysql_query('mysqldump -root - gcertificate wp_wpgc_campaigns> /mydb_tables.sql');. Really thanks for your support.Please consider this again . –  Feb 04 '14 at 07:30
  • As mentioned before, `mysqldump` cannot be executed by `mysql_query()` as it is a standalone command. Read the `mysqldump` manual. – Raptor Feb 04 '14 at 08:37