Install mysql 8 on ubuntu 20.04
MySQL is an open-source relational database management system. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database.MySQL implements the relational model and is commonly installed on an integral part of LAMP stacks. It is also used for web databases.
MySQL Server 8.0 was announced in April 2018,including NoSQL Document Store, atomic and crash safe DDL sentences and JSON Extended syntax, new functions, such as JSON table functions, improved sorting etc.
In this tutorial,we will explain the process of installing MySQL on Ubuntu 20.04 LTS server.
Also Read -> How to Install Apache Kafka on Ubuntu 20 04
Step 1: install mysql 8
you can install MySQL server 8.0 from the Ubuntu repository using apt directly.
before that update packages using apt.
sudo apt update sudo apt install mysql-server
once mysql is installed, you can check service status with below 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-04-20 07:25:21 UTC; 11min ago Main PID: 3447 (mysqld) Status: "Server is operational" Tasks: 37 (limit: 1685)
You can also change startup settings of MySQL after system boot-up through the systemctl command.
sudo systemctl enable mysql
Now you have installed MySQL 8 on your Ubuntu 20.04 system.
Also Read -> How to Install ReactJS on Ubuntu 20 04
Step 2: Secure mysql server
It provides mysql_secure_installation script that makes it easy to secure access to your MySQL database.you can run that script from terminal.
You will be asked to opt VALIDATE PASSWORD PLUGIN or not. This enables we to test MySQL Passwords and improve security.
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
Y if you want to set up the validate password plugin or any other key if you don't want to install it.
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
Next, it prompts to set a password for the MySQL root user
New password: Re-enter new password:
if you select validate password plugin, the plugin will display the strength of your new password.select Y to conform the 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
Next it prompts to Remove anonymous users,disallow root login remotely and remove to test database etc. you can press Y to all prompts.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. 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. 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!
Also Read -> How to Install Wine on Ubuntu 20 04
Step 3: Mysql login
you can use the the MySQL client tool installed with the MySQL server in order to connect to your server from the command-line.
On MySQL 8.0, the root user gets authenticated using auth_socket plugin by default whereas the auth_socket plugin is used to authenticate users that connect from localhost via the Unix socket. This implies that you can’t authenticate as root by providing a password.
now you run the following command to log in to the MySQL server as root user
sudo mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu) 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>
Step 4: create database
you can create database and user and set previliges to that user on database.
root@ubuntu20:~# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu) 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> create database lampblogsdb; Query OK, 1 row affected (0.04 sec) mysql> create user lampblogs@localhost identified by 'strong_password'; Query OK, 0 rows affected (0.03 sec) mysql> grant all privileges on lampblogsdb.* to lampblogs@localhost; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye
If you want to login to your MySQL server as root using an external program such as phpMyAdmin, then you have to change the authentication method from auth_socket to mysql_native_password.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strong_password'; mysql> FLUSH PRIVILEGES; mysql> exit Bye
Also Read -> How to Install Apache Maven on Ubuntu 20 04
Step 5: Enable mysql remote access (optional)
By default, In MySQL database server remote access is disabled for security reason. To enable remote connections of MySQL Server, we have to change bind-address in MySQL configuration file.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the line 'bind-address = 127.0.0.1'and change it as below.
bind-address = 0.0.0.0
Now restart mysql service to effect the changes.
sudo systemctl restart mysql
Now Ubuntu Server will allow remote access to the MySQL Server, But still, you need to configure MySQL users to allow access from any host.when you create a MySQL user, you should allow access from any host.
mysql> CREATE USER 'lampblogs'@'%' IDENTIFIED WITH mysql_native_password BY 'strong_password';
if you want to allow specific ip address, then
mysql> CREATE USER 'lampblogs'@'192.168.1.200' IDENTIFIED WITH mysql_native_password BY 'strong_password';
if firewall service is running on your system, then you can allow remote connections through the firewall.ufw is default tool in ubuntu.To allow access from any ip address on internet, run following command.
sudo ufw allow 3306/tcp
To allow access from specific ip address
sudo ufw allow from 10.8.0.5 to any port 3306
That's it. now you have successfully installed mysql 8 and created database in ubuntu 20.04 system.