Export MySQL database using PHP only

Question

What would be a piece of PHP code for exporting a MySQL database to a  sql file   I don t have shell access so I can t use exec  I ve found some code but I looked through the code and this code is old and not well written  Most instructions I ve found require shell access     the rest is outdated    Thanks in advance

User · Answer

If you dont have phpMyAdmin  you can write in php CLI commands such as login to mysql and perform db dump  In this case you would use shell exec function

User · Answer

Here is my code  This will backup MySQL database and store it in the specified path    lt  php function backup mysql database  options    mtables   array     contents    -- Database      options  db to backup      -- n     mysqli   new mysqli  options  db host     options  db uname     options  db password     options  db to backup     if   mysqli- gt connect error        die  Error        mysqli- gt connect errno         mysqli- gt connect error       results    mysqli- gt query  SHOW TABLES     while  row    results- gt fetch array         if   in array  row 0    options  db exclude tables               mtables      row 0            foreach  mtables as  table        contents     -- Table     table    -- n         results    mysqli- gt query  SHOW CREATE TABLE    table       while  row    results- gt fetch array              contents     row 1     n n               results    mysqli- gt query  SELECT   FROM    table        row count    results- gt num rows       fields    results- gt fetch fields         fields count   count  fields         insert head    INSERT INTO     table            for  i 0   i  lt   fields count   i              insert head          fields  i - gt name                  if  i  lt   fields count-1                        insert head                                        insert head               insert head      VALUES n                if  row count gt 0            r   0          while  row    results- gt fetch array                 if   r   400      0                    contents     insert head                             contents                     for  i 0   i  lt   fields count   i                      row content    str replace   n     n   mysqli- gt real escape string  row  i                      switch  fields  i - gt type                       case 8  case 3                           contents      row content                          break                      default                           contents          row content                                         if  i  lt   fields count-1                            contents                                                           if   r 1      row count      r   400     399                    contents        n n                else                   contents        n                              r                       if   is dir    options  db backup path                mkdir    options  db backup path    0777  true         backup file name    options  db to backup       sql-backup-     date   d-m-Y--h-i-s     sql     fp   fopen  options  db backup path            backup file name   w     if    result   fwrite  fp   contents          echo  Backup file created  -- backup file name    result       fclose  fp   return  backup file name      options   array       db host   gt   localhost      mysql host      db uname    gt   root      user      db password    gt        pass      db to backup    gt   attendance     database name      db backup path    gt    htdocs     where to backup      db exclude tables    gt  array     tables to exclude     backup file name backup mysql database  options

User · Answer

lt  php   dbhost    localhost 3036     dbuser    root     dbpass    rootpassword      conn   mysql connect  dbhost   dbuser   dbpass     if    conn       die  Could not connect      mysql error           table name    employee     backup file      tmp employee sql     sql    SELECT   INTO OUTFILE   backup file  FROM  table name     mysql select db  test db      retval   mysql query   sql   conn      if    retval       die  Could not take data backup      mysql error          echo  Backedup  data successfully n     mysql close  conn     gt

User · Answer

In  nix systems  use the WHICH command to show the location of the mysqldump  try this     lt  php  dbhost    localhost    dbuser    root    dbpass    password    dbname    test    mysqldump exec  which mysqldump       command     mysqldump --opt -h  dbhost -u  dbuser -p  dbpass  dbname  gt   dbname sql    exec  command     gt

User · Answer

You can use this command it works or me 100   exec  C   wamp  bin  mysql  mysql5 6 17  bin  mysqldump exe -uroot DatabaseName gt  c   database backup sql      note  C   wamp  bin  mysql  mysql5 6 17  bin  mysqldump exe is the path for mysqldump app   check on your pc   -uroot is -u UserName   If your database is protected with password then add after -uroot this sentense -p YourPassword

User · Answer

I would Suggest that you do the folllowing    x000D   x000D   lt  php x000D   x000D   con   mysqli connect  HostName    UserName    Password    DatabaseName    x000D   x000D   x000D   tables   array    x000D   x000D   result   mysqli query  con  SHOW TABLES    x000D  while   row   mysqli fetch row  result     x000D    tables      row 0   x000D    x000D   x000D   return       x000D   x000D  foreach   tables as  table    x000D    result   mysqli query  con   SELECT   FROM    table   x000D    num fields   mysqli num fields  result   x000D   x000D    return     DROP TABLE    table      x000D    row2   mysqli fetch row mysqli query  con   SHOW CREATE TABLE    table    x000D    return      n n   row2 1     n n   x000D   x000D   for   i 0   i  lt   num fields   i       x000D    while   row   mysqli fetch row  result     x000D      return     INSERT INTO    table   VALUES    x000D     for   j 0   j  lt   num fields   j       x000D       row  j    addslashes  row  j    x000D      if  isset  row  j      x000D        return         row  j        else    return           x000D       if  j lt  num fields-1    return           x000D        x000D       return        n   x000D       x000D      x000D     return      n n n   x000D    x000D    x000D   x000D   x000D   handle   fopen  backup sql    w     x000D  fwrite  handle   return   x000D  fclose  handle   x000D  echo  success   x000D   x000D   x000D    gt  x000D   x000D   x000D    upd  fixed error in code  added space before VALUES in line  return     INSERT INTO    table  VALUES

User · Answer

Try the following   Execute a database backup query from PHP file  Below is an example of using SELECT INTO OUTFILE query for creating table backup    lt  php  DB HOST    localhost    DB USER    xxx    DB PASS    xxx    DB NAME    xxx     con   new mysqli  DB HOST   DB USER   DB PASS   DB NAME   if  con- gt connect errno  gt  0      die  Connection failed       con- gt connect error             tableName     yourtable    backupFile    backup yourtable sql    query         SELECT   INTO OUTFILE   backupFile  FROM  tableName    result   mysqli query  con  query     gt    To restore the backup you just need to run LOAD DATA INFILE query like this    lt  php  DB HOST    localhost    DB USER    xxx    DB PASS    xxx    DB NAME    xxx     con   new mysqli  DB HOST   DB USER   DB PASS   DB NAME   if  con- gt connect errno  gt  0      die  Connection failed       con- gt connect error             tableName     yourtable    backupFile    yourtable sql    query         LOAD DATA INFILE  backupFile  INTO TABLE  tableName    result   mysqli query  con  query     gt

User · Answer

Best way to export database using php script   Or add 5th parameter array  of specific tables  array  mytable1   mytable2   mytable3   for multiple tables   lt  php        ENTER THE RELEVANT INFO BELOW      mysqlUserName         Your Username        mysqlPassword         Your Password        mysqlHostName         Your Host        DbName                Your Database Name here        backup name           mybackup sql        tables                Your tables         or add 5th parameter array  of specific tables     array  mytable1   mytable2   mytable3   for multiple tables      Export Database  mysqlHostName  mysqlUserName  mysqlPassword  DbName    tables false   backup name false         function Export Database  host  user  pass  name    tables false   backup name false                  mysqli   new mysqli  host  user  pass  name             mysqli- gt select db  name             mysqli- gt query  SET NAMES  utf8               queryTables       mysqli- gt query  SHOW TABLES             while  row    queryTables- gt fetch row                             target tables      row 0                         if  tables     false                           target tables   array intersect   target tables   tables                      foreach  target tables as  table                         result              mysqli- gt query  SELECT   FROM    table                  fields amount       result- gt field count                 rows num  mysqli- gt affected rows                    res                 mysqli- gt query  SHOW CREATE TABLE    table                 TableMLine          res- gt fetch row                 content            isset  content           content      n n   TableMLine 1     n n                for   i   0   st counter   0   i  lt   fields amount     i     st counter 0                                 while  row    result- gt fetch row                          when started  and every after 100 command cycle                       if   st counter 100    0     st counter    0                                                        content      nINSERT INTO    table   VALUES                                              content      n                        for  j 0   j lt  fields amount   j                                                      row  j    str replace   n     n   addslashes  row  j                               if  isset  row  j                                                           content         row  j                                                           else                                                            content                                                                 if   j lt   fields amount-1                                                              content                                                                                   content                              every after 100 command cycle  or at last line      p s  but should be inserted 1 cycle eariler                     if      st counter 1  100  0  amp  amp   st counter  0      st counter 1   rows num                                                     content                                                    else                                                 content                                                     st counter  st counter 1                                   content     n n n                        backup name    backup name    backup name    name        date  H-i-s       date  d-m-Y       rand  rand 1 11111111    sql            backup name    backup name    backup name    name   sql           header  Content-Type  application octet-stream               header  Content-Transfer-Encoding  Binary             header  Content-disposition  attachment  filename      backup name                  echo  content  exit          gt

User · Answer

I would Suggest that you do the folllowing    lt  php  function EXPORT TABLES  host   user   pass   name   tables   false   backup name   false         mysqli        new mysqli  host   user   pass   name        mysqli- gt select db  name        mysqli- gt query  SET NAMES  utf8          queryTables    mysqli- gt query  SHOW TABLES        while   row            queryTables- gt fetch row                   target tables      row 0             if   tables     false                 target tables   array intersect  target tables   tables              content    SET SQL MODE     NO AUTO VALUE ON ZERO    r nSET time zone      00 00    r n r n r n   40101 SET  OLD CHARACTER SET CLIENT   CHARACTER SET CLIENT     r n   40101 SET  OLD CHARACTER SET RESULTS   CHARACTER SET RESULTS     r n   40101 SET  OLD COLLATION CONNECTION   COLLATION CONNECTION     r n   40101 SET NAMES utf8     r n--Database        name      r n r n r n       foreach   target tables as  table                 result           mysqli- gt query  SELECT   FROM      table            fields amount    result- gt field count           rows num         mysqli- gt affected rows           res              mysqli- gt query  SHOW CREATE TABLE      table            TableMLine       res- gt fetch row             content      n n     TableMLine 1       n n           for   i   0   st counter   0   i  lt   fields amount   i     st counter   0                        while   row    result- gt fetch row                    when started  and every after 100 command cycle                   if   st counter   100    0     st counter    0                                         content      nINSERT INTO      table     VALUES                                      content      n                    for   j   0   j  lt   fields amount   j                                           row  j    str replace   n      n   addslashes  row  j                         if  isset  row  j                                                   content           row  j                                                   else                                                content                                if   j  lt    fields amount - 1                                                  content                                                                 content                          every after 100 command cycle  or at last line      p s  but should be inserted 1 cycle eariler                 if     st counter   1    100    0  amp  amp   st counter    0      st counter   1     rows num                                         content                                           else                                        content                            st counter    st counter   1                           content     n n n              content      r n r n   40101 SET CHARACTER SET CLIENT  OLD CHARACTER SET CLIENT     r n   40101 SET CHARACTER SET RESULTS  OLD CHARACTER SET RESULTS     r n   40101 SET COLLATION CONNECTION  OLD COLLATION CONNECTION            backup name    backup name    backup name    name            date  H-i-s           date  d-m-Y         rand    rand 1  11111111      sql       header  Content-Type  application octet-stream        header  Content-Transfer-Encoding  Binary        header  Content-disposition  attachment  filename        backup name              echo  content      exit      gt    The enitre project for export and import can be found at https   github com tazotodua useful-php-scripts

User · Answer

This tool might be useful  it s a pure PHP based export utility  https   github com 2createStudio shuttle-export

[php] Export MySQL database using PHP only

Examples related to php

Examples related to mysql

Examples related to backup