MySQL is one of the most popular database systems that utilize Structured Query Language (SQL). The relational database is commonly used alongside Apache/LightSpeed/Ngnix web servers and PHP/Perl/Python scripting languages.
Due to its stability, MySQL is used by many developers in production. Popular uses include WordPress, Joomla and Drupal content management systems. MySql is also used in major websites including Facebook, Twitter, YouTube and Google.
The database management system is very secure, scalable and performs well especially in an environment that demands the use of transactions. MySql’s complete workflow and reduced total cost of ownership gives developers the flexibility of using it.
MySQL works pretty well in an Ubuntu 16.04 Virtual Private Server and here is how to install it.
- An Ubuntu 16.04 VPS server from Digital Ocean, Vultr or Linode.
- A command line tool like putty.
- A non-root username with sudo rights.
Step 1: Downloading MySQL from the Ubuntu Package Repository
MySQL is maintained in the Ubuntu central repository and we can pull it using the apt-get command. But before we do this, we need to make sure that our Ubuntu 16.04 server is up to date.
Run the command below to update your Ubuntu server
sudo apt-get update
Then install MySQL server by running this command.
sudo apt-get install mysql-server
Press Y then Enter when you are promoted to install the package.
Step 2: Setting the MySQL Server root Password
MySQL server has a default user called root and should not be confused with the root of your Linux system. Once you complete the MySQL installation, you will be requested to supply a password for the root user.
Kindly use a strong password for security purposes. A mix of upper and lower case characters combined with numbers and special characters form a hard-to-crack password. Press Enter after entering the password and confirm the same on the next screen
Step 3: Securing the MySQL Server
The default MySql installation is not secure because it contains anonymous users and a test database. So we need to harden the installation to avoid somebody from login on the system with the default settings.
Luckily, we can run a command that will make things easier for us. Just type the following on your command line tool:
The command will run and you will be prompted to enter the MySQL root password that you created in Step 2 above.
Password Validation Plug-in
MySql secure installation will prompt you to setup a Validate Password Plug-in which is used for testing MySQL passwords to improve security.
The command will read as follows
Would you like to setup VALIDATE PASSWORD plugin?
Press Y and Enter to enable the Plug-in
The Validation Plug-in has three levels of password validation policy
LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
You will get a prompt to choose your desired policy which reads as follows
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
Just enter 2 and press Enter. This will make sure that passwords created for any MySQL database user will contain a mix of numeric, mixed case and special characters.
Changing Password for the root User
Next, you will get a prompt to change the password for the MySQL root user.
The prompt will read as follows:
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
Since we chose a strong password while installing MySQL in the above steps, we are not going to change the password. So just press N and Enter to proceed.
Removing Anonymous Users
Leaving any anonymous users on your MySQL database is very dangerous. You will get a prompt to remove the user.
The prompt will read as the below text:
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
Press Y and then Enter to remove the user
Disallowing Remote root Login
MySQL remote root login leaves an open door for hackers who can recursively connect from any IP address to your MySQL server and probably brute-force your password.
To disable this, you will get a prompt which reads:
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Press Y and then Enter to disable root login remotely
Deleting the MySQL default test Database
Fresh MySQL installation comes with a test database named ‘test’ that can be used by anyone. This is intended for testing and should be removed in a production environment.
You will get a prompt to remove the database which reads as follows:
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
Press Y and then Enter to remove the database
Reloading MySQL Server Privileges
The changes we have made so far have not yet been committed to the system and MySQL privileges need to be reloaded.
The prompt for refreshing the settings will read:
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :
Press Y and then Enter for the changes to be effected. Finally, you will see a success message.
Success. All done!
Step 4: Disabling Strict SQL Mode in MySQL 5.7
The default MySQL strict mode can bring problems when you try to insert data in a table. If a field is null and there is no default value defined, the transaction will fail.
Let’s create a new MySQL configuration file and make the changes using a nano text editor.
sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf
Once the text editor opens, enter the following text
Press CTRL + X then Y and Enter to save the changes
You need to restart the MySQL server for the changes to be effected by entering the command below on your terminal.
sudo service mysql restart
Step 5: Checking the Status of MySQL server
The MySQL server installation on our Ubuntu 16.04 VPS server is complete but we can test whether everything is working by running the below command.
sudo systemctl status mysql.service
If there were no hiccups with the MySQL installation, you should see the following status text:
That is all it takes to Install MySQL server on your Ubuntu VPS. From this point, you can create MySQL databases, tables and users using the command line. However, if you want a better tool to manage your MySQL database from a web browser, you should consider installing phpMyadmin. See my guide for installing phpMyadmin on Ubuntu server 16.04.