The complete process of changing database from SQLite to MySQL - How to migrate a Django DB from SQLite to MySQL

One of the main benefits to use Django for web development is that it comes with an inbuilt database/filesystem i.e. SQLite. Managing databases and files is super easy in Django. But there are some drawbacks to using SQLite. In my experience, if your website has minimum traffic of about 100-200 per day OR you have 1000-2000 files (or more obviously) in your database, then the problems start rising. SQLite gives amazing performance if your data is up to 2GB (can vary with your file types & size). Thus, you can not handle large traffic efficiently by using SQLite.

Here are some advantages & disadvantages of using SQLite :
Advantages :
1) Sqlite is Lightweight.
2) No Installation Needed.
3) Portable & Reliable

Disadvantages :
1) SQLite is used to handle low to medium traffic.
2) Database size is restricted up to 2GB (in most cases).

Today I will show you how you can migrate db from SQLite to MySQL in 6 simple steps. Just bare with me & i will provide the easiest way to do stuff in the right manner.

Step 1

First of all make a zip file of your whole website folder for backup security purposes (highly recommended).
Now, we have to dump all data from SQLite in a JSON file. For this purpose, we have to use the following command.

python manage.py dumpdata > datadump.json

Just go to the working directory (in the virtual environment) and run the above command. The execution may take time depending on database size.
Please try, not to close the console until the final step.

Step 2

Now install mysqlclient by using the following command. This is nothing but a connector for Python with mysql.

pip install mysqlclient

Step 3

Now you have to make some changes in the settings.py file. Search for a dictionary named "DATABASES" and replace it with the following code.

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'Database_name',
'USER': 'Username',
'PASSWORD': 'db_password',
'HOST': 'mysql_host_name',
'PORT': '3306',
'OPTION': {'init_command': 'set storage_engine=InnoDB; \
set session transaction isolation level read committed'},
}}

Make sure you can connect on your MySQL DB (permissions, etc) i.e. check the details carefully before proceeding.
Note that the OPTION may have more parameters. You can check the Django documentation for more values, but if you keep it as it is above there will be no issue.

Step 4

Run the following command to make synchronization among the database tables and the model settings.

python manage.py migrate --run-syncdb

You can ignore the warnings that pop-ups in the console (if any)

Step 5

This is the main step, you have to remove old content types to save data in MySQL fields which may use different content types.
Exclude contentypes data with this snippet in the shell.

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

Step 6

This is the final step. Everything is now all set, the database, settings, etc. Now we have to write or store the data from the json file which we saved in our very first step. For this purpose run the following command :

python manage.py loaddata datadump.json

Tips

If you face any problem for example if you have any app like sorl-thumbnail or easy-thumbnail in your INSTALLED_APPS, sometimes a file(thumbnail) is not present in media files which makes the last step incomplete. The point is if you face any issue just read the error location and the app/module which is causing the error. Then replace the website data from our backup file(that is why I recommended making a backup zip file of the whole website) and remove that app from INSTALLED_APPS (run migrate command if required).
Now you have to perform all the steps again.