Using a php file to generate a MySQL dump

Question

Here s the information I have   I am working with a Linux based system using MySQL and PHP5   I need to be able to generate a mysqldump from within a  php file  and then have that dump be stored in a file on the server in a location I would specify   As I m a PHP nooblet  I d like someone to give me some assistance  guidance  or code  that would do what I require  This would have to be run remotely from the Internet

User · Answer

Here you can find a comprehensive solution to dump mysql structure and data like in PMA (and without using exec, passthru etc.):

https://github.com/antarasi/MySQL-Dump-with-Foreign-keys

It is fork of dszymczuk project with my enhancements.

The usage is simple

<?php
//MySQL connection parameters
$dbhost = 'localhost';
$dbuser = 'dbuser';
$dbpsw = 'pass';
$dbname = 'dbname';

//Connects to mysql server
$connessione = @mysql_connect($dbhost,$dbuser,$dbpsw);

//Set encoding
mysql_query("SET CHARSET utf8");
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");

//Includes class
require_once('FKMySQLDump.php');


//Creates a new instance of FKMySQLDump: it exports without compress and base-16 file
$dumper = new FKMySQLDump($dbname,'fk_dump.sql',false,false);

$params = array(
    //'skip_structure' => TRUE,
    //'skip_data' => TRUE,
);

//Make dump
$dumper->doFKDump($params);

?>

works like a charm :-)

User · Answer

For security reasons  it s recommended to specify the password in a configuration file and not in the command  a user can execute a ps aux   grep mysqldump and see the password      create a temporary file  file     tempnam sys get temp dir     mysqldump       store the configuration options file put contents  file    mysqldump  user   user  password     password          execute the command and output the result passthru  mysqldump --defaults-file  file   dbname        delete the temporary file unlink  file

User · Answer

lt  php exec  mysqldump --all-databases  gt   your path to test sql      gt   You can extend the command with any options mysqldump takes ofcourse  Use man mysqldump for more options  but I guess you knew that

User · Answer

None of the above codes worked for me  I am using windows  Below Code worked for me      sql    SELECT   FROM   tableName WHERE yourclause    result    conn- gt query  sql     if  result            if   result- gt num rows  gt  0                  myfile   fopen  daily events  district sql    w   or die  Unable to open file                  while  row    result- gt fetch assoc                          rowToString   implode        row                    writeToFile    INSERT INTO  tableName VALUES   rowToString      PHP EOL                  fwrite  myfile  writeToFile                             echo  File saved successfully                   else           echo  No result found           This will save file in your project folder according to your query whatever data you want

User · Answer

Take a look here  https   github com ifsnop mysqldump-php   It is a native solution written in php   You can install it using composer  and it is as easy as doing    lt  php  use Ifsnop Mysqldump as IMysqldump   try        dump   new IMysqldump Mysqldump  database    username    password         dump- gt start  storage work dump sql      catch   Exception  e        echo  mysqldump-php error       e- gt getMessage         gt    It supports advanced users  with lots of options copied from the original mysqldump   All the options are explained at the github page  but more or less are auto-explicative    dumpSettingsDefault   array       include-tables    gt  array         exclude-tables    gt  array         compress    gt   None        no-data    gt  false       add-drop-database    gt  false       add-drop-table    gt  false       single-transaction    gt  true       lock-tables    gt  false       add-locks    gt  true       extended-insert    gt  true       disable-foreign-keys-check    gt  false       where    gt           no-create-info    gt  false

User · Answer

MajorLeo s answer point me in the right direction but it didn t worked for me  I ve found this site that follows the same approach and did work    dir    path to file     filename    backup    date  YmdHis       sql gz     db host    host    db username    username    db password    password    db database    database     cmd    mysqldump -h   db host  -u   db username  --password   db password    db database    gzip  gt    dir   filename    exec  cmd    header  Content-type  application octet-stream    header  Content-Disposition  attachment  filename    filename       passthru  cat   dir   filename       I hope it helps someone else

User · Answer

Please reffer to the following link which contains a scriptlet that will help you  http   davidwalsh name backup-mysql-database-php  Note  This script may contain bugs with NULL data types

User · Answer

You can use the exec   function to execute an external command   Note  between shell exec   and exec    I would choose the second one  which doesn t return the output to the PHP script -- no need for the PHP script to get the whole SQL dump as a string   you only need it written to a file  and this can be done by the command itself    That external command will     be a call to mysqldump  with the right parameters   and redirect the output to a file    For example    mysqldump --user     --password     --host     DB NAME  gt   path to output file sql    Which means your PHP code would look like this    exec  mysqldump --user     --password     --host     DB NAME  gt   path to output file sql       Of course  up to you to use the right connection information  replacing the     with those

User · Answer

x000D   x000D  global  wpdb  x000D   export posts    wpdb- gt prefix    export posts   x000D   backupFile     GET  targetDir    export-gallery sql   x000D   dbhost DB HOST  x000D   dbuser DB USER  x000D   dbpass DB PASSWORD  x000D   db DB NAME  x000D   path to mysqldump    D  xampp 5 6 mysql bin   x000D   query   D   xampp 5 6 mysql bin mysqldump exe -u dbuser -p dbpass  db  export posts gt   backupFile   x000D  exec  query   x000D  echo  query  x000D   x000D   x000D

User · Answer

Well  you can always use PHP s system function call   http   php net manual en function system php  http   www php net manual en function exec php  That runs any command-line program from PHP

User · Answer

To dump database using shell exec    below is the method    shell exec  mysqldump -h localhost -u username -ppassword databasename    gzip  gt  dbname sql gz

User · Answer

If you want to create a backup to download it via the browser  you also can do this without using a file   The php function passthru   will directly redirect the output of mysqldump to the browser  In this example it also will be zipped   Pro  You don t have to deal with temp files   Con  Won t work on Windows  May have limits with huge datasets    lt  php   DBUSER  user    DBPASSWD  password    DATABASE  user db     filename    backup-    date  d-m-Y       sql gz    mime    application x-gzip    header   Content-Type       mime    header   Content-Disposition  attachment  filename       filename            cmd    mysqldump -u  DBUSER --password  DBPASSWD  DATABASE   gzip --best       passthru   cmd     exit 0     gt

User · Answer

As long as you are allowed to use exec     you can execute shell commands through your PHP code   So assuming you know how to write the mysqldump in the command line  i e   mysqldump -u  username  -p  database   gt   database  sql   then you can use this as the parameter to exec   function   exec  mysqldump -u mysqluser -p my database  gt  my database dump sql

User · Answer

lt  php      toDay   date  d-m-Y          dbhost      localhost        dbuser      YOUR DB USER        dbpass      USER PASSWORD        dbname      DB NAME        exec  mysqldump --user  dbuser --password   dbpass  --host  dbhost  dbname  gt   home        public html    toDay   DB sql        gt

User · Answer

Here s another native PHP based option   https   github com 2createStudio shuttle-export

[php] Using a .php file to generate a MySQL dump

Examples related to php

Examples related to mysql

Examples related to mysqldump