What's the best way to migrate a Django DB from SQLite to MySQL?

Question

I need to migrate my db from sqlite to mysql, and the various tools/scripts out there are too many for me to easily spot the safest and most elegant solution.

This seemed to me nice http://djangosnippets.org/snippets/14/ but appears to be 3 years since getting an update which is worrying..

Can you recommend a solution that is known to be reliable with Django 1.1.1 ?

I'm guessing you already tried a dumpdata followed by a loaddata? — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Jun 14, 2010 at 4:34

Martin Eve · Accepted Answer · 2010-06-14 09:43:26Z

91

Execute:

python manage.py dumpdata > datadump.json

Next, change your settings.py to the mysql database.

Finally:

python manage.py loaddata datadump.json

answered Jun 14, 2010 at 9:43

Martin Eve

2,7411 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

freyley Over a year ago

Except sometimes this doesn't work, if you've put unicode data into strings in sql, the fixture creator and mysql don't seem to get along. At least, that's what I came here trying to figure out how to solve.

Marcin Over a year ago

Or this doesn't work because of integrity errors...or many other issues.

Nhu Trinh Over a year ago

Sometimes it raise "... is not JSON serializable"

user9608133 Over a year ago

Running dumpdata > datadump.json raises error: CommandError: No installed app with label '>'.

gdreamlend · Accepted Answer · 2019-04-21 05:55:17Z

80

After some hard searching I got several problems that I hope future answer looking people will find useful.

my formula is

python manage.py dumpdata > datadump.json
Change settings.py to your mysql
Make sure you can connect on your mysql (permissions,etc)
python manage.py migrate --run-syncdb
Exclude contentype data with this snippet in shell

python manage.py shell

from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()
python manage.py loaddata datadump.json

Hope that will help you!

edited Apr 21, 2019 at 5:55

gdreamlend

1069 bronze badges

answered Dec 8, 2016 at 18:05

Carlos Henrique Cano

1,52811 silver badges15 bronze badges

6 Comments

DeltaG Over a year ago

What does the contenttype data exclusion do?

Carlos Henrique Cano Over a year ago

@DeltaG The canonical answer is this. But the best answer I found is: this one

user3770447 Over a year ago

That worked for me too. Step 5 was needed for the loaddata command to work.

Aniruddh Agarwal Over a year ago

I am getting this answer django.db.models.fields.related_descriptors.RelatedObjectDoesNotExist: Problem installing fixture '/home/ubuntu/news-manifest-master/datadump.json': Comment has no content_type.

cikatomo Over a year ago

why run-syncdb? I had to run without that

|

Aidan Fitzpatrick · Accepted Answer · 2019-07-12 22:18:23Z

12

This is a neater way to avoid the ContentType issues described elsewhere:

./manage.py dumpdata --exclude contenttypes --exclude auth.permission --exclude sessions --indent 2 > dump.json

Then:

./manage.py loaddata dump.json

answered Jul 12, 2019 at 22:18

Aidan Fitzpatrick

2,0751 gold badge22 silver badges26 bronze badges

2 Comments

florisla Over a year ago

This is a useful addition; you could improve this answer by adding more steps to it like the answer by Carlos Henrique Cano.

mercury Over a year ago

also --exclude admin.logentry

Alexei Yur · Accepted Answer · 2018-11-04 00:51:45Z

2

A (fuller) list of the steps I needed for moving from sqlite to MySQL, YMMV:

python manage.py dumpdata > datadump.json
Make sure you can connect on your mysql (permissions, etc)
Make sure you HAVE PRIVILEGES to modify FOREIGN_KEY_CHECKS (I had to install and run my own private instance of mysql for that)
Make sure InnoDB engine is NOT used (use MyISAM in every table) or the next step won't work (failing silently)!
Relax validation by this command (this won't take effect in InnoDB): SET GLOBAL FOREIGN_KEY_CHECKS = 0;
Load django_site.sql table separately (if using contrib.sites)
Change settings.py to your new mysql
python manage.py migrate --run-syncdb
Fix syncdb errors errors by tinkering with the code in /migrations directories of your Django apps and the DB tables as necessary
Exclude contentype data with this snippet (can put it in the main urls.py module): from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()
If need editing json data have to prettify it first: cat datadump.json | python -m json.tool > datadump_pretty.json
python manage.py loaddata datadump.json
Fix any data truncation issues
Add timezone data to the database: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -P 1234 -u root -p --protocol=tcp mysql -P 1234 -u root -p -e "flush tables" --protocol=tcp
Test the site is working without submitting any data
SET GLOBAL FOREIGN_KEY_CHECKS = 1;
Test the rest

answered Nov 4, 2018 at 0:51

Alexei Yur

213 bronze badges

1 Comment

am70 Over a year ago

From the official documentation: MySQL’s default storage engine is InnoDB. This engine is fully transactional and supports foreign key references. It’s the recommended choice.

Collectives™ on Stack Overflow

What's the best way to migrate a Django DB from SQLite to MySQL?

4 Answers 4

4 Comments

6 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related