How to dump a Mysql database with PHP?

Question

Currently, to export only the structure of a given database, I use the following:

mysqldump -h host -u usuario --no-data --database banco > dump.sql -psenha

This generates an SQL file, all solved. However, this is a manual process that costs me time, and I can forget to do it, so I thought of making a PHP script to automate it within my system. The problem is I don’t know how to do it.

I would like to know if there is any specific SQL command for me to generate this file. The requirement to be in PHP is that I may need to run this on a shared server, then there would be several restrictions of other languages (like shell script, use the own mysqldump, etc..).

Answer 1

Use the function system of PHP:

system('mysqldump -h host -u usuario --no-data --database banco > dump.sql -psenha');

See more information: http://php.net/manual/en/function.system.php

And then just create a cronjob so that the script runs 1 time a day, or as desired.

---

Here a way to receive a copy of the database by email:

<?php
// Backup do site

$dbhost = 'localhost';
$dbuser = 'usuario_aqui';
$dbpass = 'password_aqui';
$dbname = 'database_aqui';

// Seu e-mail aqui
$sendto = 'Eu <[email protected]>';

// O remetente. Pode ser [email protected]
$sendfrom = 'Backup <[email protected]>';

// Assunto do e-mail
$sendsubject = 'Backup do site ' . date('d/m/Y');

// Corpo do e-mail
$bodyofemail = 'Backup diário do meu site';

$backupfile = 'Autobackup_' . date("Ymd") . '.sql';
$backupzip = $backupfile . '.tar.gz';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass --lock-tables $dbname > $backupfile");
system("tar -czvf $backupzip $backupfile");

include('Mail.php');
include('Mail/mime.php');

$message = new Mail_mime();
$text = "$bodyofemail";
$message->setTXTBody($text);
$message->AddAttachment($backupzip);
$body = $message->get(array(
    'head_charset' => 'utf-8',
    'text_charset' => 'utf-8',
    'html_charset' => 'utf-8'
));
$extraheaders = array("From"=>"$sendfrom", "Subject"=>"$sendsubject");
$headers = $message->headers($extraheaders);
$mail = Mail::factory("mail");
$mail->send("$sendto", $headers, $body);

// Remover o arquivo do servidor (opcional)
unlink($backupzip);
unlink($backupfile);
?>

I don’t remember who originally created the above script. I only remember modifying the code a little.

Answer 2

Use the crontab for scheduling backup jobs, and compressing the banks with gzip, which makes the size of backups much smaller.

To schedule tasks with the crontab, Assuming you have the proper permissions on the system, run the command crontab -e.

For example, in my case I use the crontab to schedule daily backup uym from my bank:

0 * * * * /bin/sh /home/ec2-user/sh/backup_mysql.sh > /dev/null 2>&1

And within the backup_mysql.sh I leave several commands mysqldump, in the model below:

mysqldump -u<usuario> -p<senha> <banco> | gzip > /home/ec2-user/backups/mysql/<banco>-`date '+%Y-%m-%d'`.sql.gz

This is what I believe is best practice for a routine of periodic backups of databases. Try to use this template to find what best suits your purposes.

Answer 3

I ended up arriving at a solution in PHP itself, based on the site script posted by @Gabriel Santos. Follow:

class DatabaseDump {
    private $db;
    private $host, $user, $pass, $dbname;
    private $sql, $removeAI;

    public function __construct($host, $user, $pass, $dbname) {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->dbname = $dbname;
        $this->removeAI = true;

        try {
            $this->db = new PDO('mysql:dbname='.$dbname.';host='.$host, $user, $pass);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();          
            die;
        }
    }

    private function ln($text = '') {
        $this->sql = $this->sql . $text . "\n";
    }

    public function dump($file) {
        $this->ln("SET FOREIGN_KEY_CHECKS=0;\n");

        $tables = $this->db->query('SHOW TABLES')->fetchAll(PDO::FETCH_BOTH);

        foreach ($tables as $table) {
            $table = $table[0];
            $this->ln('DROP TABLE IF EXISTS `'.$table.'`;');

            $schemas = $this->db->query("SHOW CREATE TABLE `{$table}`")->fetchAll(PDO::FETCH_ASSOC);

            foreach ($schemas as $schema) {
                $schema = $schema['Create Table'];
                if($this->removeAI) $schema = preg_replace('/AUTO_INCREMENT=([0-9]+)(\s{0,1})/', '', $schema);
                $this->ln($schema.";\n\n");
            }
        }

        file_put_contents($file, $this->sql);
    }
}

Answer 4

// aqui esta o script para tal função funcionando perfeitamente e testado
// exaustivamente em meu servidor; não inventei nada somente fiz a junção de
// uma parte de código que peguei aqui e uma outra parte de um outro lugar e // efetuei pequenas correções; Não necessita de mais nada além do código
// na tarefa CRON inclua php -q /home/usuariodosite/pasta aonde esta o script

<?php
// Backup do banco de dados do site

$dbhost = 'localhost';          //local aonde se encontra o banco de dados
$dbuser = '---';    // usuário do banco de dados
$dbpass = '---';         // senha do usuário do banco de dados
$dbname = '---';   // nome do banco de dados

// rotina que faz o backup não mexer

$backupfile = 'Autobackup_' . date("Ymd") . '.sql';
$backupzip = $backupfile . '.tar.gz';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass --lock-tables $dbname > $backupfile");
system("tar -czvf $backupzip $backupfile");

// ROTINA DE ENVIO DO EMAIL COM O ANEXO 

$to = "---";         //Quem vai receber o email
$from = "---";  //Quem está enviando (Endereço a ser apresentado como da pessoa que está enviando)
$subject = 'Backup do Banco de Dados Sql ';          //Assunto do email
$messagem = 'cópia do backup do banco de dados Sql'; //Mensagem a ser enviada
$path = "---";                  //Diretório onde o arquivo a ser enviado está salvo
$filename = "$backupzip";                        //Nome do arquivo anexo a ser enviado - não mexer aqui

// ---------- Não altere nada deste ponto em diante ----------

$headers = 'From: ' .     "$from\r\n" . 'Reply-To: ' . "$from\r\n";

$file = $path . "/" . $filename;
$file_size = filesize($file);
$handle = fopen($file, "r");
$content = fread($handle, $file_size);
fclose($handle);
$content = chunk_split(base64_encode($content));

$separator = md5(time());   // a random hash será necessário para separar conteúdos diversos a serem enviados
$eol = PHP_EOL;   // Define o retorno de carro a ser utilizado

// main header (multipart mandatory)
$headers = "From: < $from >" . $eol;
$headers .= "MIME-Version: 1.0" . $eol;
$headers .= "Content-Type: multipart/mixed; boundary=\"" . $separator . "\"" . $eol . $eol;
$headers .= "Content-Transfer-Encoding: 7bit" . $eol;
$headers .= "This is a MIME encoded message." . $eol . $eol;

// messagem
$headers .= "--" . $separator . $eol;
$headers .= "Content-Type: text/plain; charset=\"utf-8\"" . $eol;
$headers .= "Content-Transfer-Encoding: 8bit" . $eol . $eol;
$headers .= $messagem . $eol . $eol;

// attachment
$headers .= "--" . $separator . $eol;
$headers .= "Content-Type: application/octet-stream; name=\"" . $filename . "\"" . $eol;
$headers .= "Content-Transfer-Encoding: base64" . $eol;
$headers .= "Content-Disposition: attachment" . $eol . $eol;
$headers .= $content . $eol . $eol;
$headers .= "--" . $separator . "--";

//SEND Mail

if (mail($to, $subject, "", $headers)) {
   echo "Sucesso no envio do Email";
} else {
   echo "Erro! Não foi possível enviar o email solicitado";
}

// Remover o arquivo do servidor (opcional)
unlink($backupzip);
unlink($backupfile);
?>

Answer 5

mysqldump

It’s not that simple. It’s better to use the mysqldump from PHP with shell_exec or system, or simply schedule a cronjob, as suggested.

I particularly use cronjob from a remote system, which calls the mysqldump, generating my daily backup of my customers' small application database out there.

---

$this->dbutil->backup()

On one occasion, the server of the annoying provider that hosted the project application I was working on did not allow remote access to Mysql at all. I didn’t have SSH access. O mysqldump wasn’t an option. I didn’t have one phpMyAdmin of life to generate me a dump.

So that the previous developers had even made their PHP script that made the darlings, since SHOW TABLES up to everything else you can imagine, and mounted the respective SQL file, containing the dump.

The problem was that it didn’t work. But nobody knew it. Apparently it was going well. But, right strings were truncated. I only "figured it out" because using my local version of the application, I suddenly noticed a slightly different content from the remote application. I was intrigued, and investigating got to the fact that the script didn’t work well enough to generate a dump faithful.

The solution I found was the method backup of Database Utility Class of Code Igniter. I finally got my dump 100% bulletproof, from PHP.

The implementation of this function that generates the dump is a delicate task. I recommend you take a look at function code backup in the Code Igniter repository (have to take a look here too). The people I worked on tried to reinvent this wheel and failed.

Therefore, if need get a dump of a Mysql database with PHP, I suggest you use this function, or make your version from this, which has proven to work.

Code example:

// Carrega a classe de utilidades de banco de dados
$this->load->dbutil();

// Coloca o dump de todo o seu banco de dados numa variável
$backup =& $this->dbutil->backup(); 

// Gravando o dump num arquivo
$this->load->helper('file');
write_file('/path/to/mybackup.gz', $backup); 

// Enviando o arquivo para o navegador
$this->load->helper('download');
force_download('mybackup.gz', $backup);

Settings for the dump can be made through a array options, to be used as the first parameter for the backup function. For example:

$prefs = array(
    'tables'      => array('table1', 'table2'),  // Tabelas para incluir no dump
    'ignore'      => array(),           // Tabelas para não incluir no dump
    'format'      => 'txt',             // gzip, zip, txt
    'filename'    => 'mybackup.sql',    // Nome do arquivo - APENAS PARA FORMATO ZIP
    'add_drop'    => TRUE,              // Incluir comandos DROP TABLE no dump
    'add_insert'  => TRUE,              // Incluir os dados das tabelas em comandos INSERT no dump
    'newline'     => "\n"               // Caracter para final de linha a ser usado no dump
);

$this->dbutil->backup($prefs);

Answer 6

I don’t know if this is what you need, but follow a complete code to make bkp of your database, only save the code in a php file, create a folder called "db_bkp" and do not forget to fill in the database access data

<?php
ini_set('display_errors',1); ini_set('display_startup_erros',1); error_reporting(E_ALL);//force php to show any error message

backup_tables(DB_HOST,DB_USER,DB_PASS,DB_NAME);//don't forget to fill with your own database access informations

function backup_tables($host,$user,$pass,$name)
{
    $link = mysqli_connect($host,$user,$pass);
    mysqli_select_db($link, $name);
        $tables = array();
        $result = mysqli_query($link, 'SHOW TABLES');
        $i=0;
        while($row = mysqli_fetch_row($result))
        {
            $tables[$i] = $row[0];
            $i++;
        }
    $return = "";
    foreach($tables as $table)
    {
        $result = mysqli_query($link, 'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);
        $return .= 'DROP TABLE IF EXISTS '.$table.';';
        $row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        for ($i = 0; $i < $num_fields; $i++)
        {
            while($row = mysqli_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j < $num_fields; $j++)
                {
                    $row[$j] = addslashes($row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j < ($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }
    //save file
    $handle = fopen('db_bkp/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');//Don' forget to create a folder to be saved, "db_bkp" in this case
    fwrite($handle, $return);
    fclose($handle);
    echo "bkp efetuado com sucesso";//Sucessfuly message
}
?>

Answer 7

Here is a complete and updated tutorial to resolve your need:

https://startinn.com.br/blog/post?p=93_realizando-backup-automatico-do-banco-em-servidor-de-hospedagem-linux-com-php

With it it is possible to create a copy of the Mysql database automatically using a PHP file and a CRON task. The file is compressed with ZIP.