2

I have 3 sqlite3 database files, from 170G to 1.07T on my CentOS 7 sever. I can create dump files by sqlite3 logs.db .dump > logs.sql but these sql files can't be imported to MySQL because of syntax.

I've read these solutions:

Quick easy way to migrate SQLite3 to MySQL?

http://www.sqlite.org/cvstrac/wiki?p=ConverterTools

http://www.redmine.org/boards/2/topics/12793

https://realpython.com/blog/python/web2py-migrating-from-sqlite-to-mysql/

https://github.com/athlite/sqlite3-to-mysql

I tried some of them but it seems not working. I don't have enough memory for the string searching and replacement.

Is there some effective way for large sqlite databases to be migrated?

Community
  • 1
  • 1
leoce
  • 715
  • 1
  • 8
  • 24
  • Can you perhaps handle structure and data in two separate steps? Export the statements that create the tables, and modify those where necessary due to syntax differences. And then export the data as CSV, so that you can import it into the already prepared MySQL tables (using `LOAD DATA INFILE` or something like that.) – CBroe Aug 11 '15 at 12:18
  • You should try some multi-database admin app like Navicat Premium which can migrate data between various database types - http://www.navicat.com/products/navicat-premium – Ondřej Šotek Aug 11 '15 at 12:20
  • Thanks, @CBroe! I'm not so sure about that. It's not just the statements, but also values and symbols, if you take a look into the steps in http://www.redmine.org/boards/2/topics/12793. That script goes line by line. – leoce Aug 11 '15 at 12:24
  • Thanks, @OndřejŠotek! I guess...a purchased solution will do. Forgot to add in the question, I'm looking for some free solution... don't think my supervisor is willing to pay 600 USD for that... – leoce Aug 11 '15 at 12:27

1 Answers1

4

Apparently I didn't use all the scripts in http://www.redmine.org/boards/2/topics/12793, I only used the latest script by Peter sørensen and it can't handle big files as the console returned "Killed". I guess it has something to do with the temp file operation.

My problem was solved by Pavel Medvedev's script. Although Peter's post says that the updated script deals with "a problem where not all was converted to the correct mysql value when a sting stretched across multiply lines". My sql files have multiple lines of string and Pavel's script can handle it. There's no problem. Maybe the problem Peter mentioned would occur with someone's files.

leoce
  • 715
  • 1
  • 8
  • 24