How to do a dump of a MySQL database with PHP?

I ended up getting a solution in PHP itself, based on the script of the site posted by @Gabriel Santos. Following:

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);
    }
}

Use the system function of PHP:

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

See more information: link

And then just create a cronjob so the script runs once a day, or as you wish.

Here is a way to receive a copy of the database via 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 do not remember who originally created the script above. I just remember changing the code a little bit.

Use crontab to schedule backup jobs, as well as compress banks with gzip , which makes backups much smaller.

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

For example, in my case I use crontab to schedule a daily backup of my bank:

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

And within 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 to be the best practice for a routine of periodic backups of databases. Try to use this template to find what works best for your purposes.

mysqldump

It's not that simple. It is best to use 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 mysqldump , generating my daily backups there.

$ this-> dbutil-> backup ()

On one occasion, the boring provider server hosting the project application I was working on did not allow remote access to MySQL at all. I did not have SSH access. mysqldump was not an option. I did not have a phpMyAdmin of life to generate a dump .

So the previous developers had already done their PHP script, which made queries , from SHOW TABLES to everything else you could imagine, and mounted the corresponding SQL file containing the dump .

The problem is that it did not work. But nobody knew that. Apparently everything was going well. However, certain strings were truncated. I just "discovered" this because using my local version of the application, I suddenly noticed a slightly different content from the remote application. I was intrigued, and investigating came to the fact that the script did not work well enough to generate a faithful dump .

The solution I found was the % method of the Database Utility Class