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.
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.
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
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 |
+------------------+-----------+
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.
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 |
+------------------+-----------+-----------------------+
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