GNU/Linux, Open Source, Cloud Computing, DevOps y más...

Backup de bases de datos MySQL con PHP

32 comentarios

Este artículo muestra cómo realizar un backup parcial o completo de bases de datos MySQL utilizando tan sólo código PHP. Esto es muy útil por ejemplo cuando un cliente no nos facilita datos de acceso a la base de datos de una aplicación web y sólo disponemos de un acceso FTP, o cuando sí disponemos de los datos de la base de datos pero no podemos acceder a ésta a través de la red (sólo disponible en localhost) y tampoco podemos utilizar herramientas como mysqldump para respaldar la información porque no disponemos de privilegios para ello, o simplemente porque no tenemos acceso a una shell a la que conectarnos y ejecutar comandos.

diagrama-backup-mysql-php

En cualquiera de estos escenarios, simplemente copiando y ejecutando el script PHP Backup_Database.php que se muestra a continuación podríamos hacer un export completo o parcial (sólo algunas tablas) de la base de datos a un fichero que podremos descargar después desde la misma cuenta FTP. Normalmente las aplicaciones PHP disponen de un fichero de configuración tipo config.php o similar en el que podremos encontrar el usuario y contraseña de la base de datos si por ejemplo el cliente los ha olvidado, y normalmente también disponen de algún directorio con permisos suficientes para crear nuevos archivos (cache, tmp, temp, etc.), que es donde diremos a nuestro script Backup_Database.php que deje el fichero con el backup de la base de datos. El fichero .sql resultante de la ejecución del script Backup_Database.php tendrá un nombre del tipo db-backup-database-date-time.sql:

Estructura del nombre del fichero generado por Backup_Database.php:

~$ ls cache/*.sql
cache/db-backup-smf-2012-01-31-11-17-35.sql

Este fichero contendrá el código SQL necesario para reproducir la estructura y el contenido de la base de datos original en otra base de datos distinta mediante el comando mysql:

~$ mysql -p -u admin smf < db-backup-smf-2012-01-31-11-17-35.sql

Comando para restaurar la base de datos exportada.

 

Código fuente de Backup_Database.php

<!--?php 
/**
 * This file contains the Backup_Database class wich performs
 * a partial or complete backup of any given MySQL database
 * @author Daniel López Azaña <-->
 * @version 1.0
 */

// Report all errors
error_reporting(E_ALL);

/**
 * Define database parameters here
 */
define("DB_USER", 'admin');
define("DB_PASSWORD", 'test_passwd');
define("DB_NAME", 'database_name');
define("DB_HOST", 'localhost');
define("OUTPUT_DIR", 'cache');
define("TABLES", '*');

/**
 * Instantiate Backup_Database and perform backup
 */
$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
echo "


Backup result: ".$status;

/**
 * The Backup_Database class
 */
class Backup_Database {
    /**
     * Host where database is located
     */
    var $host = '';

    /**
     * Username used to connect to database
     */
    var $username = '';

    /**
     * Password used to connect to database
     */
    var $passwd = '';

    /**
     * Database to backup
     */
    var $dbName = '';

    /**
     * Database charset
     */
    var $charset = '';

    /**
     * Constructor initializes database
     */
    function Backup_Database($host, $username, $passwd, $dbName, $charset = 'utf8')
    {
        $this->host     = $host;
        $this->username = $username;
        $this->passwd   = $passwd;
        $this->dbName   = $dbName;
        $this->charset  = $charset;

        $this->initializeDatabase();
    }

    protected function initializeDatabase()
    {
        $conn = mysql_connect($this->host, $this->username, $this->passwd);
        mysql_select_db($this->dbName, $conn);
        if (! mysql_set_charset ($this->charset, $conn))
        {
            mysql_query('SET NAMES '.$this->charset);
        }
    }

    /**
     * Backup the whole database or just some tables
     * Use '*' for whole database or 'table1 table2 table3...'
     * @param string $tables
     */
    public function backupTables($tables = '*', $outputDir = '.')
    {
        try
        {
            /**
            * Tables to export
            */
            if($tables == '*')
            {
                $tables = array();
                $result = mysql_query('SHOW TABLES');
                while($row = mysql_fetch_row($result))
                {
                    $tables[] = $row[0];
                }
            }
            else
            {
                $tables = is_array($tables) ? $tables : explode(',',$tables);
            }

            $sql = 'CREATE DATABASE IF NOT EXISTS '.$this->dbName.";\n\n";
            $sql .= 'USE '.$this->dbName.";\n\n";

            /**
            * Iterate tables
            */
            foreach($tables as $table)
            {
                echo "Backing up ".$table." table...";

                $result = mysql_query('SELECT * FROM '.$table);
                $numFields = mysql_num_fields($result);

                $sql .= 'DROP TABLE IF EXISTS '.$table.';';
                $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
                $sql.= "\n\n".$row2[1].";\n\n";

                for ($i = 0; $i < $numFields; $i++) 
                {
                    while($row = mysql_fetch_row($result))
                    {
                        $sql .= 'INSERT INTO '.$table.' VALUES(';
                        for($j=0; $j<$numFields; $j++) 
                        {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                            if (isset($row[$j]))
                            {
                                $sql .= '"'.$row[$j].'"' ;
                            }
                            else
                            {
                                $sql.= '""';
                            }

                            if ($j < ($numFields-1))
                            {
                                $sql .= ',';
                            }
                        }

                        $sql.= ");\n";
                    }
                }

                $sql.="\n\n\n";

                echo " OK" . "
";
            }
        }
        catch (Exception $e)
        {
            var_dump($e->getMessage());
            return false;
        }

        return $this->saveFile($sql, $outputDir);
    }

    /**
     * Save SQL to file
     * @param string $sql
     */
    protected function saveFile(&$sql, $outputDir = '.')
    {
        if (!$sql) return false;

        try
        {
            $handle = fopen($outputDir.'/db-backup-'.$this->dbName.'-'.date("Ymd-His", time()).'.sql','w+');
            fwrite($handle, $sql);
            fclose($handle);
        }
        catch (Exception $e)
        {
            var_dump($e->getMessage());
            return false;
        }

        return true;
    }
}
 

Sobre el autor

Daniel López Azaña
Arquitecto de soluciones Cloud

Emprendedor, generador de ideas y mente inquieta. Apasionado de las nuevas tecnologías, especialmente de los sistemas Linux y del software libre. Me gusta escribir además sobre actualidad tecnológica, Cloud Computing, DevOps, seguridad, desarrollo web y programación, SEO, ciencia, innovación, emprendimiento, etc.

DanielBackup de bases de datos MySQL con PHP

Artículos relacionados

32 comentarios

Unirte a la conversación
  • Carlos Andrés Restrepo - 28/08/2012 responder

    Hola me funciono perfecto en un crontab solo un detalle retire de la linea $handle = fopen($outputDir.’/db-backup-‘.$this->dbName.’-‘.date(“Ymd-His”, time()).’.sql’,’w+’);
    la variable $outputDir dado que agrega un directorio de nombre cache y se pierde la ruta
    pero funciona de lujo + 10
    ya solo falta meterle los detallitos que uno considere que hagan falta para cada necesidad especifica

    saludos

  • JOSE PASTOR - 07/09/2012 responder

    El script para hacer copia de la base de Datos me parece magnifico, por favor me pueden colaborar con el script para restaurar la base de Datos a partir del archivo creado con el script Backup_Databese.php

    Les quedo altamente agradecido

    Daniel - 03/10/2012 responder

    Muchas gracias José. Lamentablemente en este momento no dispongo de tiempo para publicar el script de restauración. No obstante, tengo intención de hacerlo. Lo subiré tan pronto como pueda. Un saludo.

    Pierre - 25/11/2012 responder

    Por favor, quien me puede a ayudar a restaurar mi base de datos ?
    Gracias de ante mano !

  • Pierre - 25/11/2012 responder

    Hola, por favor quien puede decir porque tengo este error al intentar respaldar mi base de datos

    Deprecated: Function ereg_replace() is deprecated in C:xampphtdocsictvttrespaldarbackup.php on line 133

    Daniel - 27/11/2012 responder

    Gracias por tu comentario Pierre, pero en realidad no se trata de un mensaje de error. Menciono la causa de ese mensaje en este mismo hilo, unos cuantos comentarios más arriba.

    leonardo - 05/09/2014 responder

    ayuda por favor se realizó bien el respaldo pero no se como encontrarlo mas o menos entiendo q esta en la cache …ayuda

    Daniel - 05/09/2014 responder

    El destino del respaldo se indica en la constante OUTPUT_DIR definida al principio del script. En mi caso era en el directorio cache, pero este directorio no tiene por qué existir en todas las instalaciones donde hagais funcionar el script. Por tanto, debes poner un directorio que sea válido en tu escenario concreto.

  • Martin - 05/02/2013 responder

    Hola Daniel, que pasa con valores NULL?, los escribe como “”?

    Daniel - 02/04/2013 responder

    Hola Martin. Pues sinceramente no lo sé, no lo he probado con valores nulos… 🙂

    Este script lo escribí para un trabajo puntual con un par de bases de datos específicas de un cliente y no se dio el caso que comentas. Es seguro que mi script es bastante mejorable aún. De hecho gracias a comentarios como el tuyo podré ir afinándolo más para que sea de utilidad a más personas. El problema es que no encuentro el tiempo necesario para hacerlo… 🙁

    Si pudieras publicar el cambio que sería necesario hacer para corregir el problema te estaría muy agradecido, al igual que todos los lectores de tu comentario 😉

    ¡Gracias!

  • Fernando - 22/03/2013 responder

    Hola, agradecido por tu script. buen trabajo.
    Te comento algunas cosas para quien llegue como yo a éste blog, pueda estar corregido.
    – El nombre del constructor de la clase, debe llamarse __construct()
    – El aviso que da de ereg_replace, se puede sustituir por str_replace.

    Y creo que lo más importante, es que tu código exporta los datos a un fichero sql pero no tiene en cuenta las relaciones, por lo que para exportar está muy bien, pero para importarlo no es válido.

    Por lo demás, buen trabajo. Perfecto.

    Saludos y gracias por compartir tus conocimientos!

    Daniel - 02/04/2013 responder

    Gracias Fernando por tus aportaciones. Efectivamente, hay algunas cosas que se han quedado anticuadas en mi script, como el uso de ereg_replace y el nombre del constructor, que debe llamarse __construct() en PHP5, aunque por compatibilidad con versiones anteriores se siga aceptando el nombre de la propia clase.

    Tengo pendiente hacer varias mejoras e implementar una nueva clase para realizar importaciones y para aceptar tablas InnoDB con sus relaciones y demás, pero lamentablemente no he encontrado el momento por falta de tiempo.

    Tendré en cuenta tu comentario para esa futura versión. Gracias.

  • Omar Hernandez - 09/04/2013 responder

    Excelente Script, funciona a la perfeccion.

  • felix - 09/07/2013 responder

    hola esta muy bien tu código, pero no me funciona cuando tengo tablas relacionadas….

  • alex - 18/07/2013 responder

    por alguna razon no me carga nada con un base de datos en especifico, sabes que pudo haber pasado?

    que tenga algun tipo de proteccion o algo parecido?

  • Berdejo Humberto - 14/08/2013 responder

    Al restaurar el archivo sql los acentos y caracteres Ñ o ñ desaparecen y truncan la cadena en MySQL ¿Sabes a que se debe?

  • Bruce - 03/10/2013 responder

    Funciona perfectamente,its a perfect work thank you so much

  • Roberto Carlos Garcia Andino - 02/11/2013 responder

    Gracias, me sirvió tu código, tuve que agregarle una línea encima de tu línea 108 y concatenar la siguente:
    $sql = “nn”;
    $sql .= ‘CREATE DATABASE IF NOT EXISTS ‘.$this->dbName.”;nn”;
    pues a la hora de leer los datos con la función file() me pone dos caracteres al inicio y es necesario separarlos de la primera línea sql. Espero este código le sirva a alguien más. Saludos.

    $url = “db-backup-nominaplus-20131102-083116.sql”;
    $nowhost = “localhost”;
    $nowdatabase = “nominaplus1”;
    $nowuser = “root”;
    $nowpass = “”;

    $link = mysql_connect($nowhost, $nowuser, $nowpass);

    $lines = file($url);
    foreach ($lines as $line_num => $line) {
    echo “Line #{$line_num} : ” . $line . “n”;
    mysql_query($line);
    }

  • Rama - 03/09/2014 responder

    Hola, en algunos casos me informa que todas las tablas las pasa OK pero luego crea el archivo con longitud cero … y no informa ningun error

  • zarith - 23/09/2014 responder

    Muchas gracias amigo, me funciono muy bien. Solo que gustaria que el archivo se descargara al escritorio o a la carpeta de descargas del computador para que sea mas fácil para el cliente ubicarlo. Espero pueda ayudarme con eso. Gracias de antemano.

    Daniel - 23/09/2014 responder

    Lo siento, pero no es así como funciona. El script se ejecuta en el servidor y deja el resultado en el servidor para ser recogido mediante FTP. No obstante, gracias por la sugerencia. Lo tendré en cuenta como una mejora en la revisión del código que tengo pendiente.

  • pandorasoft - 09/10/2014 responder

    Hola, todos los resultados aparecen ok, pero el archivo que crea es ta a 0. ¿que puede ser?

    Daniel - 09/10/2014 responder

    Pues no te sabría decir, habría que meter un poco de código de depuración en el script e ir viendo si el valor de la variable $sql se queda vacío y por qué. Sin algo más de información no te puedo decir qué ocurre.

  • Antonio Munguia - 20/02/2015 responder

    Hola Daniel buen script muy útil, una pregunta, en: define(“TABLES”, ‘*’); el asterisco * significa que respaldara todas las tablas de la base de datos que tu elijas, o se tienen que poner el nombre de las tablas de la bd, muchas gracias por tu tiempo, y sigue así….

  • Mariana - 11/08/2015 responder

    Muy buen script! Yo necesité hacer unos cambios, para conseguir un formato como el de phpmyadmin. No sé si es lo más correcto, pero a mí me funcionó así:

    $row[$j] = str_replace( array(“r”, “n”) , array(‘\r’, ‘\n’) , $row[$j] );

    Por empezar, cambié ereg_replace por str_replace, (que daba error en las versiones nuevas de PHP). Y pasé un array con las distintas formas en que puede venir guardado el salto de línea.

    Usé comillas dobles en el primer argumento y simples en el segundo, porque necesito que el primero interprete los saltos de línea y el segundo los escriba como string. Como estaba en tu script me interpretaba los saltos de línea en el documento, en lugar de mostrarlos como string.

  • Mariana - 11/08/2015 responder

    Me olvidé, en el comentario anterior, de agregar las tabulaciones, quedaría así:

    $row[$j] = str_replace( array(“r”, “n”, “t”) , array(‘\r’, ‘\n’, ‘\t’) , $row[$j] );

  • Diego Soto - 28/01/2016 responder

    El presente es una actualización del excelente artículo de Daniel Lopez Azaña quien se merece todos los créditos por el trabajo.

    Lo que yo hice fue recopilar e incorporar todas las sugerencias propuestas por los que comentaron en el blog original y agregué de mi parte la especificación de la zona horaria para que el archivo de backup tenga el nombre acorde a la hora real de la zona.

    Muchas gracias.

    http://disientoconusted.blogspot.com.ar/2016/01/backup-de-bases-de-datos-mysql-con-php.html

    This is an update of the excellent article by Daniel Lopez Azana, who deserves all the credit for the work.

    What I did was collect and incorporate all suggestions made by those who commented on the original blog, and added specifying the time zone in order the backup file has the name according to the real time in your zone.

    Thank you very much.

  • mario - 07/04/2016 responder

    como hago para hacer un respaldo de todas mis bases de datos

    Daniel - 05/05/2016 responder

    Me temo que con el script actual sólo puedes hacerlo una a una cambiando los datos de conexión que aparecen al principio.

  • Jorge - 12/04/2016 responder

    Excelente, no pudo hacer respaldo de todas mis tablas, se terminaba la memoria, no sé si de php o de mysql, pero hice un loop y lo tengo en un conjob, me ahorraste mucho tiempo. Gracias.

  • Jorge Alejandro - 16/12/2016 responder

    Muchas Gracias por este aporte Ing. Luego se ve la gente que se apasiona de verdad por la programación. Unos libros que me recomiendes de php y mysql

    Daniel - 17/12/2016 responder

    Gracias Jorge Alejandro. Echa un vistazo a este enlace para lo de los libros: https://www.cloudways.com/blog/best-books-to-help-you-learn-php/

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *