将 Django DB 从 SQLite 迁移到 MySQL 的最佳方法是什么？

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

提问by GJ.

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.

我需要将我的数据库从 sqlite 迁移到 mysql，而且各种工具/脚本对我来说太多了，无法轻松找到最安全、最优雅的解决方案。

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

这在我看来很好http://djangosnippets.org/snippets/14/但似乎是 3 年以来获得令人担忧的更新..

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

你能推荐一个已知对 Django 1.1.1 可靠的解决方案吗？

回答by Martin Eve

Execute:

执行：

python manage.py dumpdata > datadump.json

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

接下来，将您的 settings.py 更改为 mysql 数据库。

Finally:

最后：

python manage.py loaddata datadump.json

回答by Carlos Henrique Cano

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

经过一番努力搜索后，我遇到了几个问题，我希望将来寻找答案的人会觉得有用。

my formula is

我的公式是

1. python manage.py dumpdata > datadump.json
2. Change settings.py to your mysql
3. Make sure you can connect on your mysql (permissions,etc)
4. python manage.py migrate --run-syncdb

5. Exclude contentype data with this snippet in shell

   python manage.py shell

   from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()

6. python manage.py loaddata datadump.json

1. python manage.py dumpdata > datadump.json
2. 将 settings.py 更改为您的 mysql
3. 确保您可以连接到您的 mysql（权限等）
4. python manage.py migrate --run-syncdb

5. 在 shell 中使用此代码段排除 contenttype 数据

   python manage.py shell

   from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()

6. python manage.py loaddata datadump.json

Hope that will help you!

希望能帮到你！

回答by Aidan Fitzpatrick

This is a neater way to avoid the ContentTypeissues described elsewhere:

这是避免ContentType其他地方描述的问题的更简洁的方法：

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

Then:

然后：

./manage.py loaddata dump.json

回答by Alexei Yur

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

我从 sqlite 迁移到 MySQL 所需的（更完整的）步骤列表，YMMV：

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

1. python manage.py dumpdata > datadump.json
2. 确保您可以连接到您的 mysql（权限等）
3. 确保您拥有修改 FOREIGN_KEY_CHECKS 的权限（为此我必须安装并运行我自己的 mysql 私有实例）
4. 确保未使用 InnoDB 引擎（在每个表中使用 MyISAM ）否则下一步将不起作用（静默失败）！
5. 通过这个命令放宽验证（这不会在 InnoDB 中生效）： SET GLOBAL FOREIGN_KEY_CHECKS = 0;
6. 单独加载 django_site.sql 表（如果使用 contrib.sites）
7. 将 settings.py 更改为您的新 mysql
8. python manage.py migrate --run-syncdb
9. 根据需要修改 Django 应用程序和数据库表的 /migrations 目录中的代码来修复同步数据库错误
10. 用这个片段排除 contentype 数据（可以把它放在主 urls.py 模块中）： from django.contrib.contenttypes.models import ContentTypeContentType.objects.all().delete()quit()
11. 如果需要编辑json数据必须先美化它： cat datadump.json | python -m json.tool > datadump_pretty.json
12. python manage.py loaddata datadump.json
13. 修复任何数据截断问题
14. 将时区数据添加到数据库： mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -P 1234 -u root -p --protocol=tcpmysql -P 1234 -u root -p -e "flush tables" --protocol=tcp
15. 在不提交任何数据的情况下测试站点是否正常工作
16. 设置全局 FOREIGN_KEY_CHECKS = 1;
17. 测试其余的