Converting a MySQL database to SQLite on a Mac

I recently decided to migrate an e-commerce shopping cart system I have been developing for a long time from MySQL to SQLite. I went back and forth over this idea, but I decided it made sense to have an e-commerce shopping cart that the user can install by literally dropping the folder into the site.

In order to make the transition as easy as possible, I decided to try to convert the database rather than re-create the many tables and fields needed to make it all work. During my research I found some paid products and a bunch of scripts scattered around, many in Perl. I was looking for something simple that I could try out with minimal effort.

I found a script posted at http://www.jbip.net/content/how-convert-mysql-sqlite that worked like a charm for me. I use a Macbook Pro running Snow Leopard. I develop using a local MySQL database and Apache using  MAMP.

In order to get started, you need to do a MySQL dump of your database (in the correct format). I tried using Navicat to do the dump and it didn’t work. So, I dug around and discovered that from the terminal you can go to /Applications/MAMP/Library/bin/ and run the following command

mysqldump -u root -p –compatible=ansi –skip-opt dbname > dumpfile

This created a file called dumpfile which I then moved into a temp folder on my desktop. From there, I pasted the script from JBipNet into my editor and saved it as mysql-to-sqlite.sh into the temp folder.

Finally, I went into the terminal again and set the permissions on the script and ran it:

  1. cd /Users/myname/Desktop/temp
  2. chmod +x mysql-to-sqlite.sh
  3. ./mysql-to-sqlite.sh dumpfile
Mysql2sqlite-conversion
Mysql2sqlite-conversion

MySQL to SQLite Conversion (in OSX Terminal)

This worked like a charm for me and my database was quickly converted to a valid SQLite 3 database. I then opened the database up using my favorite free SQLite 3 database management tool (a free FireFox plugin).

Read and post comments | Send to a friend

Comments

  1. EmmanuelMay 5, 2011 at 4:52 AM

    Nicely explained. It worked perfectly. Thank's for the tip ! And beautiful website btw.

    ReplyDelete

Post a Comment

Popular posts from this blog

Convert a LiveCycle Form back to an Acrobat Form

Image
PDF Forms that were saved with Adobe LiveCycle cannot be programatically filled and flattened using most API's, like ActivePDF. However, you can convert your LiveCycle PDF form back into a static PDF. 1. Open the form in LiveCycle and save it as Adobe Static PDF form. (Example using Acrobat X Pro) 2. Download PDFTK  http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/ 3. Remove extra XML data with the following command line. I copied the creditApplication.pdf file to the same folder. "pdftk creditApplication.pdf output test.pdf drop_xfa" 4. Now you can open the form in Acrobat and rename the forms fields. By default, the conversion might append form[0] stuff to the field names, so I rename them. 5. Save the new PDF using "Save As". Choose "Adobe PDF Files, Optimized (*.pdf)" and in the optimizer window, make it compatible with Acrobat 4.0 and later. 6. You now have a form fillable PDF that you can use programatically and will flatten properly. [gallery
Read more

Apple TV - Recover from nothing Take 2

Ok, so I have updated this for Take 2 and Leopard. This is more of a rought draft, but it worked well for me. Please comment if I have missed any steps since I am writing this after the fact. One big change from the original article us you can let Disk Utility do some of the work for you now. Also, I just skipped the restore partition to keep it simple since that was the source of many problems. Connect your Apple TV drive in Leopard. Remember disk1 may not be the name of your drive. From the terminal: Wipe out and re-create the partition structure sudo diskutil unmountDisk /dev/disk1 sudo gpt destroy /dev/disk1 sudo gpt create /dev/disk1 Create the OSBoot Partition sudo gpt add -b 888872 -i 3 -s 1843200 -t hfs /dev/disk1 diskutil unmountDisk /dev/disk1 sudo diskutil eraseVolume "Journaled HFS+" OSBoot /dev/disk1s3 diskutil unmountDisk /dev/disk1 Create the Media Partition Go to Disk Utility and format the Media partition - Name i
Read more