Introducción

En una clase que acabo de tomar tuve que investigar cómo respaldar una base de datos de MySQL desde PHP. Lo que terminé logrando es un script que hace un respaldo completo y genera un archivo que se guarda en el disco duro.

Es decir, utilizando únicamente PHP respaldar una base de datos de MySQL. Nada de mysqldump o llamadas al sistema.

Investigando encontré esta respuesta de stackoverflow y me pareció útil modificarla y traducirla para explicarla aquí.

¿Qué hace este script?

Este script toma una base de datos y la respalda. Respalda todos sus datos así como la estructura de las tablas. Es muy parecido a cómo phpmyadmin exporta las bases de datos.

La modifiqué de tal manera que exporte todas las tablas y calcule un nombre de archivo único.

Creará una carpeta llamada respaldos (en caso de que no exista), y dentro pondrá el respaldo. Podemos llamarla múltiples veces, ya que genera un nombre único.

¿Cómo funciona?

Obtiene las tablas que tiene la base de datos. Una vez que las tiene, una por una va consultando su sentencia de creación con SHOW CREATE TABLE nombreDeLaTabla; que da un resultado como el de la imagen:

La parte que censurada no fue tapada porque quise, sino porque da datos que confunden. El punto es que ese comando da la creación de la tabla, y eso es lo que se escribe en el archivo.

Eso lo escribe en el archivo, y ahora selecciona todos los datos con SELECT * FROM elNombreDeLaTabla;. Recorre todas las filas, y concatena en muchos INSERT los datos. Cada 100 iteraciones separa los INSERT para que no sea muy pesado.

Lo demás es cosa de agregar comas o puntos y comas cuando es necesario, así como el cierre de paréntesis.

Al inicio y fin pone el juego de caracteres. Finalmente ese contenido se escribe en un archivo.

Script para respaldar base de datos de MySQL desde PHP

Lecturas recomendadas.

1. Sintaxis corta de arreglo en PHP: lo usamos para declarar las tablas a respaldar
2. CRUD de archivos y carpetas en PHP: para saber cómo funciona file_exists, mkdir y otros
3. Formateando fechas en PHP: para saber cómo devuelve la fecha

<?php
/**
 * Respaldar base de datos de MySQL con PHP
 * Función modificada de: https://stackoverflow.com/a/21284229/5032550
 *
 * Visita: https://parzibyte.me/blog/2018/10/22/script-respaldar-base-de-datos-mysql-php/
 */
// Ejemplo de llamada: exportarTablas("localhost", "root", "123", "foo");

function exportarTablas($host, $usuario, $pasword, $nombreDeBaseDeDatos)
{
    set_time_limit(3000);
    $tablasARespaldar = [];
    $mysqli = new mysqli($host, $usuario, $pasword, $nombreDeBaseDeDatos);
    $mysqli->select_db($nombreDeBaseDeDatos);
    $mysqli->query("SET NAMES 'utf8'");
    $tablas = $mysqli->query('SHOW TABLES');
    while ($fila = $tablas->fetch_row()) {
        $tablasARespaldar[] = $fila[0];
    }
    $contenido = "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--\r\n-- Database: `" . $nombreDeBaseDeDatos . "`\r\n--\r\n\r\n\r\n";
    foreach ($tablasARespaldar as $nombreDeLaTabla) {
        if (empty($nombreDeLaTabla)) {
            continue;
        }
        $datosQueContieneLaTabla = $mysqli->query('SELECT * FROM `' . $nombreDeLaTabla . '`');
        $cantidadDeCampos = $datosQueContieneLaTabla->field_count;
        $cantidadDeFilas = $mysqli->affected_rows;
        $esquemaDeTabla = $mysqli->query('SHOW CREATE TABLE ' . $nombreDeLaTabla);
        $filaDeTabla = $esquemaDeTabla->fetch_row();
        $contenido .= "\n\n" . $filaDeTabla[1] . ";\n\n";
        for ($i = 0, $contador = 0; $i < $cantidadDeCampos; $i++, $contador = 0) {
            while ($fila = $datosQueContieneLaTabla->fetch_row()) {
                //La primera y cada 100 veces
                if ($contador % 100 == 0 || $contador == 0) {
                    $contenido .= "\nINSERT INTO " . $nombreDeLaTabla . " VALUES";
                }
                $contenido .= "\n(";
                for ($j = 0; $j < $cantidadDeCampos; $j++) {
                    $fila[$j] = str_replace("\n", "\\n", addslashes($fila[$j]));
                    if (isset($fila[$j])) {
                        $contenido .= '"' . $fila[$j] . '"';
                    } else {
                        $contenido .= '""';
                    }
                    if ($j < ($cantidadDeCampos - 1)) {
                        $contenido .= ',';
                    }
                }
                $contenido .= ")";
                # Cada 100...
                if ((($contador + 1) % 100 == 0 && $contador != 0) || $contador + 1 == $cantidadDeFilas) {
                    $contenido .= ";";
                } else {
                    $contenido .= ",";
                }
                $contador = $contador + 1;
            }
        }
        $contenido .= "\n\n\n";
    }
    $contenido .= "\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 */;";

    # Se guardará dependiendo del directorio, en una carpeta llamada respaldos
    $carpeta = __DIR__ . "/respaldos";
    if (!file_exists($carpeta)) {
        mkdir($carpeta);
    }

    # Calcular un ID único
    $id = uniqid();

    # También la fecha
    $fecha = date("Y-m-d");

    # Crear un archivo que tendrá un nombre como respaldo_2018-10-22_asd123.sql
    $nombreDelArchivo = sprintf('%s/respaldo_%s_%s.sql', $carpeta, $fecha, $id);

    #Escribir todo el contenido. Si todo va bien, file_put_contents NO devuelve FALSE
    return file_put_contents($nombreDelArchivo, $contenido) !== false;
}

exportarTablas("localhost", "root", "", "mascotas");

Así es como queda la función. Recibe, en orden, el host, usuario, contraseña y el nombre de la base de datos a respaldar.

Ejemplo de respaldo de base de datos de MySQL

Tengo aquí una base de datos de mascotas que ya ni sé qué tiene pero servirá como ejemplo. La usé cuando mostré cómo replicar datos de MySQL.

Desde la CLI de MySQL se ve así:

Eso es únicamente para demostrar los datos que hay. Ahora desde PHP llamaré a la función que vimos anteriormente, así:

<?php
exportarTablas("localhost", "root", "", "mascotas");

Lo sé, mi contraseña es una cadena vacía; es decir, no tengo contraseña, pero no importa si estoy localmente en mi servidor de pruebas.

La cosa realmente importante es que al ejecutar ese código obtengo  un archivo dentro de la carpeta respaldos:

Vemos que el nombre es como lo dijimos anteriormente, y que se ha generado según la fecha en la que fue realizado. De esa forma podemos llevar un seguimiento.

El contenido del mismo es el que se ve a continuación:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `mascotas`
--




CREATE TABLE `mascotas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(255) NOT NULL,
  `edad` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;


INSERT INTO mascotas VALUES
("3","Cuc0","1"),
("4","Capuchina","2"),
("6","¡Desde Linux!","10"),
("7","Desde Linux!","100"),
("8","Desde Linux!","100"),
("9","Desde Linux!","100"),
("10","Desde Linux!","100"),
("11","Desde Linux!","100");




/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Es un respaldo total de mi base de datos, hecho con un simple script que usa mysqli. Así podemos respaldar bases de datos reales, las cuales se quedarán guardadas en el disco duro.