MySQL is a popular open source DBMS used to store and retrieve data for a wide variety of applications.
Our tutorial will cover the installation of MySQL, the creation of database and user, and the configuration of the server for remote access.
Setting-up MySQL Server
Get the latest version of Ubuntu’s APT
sudo apt-get update
Install mysql server using APT and follow the wizard
sudo apt-get install mysql-server
Creating User and Database
To create database, enter the MySQL command line by typing in
mysql
and execute the create database command (replace databasename with your database name)
CREATE DATABASE databasename;
next we need to create MySQL user (replace newuser and password by your desired values)
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
Next, the newly created user have to be given access to access the database we created
GRANT ALL PRIVILEGES ON databasename . * TO 'newuser'@'%';
Flush the privilege cache so MySQL can pick up the changes and exit MySQL command line.
FLUSH PRIVILEGES;
exit;
Restart MySQL service
sudo service mysql restart
Configure MySQL Server For Remote Access
Only do this if you want to access the MySQL server remotely. When doing this, take extra precautionary measures for the security of your server like limiting the access to port 3306 to specific IP addresses
Open the configuration file
nano /etc/mysql/mysql.conf.d/mysqld.cnf
and replace the line that contain bind-address = 127.0.0.1 with bind-address = 0.0.0.0. Save and close the file after done editing.
Restart mysql service by executing
sudo service mysql restart
Lastly, open port 3306
sudo ufw allow 3306