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

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/