php - restaurar - Copia de seguridad automatizada o regular de datos mysql

respaldar base de datos mysql phpmyadmin automaticamente

Question

Quiero hacer copias de seguridad periódicas de algunas tablas en mi base de datos mysql usando <insertar marco PHP favorito aquí> / plain php / mi segundo idioma favorito. Quiero que se automatice para que la copia de seguridad se pueda restaurar más adelante en caso de que algo salga mal.

Intenté ejecutar una consulta y guardar los resultados en un archivo. Terminé con un código que se parece a esto.

$sql = ''SELECT * FROM my_table ORDER id DESC'';
$result = mysqli_query( $connect, $sql );  
if( mysqli_num_rows( $result ) > 0){

    $output=fopen(''/tmp/dumpfile.csv'',''w+'');

    /* loop through recordset and add that to the file */
    while( $row = mysqli_fetch_array( $result ) ) {
        fputcsv( $output, $row, '','', ''"'');
    }

    fclose( $output );
}

Configuré un trabajo cron en mi máquina local para acceder a la página web con este código. También intenté escribir un cronjob en el servidor, ejecutar el script como CLI. Pero está causando todo tipo de problemas. Éstos incluyen

1. Algunas veces los datos no son consistentes
2. El archivo parece estar truncado
3. La salida no se puede importar a otra base de datos
4. A veces el guión se agota

También he escuchado sobre mysqldump . Traté de ejecutarlo con exec pero produce un error.

¿Como puedo resolver esto?

Answer 1

CSV y SELECCIONAR EN OUTFILE

http://dev.mysql.com/doc/refman/5.7/en/select-into.html

SELECT ... INTO OUTFILE escribe las filas seleccionadas en un archivo. Los terminadores de columna y línea se pueden especificar para producir un formato de salida específico.

Aquí hay un ejemplo completo:

SELECT * FROM my_table INTO OUTFILE ''/tmp/my_table.csv''
  FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
  LINES TERMINATED BY ''/n''
  FROM test_table;

El archivo se guarda en el servidor y la ruta elegida debe poder escribirse. Aunque esta consulta se puede ejecutar a través de PHP y una solicitud web, se ejecuta mejor a través de la consola mysql.

Los datos que se exportan de esta manera se pueden importar a otra base de datos usando LOAD DATA INFILE

Si bien este método es superior iterando a través de un conjunto de resultados y guardando en un archivo fila por fila, no es tan bueno como usar ...

mysqldump

mysqldump es superior a SELECT INTO OUTFILE de muchas maneras, producir CSV es solo una de las muchas cosas que este comando puede hacer.

La utilidad del cliente mysqldump realiza copias de seguridad lógicas, produciendo un conjunto de instrucciones SQL que se pueden ejecutar para reproducir las definiciones de objetos de la base de datos original y los datos de la tabla. Vuelca una o más bases de datos MySQL para hacer copias de seguridad o transferirlas a otro servidor SQL. El comando mysqldump también puede generar resultados en CSV, otro texto delimitado o formato XML.

Idealmente, mysqldump debe invocarse desde su shell. Es posible usar exec en php para ejecutarlo, pero dado que producir el volcado puede tomar mucho tiempo dependiendo de la cantidad de datos, y los scripts php generalmente se ejecutan solo durante 30 segundos, deberá ejecutarlo como un proceso en segundo plano.

mysqldump no está exento de problemas.

No pretende ser una solución rápida o escalable para realizar copias de seguridad de cantidades sustanciales de datos. Con tamaños de datos grandes, incluso si el paso de copia de seguridad lleva un tiempo razonable, la restauración de los datos puede ser muy lenta porque la reproducción de las instrucciones SQL implica E / S de disco para inserción, creación de índice, etc.

Un ejemplo clásico ve esta pregunta: El servidor se cuelga en la copia de seguridad de MySQL usando Python donde un mysqldump parece comenzar antes de que el anterior haya terminado y el sitio web no responda por completo.

Replicación Mysql

La replicación permite que los datos de un servidor de base de datos MySQL (el maestro) se copien en uno o más servidores de base de datos MySQL (los esclavos). La replicación es asíncrona por defecto; los esclavos no necesitan estar conectados permanentemente para recibir actualizaciones del maestro. Dependiendo de la configuración, puede replicar todas las bases de datos, bases de datos seleccionadas o incluso tablas seleccionadas dentro de una base de datos.

Por lo tanto, la replication funciona de manera diferente a SELECT INTO OUTFILE o msyqldump Es ideal mantener los datos en la copia local casi actualizados (lo habría dicho perfectamente sincronizado, pero hay algo llamado esclavo de retraso). Por otro lado, si usa una tarea programada para ejecutar mysqldump una vez cada 24 horas ¿Imagina lo que puede pasar si el servidor falla después de 23 horas?

Cada vez que ejecutas mysqldump estás produciendo una gran cantidad de datos, sigue haciéndolo regularmente y encontrarás que tu disco duro está lleno o tus facturas de almacenamiento de archivos están llegando al límite. Con la replicación, solo los cambios se pasan al servidor (mediante el llamado binlog)

XtraBackup

Una alternativa a la replicación es usar Percona XtraBackup .

Percona XtraBackup es una utilidad de respaldo en caliente de código abierto para servidores basados ​​en MySQL que no bloquea su base de datos durante el respaldo.

Aunque por Percona, es compatible con Mysql y Mariadb. Tiene la capacidad de hacer copias de seguridad incrementales, la cual es la mayor limitación de mysqldump.

Answer 2

Sugiero obtener una copia de seguridad de la base de datos mediante la utilidad de línea de comandos utilizando un archivo de script en lugar de un script PHP.

Haga el archivo my.ini para configuraciones de tienda

cree el archivo my.ini para el nombre de usuario y la contraseña predeterminados de db en el directorio raíz del usuario. entonces el script tomará nombre de usuario, contraseña y nombre de host de este archivo

[client]
user = <db_user_name>
password = <db_password>
host = <db_host>

Crear un archivo sh llamado backup.sh

#!/bin/sh
#
# script for get backup everyday

#change directory to your backup directory
cd /path_of_your_directory
#get backup of database of applications
mysqldump <your_database_name> tmp_db.sql;

#compress it in zip file
zip app_database-$(date +%Y-%m-%d).sql.zip tmp_db.sql;

#remove  sql file
rm -rf tmp_db.sql;

Dar permiso ejecutable a sh.file

chmod +x backup.sh

Establecer Cronjob

sh /<script_path>/backup.sh >/dev/null 2>&1

Eso es todo

Buena suerte