Transfer sqlite3 data to mysql

sqlite3 mydb.sqlite3 '.dump' | grep -vE '^(BEGIN|COMMIT|CREATE|DELETE)|"sqlite_sequence"' | sed -r 's/"([^"]+)"/`\1`/' | tee mydb.sql | mysql -p mydb
Filters out all non-insert SQL operations (we couldn't filter out only lines starting with "INSERT" because inserts can span multiple lines), quotes table names with backticks, saves dump to a file and pipes it straight to mysql. This transfers only data--it expects your schema is already in place. In Ruby on Rails, you can easily recreate the schema in MySQL with "rake db:schema:load RAILS_ENV=production".

0
By: mislav
2009-10-02 14:40:51

These Might Interest You

  • If you have servers on Wide Area Network (WAN), you may experience very long transfer rates due to limited bandwidth and latency. To speed up you transfers you need to compress the data so you will have less to transfer. So the solution is to use a compression tools like gzip or bzip or compress before and after the data transfer. Using ssh "-C" option is not compatible with every ssh version (ssh2 for instance).


    0
    ssh 10.0.0.4 "gzip -c /tmp/backup.sql" |gunzip > backup.sql
    ultips · 2012-01-06 17:44:06 0
  • Crude, but works. Note for security, /data/ will be inaccessible unless your device has been *rooted*. On the other hand, if a device has been rooted, its data is now wide open to anyone with a USB cable and the above "one-liner". `adb` is one of the platform tools in the android SDK. To get SMS messages: adb pull /data/data/com.android.providers.telephony/databases/mmssms.db ; sqlite3 -batch <<EOF contacts2.db <CR> .header on <CR> .mode tabs <CR> select * from sms; <CR> EOF


    2
    adb pull /data/data/com.android.providers.contacts/databases/contacts2.db ; sqlite3 -batch <<EOF contacts2.db <CR> .header on <CR> .mode tabs <CR> select * from data; <CR> EOF
    mhs · 2012-12-26 11:35:23 0
  • With no '-q 0' switch, nc simply waits, and whatever awaits the data hangs.


    3
    echo data | nc -q 0 host 5000
    strake · 2010-06-26 11:57:55 0
  • This option makes a copy of your current db and via ssh it transfer to the server you specify and upload the database to the specific User & Password you specify (Note the db User & Pass, can be different from the one you use in the other server) If you are going to use "localhost" as your main db remove (-h) and youst add "localhost"


    5
    mysqldump -uUserName -pPassword tudb | ssh root@rootsvr.com "mysql -uUserName -pPassword -h mysql.rootsvr.com YourDBName"
    elecboy · 2009-02-17 22:36:06 2

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: