Free SQLite to MySql Converter – Super Easy!

Heres how to copy my a development SQLite Database into a MySQL Production Database in 5 Easy Steps. Here’s the easiest way to do it. Seriously:

1. Install the SQLite Manager Firefox Addon

2. After the Addon is installed, in Firefox, go up to Tools > SQLite Manager (Opens the SQLite Manager Addon Window)

3. Click on the “Connect Database” Icon, looks like the Folder Open icon in any other program. Locate your SQLite Database file.

In a Ruby on Rails project you can find it in your rails_project > db > development.sqlite3

4. Back in the SQLite Manager Firefox Addon Window, go up to Database > Export Database (It will automatically export it as a SQL file just about ready to import into MySQL after the next step

5. Open that .sql file that was just exported in any basic text or code editor (ex: Notepad, TextEdit, TextMate, etc…). All you have to do now is do some find and replace SQLite specific syntax to MySQL syntax (easy):

find: AUTOINCREMENT   –   replace: AUTO_INCREMENT

find: ”   –   replace: [blank space - nothing - delete them all!!!]

Delete any line with this in it: “sqlite_sequence”, like the whole row. Example, you would delete both these rows:

CREATE TABLE sqlite_sequence(name,seq);
INSERT INTO sqlite_sequence VALUES(‘teams’,4);

Now it should be ready to import into your MySQL database. I used phpmyadmin by just going to yourhostingdomain.com/phpmyadmin, then clicked on the import link on the phpmyadmin front page, and browsed for our file we just finished modifying. You should be ready to rock.

Extra Reading

Here’s another article that explains only what is going on, but a good read nonetheless: Quick Easy Way to Migrate SQLite 3 to MySQL?

43 thoughts on “Free SQLite to MySql Converter – Super Easy!

  1. Thank you for your write up!
    I´m exporting a relatively big sqlite database right now and this works pretty well…
    i have two suggestions though (at least for rails apps):
    - boolean needs to be replaced with tinyint(1)
    - doublequotes should not be replaced with a blank string (breaks text fields that actually have doublequotes in them)… right now i´m replacing with backticks but that´s not ideal either.

    cheers,
    Alexander

  2. thank you so much.
    firefox got frozen because was a large db, but i got standalone program and i exporting right now

  3. Thanks for the pointers, but how do you get around sqlite’s dislike for proper creates? I can’t seem to figure out how to get it to include the length of a field in the create syntax?

    Any ideas?

    thanks

    Martin

  4. Great! I used this to convert the default TurboGears2 sqlite database to mysql and worked flawlessly. Thanks!

    Just one advice: better to substitute double quotes ( ” ) to backtics (`) instead of blank/whitespace.

  5. boolean is ok in MySQL 5.1. However, ‘t’ and ‘f’ need to be replaced with the words true and false, no quotes. Also ( I’m converting redmine ) regexp needs backtics, see @noisebleeds comment…

  6. How about going back the other way? .sql from mysql export via myphpadmin converted to sqlite .db?

  7. Perfect. Everything working. I have 1.5 Gb sqllite DB and by these steps I imported into mySQL.

    Thanks mate ;)

  8. That’s nice :)

    I also need a way to convert in the other direction.

    Mysql to SqlLite … has anyone any idea?

  9. Thanks a lot dude, just something.
    In my case I have to replace:
    This: ” to this: `
    And everything is good now.

  10. Thanks a lot. I encountered an error that didn’t let me import from phpmyadmin straight away. I had to re-order the queries especially the ones that REFERENCES other tables, hence i create all the tables that are referenced first and then create the ones that reference other tables.
    Hope this might help someone hence i posted it.
    Cheers.

  11. Brilliant. Thank you. Used your method to convert my web2py prototype SQLite DB to an MySQL script I could import into MySQL Workbench in order to create the EER used for my physical database description in my software project documentation.

  12. Hi,
    at the first time I have used it and it was Pico- Bello without any problem.
    But now for the 2nd time I wanted to do it again but I get this ERror: Drop table if exist ….
    do you know waht it is and why do I get it?

    Thank you

  13. Hi,

    All fields with VARCHAR type should be also replaced with VARCHAR(X). eg. VARCHAR(255)

  14. Trying this borked my sqlite db. now I dont have a db to play with my boss not very happy

    what you gonna do about it punk?

  15. Be careful when deleting double quotes “. Some values might have them and to be important.
    Replace only the ones in the syntax, around the table names.

  16. I was recommended this web site by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my difficulty. You’re incredible!
    Thanks!

  17. Also, take a tiny to check out the testimonials of the seller. This will keep you away from any scammer effects. If the online troop offers you a useful quantity for your circulating medium and they have been in the craft for a while, with a continued proceeds stream and few official complaints, then you are in for the bargain of your life. In a store that sells cheap A Lange Sohne Autograph copy Watches, you will be able to buy a specifical timepiece that is also a scrap of genteel jewels and a classy accessory, all under a noted fire-brand name.

  18. TroyesThere are other large outlet malls a bit farther outside of the city, most notably in Troyes, with both huge individual outlet stores for brands such as Lacoste, as well a combined in large malls; but they are harder to reach by public transportation. Troyes is a charming old town with half-timbered buildings and a delightful history, and the best way to get there is to rent a car, then spend some time in the ancient town, as well as the outlet malls. We took that opportunity to rent a fun little Smart Car, and our packages just barely fit-but the car’s interior was surprisingly roomy and comfortable! Even if you drive to Troyes, you should then take a bus that deposits you directly at the McArthur Glen discount mall.

  19. Good blog you have got here.. It’s hard to
    find high-quality writing like yours these days.
    I truly appreciate individuals like you! Take care!!

  20. Hi would you mind stating which blog platform you’re working with?
    I’m planning to start my own blog soon but I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and
    Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely
    unique. P.S Sorry for being off-topic but I had to ask!

    Review my homepage … ?? ???? ??????? ??

  21. Its such as you read my mind! You seem to grasp so much approximately this, such as you wrote the e-book in
    it or something. I feel that you simply could do with
    some percent to drive the message home a little bit, however instead
    of that, that is magnificent blog. An excellent read.

    I’ll certainly be back.

  22. I must thank you for the efforts you’ve put in penning this website.
    I’m hoping to check out the same high-grade content by you later on as well.
    In fact, your creative writing abilities has encouraged me
    to get my own, personal website now ;)

    my weblog; ???? ???? ???

  23. Good day! I could have sworn I’ve been to this web site before
    but after going through some of the posts I realized it’s new
    to me. Regardless, I’m certainly delighted I came across it and I’ll be bookmarking it and checking back regularly!

    Also visit my webpage – ?? ??? ?????

  24. I drop a comment when I like a article on a site or if I have something to add to the discussion.

    It is caused by the fire displayed in the post I looked at.
    And after this post Free SQLite to MySql Converter – Super Easy!

    | SEO, Online Marketing, and Web Production. I was moved enough to drop a thought ;)
    I actually do have a couple of questions for you if you do not mind.

    Could it be just me or do some of the responses come across like they are written by brain dead
    visitors? :-P And, if you are posting at other places, I would like to keep
    up with you. Would you list all of your public pages like your twitter feed, Facebook page or linkedin profile?

  25. Greetings! Very helpful advice within this article!
    It’s the little changes that produce the largest changes.
    Thanks for sharing!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>