PCARI: Migrating SQLite Databases to MySQL

30 May 2017

Intro

This is a version of my “Migrating SQLite Databases to MySQL (Django)” specifically tailored to the PCARI project.

Prerequisites/Assumptions:


Step 1 (OS X): Downloading and Setting Up MySQL

You can skip this step if you already have MySQL set up on your computer.

First, we will download and set up MySQL using instructions here. I used the native package installer rather than the TAR. Make sure to save the temporary root password they give you! Once MySQL is installed, we will set up a launch daemon that allows MySQL to start up on system/terminal launch.

After you finish installing, double check that your server is running. On Mac OS, you can do this my going to System Preferences > MySQL. If not, you can turn it on through that menu.


Step 1 (Ubuntu): Downloading and Setting Up MySQL

You can skip this step if you already have MySQL set up on your computer or server.

If you are using Ubuntu, you can install using

$ sudo apt-get update
$ sudo apt-get install mysql-server

You will be prompted to set up a root password. Don’t forget it, as you will use it later! Then, run the security script to configure MySQL.

$ sudo mysql_secure_installation

To test MySQL is working, enter

systemctl status mysql.service

And you should see an output like

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Wed 2016-11-23 21:21:25 UTC; 30min ago
 Main PID: 3754 (mysqld)
    Tasks: 28
   Memory: 142.3M
      CPU: 1.994s
   CGroup: /system.slice/mysql.service
           └─3754 /usr/sbin/mysqld

If MySQL isn’t running, start it using

sudo systemctl mysql start

Step 2: Finshing Up Installation

You can skip this step if you already have MySQL set up on your computer.

Try running mysql in terminal. You might get an error like “command not found”. If so, in terminal, configure your computer’s $PATH so it recognizes mysql as an exectutable:

$ export PATH=$PATH:/path/to/your/mysql/bin

For me, the command was was

$ export PATH=$PATH:/usr/local/mysql/bin

Check and make sure that your terminal recognizes mysql now. You might still get a MySQL specific error, but that’s okay–we will fix that in the next steps.

Unfortunately, this fix only works temporarily. If you open a new terminal tab, you will have to do export PATH... again. To allow mysql to be a recognizable command every time, we will edit your computer’s bash profile. Open the bash profile using your preferred text editor:

$ vim ~/.bash_profile

In the file, copy-paste this:

# Set architecture flags
export ARCHFLAGS="-arch x86_64"
# Ensure user-installed binaries take precedence  
export PATH=/usr/local/mysql/bin:$PATH  
# Load .bashrc if it exists  
test -f ~/.bashrc && source ~/.bashrc  

The above code allows mysql to be recognized every time. Save the file, restart terminal, and it should work.


Step 3: Creating MySQL Users

NOTE: in this tutorial, we are altering the root user because it is assumed you do not have any other local users on your MySQL server. If you do, change the usernames accordingly

Now we are ready to use MySQL! Enter

$ mysql -u root -p

Log in using your root password. If this is your first time logging in, use the password that you saved in step 1. Now, if you want to change the root password to something of your own preference, in the MySQL shell, enter

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Now, you have your root user set up and can log into it using your own password.


Step 4: Creating the Project Database

Then, to create the database for your project (please be sure to name the database pcari to limit conflicts in version control):

mysql> CREATE DATABASE pcari CHARACTER SET UTF8;
mysql> GRANT ALL PRIVILEGES ON pcari.* TO root@localhost;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

Step 5: Changing Django App Settings

Then, install the relevent dependencies in your Python environment using pip or conda:

$ pip install MySQL-python

If you are using a virtual environment for Python, make sure you have pip installed in your virtual environment before using the pip commands above, or else the dependencies will be installed globally.

Finally, in your settings.py file in your app, change the DATABASES section to the following:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'pcari', #make sure your database name is 'pcari'
        'USER': 'root',
        'PASSWORD': 'your_password',
    }
}

A reminder that your_project_name should be the same name as the database you created in MySQL in Step 2. your_password is the same password that you use to log into MySQL.

We will need to add additional precautions to make sure your MySQL password is secure. We will set your password as an environmental variable, and access your password through the os module.

To allow your own user account to have access to the password, we will edit our .bashrc file, which sets system-wide configurations.

$ vim ~/.bashrc

At the bottom of the file, add

export mysql_pass="your_mysql_password" # make sure your environmental variable name is mysql_pass

This will set mysql_pass as an environment variable on your machine permanently.

Then, at the top of settings.py, write

import os

if it doesn’t already exist. We will then change our database configuration to be

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'pcari',
        'USER': 'root',
        'PASSWORD': os.environ.get('mysql_pass'),
    }
}

And you should be good!


Step 6: Make Migrations

We are in the home stretch! Now, all we need to do is apply any migrations you made to the new MySQL database.

Before we do so, however, we also need to edit a migrations so we avoid an error when migrating. Go to malasakit-v1 > malasakit-django > pcari > migrations > 0039_auto_20161024_1727.py and and change all the max_length=30 and max_length=300 arguments to max_length=255` which is the conventional amount. This is because some input strings may be more than 30 characters long, so we need to account for that. You can do this in your text editor using any “find-and-replace” commands.

Change all the max_length attributes to max_length=255
Change all the max_length attributes to max_length=255

Also, in 0052_auto_20170609_1902.py and models.py I changed the max_length of langauge = models.Charfield to max_length=25. This is also to account for the differences in string length.

Change all the max_length attributes to max_length=25
Change all the max_length attributes to max_length=25

Finally, in 0049_model_refactor.py, I changed the default=0 value to default=None because MySQL complains when you have a default=0 attribute on a ForeignKey.

Change all the default=0 attributes to default=None
Change all the default=0 attributes to default=None

A Note

If your migration fails, you may need to do some debugging. Every case may be different, so I cannot give much advice here, unfortunately. However, if you want to retry a migration after making some changes, you will need to start with an empty database. To do that, we will DROP the database and create a new one. To do so:

mysql> DROP DATABASE pcari;
mysql> CREATE DATABASE pcari CHARACTER SET UTF8;
mysql> GRANT ALL PRIVILEGES ON pcari.* TO root@localhost;
mysql> FLUSH PRIVILEGES;
mysql> QUIT

Then, you can try the migration again. Ok, lets begin!

The details are all abstracted away for you, so all you need to do is run:

$ python manage.py migrate --run-syncdb

Do not run makemigrations, as Jonathan as written up a custom migration.

Finally, to load all the data from the old database, we checkout our git HEAD to an older commit, get a data dumpfile from that older db.sqlite3 file, and then switch back to our current commit. Here are the steps to doing so:

$ git checkout cc83f886101a96369cae20796f0433759b4a9ada  # This is the README update commit on `master` (using SQLite)
$ python2 manage.py dumpdata --natural-primary --natural-foreign > data.json
$ git checkout your_working_branch  # Go back to the latest commit on your working branch with the MySQL configuration
$ python2 manage.py loaddata data.json

You should see a message like

Installed 9522 object(s) from 1 fixture(s)

Step 7: Wrap Up

And that’s it! Now you have transitioned your webapp from SQLite to MySQL. While SQLite is quick, reliable, and useful for most development purposes, when your app transitions into production you often need to transition your backend database to fill the needs. And with this tutorial, you now know how!

If you are having trouble, here are some pages that I found helpful when working on this writeup:

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-16-04

http://stackoverflow.com/questions/3034910/whats-the-best-way-to-migrate-a-django-db-from-sqlite-to-mysql

http://stackoverflow.com/questions/19189813/setting-django-up-to-use-mysql

https://docs.djangoproject.com/en/1.10/ref/databases/#mysql-notes

http://stackoverflow.com/questions/11546151/how-to-make-db-dumpfile-in-django

https://www.digitalocean.com/community/tutorials/how-to-use-mysql-or-mariadb-with-your-django-application-on-ubuntu-14-04

https://docs.djangoproject.com/en/1.11/topics/migrations/