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.
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.
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 &
--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.
5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';
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.
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!