Publish MySQL externally and log in on Ubuntu

There are many articles like this title, but I wonder if there are few examples on Ubuntu. I didn't go straight, so I decided to write an article.

environment

Version
Host OS Ubuntu Server 20.04.1 LTS
Host side MySQL Ver 8.0.22
Client side MySQL(MariaDB) 10.3.23-MariaDB

Since the client side was using Raspberry Pi this time, I am using mariaDB (Is mysql not included in raspbian?). Since MySQL and mariaDB are almost compatible, I think that there is no problem if you do it between MySQL 8 series.

However, MariaDB 10.3 is equivalent to MySQL 5.7, There is a problem when logging in from 5th to 8th series, so I will explain it below.

It is assumed that MySQL is already installed on the host side and the client side.

[Host side] External disclosure settings

By default, only localhost can be connected, so set the disclosure range. Access /etc/mysql/mysql.conf.d/mysqld.cnf and change the location of bind-address as follows.

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bash:/etc/mysql/mysql.conf.d/mysqld.cnf


bind-address                    = 0.0.0.0
default_authentication_plugin=mysql_native_password #This line is also added when the client side is 5 series. Not required for 8 series.

After completing the settings, restart MySQL.

$ /etc/init.d/mysql restart

[Host side] Create a user for connection

Log in to MySQL and create a user with the following command. Here, admin means the user name, and'%' after @ means any IP address.


CREATE USER 'admin'@'%' IDENTIFIED BY 'P@ssw0rd';
GRANT ALL ON *.* TO 'admin'@'%';

Let's check with the following command.

select user,host from mysql.user;

It is OK if it looks like the following.

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

Login from the remote side

Let's log in from the remote side.

$ mysql -u admin -h [IP address] -p

If the remote side is MySQL8 series, you can log in with this.

Log in to MySQL 5 to 8

If the remote side is 5 series, the following error will occur.

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib/arm-linux-gnueabihf/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

This is because the authentication plug-in has been changed in the 8 series. This time, I would like to solve it by changing the authentication plug-in on the host side to the one used in the 5 series.

Add the following to the MySQL settings. (In the above setting, this is added)

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

:/etc/mysql/mysql.conf.d/mysqld.cnf


default_authentication_plugin=mysql_native_password

After completing the settings, restart MySQL.

$ /etc/init.d/mysql restart

Now, by creating the user in the same way as before, the authentication plugin will be changed. Let's check.

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | mysql_native_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

Reference URL

https://qiita.com/katzueno/items/e735950c7440f232ef27 https://sys-guard.com/post-8501/ https://symfoware.blog.fc2.com/blog-entry-2159.html https://www.s-style.co.jp/blog/2018/05/1807/

Recommended Posts

Publish MySQL externally and log in on Ubuntu
Ubuntu unable to log in
Install docker and docker-compose on ubuntu in the shortest process
Install JDK and JRE on Ubuntu 16.10
Enable Java 8 and Java 11 SDKs on Ubuntu
Installing and using Ansible on Ubuntu 16.04
Install Ubuntu Server 20.04 in VirtualBox on Mac and connect with SSH
Use docker in proxy environment on ubuntu 20.04.1
Put Zabbix in Ubuntu with Docker and monitor Docker on the same host
Try putting Docker in ubuntu on WSL
Build and install Wireshark Development Release (3.3.1) on Ubuntu
Protobuf and gRPC C ++ environment construction on Ubuntu 18.04
Output request and response log in Spring Boot
Build a DHCP and NAT router on Ubuntu 16.04
Put CSV files containing "'" and "" "in MySQL in Ruby 2.3
Install and switch between multiple Javas on Ubuntu
Docker on Ubuntu18.04 on WSL2 and VSCode installation instructions
How to log in automatically when Ubuntu restarts
On ubuntu, scilab, octave and R, sympy, etc.
Upload and download notes in java on S3
Install Ubuntu 20.04 in virtual box on windows10 and build a development environment using docker
Stumble in MySQL 5.5-> 5.7
Install Veeam PN on Ubuntu in IBM Cloud VPC and connect with SSL VPN
tmux on Ubuntu
I tried using YOLO v4 on Ubuntu and ROS
Install rbenv with apt on ubuntu and put ruby
Install Rust in WSL2 Ubuntu environment and build WASM build environment
Talk about introducing Ubuntu 20.04 on Windows 10 and text editor
Build TensorFlow 2.3.1 from source on Ubuntu20.04 LTS and create a shared library in C ++ language