MySQL version 8.0 is a free and open-source database system used by most web applications and sites on the Internet. Typically MySQL is part of the LAMP (Linux, Apache, MySQL, Perl/Python/PHP) stack. Popular opensource software such as WordPress, MediaWiki, and others heavily used by MySQL as a database storage engine. Let us see how to install MySQL server version 8.x on Ubuntu 20.04 LTS Linux server for your web apps or blog system.
| Tutorial requirements | |
|---|---|
| Operating system/app | Ubuntu Linux 20.04 LTS |
| Root privileges required | Yes |
| Difficulty | Easy (rss) |
| Estimated completion time | 10m |
How to install MySQL server on Ubuntu 20.04 LTS server
The procedure is as follows. First, open the terminal application and log in to your server using the ssh command and then update Ubuntu repo to apply security updates and fixes on Ubuntu Linux:
sudo apt update
sudo apt upgrade
## Reboot Linux system if a new Linux kernel installed ##
sudo reboot
Step 1 – Installing MySQL server
Let us search for package:
apt search mysql-server
Get information about the mysql-server version and then install the mysql server:
sudo apt info mysql-server
Outputs:
Package: mysql-server Version: 8.0.21-0ubuntu0.20.04.4 Priority: optional Section: database Source: mysql-8.0 Origin: Ubuntu Maintainer: Ubuntu Developers Original-Maintainer: Debian MySQL Maintainers Bugs: https://bugs.launchpad.net/ubuntu/+filebug Installed-Size: 111 kB Depends: mysql-server-8.0 Homepage: http://dev.mysql.com/ Task: lamp-server Download-Size: 9552 B APT-Sources: http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages Description: MySQL database server (metapackage depending on the latest version) This is an empty package that depends on the current "best" version of mysql-server (currently mysql-server-8.0), as determined by the MySQL maintainers. Install this package if in doubt about which MySQL version you need. That will install the version recommended by the package maintainers. . MySQL is a fast, stable and true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular database query language in the world. The main goals of MySQL are speed, robustness and ease of use.
Install it on Ubuntu 20.04 LTS:
sudo apt install mysql-server
Step 2 – Securing MySQL server
We need to secure our new MySQL server installation by running the following command:
sudo mysql_secure_installation
You need to set up a secure password:
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: Y 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: 2 Please set the password for root here. New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
Of course, we must delete an anonymous user too:
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) : Y
Success.
Do not allow root login over the LAN or Internet. It is a security feature:
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) : Y
Success.
Finally, delete a Oracle MySQL database called test and reload privilege:
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) : Y - Dropping test database... Success. - Removing privileges on test database... Success. 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) : Y Success. All done!
Step 3 – Enabling MySQL server at boot time
Make sure our server start when Ubuntu 20.04 LTS boots:
sudo systemctl is-enabled mysql.service
If not enabled, type the following command to enable the server:
sudo systemctl enable mysql.service
Verify server status by typing the following systemctl command:
sudo systemctl status mysql.service
● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Fri 2020-10-23 09:20:17 UTC; 16min ago Main PID: 1129 (mysqld) Status: "Server is operational" Tasks: 40 (limit: 4680) Memory: 338.6M CGroup: /system.slice/mysql.service └─1129 /usr/sbin/mysqld Oct 23 09:20:16 ubuntu-pdb systemd[1]: Starting MySQL Community Server... Oct 23 09:20:17 ubuntu-pdb systemd[1]: Started MySQL Community Server.
Step 4 – Starting/Stopping/Restarting MySQL server
We can control the MySQL server on Ubuntu using the command line option itself. Let us start it with
sudo systemctl start mysql.service
Stop the MySQL server, enter:
sudo systemctl stop mysql.service
Restart the MySQL server as follows:
sudo systemctl restart mysql.service
We can view the MySQL service log as follows using the journalctl command:
sudo journalctl -u mysql.service -xe
sudo tail -f /var/log/mysql/error.log
Sample log file:
2020-10-23T09:20:13.285531Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory. 2020-10-23T09:20:13.309795Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.21-0ubuntu0.20.04.4). 2020-10-23T09:20:15.758296Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-0ubuntu0.20.04.4) (Ubuntu). 2020-10-23T09:20:16.780030Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 1129 2020-10-23T09:20:16.789608Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-10-23T09:20:17.046682Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-10-23T09:20:17.151840Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2020-10-23T09:20:17.220152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-10-23T09:20:17.220347Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2020-10-23T09:20:17.242986Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
Step 5 – Login into MySQL server for testing purpose
So far, we learned how to install, set up, secure, and start/stop the MySQL server version 8 on Ubuntu 20.04 LTS. It is time to log in as a root (admin) user. The syntax is:
mysql -u root -p
mysql -u USER -h host -p
The STATUS command displays the version and other info:
STATUS;
We can see MySQL version as follows:
SHOW VARIABLES LIKE "%version%";
Step 6 – Creating a new MySQL database and user/password
Let create a new database called wpblog, type:
CREATE DATABASE wpblog;
Next, I’m going to create a new user named ‘wpuser’ for our database called wpblog as follows:
CREATE USER 'wpuser'@'%' IDENTIFIED BY 'Your_Super_Secret_Password';
Finally, give permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON wpblog.* TO 'wpuser'@'%';
Of course, I can grant ALL PRIVILEGES too as follows:
GRANT ALL PRIVILEGES ON wpblog.* TO 'wpuser'@'%';
See MySQL users and their grants:
SELECT user,host FROM mysql.user;
SHOW GRANTS for wpuser;
Test it as follows:
mysql -u wpuser -p wpblog
mysql -u wpuser -h localhost -p wpblog
Where,
- -u wpuser : User for login
- -h localhost : Connect to host named localhost
- -p : Prompt for password
- wpblog : Connect to database named wpblog
Conclusion
And there you have it, Oracle MySQL server version 8.x set up and running correctly on Ubuntu Linux 20.04 LTS server. Further, you learned how to add a new database, user, and password for your project. See Oracle MySQL database docs for SQL and other commands.
🐧 1 comment so far... add one ↓
| Category | List of Unix and Linux commands |
|---|---|
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
Mariadb is much better choice instead of Oracle controlled app.