Installing, Configuring, and Maintaining MySQL on Ubuntu 16.04 LTS

This will be a relatively short informative blog regarding how to setup and secure an installation of MySQL on Ubuntu.

MySQL Server Installation

Firstly, update your package library and install your MySQL server.

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

You’ll be prompted to create a root password during the installation. Make sure it’s a complicated password that you’ll remember, because you’ll be needing it.

MySQL Server Configuration

You’ll want to be sure to harden security on your MySQL installation by running the security script.

sudo mysql_secure_installation

This will prompt you to enter your root user password that you created during installation.

Firstly, the setup will ask if you would like to install a VALIDATE PASSWORD PLUGIN that can test passwords and improve security. If you’re the only one administering databases and are diligent about using great passwords, you opt-out.

Second, it will ask if you’d like to change the root password. If you’re having second thoughts about your password strength, you can change it now.

Third, it will ask if you would like to remove anonymous users. I typically use applications like WordPress and Magento, so I always have database users created out of the gate. I opted to remove anonymous users, but you may decide to run this script again before launching your site and remove them at a later date.

Fourth, it will ask if you would like to disallow root user remote access. I strongly suggest enabling this, especially considering that we’ll have phpMyAdmin installed shortly, removing any need for this.

The last two questions are to remove the test database, and to reload the privilege table, both of which I answered yes, as I won’t be needing the test database, and the privileges are important.

Server Status

To check the status of MySQL server you can run the sudo service mysql status command. If the server is not running, you can start it with sudo service mysql restart or sudo service mysql start.

Reset MySQL Root Password

You may find yourself in the situation that you have forgotten your root password. This recently happened to me after I jumped into a server that I had not maintained in quite some time (it was development, don’t worry).

The first step to resetting your password is to gain access to the terminal (ssh is typically what I use). Once you’re in, you’ll want to stop the MySQL service.

sudo service mysql stop

Once you’ve stopped running your server, you’ll need to prep the next command by creating a folder for it to access.

sudo mkdir /var/run/mysqld
sudo chown mysql: /var/run/mysqld

You can start the server with a few options that I’ll explain.

sudo mysqld_safe --skip-grant-tables --skip-networking &

The --skip-grant-tables flag turns off the need for authentication, and the --skip-networking flag turns off the ability to access the database remotely (important when authentication is disabled).

Once you’ve run the server, enter the mysql command line tool, and change the password.

sudo mysql

For MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';

For MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourNewPassword');

Then you’ll need to reboot your MySQL server.

#Shut down MySQL
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

#Start the MySQL service normally.
sudo service mysql start

From there on out, you can use whatever you set as YourNewPassword to access root functionality.

Notes for PHPMyAdmin

If you’ve decided to do your database administration through PHPMyAdmin, I would strongly suggest disallowing any unauthorized access to it. I’ve seen people block access with an Apache password option, but this doesn’t really help as much as you would think.

Blocking all access except your own is quite easy if you have a static IP address. If you don’t have a static IP address, you can contact your ISP and let them know that you would like one. If a static IP address isn’t in the books for you for one reason or another, you could always check your IP address and change the IP address in the configuration file each time you need access. Which shouldn’t be terribly often.

Either way, here’s how you do it:

sudo vim /etc/apache2/conf-enabled/phpmyadmin.conf

and add these lines to your code:

# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
 Options SymLinksIfOwnerMatch
 DirectoryIndex index.php

 # Add Your IP instead of ##.##.##.##
 Order Allow,Deny
 Allow from ##.##.##.## 

This will effectively block all access except for your IP address. It doesn’t get much more secure than this for PHPMyAdmin.

Conclusion

MySQL is fun. Don’t get bogged down with the basics! With this basic installation information, you’ll be well on your way to working with databases and enjoying all that relational databases have to offer!

Cheers,
Cole Speelman

Leave a Reply

Your email address will not be published. Required fields are marked *