What's the best way to migrate a Django DB from SQLite to MySQL?
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.
Thi开发者_运维知识库s 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 ?
Execute:
python manage.py dumpdata > datadump.json
Next, change your settings.py to the mysql database.
Finally:
python manage.py loaddata datadump.json
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!
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
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
精彩评论