restaurar - ¿Usando mysqldump para formatear una inserción por línea?

respaldar todas las bases de datos mysql

Question

Esto se ha preguntado algunas veces, pero no puedo encontrar una solución a mi problema. Básicamente, cuando uso mysqldump, que es la herramienta integrada para la herramienta de administración MySQL Workbench, cuando vuelco una base de datos usando inserciones extendidas, obtengo enormes líneas de datos. Entiendo por qué hace esto, ya que acelera las inserciones insertando los datos como un solo comando (especialmente en InnoDB), pero el formato hace REALMENTE difícil ver los datos en un archivo volcado, o comparar dos archivos con una herramienta diff si los está almacenando en control de versiones, etc. En mi caso, los estoy almacenando en control de versiones, ya que utilizamos los archivos de volcado para hacer un seguimiento de nuestra base de datos de prueba de integración.

Ahora sé que puedo desactivar insertos extendidos, así que obtendré una inserción por línea, que funciona, pero cada vez que realice una restauración con el archivo de volcado será más lento.

Mi problema principal es que en la herramienta VIEJA que solíamos usar (Administrador de MySQL) cuando vuelco un archivo, básicamente hace lo mismo, pero FORMATOS que INSERT secuencia para poner una inserción por línea, mientras todavía hace inserciones masivas. Entonces en vez de esto:

INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES (887,''0.0000''),191607,''1.0300'');

obtienes esto:

INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES 
 (887,''0.0000''),
 (191607,''1.0300'');

No importa qué opciones intente, no parece haber ninguna forma de poder obtener un botadero como este, que es realmente lo mejor de ambos mundos. Sí, toma un poco más de espacio, pero en situaciones en las que necesita un humano para leer los archivos, lo hace MUCHO más útil.

¿Me estoy perdiendo algo y hay una forma de hacerlo con MySQLDump, o todos hemos retrocedido y esta característica en la antigua (ahora obsoleta) herramienta de administrador de MySQL ya no está disponible?

Answer 1

¿Qué hay de almacenar el volcado en un archivo CSV con mysqldump, usando la opción --tab como esta?

mysqldump --tab=/path/to/serverlocaldir --single-transaction <database> table_a

Esto produce dos archivos:

• table_a.sql que contiene solo la declaración de creación de tabla; y
• table_a.txt que contiene datos separados por tabuladores.

RESTAURANDO

Puede restaurar su tabla a través de LOAD DATA :

LOAD DATA INFILE ''/path/to/serverlocaldir/table_a.txt'' 
  INTO TABLE table_a FIELDS TERMINATED BY ''/t'' ...

LOAD DATA es usualmente 20 veces más rápido que usar las instrucciones INSERT.

Si tiene que restaurar sus datos en otra tabla (por ejemplo, para fines de revisión o prueba), puede crear una tabla "espejo":

CREATE TABLE table_for_test LIKE table_a;

A continuación, cargue el CSV en la nueva tabla:

LOAD DATA INFILE ''/path/to/serverlocaldir/table_a.txt'' 
  INTO TABLE table_for_test FIELDS TERMINATED BY ''/t'' ...

COMPARAR

Un archivo CSV es más simple para diffs o para mirar adentro, o para usuarios técnicos no SQL que pueden usar herramientas comunes como Excel , Access o línea de comando ( diff , comm , etc.)

Answer 2

Como otros han dicho usando sed para reemplazar "), (" no es seguro ya que puede aparecer como contenido en la base de datos. Sin embargo, hay una manera de hacerlo: si el nombre de su base de datos es my_database, ejecute lo siguiente:

$ mysqldump -u my_db_user -p -h 127.0.0.1 --skip-extended-insert my_database > my_database.sql
$ sed '':a;N;$!ba;s/)/;/nINSERT INTO `[A-Za-z0-9$_]*` VALUES /),/n/g'' my_database.sql > my_database2.sql

también puede usar "sed -i" para reemplazar en línea.

Esto es lo que hace este código:

1. --skip-extended-insert creará un INSERT INTO para cada fila que tenga.
2. Ahora usamos sed para limpiar los datos. Tenga en cuenta que la búsqueda regular / reemplazar con sed se aplica a una sola línea, por lo que no podemos detectar el carácter "/ n" ya que sed trabaja una línea a la vez. Es por eso que ponemos ": a; N; $! Ba;" que básicamente le dice a sed que busque multilíneas y que almacene en búfer la siguiente línea.

Espero que esto ayude

Answer 3

Con el formato predeterminado mysqldump, cada registro descargado generará un comando INSERT individual en el archivo de volcado (es decir, el archivo sql), cada uno en su propia línea. Esto es perfecto para el control de fuente (por ejemplo, svn, git, etc.) ya que hace que la resolución de diferencia y delta sea mucho más fina, y en última instancia resulta en un proceso de control de fuente más eficiente. Sin embargo, para tablas de tamaño significativo, la ejecución de todas esas consultas INSERT puede potencialmente hacer que la restauración desde el archivo sql sea prohibitivamente lenta.

El uso de la opción --extended-insert corrige el problema de múltiples INSERT envolviendo todos los registros en un solo comando INSERT en una sola línea en el archivo sql volcado. Sin embargo, el proceso de control de origen se vuelve muy ineficiente. Todo el contenido de la tabla se representa en una sola línea en el archivo sql, y si un solo carácter cambia en cualquier lugar de esa tabla, el control de origen marcará toda la línea (es decir, toda la tabla) como el delta entre las versiones. Y, para las tablas grandes, esto niega muchos de los beneficios de usar un sistema de control de fuente formal.

Entonces, idealmente, para una restauración eficiente de la base de datos, en el archivo sql, queremos que cada tabla esté representada por un único INSERT. Para un proceso de control de fuente eficiente, en el archivo sql, queremos que cada registro en ese comando INSERT resida en su propia línea.

Mi solución a esto es el siguiente script de respaldo:

#!/bin/bash

cd my_git_directory/

ARGS="--host=myhostname --user=myusername --password=mypassword --opt --skip-dump-date"
/usr/bin/mysqldump $ARGS --database mydatabase | sed ''s$VALUES ($VALUES/n($g'' | sed ''s$),($),/n($g'' > mydatabase.sql

git fetch origin master
git merge origin/master
git add mydatabase.sql
git commit -m "Daily backup."
git push origin master

El resultado es un archivo sql con formato de comando INSERT que se ve así:

INSERT INTO `mytable` VALUES
(r1c1value, r1c2value, r1c3value),
(r2c1value, r2c2value, r2c3value),
(r3c1value, r3c2value, r3c3value);

Algunas notas:

• contraseña en la línea de comando ... Sé, no es seguro, diferente discusión.
• --opt: entre otras cosas, activa la opción --extended-insert (es decir, una INSERT por tabla).
• --skip-dump-date: mysqldump normalmente coloca un sello de fecha / hora en el archivo sql cuando se crea. Esto puede volverse molesto en el control de fuente cuando el único delta entre versiones es ese sello de fecha / hora. El sistema operativo y el sistema de control de fuente marcarán la fecha / hora del archivo y la versión. No es realmente necesario en el archivo sql.
• Los comandos de git no son fundamentales para la pregunta fundamental (formatear el archivo sql), pero muestra cómo devuelvo mi archivo sql al control de fuente, algo similar se puede hacer con svn. Al combinar este formato de archivo sql con su control de origen de elección, encontrará que cuando los usuarios actualizan sus copias de trabajo, solo necesitan mover los deltas (es decir, registros modificados) a través de Internet, y pueden aprovechar las utilidades de diff. para ver fácilmente qué registros en la base de datos han cambiado.
• Si está volcando una base de datos que reside en un servidor remoto, si es posible, ejecute esta secuencia de comandos en ese servidor para evitar empujar todo el contenido de la base de datos a través de la red con cada vuelco.
• Si es posible, establezca un repositorio de control de origen que funcione para sus archivos sql en el mismo servidor desde el que está ejecutando este script; verifícalos en el repositorio desde allí. Esto también ayudará a evitar tener que empujar toda la base de datos a través de la red con cada vuelco.

Answer 4

Encontré esta herramienta muy útil para tratar insertos extendidos: http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html

Analiza la salida de mysqldump e inserta saltos de línea después de cada registro, pero sigue utilizando las inserciones extendidas más rápidas. A diferencia de un script sed, no debería haber ningún riesgo de romper líneas en el lugar equivocado si la expresión regular coincide dentro de una cadena.

Answer 5

Intente usar la siguiente opción: --skip-extended-insert

Funcionó para mí

Answer 6

Me gustó la solución de Ace.Di con sed, hasta que obtuve este error: sed: no se pudo reasignar la memoria

Por lo tanto, tuve que escribir un pequeño script PHP

mysqldump -u my_db_user -p -h 127.0.0.1 --skip-extended-insert my_database | php mysqlconcatinserts.php > db.sql

El script PHP también genera un nuevo INSERT para cada 10.000 filas, nuevamente para evitar problemas de memoria.

mysqlconcatinserts.php:

#!/usr/bin/php
<?php
/* assuming a mysqldump using --skip-extended-insert */
$last = '''';
$count = 0;
$maxinserts = 10000;
while($l = fgets(STDIN)){
  if ( preg_match(''/^(INSERT INTO .* VALUES) (.*);/'',$l,$s) )
  {
    if ( $last != $s[1] || $count > $maxinserts )
    {
      if ( $count > $maxinserts ) // Limit the inserts
        echo ";/n";
      echo "$s[1] ";
      $comma = ''''; 
      $last = $s[1];
      $count = 0;
    }
    echo "$comma$s[2]";
    $comma = ",/n";
  } elseif ( $last != '''' ) {
    $last = '''';
    echo ";/n";
  }
  $count++;
} 

Answer 7

Me temo que esto no será posible. En el antiguo administrador de MySQL, escribí el código para eliminar objetos db, que era completamente independiente de la herramienta mysqldump y, por lo tanto, ofrecía una serie de opciones adicionales (como este formato o retroalimentación de progreso). En MySQL Workbench se decidió utilizar la herramienta mysqldump que, además de ser un paso atrás en algunos aspectos y producir problemas de versión, tiene la ventaja de mantenerse siempre actualizado con el servidor.

Entonces la respuesta corta es: el formato no es posible actualmente con mysqldump.