Dumping MySQL to SQLite

DEPRECATED! Use this method instead.

Inspired by this comment, we can migrate the MySQL database from production MySQL to local development SQLite. Because of various incompatibilities between MySQL and SQLite, I've modified the process described in that comment to be more consistently useful.

Here are the steps:

1. Create a new, clean SQLite database using Django migrations.

   First, make a copy of your current SQLite database, just in case. Then, create a new one. The easiest way to accomplish this is inside the portal docker container.

   docker exec -it portal_portal_1 bash
   cp db.sqlite3 db.sqlite3.bak
   python manage.py migrate
   

2. Dump the database data only!

   mysqldump --no-create-info --skip-create-options --compatible=ansi \
       --skip-extended-insert --compact --single-transaction \
       -h $DB_HOST -u $DB_USER -p $DB_NAME > database.mysqldump
   

3. Substitute unsupported SQL in the data dump. Make a copy first.

   cp database.mysqldump database.mysqldump.1
   vim database.mysqldump.1
   

   Then, in Vim, run the following substitutions:

   :%s:\\':'':g
   :%s:\\":":g
   :%s:\\r\\n:\r:g
   :%s:\\n:\r:g
   

   Save and quit.

4. Insert the data into the SQLite database.

   sqlite3 db.sqlite3 < database.mysqldump.1
   

   You can expect to see some unique constraint errors here, you can safely ignore them.

5. (Extra credit) Migrate CMS media from production to local to fix broken images/files

   cd /path/to/local/chameleon/django/media
   rsync -avz --delete chameleoncloud.org:/path/to/chameleon/django/media/ .