Migrating a Django Project from SQLite to MySQL
I have several Django apps which are hosted on a Digital Ocean droplet, with development being done on my local Mac. For the most part, the initial SQLite setup works well, but for one of my larger projects I wanted to incorporate the use of MySQL. Here's how I went about the migration process:
Pre-Requisites
- A Django app which uses SQLite.
- MySQL is installed and running. See How to Install the Latest MySQL on Debian 10 tutorial for instructions on how to do this.
Recommended
- A thorough unit-testing framework for validating data integrity post-migration.
Setup the MySQL Database
The Digital Ocean tutorial How to Create a Django App and Connect it to a Database was very helpful helpful here. In a nutshell:
Open the my.cnf file for editing: vim ~/.my.cnf and add the following:
[client] user = USERNAME password = PASSWORD detault-character-set = utf8
A couple of notes here:
First of all, the possible locations of the my.cnf file can be found via the following comand: mysql --help | grep "Default options" -A 1. Since this is for development purposes only, and to avoid any permission issues, I've chosen the simplest location within the user home directory.
Second, the setting default-character-set value in the tutorial was actually utf-8, not utf8. For whatever reason that tutorial value was causing "invalid character set" exceptions to be thrown when attempting to connect (though the alias in the Index.xml file should have sufficed). Regardless, the workaround was to use the exact name, utf8
Third, the tutorial indicates to set the database within the my.cnf file. This caused issues with running mysqldump later, so I found it better to remove it from the my.cnf file, and add it within the name property of the DATABASES setting with Django.
Install mysqlclient Package
Next we'll need to install the mysqlclient package. You can do this via pip individually, or via requirements file:
# requirements.txt ... mysqlclient==2.1.0
Then install via pip:
(venv) $ pip install --upgrade pip (venv) $ pip install -r requirements.txt
When doing this initially however, I ran into what seemed to be an egg issue which resulted in a "No matching distribution found for mysqlclient" exception. What ended up being the issue was a lack of Python 3 headers for the install mysql version, and this was resolved by following the "Install" instructions within the PyPi mysqlclient page. After that, the pip install worked fine.
Update Django DATABASES Settings within settings.py
Next we'll take the existing default database Django settings and re-define them as legacy so we can continue to reference them, while defining MySQL as our new default database. Note that we add the 'NAME' attribute for the database, since we didn't define it within my.cnf. Also note that the username segment of the read_default_file value needs to be changed to reflect the actual user directory on your computer.
DATABASES = {
'legacy': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
},
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'DATABASE',
'OPTIONS': {
'read_default_file': '/Users/username/my.cnf',
},
},
}
Run Database Migrations Against MySQL Database
Run the following to build out the database tables within the newly created "default" MySQL database: (venv) $ python manage.py migrate --settings path.to.settings --database default
Dump Existing Data
To transfer the from one datbase to the other, I used the dumpdata and loaddata commands from Django fixtures.
To dump the existing SQLite data:
(venv) $ python manage.py dumpdata --settings path.to.settings --database legacy -o data.json --exclude contenttypes
Note that I excluded contenttypes here, as including that data was causing integrity errors later with loaddata.
Then, load the data into the "default" MySQL database:
(venv) $ python manage.py loaddata data.json --settings path.to.settings --database default
If all went well, you should be able to startup a development server and browse around your site. Be sure to run tests to ensure everything migrated properly. When all looks good, it's probably also a good idea to remove the legacy database settings and file.
Links: