MySQL 8 Installation on Ubuntu 20.04 LTS

 Recently I installed MySQL on Ubuntu 20.04 LTS and tested.

Initially, it planned to install MariaDB. There was no problem with DB installation and remote access, but there was a problem with ODBC setup.

After a long time of googling, I discovered that there was a problem setting up MariaDB ODBC in Ubuntu 20.04. It will probably be resolved after some time, but I decided to reinstall MySQL instead of MariaDB because we need to proceed with the test urgently.

I installed Ubuntu using ubutu-20.04.1-live-server-amd64.iso image that does not support GUI, and I will omit the information about Ubuntu 20.04 installation.


Delete Previous Version

If you want to erase MariaDB and rephrase MySQL like me, you should delete it cleanly. The "apt remove" command is not a good method because it leaves a set value. It is recommended that you completely delete the set value with the "apt purge" command.


Install MySQL

Installing MySQL is very simple.

Install MySQL Server and client tools

apt-get update
apt install build-essential libssl-dev net-tools
apt-get install mysql-server mysql-client

Setup MySQL Server

I will make MySQL accessible from remote location. However, root users will not be allowed remote access for security reasons. 

I will create a test database and a user and try to remote access the test database using this account.


Initial setup using mysql_secure_installation

I will use the mysql_secure_installation command to proceed with the initial setup of MySQL.

<Validate Password option>

If you choose whether to set "Validate Password", all MySQL users must have a password that meets the selected policy, but if you select the strongest policy level 2, as shown in the picture below, the password must be at least 8 characters long and a mixture of uppercase, lowercase, numbers, and special characters.

If you use it for production, not for development, it is recommended that you select Y. I will proceed with Y.

<Password validation policy>

The following menu sets how strongly you want your password to be set. For production, it is recommended to set to 2 (STRONG. I will proceed with 1 (MEDIUM) because I will use it for development.

Now, let's set the password for the root. You can set it to match the password strength selected above. If the password strength condition is met, the strength of password value is 100 as shown in the figure below.

<setup root password>

The following is whether or not an anonymous user account is enabled or disabled. It is advantageous for security to delete.
Also, it is whether or not root user is allowed remote access, I will disallow it.



Lastly, I will delete the test database and make a new one to use. In addition, selecting Y in Reload privacy tables now? to apply all the above work ends the initial setup task.


Setup MySQL using mysql command

Now use the mysql command to connect directly to MySQL server and complete setup tasks. Enter the password for the root you just set up to access the locally installed MySQL server.


First check the MySQL verson like this. I've installed Ver 8.0.22 MySQL!

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.22-0ubuntu0.20.04.3 |
+-------------------------+
1 row in set (0.00 sec)

Grant the root user privilege to do all the work.

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'Root1234!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now create a study database and study_user user for the test. Please note that you create both users for 'localhost' and for '%' with remote access.

mysql> CREATE DATABASE study_db default CHARACTER SET UTF8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> CREATE USER 'study_user'@'localhost' identified by 'Study1234!';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'study_user'@'%' identified by 'Study1234!';
Query OK, 0 rows affected (0.00 sec)

The study_user user is now authorized to access the study_db and perform all tasks.

mysql> GRANT ALL PRIVILEGES ON study_db.* TO 'study_user'@'localhost' WITH  GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON study_db.* TO 'study_user'@'%' WITH  GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

You can now leave the root user and connect back to the study_user user account to create a table and enter data.

root@ubuntuserver:~# mysql -ustudy_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> use study_db;
Database changed
mysql> CREATE TABLE professor ( _id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, belong VARCHAR(12) DEFAULT 'FOO', phone VARCHAR(12) ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

You can see that the professor table is well made.

Setup configuration file

Most of the work is done, but remote access is not yet available. MySQL has the initial setting that prevents remote access. Finally, you can modify this setting value and restart the mysql service.

If mysql checks the current service status using the netstat command before modifying the settings, it is as follows.

root@ubuntuserver:~# netstat -nltp|grep mysqld
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      9136/mysqld
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      9136/mysqld
It can be seen that it only allows connections from the local host.


Most MySQL-related documents use the /etc/my.cnf file. However, if MySQL 8.0 is installed on Ubuntu 20.04, the file /etc/mysql/mysql.conf.d/mysqld.cnf must be modified.

Locate the next line from the file. 

bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1

And local ip (127.0.0.1) 0.0.0.0 modifications in the value. If your specific only to allow access to ip ip can use the address.
Allows unrestricted access from anywhere to 0.0.0.0.

bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

Then restart the mysql service, then run the netstat command again.

root@ubuntuserver:~# systemctl restart mysql
root@ubuntuserver:~# netstat -nltp|grep mysqld
tcp        0      0 0.0.0.0:33060           0.0.0.0:*               LISTEN      12468/mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      12468/mysqld

You can now see that any remote host is accessible.

Tip : Finally, check Ubuntu Firewall ufw. ufw users should open the 3306 port for external access.

Connect MySQL from remote host

Let's now connect the study_db database just created on the remote host using the study_user account. I will use MySQL Workbench program to connect.

<MySQL workbench connection screen>

And as shown in the following figure, you can successfully connect using the study_user account on the remote computer. And you can also see that there is only study_db visible in schema.



MySQL ODBC Setup

Next, we will install ODBC drivers. The ODBC driver will be installed on the local Ubuntu server. Of course, it can be installed on a remote computer. The first thing to do is download the ODBC driver from the MySQL site.

Select the Ubuntu operating system and version 20.04 from the official MySQL site at https://dev.mysql.com/downloads/mysql).



The following two files are downloaded and stored on Ubuntu server.

  • mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb
  • mysql-connector-odbc_8.0.22-1ubuntu20.04_amd64.deb


Install unixodbc

First, install unixodbc and unixodbc-dev.

apt-get install unixodbc unixodbc-dev


Install mysql odbc drivers

Among the packages downloaded earlier, install "mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb" first, and then install "mysql-connector-odbc_8.0.22-1ubuntu20.04_amd64.deb"

root@ubuntusrv:/usr/local/src# dpkg -i mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb 
Selecting previously unselected package mysql-community-client-plugins.
(Reading database ... 126920 files and directories currently installed.)
Preparing to unpack mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb ...
Unpacking mysql-community-client-plugins (8.0.22-1ubuntu20.04) ...
Setting up mysql-community-client-plugins (8.0.22-1ubuntu20.04) ...
root@ubuntusrv:/usr/local/src# dpkg -i mysql-connector-odbc_8.0.22-1ubuntu20.04_amd64.deb 
(Reading database ... 126926 files and directories currently installed.)
Preparing to unpack mysql-connector-odbc_8.0.22-1ubuntu20.04_amd64.deb ...
Unpacking mysql-connector-odbc:amd64 (8.0.22-1ubuntu20.04) over (8.0.22-1ubuntu20.04) ...
Setting up mysql-connector-odbc:amd64 (8.0.22-1ubuntu20.04) ...
Registering Unicode driver from in file
Success: Usage count is 1
Registering ANSI driver from in file
Success: Usage count is 1


Config odbc.ini

Installing the above two packages automatically creates MySQL related entries in the /etc/odbcinst file.

root@ubuntusrv:/usr/local/src# cat /etc/odbcinst.ini
[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
UsageCount=1

If there is no particular reason, use unicode driver. Now open the odbc.ini file to access study_db.

root@ubuntusrv:/usr/local/src# cat /etc/odbc.ini
[study_mysql]
Description     = connect to study_db
Driver          = MySQL ODBC 8.0 Unicode Driver
SERVER          = 127.0.0.1
PORT            = 3306
DATABASE        = study_db
User            = study_user
Password        = study

Test the connection with the isql command.

root@ubuntusrv:/usr/local/src# isql -v study_mysql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from professor;
+-----------+---------------------------------+-------------+-------------+
| _id       | name                            | belong      | phone       |
+-----------+---------------------------------+-------------+-------------+
| 1         | ???                          ...| IDE         | 01112345678 |
| 2         | ???                          ...| MSE         | 01121342443 |
| 3         | ???                          ...| ESE         | 01123424343 |
| 256       | ???                          ...| IME         | 01134343222 |
| 257       | ???                          ...| IDE         | 01123432432 |
+-----------+---------------------------------+-------------+-------------+
SQLRowCount returns 5
5 rows fetched

Successful connection!

Wrapping up

With MySQL upgraded to 8.X, some of the settings have changed. I hope this article will help those who want to install MySQL on Ubuntu Server 20.04.


댓글

댓글 쓰기

이 블로그의 인기 게시물

Connecting to SQL Server on Raspberry Pi

이미지
 Recently, Microsoft provides ODBC client S/W for Linux that can connect to its MSSQL. This Linux client S/W can be used in almost all distributions such as CentOS, RedHat, Ubuntu, Debian, and SUSE. However, all of these client modules for MS SQL are for x86 (amd64). Therefore, it cannot be used in Raspberry Pi (Debian distribution) and NVidia Jetson series (Ubuntu distribution) using ARM CPU. Therefore, you should use FreeTDS modules previously provided by the open source community. The following is an introduction to freetds.org/. F reeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. T echnically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients. It supports many different flavors of the protocol and three APIs to access it. Additionally FreeTDS works with other software such as Perl and PHP, provi...
자세한 내용 보기

Making VoIP Phone Using Raspberry Pi

이미지
 Since I'm engaged in VoIP-related job, I often do things other than SIP (Session Initiation Protocol) devices. In some cases, Android or IOS-based SIP terminal apps are made, and in some cases, hardware phones sold in the market are tested. Sometimes I have inquiries regarding the production of special purpose SIP devices. One of the recent proposals was to develop a telephone for the elderly living alone or in the home of a woman living alone. This phone is not a phone that usually makes a call by pressing a phone button, but requires a function that detects an emergency situation and automatically makes a call when an emergency situation occurred. When the embedded voice recognition device recognizes an urgent sentence, it transmits a signal to the phone, and the phone automatically calls the rescue center to connect with the rescuer. <System configuration requested> The requested phone has the following features. There is no need to connect with a typi...
자세한 내용 보기

MQTT - Mosquitto MQTT Broker setup on the Ubuntu 20.04

이미지
 I quoted a lot from " How to Install and Secure the Mosquito MQTT Messaging Broker on Ubuntu 18.04 ". MQTT is a machine-to-machine messaging protocol, designed to provide lightweight publish/subscribe communication to “Internet of Things” devices. It is commonly used for geo-tracking fleets of vehicles, home automation, environmental sensor networks, and utility-scale data collection. Mosquitto is a popular MQTT server (or broker, in MQTT parlance) that has great community support and is easy to install and configure.  MQTT is often thought of only as IoT platform and applied to devices such as Arduino and many sensors. However, Message Queue (MQ) is a protocol that has long been used to deliver asynchronous messages.  It can be applied to various applications for asynchronous message delivery. The Public, Subscribe model for a particular topic can also be useful for implementing chat rooms, etc. There are rumors that Facebook's messenger actually used Mosquito.(This is ...
자세한 내용 보기