Installing System Packages
The first step is obviously to install MySQL itself, which is easiest to do using your systems package repository. In my case, I'm using Ubuntu 18.04 on a Digital Ocean droplet.
sudo apt-get update
sudo apt-get install mysql-server libmysqlclient-devSecuring the Database
Now we can work on some initial setup of the new database server. Conveniently, MySQL provides a tool that walks you thru the basics of securing the installation. The tool will ask you to decide on a password for the MySQL root user, so before we get started I strongly suggest you get a password manager ready to create and store a strong password. If you don't already have a password manager, I'll recommend Bitwarden, which is free and open-source.
sudo mysql_secure_installationAt this point, you'll be guided thru a series of questions. For the greatest level of security, just answer 'yes' to all of the prompts. I personally declined installing the password checking plugin since I can just use my password manager to create and save long complicated passwords.
The output of the tool will look like this:
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: n
Please set the password for root here.
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!We have a little bit more work to do though because the root password we set isn't actually being used. By default, the root user's authentication is set to use the auth_socket plugin, which only checks if the socket user name matches the MySQL user name. You can confirm this by connecting to MySQL and running the following query:
sudo mysql
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *EUFHL2894JFLJS819FIUSFKLJDFDFJFLSDFJFPOF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)We'll want to change the plugin from auth_socket to mysql_native_password. To do so, execute the following command using the same password you came up with earlier:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpasswordhere';Now we can flush the privileges and confirm the change:
mysql> FLUSH PRIVILEGES;
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *IJKHL4567JFLJS093FIUSFKLJDFDFJFLSDFJFLMV | mysql_native_password | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *EUFHL2894JFLJS819FIUSFKLJDFDFJFLSDFJFPOF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)Configuring the Database For Django
Now that we have MySQL nice and secure, we can start getting it ready for our Django application. We'll start by creating a new database and a user with access to that new database. Again, you'll need to come up with a strong password for your database user.
mysql> CREATE DATABASE mydatabasename CHARACTER SET utf8mb4;
mysql> CREATE USER ave@localhost IDENTIFIED BY 'youruserspasswordhere';When we give our new user access to the database that Django will use and flush the privileges again:
mysql> GRANT ALL PRIVILEGES ON personalsite.* TO ave@localhost;
mysql> FLUSH PRIVILEGES;Finally, we need to feed MySQL timezone information to avoid issues with django datetimes. To do that, we'll use the mysql_tzinfo_to_sql tool. When prompted for a password, use the password you created for the MySQL root user.
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -u root -pDone with MySQL. Go ahead and exit so we can move on.
mysql> exitConfiguring Django
If you already have a virtual environment setup to use with your Django application activate it now. Otherwise, you can create one using virtualenv. You can download the tool using pip like this:
sudo apt-get install python3-pip
pip3 install virtualenvWith virtualenv installed, you can specify a python version and directory to create the virtual environment. In my case, I'll be using Python 3.8 to create a virtual environment in a directory called 'venv' in my home directory. If you want to see how to install Python 3.8 from source for your virtual environment, check out my post on Setting Up Django with Nginx + Daphne.
virtualenv -p python3.8 ~/venvActive the new environment by sourcing it's activation script:
source ~/venv/bin/activateIf you don't yet have a django project ready to go, you can get started by creating a new directory, installing django inside of it, and starting a new project. Note the period ('.') at the end of the last command which specifies that you want to install django in the current directory.
mkdir ~/myprojectdirectory
cd ~/myprojectdirectory
pip install django
django-admin.py startproject nameofmyproject .Now we can actually install mysqlclient which will allow our python app to talk to the database:
pip install mysqlclientBefore starting the Django server, you'll want to make sure you have your database settings configured in Django. In a default django install, you can find these settings in ~/myprojectdirectory/nameofmyproject/settings.py.
Within this file, look for the DATABASES environment variable. By default, it looks like this:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
}
}Instead of using sqlite3 though, we want to use MySQL. Modify the code to something like this:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'OPTIONS': {
'read_default_file': '/home/myusername/mysql_django.cnf',
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
'isolation_level': 'read committed',
},
}
}The above code instructs django to read our database credentials from a configuration file so we'll need to create that. The init_command and isolation_level arguments also set some other database level settings that are recommended in the Django docs.
Make sure you save your configuration file in the same location that you specified above:
nano /home/myusername/mysql_django.confWithin the file, add something like the following, substituting in the vales you used when configuring MySQL:
[client]
database = mydatabasename
user = myusername
password = 'mystrongpassword'
host = 127.0.0.1
port = 3306
default-character-set = utf8And with that done we can finally get django started! We just need to run a few django management commands to get our migrations in order. Make sure you're in your django project directory where the manage.py script lives.
python manage.py makemigrations
python manage.py migrateHopefully this command successfully sets up of your tables in your newly created database. If you run into an error, be sure to double check your configuration file to make sure you gave Django all of the correct credentials to connect to the database. Once the migrations are done, we can finally run the server. Make sure you use the IP address of your own server so you can actually connect to it.
python manage.py runserver 123.123.123.123:8000In the above command, I'm assuming the remote server running the web app has an IP address of 123.123.123.123 and I'm serving the application thru port 8000. Now from my local computer I can connect to the running web application by typing http://123.123.123.123:8000 into my browser.
If the runserver command seems to be working on your remote server but you still can't connect to the web app from your local machine, you may need to make sure that port 8000 isn't being blocked by a firewall on your remote machine.
Now that you have MySQL working with Django, you'll want to serve it with something other than the runserver command since it isn't appropriate for use in a production environment. For help with that, check out my blog post on Setting Up Django with Nginx + Daphne.