In this guide we are going through the process of installing and configuring mysql server 8 in Ubuntu 20.04. We will also test our installation by creating a database and a user.
MySQL is one of the popular open-source relational database management system. It is commonly installed as part of the popular LAMP or LEMP (Linux, Apache/Nginx, MySQL/MariaDB, PHP/Python/Perl) stack.
- How to Install and Set Up mysql 8 on Ubuntu 22.04
- How to install Mysql 8 on Rocky Linux/Centos 8
- How to install and Configure Mysql Server 8 on Fedora 34/35
- Mysql Permissions – Create, Update, Delete Database Users
- How to run Mysql 8 with Docker and Docker-Compose
- Install and Setup Nginx, WordPress and Mysql 8 in Centos 8
- Create an RDS instance in terraform with a Mariadb Example
- Using Ansible to Install and Initialize Mysql 8 on Centos 8
To follow along this guide, ensure you have the following:
- Up to date Ubuntu Server 20.04 with Internet access
- Server root access or user with sudo acces
Steps to Install Mysql 8
- Ensure our ubuntu server is up to date
- Set up the repo for mysql 8 installation
- Install Mysql 8 Server
- Test connection to mysql with the root password
1. Ensure our ubuntu server is up to date
Before proceeding let us ensure that the ubuntu server is up to date. First update the repos then do a system upgrade to ensure all the installed packages are up to date:
In your terminal, type these. The
-y option in
apt upgrade is to ensure that the system doesn’t pause for us to accept the upgrade.
$ sudo apt update $ sudo apt upgrade -y
2. Set up the repo for mysql 8 installation
Mysql server 8 is not available in the default ubuntu repositories. The mysql team provides a downloadable
.deb file that will configure the repositories for mysql server 8 installation. Download it with this command:
curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb
You should see an output almost similar to this:
$ curl -LO https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0 100 35536 100 35536 0 0 55874 0 --:--:-- --:--:-- --:--:-- 55874
Once the download is done, we need to install the downloaded deb file. Use this command to install:
sudo dpkg -i ./mysql-apt-config_0.8.19-1_all.deb
This will open a configuration window prompting you to choose mysql server version and other components such as cluster, shared client libraries, or the MySQL workbench.
For now since we are only interested in the Mysql Server Installation, leave the default settings and click OK to proceed.
This is the output on successful installation and configuration.
$ sudo dpkg -i ./mysql-apt-config_0.8.19-1_all.deb (Reading database ... 91535 files and directories currently installed.) Preparing to unpack .../mysql-apt-config_0.8.19-1_all.deb ... Unpacking mysql-apt-config (0.8.19-1) over (0.8.19-1) ... Setting up mysql-apt-config (0.8.19-1) ... Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config) OK
3. Install Mysql 8 Server
Now that the repos have been added to include mysql server, we can now install the mysql-server package.
First, refresh the repositories to get the latest from the added repo:
$ sudo apt update
Then Install Mysql 8 Server using this command:
sudo apt install -y mysql-server
Enter your administrator credentials, and the system will install the MySQL server package, client packages, and database common files.
The installation will prompt you to enter and confirm a root user and password for the MySQL database.
After that you will be asked to select Authentication plugin. It is recommended that you choose to use a strong password:
After that the new password will be set and service reloaded.
Confirm that mysql server is up and running with this command:
$ sudo systemctl status mysql ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2021-09-28 13:08:22 UTC; 2min 23s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 45123 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 45173 (mysqld) Status: "Server is operational" Tasks: 37 (limit: 4710) Memory: 353.0M CGroup: /system.slice/mysql.service └─45173 /usr/sbin/mysqld Sep 28 13:08:21 ip-172-26-11-229 systemd: Starting MySQL Community Server... Sep 28 13:08:22 ip-172-26-11-229 systemd: Started MySQL Community Server.
Active: active (running) since ... portion in the above shows that the app is up and running.
4. Test connection to mysql with the root password
Now that mysql is all set up and is running, we need to confirm that it can accept connections.
To test, connect to mysql with root user –
mysql -h 127.0.0.1 -u root -p
$ mysql -h 127.0.0.1 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Check mysql version to confirm that everything is Ok:
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.26 | +-----------+ 1 row in set (0.00 sec)
5. Extra – Creating users in mysql
To connect remotely to mysql, it is recommended to create an app specific user and a database. Let us do that here.
To create a database, use this command in the mysql prompt; This command creates a. database called
create database citizix_db;
To create a user, use this in the mysql prompt; this command will create a user
citizix_user that can connect from anywhere with the specified password:
create user 'citizix_user'@'%' identified by 'S0mStrongPa$word';
Now grant the created user all privileges on the created database:
grant all privileges on citizix_db.* to 'citizix_user'@'%';
Confirm that you can connect as the new user. Use the
mysql command on the terminal specifying the host with
-h and user with
-u. Also supply the password prompt flag
-p then enter the password:
❯ mysql -h 192.168.10.20 -u citizix_user -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Confirm the permissions we have using these commands:
mysql> show grants; +--------------------------------------------------------------+ | Grants for [email protected]% | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO `citizix_user`@`%` | | GRANT ALL PRIVILEGES ON `citizix_db`.* TO `citizix_user`@`%` | +--------------------------------------------------------------+ 2 rows in set (0.44 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | citizix_db | | information_schema | +--------------------+ 2 rows in set (0.22 sec) mysql>
Up to this point, we have been able to install mysql server 8 on Ubuntu 20.04 and tested that its working as expected by adding some user and a database.