Introduction
This tutorial is about the installation and the initial configuration of a MySQL server.
Prerequisites
- A Ubuntu Server
Step 1 - Install the MySQL-server Package and run the installer
Optional: Update and install updates: sudo apt update && sudo apt upgrade
Do you want to continue? [Y/n] yType y and press enter.
Install the MySQL-server package: sudo apt install mysql-server
Do you want to continue? [Y/n] YType y and press enter.
Now start the installer using following command: sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT 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 component?
Press y|Y for Yes, any other key for No:If you want to use the "VALIDATE PASSWORD COMPONENT" type Y otherwise N, then press enter.
Step 1.1 - Configure "VALIDATE PASSWORD COMPONENT" (Optional)
There are 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
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:In our tutorial we use MEDIUM so we type 1 and press enter.
Step 1.2 - Set the password for the root user
Please set the password for root here.
New password: mySecretPassword
Re-enter new password: mySecretPasswordEnter your password, press enter and re-enter your password, then again press enter.
If you have set up the "VALIDATE PASSWORD COMPONENT" press y now.
Step 1.3 - Remove anonymous user (Optional)
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) : YType Y, then press enter
Step 1.4 - Configure root remote root login
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) :You have to choose this by yourself! If you want to access remotely type n and press enter if not type y and press enter.
If you want to remote access from everywhere you have to follow Step 4
Step 1.5 - Remove the test database
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) : YType Y, then press enter
- Dropping test database...
Success.
- Removing privileges on test database...
Success.Step 1.6 - Reload privilege tables
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) : YType Y, then press enter
Success.
All done!Step 2 - Edit config file to enable remote access (Optional)
You should need this if you want to access remotely to your database. For example if u run a gameserver on server A and your database runs on server B you need to enable.
Open the config file with an editor of your choice, we use nano in our tutorial.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the following lines and add a # at the beginning of the lines.
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1Now it should look like this:
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#mysqlx-bind-address = 127.0.0.1The installation and initial configuration are now complete!
There are two more optional steps:
Step 3 - Disallow login via commandline without password
Step 4 - Configure the MySQL root user for remote access
Step 3 - Disallow login via commandline without password (Optional)
Login into the MySQL-server as root: sudo mysql -u root
Now run this querys to disable login without password for user root@localhost.
You have to replace YourSuperSecretPassword (line 2) with your own secure password!
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourSuperSecretPassword';
FLUSH PRIVILEGES;
exit;After this restart the MySQL service to apply the settings: sudo service mysql restart
If you now try to login using sudo mysql -u root this should happen:
root@tutorial:~# sudo mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)To log in you have to use sudo mysql -u root -p from now on.
Step 4 - Configure the MySQL root user for remote access (Optional)
To check which users exist log in to MySQL console and run the followning query:
SELECT User, Host FROM mysql.user;This should be look like this:
+------------------+-----------+
| User | Host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)You can set the host of the root user to %, so you can connect from anywhere with the root user.
UPDATE mysql.user SET host='%' WHERE user='root' AND host='localhost';To apply these change you have to restart the mysql server: sudo service mysql restart.
Now you can connect from anywhere, remember to secure your server properly and that this is NOT recommended for production use.
Conclusion
The MySQL server is now installed after following this tutorial.