Migrating your Django Project database from SQlite3 to MySQL

Django comes with an in-build default setup for SQLLite3 which is good for learning and minor static page based websites. Like blogs which is a standard example . But as soon you decide to create projects that might entail multiple users performing different operations at the same time, SQLLite is not recommended.

Hence the decision to migrate to a better DB system must be as early as possible. I had to under go a similar experience recently . It look a lot of time, trials to finally get it working . I am going to share you the complete steps assuming you don't even have a MySQL setup .

My Setup environment :

OS:  Windows 7
Django: 1.11
Python : 2.7

STEP 1: MySQL Installation

• Download the file "mysql-installer-community-8.0.13.0.msi" from this link. 
• Double Click the file and start installation . Choose Server and shell . At the end, start the server. 
• In windows environment key "Path" , add the following: 

C:\Program Files\MySQL\MySQL Server 8.0\bin;

• Launch a command prompt and type

mysql -u root -p 

• Enter the password and you should see 

STEP 2: Setting a database for our project.

• Create a database for our project . Use command (in the above MySQL console): 

                                   createdatabase your_db_name;

• You can now create a separate user which is to be used for the project. I got these settings from another good article . 

mysql> CREATE USER 'your_new_username'@'localhost' IDENTIFIED BY 'new_password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'your_new_username'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

STEP 3: Creating a dump data from our SQLite db file , to upload it to our mysql database. 

python manage.py dumpdata --natural-foreign --natural-primary -e contenttypes -e auth.Permission -e admin.Logentry > datadump.json

STEP 4: Changing settings from sqlite to mysql

In settings.py file, replace the DB setting with this :

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'username',
        'PASSWORD': "PASSWORD",
        "HOST": '',
        'PORT': '',
    }

}

STEP 5: Create tables inside your my sql db

python manage.py makemigrations

python manage.py migrate --run-syncdb

STEP 6: Load date into mysql from dump data we created in step 3

python manage.py loaddata datadump.json

The result should look like the above screen shot. Now run the Django server and verify if you can see all the data you had in your sqllite db. 

That's it. Thanks for reading . 😊