MySQL 将mysql转储sql文件转换成可导入sqlite3 db格式的脚本

script to convert mysql dump sql file into format that can be imported into sqlite3 db

提问by DEzra

I have an export SQL file containing tables and data from MySQL and I want to import it into a Sqlite 3 DB.

我有一个包含来自 MySQL 的表和数据的导出 SQL 文件，我想将它导入到 Sqlite 3 DB 中。

What is the best way to do that?

最好的方法是什么？

Just importing the file via the sqlite3 tool doesn't work.

仅通过 sqlite3 工具导入文件是行不通的。

回答by Igor

This shell script help you

这个shell脚本可以帮助你

#!/bin/sh
if [ "x" == "x" ]; then
   echo "Usage: 
#!/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=""    
else
    echo "Usage: 
#!/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 = ;            # 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 > "; 
}
 <MySQL_database> <user>"
    exit
fi
if [ -s .db ]; then
    read -p "File <.db> exists. Overwrite? [y|n] " ANS
    if [ "$ANS" = "y" ] || [ "$ANS" = "Y" ] ; then
        rm .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  > /tmp/_$$_str.sql
# verify
if [ ! -s /tmp/_$$_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/_$$_str.sql \
    1> /tmp/_$$.sqlite 2> /tmp/_$$_sqlt.log
# verify
if [ ! -s /tmp/_$$.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/_$$.sqlite
echo "BEGIN TRANSACTION;" >> /tmp/_$$.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  >> /tmp/_$$.sqlite
# adds statements to finish the transaction:
echo "COMMIT;" >> /tmp/_$$.sqlite
echo "PRAGMA foreign_keys=ON;" >> /tmp/_$$.sqlite
# correct single quotes in inserts
perl -pi -e ' if (/^INSERT INTO/) { s/\'\''/'\'\''/g; } ' /tmp/_$$.sqlite
# load the sql file and generate the SQLite db with the same name 
# of the MySQL database
sqlite3 .db < /tmp/_$$.sqlite 2> /tmp/_$$sqlite.errlog
# verify
ERRORS=`cat /tmp/_$$sqlite.errlog | wc -l`
if [ $ERRORS = 0 ]; then
    echo "* Conversion complete. Verify the file < .db >"
    rm /tmp/_$$*
else
    echo "*** There are some problem. Verify the files < /tmp/_$$* >"
fi
 <dumpname>"
   exit
fi
cat  |
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=;
     s/\'\''/'\'\''/g;
     s/\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > .sql
cat .sql | sqlite3 .db > .err
ERRORS=`cat .err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: .db"
  rm .sql
  rm .err
    rm tmp
else
   echo "There were errors during conversion.  Please review .err and .sql for details."
fi

回答by Mike Fogel

To get the above script to work, I made the following changes:

为了使上述脚本正常工作，我进行了以下更改：

1. run it with #!/bin/bash
2. add two seds to the list of pipelined seds:
   • sed 's/\\r\\n/\\n/g'
   • sed 's/\\"/"/g'
3. the 'rm tmp' line is a no-op (unless you have a file named 'tmp' lying around :O )

4. my mysqldump command looked like this:

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

1. 用 #!/bin/bash 运行它
2. 将两个 sed 添加到流水线 sed 列表中：
   • sed 's/\\r\\n/\\n/g'
   • sed 's/\\"/"/g'
3. 'rm tmp' 行是一个空操作（除非你有一个名为 'tmp' 的文件：O）

4. 我的 mysqldump 命令如下所示：

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

Then it worked nicely... thanks for the script.

然后它工作得很好...感谢您的脚本。

回答by nfuria

I tried some of these scripts that uses sed or awk, but always occurs an error, probably due to the indexes and foreign keys of my MySQL db and the mysqldump options needed.

我尝试了其中一些使用 sed 或 awk 的脚本，但总是出现错误，可能是由于我的 MySQL 数据库的索引和外键以及所需的 mysqldump 选项。

Then I found the Perl module SQL::Translator"that converts vendor-specific SQL table definitions into other formats..."
This module create all foreign keys and correct the indexes, changing the names if necesssary.

然后我发现 Perl 模块SQL::Translator“将供应商特定的 SQL 表定义转换为其他格式......”
该模块创建所有外键并更正索引，必要时更改名称。

So, I rewrite the shell script, including the dump of the MySQL db. There are two dumps because the script "sqlt" only generates the structure and works fast if the dump has no data. Note that it can be adapted to others conversions suported by the SQL::Translator.

所以，我重写了 shell 脚本，包括 MySQL 数据库的转储。有两个转储，因为脚本“sqlt”只生成结构并且在转储没有数据时运行速度很快。请注意，它可以适应 SQL::Translator 支持的其他转换。

After I posted this shell script I realized that the question is about to convert a MySQL dumpfile, so I did a Perl script that do that, using the module SQL::Translator. In my tests, I used a dumpfile generated without options (mysqldump -u user --password database > dumpfile). I had no problems with character sets.

在我发布这个 shell 脚本后，我意识到问题是关于转换 MySQL 转储文件，所以我使用模块 SQL::Translator 做了一个 Perl 脚本来做到这一点。在我的测试中，我使用了一个没有选项生成的转储文件（mysqldump -u user --password database > dumpfile）。我对字符集没有任何问题。

In other test I had problem with mysql triggers, so I altered the scripts to skip it.

在其他测试中，我遇到了 mysql 触发器的问题，因此我更改了脚本以跳过它。

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

Here the Perl script to convert a dumpfile in a SQLite database file.

这里是将转储文件转换为 SQLite 数据库文件的 Perl 脚本。

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

回答by amjoconn

I had an issue with the mysql db being ISO-8859-1 (Latin-1). When did the conversion to sqlite3 assumed the data was UTF-8 resulting in decoding errors.

我对 mysql db 的问题是 ISO-8859-1 (Latin-1)。什么时候转换为 sqlite3 假设数据是 UTF-8 导致解码错误。

It was easy to fix with this:

很容易解决这个问题：

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

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

Incase this helps someone.

柜面这有助于某人。

回答by Srinidhi

At least, with mysql 5.0.x, I had to remove collate utf8_unicode_cifrom mysql's dump before importing it to sqlite3. So I modified the script to include the following to the list of seds:

至少，在 mysql 5.0.x 中，我必须先从mysql 的转储中删除collat​​e utf8_unicode_ci，然后再将其导入 sqlite3。因此，我修改了脚本以将以下内容包含在 sed 列表中：

sed 's/ collate utf8_unicode_ci/ /g' |

sed 's/ collate utf8_unicode_ci/ /g' |

Update:

更新：

MySQL treats boolean fields as "tinyint(1)", so I had to add the following beforetinyint([0-9]*)sed:

MySQL 将布尔字段视为“tinyint(1)”，因此我必须在tinyint([0-9]*)sed之前添加以下内容：

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

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

Also, since I'm trying to replicate a mysql db (production) to a sqlite3 db (development) of a Ruby On Rails application, I had to add the following line in order to set an auto-incrementing primary key:

此外，由于我试图将 mysql db（生产）复制到 Ruby On Rails 应用程序的 sqlite3 db（开发），因此我必须添加以下行以设置自动递增的主键：

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

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

I'm still trying to figure out a way to change the KEYentries from mysql to its corresponding CREATE INDEXentry of sqlite3.

我仍在尝试找出一种方法将KEY条目从 mysql更改为它对应的 sqlite3 的CREATE INDEX条目。

回答by goelectric

To convert databases with BLOBs in them I added --hex-blob to the mysqldump command and the following to the list of pipelined seds:-

要转换其中包含 BLOB 的数据库，我将 --hex-blob 添加到 mysqldump 命令，并将以下内容添加到流水线 seds 列表中：-

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

this replaces the mysql hex dump strings e.g. 0x010A…. with X'010a…' for importing with sqlite.

这将替换 mysql 十六进制转储字符串，例如 0x010A ...。使用 X'010a...' 使用 sqlite 导入。

回答by ucefkh

This is the best written and well documented shell Script to convert ssql to .db

这是将 ssql 转换为 .db 的最好的书面和有据可查的 shell 脚本

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

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

or better use this tools It's amazing and fastESF Database Migration Toolkit.

或者更好地使用这个工具它是惊人的和快速的ESF 数据库迁移工具包。

after having tried all the script here it didn't work until i did use the esf tool .

在尝试了这里的所有脚本之后，直到我确实使用了 esf 工具它才起作用。

Note :

笔记 ：

tinyint([0-9]*) 

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

回答by daicoden

When the sqlite3 database is going to be used with ruby you may want to change:

当 sqlite3 数据库将与 ruby​​ 一起使用时，您可能需要更改：

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)

to:

到：

##代码##

alas, this only half works because even though you are inserting 1's and 0's into a field marked boolean, sqlite3 stores them as 1's and 0's so you have to go through and do something like:

唉，这只有一半有效，因为即使您将 1 和 0 插入标记为布尔值的字段，sqlite3 也将它们存储为 1 和 0，因此您必须执行以下操作：

##代码##

but it was helpful to have the sql file to look at to find all the booleans.

但是查看 sql 文件以查找所有布尔值很有帮助。

回答by themattreid

works fine on Centos 5.3 64bit. once you have the output file load it like so:

在 Centos 5.3 64 位上运行良好。一旦你有输出文件，像这样加载它：

shell> sqlite3 file_name.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> .databases seq name file

shell> sqlite3 file_name.db SQLite version 3.3.6 输入“.help”获取说明 sqlite>.databases seq name file

---

---

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

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