guia - secuencia de comandos para convertir mysql dump archivo sql en formato que se puede importar en sqlite3 db

qgis manual

Question

Tengo un archivo SQL de exportación que contiene tablas y datos de MySQL y deseo importarlo en un DB Sqlite 3.

¿Cuál es la mejor manera de hacer eso?

Solo importar el archivo a través de la herramienta sqlite3 no funciona.

Answer 1

Al menos, con mysql 5.0.x, tuve que eliminar cotejar utf8_unicode_ci del volcado de mysql antes de importarlo a sqlite3. Así que modifiqué el script para incluir lo siguiente en la lista de seds:

sed ''s/ collate utf8_unicode_ci/ /g'' |

Actualizar:

MySQL trata los campos booleanos como "tinyint (1)", así que tuve que agregar lo siguiente antes de tinyint([0-9]*) sed:

sed ''s/ tinyint(1) / boolean /g'' |

Además, dado que estoy tratando de replicar un db (producción) mysql a un sqlite3 db (desarrollo) de una aplicación Ruby On Rails, tuve que agregar la siguiente línea para establecer una clave primaria de incremento automático:

sed ''s/) NOT NULL/) PRIMARY KEY AUTOINCREMENT NOT NULL/g'' |

Todavía estoy tratando de encontrar una forma de cambiar las entradas de KEY de mysql a su correspondiente entrada CREATE INDEX de sqlite3.

Answer 2

Cuando la base de datos sqlite3 se va a usar con ruby, es posible que desee cambiar:

tinyint([0-9]*) 

a:

sed ''s/ tinyint(1*) / boolean/g '' |
sed ''s/ tinyint([0|2-9]*) / integer /g'' |

por desgracia, esto solo funciona a medias porque a pesar de que está insertando 1 y 0 en un campo marcado como booleano, sqlite3 los almacena como 1 y 0, por lo que debe pasar y hacer algo como:

Table.find(:all, :conditions => {:column => 1 }).each { |t| t.column = true }.each(&:save)
Table.find(:all, :conditions => {:column => 0 }).each { |t| t.column = false}.each(&:save)

pero fue útil tener el archivo sql para buscar todos los booleanos.

Answer 3

Este es el mejor Script escrito y bien documentado para convertir ssql a .db

https://gist.github.com/esperlu/943776

o mejor use estas herramientas. Es una herramienta de migración de bases de datos ESF increíble y rápida .

después de haber probado todo el script aquí, no funcionó hasta que utilicé la herramienta de esf.

Nota :

Trial version add a ''T'' to the begingn of each text value you have 
 But the pro version worked like a charm :)

Answer 4

Este script de shell te ayuda

#!/bin/sh
if [ "x$1" == "x" ]; then
   echo "Usage: $0 <dumpname>"
   exit
fi
cat $1 |
grep -v '' KEY "'' |
grep -v '' UNIQUE KEY "'' |
grep -v '' PRIMARY KEY '' |
sed ''/^SET/d'' |
sed ''s/ unsigned / /g'' |
sed ''s/ auto_increment/ primary key autoincrement/g'' |
sed ''s/ smallint([0-9]*) / integer /g'' |
sed ''s/ tinyint([0-9]*) / integer /g'' |
sed ''s/ int([0-9]*) / integer /g'' |
sed ''s/ character set [^ ]* / /g'' |
sed ''s/ enum([^)]*) / varchar(255) /g'' |
sed ''s/ on update [^,]*//g'' |
perl -e ''local $/;$_=<>;s/,/n/)//n/)/gs;print "begin;/n";print;print "commit;/n"'' |
perl -pe ''
  if (/^(INSERT.+?)/(/) {
     $a=$1;
     s///'/'''/''/'/'''/g;
     s///n//n/g;
     s//),/(//);/n$a/(/g;
  }
  '' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql
  rm $1.err
    rm tmp
else
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi

Answer 5

Para convertir bases de datos con BLOB en ellas, agregué --hex-blob al comando mysqldump y lo siguiente a la lista de canales segmentados: -

sed -e "s/,0x/([0-9A-Z]*/),/,X''/L/1'',/g" |

esto reemplaza las cadenas de volcado hexagonal mysql, por ejemplo, 0x010A .... con X''010a ... ''para importar con sqlite.

Answer 6

Para que funcione la secuencia de comandos anterior, realicé los siguientes cambios:

1. ejecutarlo con #! / bin / bash
2. agrega dos seds a la lista de sedes segmentados:
   • sed ''s / // r // n / // n / g''
   • sed ''s / // "/" / g''
3. la línea ''rm tmp'' no funciona (a menos que tenga un archivo llamado ''tmp'' por ahí: O)

4. mi comando mysqldump se veía así:

   $ mysqldump -u usernmae -h host --compatible = ansi --skip-opt -p nombre_base_de_datos> dump_file

Luego funcionó bien ... gracias por el guión.

Answer 7

Probé algunas de estas secuencias de comandos que usan sed o awk, pero siempre ocurre un error, probablemente debido a los índices y las claves externas de mi base de datos MySQL y las opciones de mysqldump necesarias.

Luego encontré el módulo Perl SQL::Translator "que convierte definiciones de tablas SQL específicas del proveedor en otros formatos ..."
Este módulo crea todas las claves externas y corrige los índices, cambiando los nombres si es necesario.

Por lo tanto, reescribo el script de shell, incluido el volcado de MySQL db. Hay dos vuelcos porque el script "sqlt" solo genera la estructura y funciona rápido si el volcado no tiene datos. Tenga en cuenta que se puede adaptar a otras conversiones soportadas por SQL :: Translator.

Después de publicar este script de shell, me di cuenta de que la cuestión era convertir un archivo de volcado de MySQL, así que hice un script de Perl que hiciera eso, usando el módulo SQL :: Translator. En mis pruebas, utilicé un archivo de volcado generado sin opciones ( mysqldump -u usuario -password database> dumpfile ). No tuve problemas con los juegos de caracteres.

En otra prueba tuve un problema con los desencadenadores de mysql, por lo que modifiqué los guiones para omitirlo.

#!/bin/sh
#===============================================================================
#         USAGE: ./mysql2sqlite.sh <MySQL_database> <user>
#   DESCRIPTION: Converts MySQL databases to SQLite
#                Triggers are not converted
#  REQUIREMENTS: mysqldump, Perl and module SQL::Translator, SQLite 
#===============================================================================
if [ "$#" = 2 ]; then
    USER="$2"    
else
    echo "Usage: $0 <MySQL_database> <user>"
    exit
fi
if [ -s $1.db ]; then
    read -p "File <$1.db> exists. Overwrite? [y|n] " ANS
    if [ "$ANS" = "y" ] || [ "$ANS" = "Y" ] ; then
        rm $1.db 
    else 
        echo "*** Aborting..."
        exit
    fi
fi   
# extracts the necessary structure for SQLite:
mysqldump --skip-triggers --skip-add-locks --routines --no-data --compatible=ansi /
    --compact -u $USER --password $1 > /tmp/$1_$$_str.sql
# verify
if [ ! -s /tmp/$1_$$_str.sql ]; then
    echo "*** There are some problem with the dump. Exiting."
    exit
fi
# translates MySQL syntax structure to SQLite using the script "sqlt" of the
# perl module SQL::Translator (that corrects the foreign keys, indexes, etc.)
sqlt -f MySQL -t SQLite --show-warnings /tmp/$1_$$_str.sql /
    1> /tmp/$1_$$.sqlite 2> /tmp/$1_$$_sqlt.log
# verify
if [ ! -s /tmp/$1_$$.sqlite ]; then
    echo "*** There are some problem with the sql translation. Exiting."
    exit
fi
# adds statements to allow to load tables with foreign keys:
echo "PRAGMA foreign_keys=OFF;" >> /tmp/$1_$$.sqlite
echo "BEGIN TRANSACTION;" >> /tmp/$1_$$.sqlite
# extracts the data (simple inserts) without locks/disable keys, 
# to be read in versions of SQLite that do not support multiples inserts:
mysqldump --skip-triggers --no-create-db --no-create-info --skip-add-locks /
    --skip-extended-insert --compatible=ansi --compact -u $USER /
    --password $1 >> /tmp/$1_$$.sqlite
# adds statements to finish the transaction:
echo "COMMIT;" >> /tmp/$1_$$.sqlite
echo "PRAGMA foreign_keys=ON;" >> /tmp/$1_$$.sqlite
# correct single quotes in inserts
perl -pi -e '' if (/^INSERT INTO/) { s///'/'''/''/'/'''/g; } '' /tmp/$1_$$.sqlite
# load the sql file and generate the SQLite db with the same name 
# of the MySQL database
sqlite3 $1.db < /tmp/$1_$$.sqlite 2> /tmp/$1_$$sqlite.errlog
# verify
ERRORS=`cat /tmp/$1_$$sqlite.errlog | wc -l`
if [ $ERRORS = 0 ]; then
    echo "* Conversion complete. Verify the file < $1.db >"
    rm /tmp/$1_$$*
else
    echo "*** There are some problem. Verify the files < /tmp/$1_$$* >"
fi

Aquí la secuencia de comandos de Perl para convertir un archivo de volcado en un archivo de base de datos SQLite.

#!/usr/bin/perl 
#===============================================================================
#        USAGE: ./mysql2sqlite.pl <MySQL_dumpfile>
#  DESCRIPTION: Converts MySQL dumpfile to SQLite database
#               Triggers are not converted
#               The dump must be done with
#               > mysqldump --skip-triggers -u [user] --p [database] > dumpfile
# REQUIREMENTS: Perl and module SQL::Translator, SQLite
#===============================================================================
use strict;
use warnings;
use Carp;
use English qw( -no_match_vars );
use SQL::Translator;
use 5.012;

my $file = $ARGV[0];
my $filedb = $file;
$filedb =~ s//.*[^.]*$/.db/;
if ( -s $filedb ) { 
    say "*** Ja existe o arquivo < $filedb >. Abandonando...";
    exit;
}
my @stru;
my @data;

open( my $SQLFILE, "<", $file )
    or croak "Can''t open $file: $OS_ERROR";
while (<$SQLFILE>) {
    # nao considera linhas com comentarios e lock/unlock/drop
    next if ( /^--/ || /^///*/ || /^lock/i || /^unlock/i || /^drop/i );
    # processa os inserts
    if (/^(INSERT.+?)[(]/) {     
        my $ins = $1;            # captura o nome da tabela
        s///['']/''''/g;            # substitue aspas simples - /'
        s/[)],[(]/);/n$ins(/g;   # divide multiplos inserts
        push( @data, $_ );
    }
    # processa a estrutura
    else { push( @stru, $_ ); }
}
close($SQLFILE);

my $strusql = join( '''', @stru );
my $datasql = join( '''', @data );
#open( my $STRU,   ">", "stru.sql" ); # to verify the results
#open( my $DATA,  ">", "data.sql" );
#print $STRU  $strusql;
#print $DATA  $datasql;

# here the conversion
my $translator = SQL::Translator->new(
    no_comments       => 0,
    show_warnings     => 0,
    quote_table_names => 1,
    quote_field_names => 1,
    validate          => 1,
);
my $struout = $translator->translate(
    from => ''MySQL'',
    to   => ''SQLite'',
    data => /$strusql,
    # filename => $file,
) or croak "Error: " . $translator->error;

# define inicio e final da transacao de inserts
my $prgini = "PRAGMA foreign_keys=OFF;/n";
my $traini = "BEGIN TRANSACTION;/n";
my $trafin = "COMMIT;/n";
my $prgfin = "PRAGMA foreign_keys=ON;/n";

#gera o arquivo final sqlite
my $sqlout = join( "/n", $struout, $prgini, $traini, $datasql, $trafin, $prgfin);
open( my $FINAL, ">", "/tmp/final.sql" );
print $FINAL $sqlout;

# Monta o SQLite database
my $log = "/tmp/sqlite.errlog";
my $command = "sqlite3 $filedb < /tmp/final.sql 2> $log";
system($command) == 0 or die "system $command failed: $?";
if ( -s $log ) { 
    say "*** Houve algum problema. Verifique o arquivo < /tmp/sqlite.errlog > "; 
}
else { 
    say "*** Conversao completa. Verifique o arquivo < $filedb > "; 
}

Answer 8

Tuve un problema con el mysql db siendo ISO-8859-1 (Latin-1). ¿Cuándo la conversión a sqlite3 asumió que los datos eran UTF-8 dando como resultado errores de descodificación?

Fue fácil de arreglar con esto:

iconv -f ISO-8859-1 -t UTF-8 mysql_dump_file> mysql_dump_file_utf8

En caso de que esto ayude a alguien.

Answer 9

funciona bien en Centos 5.3 64bit. una vez que tenga el archivo de salida, cárguelo así:

shell> sqlite3 file_name.db SQLite versión 3.3.6 Ingrese ".help" para las instrucciones sqlite> .databases archivo de nombre seq

0 main / current_directory / file_name.db
sqlite> select * from table; . . . . . resultados ... sqlite> .quit