MySQL8.0.20 外部接続について

Question

■質問内容

レンタルサーバを借りて、DBサーバの構築を行っています。
構築したDBに外部から接続が出来ず、どなたか知恵をお貸し頂ければ助かります。
設定でおかしい箇所や、抜けている箇所、試したらいい事、参考出来そうなサイト等、教えて頂ければ助かります。
一通り試して、手詰まり状態となっています。。

■環境

CentOS7.4 、MySQL8.0.20 、借りているサーバはさくらのレンタルサーバでレンタルしています。
自身のPC(Windows10)で使用していたMySQLのバックアップを取得し、バックアップを使用してDBサーバに移行しています。

■試したこと

①外部接続用のユーザ作成
user：admin
host : %
plugin:mysql_native_passward
※hostは特定のIPに絞ったほうが良いと思いますが、現状’%’にしています。

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

②/etc/my.cnf の設定変更

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


#20200507
#bind-address = 127.0.0.1
#bind-address = *********自身のIPの為、*にしています。
~

bind-addressをすべてコメントアウト
default-authentication-plugin=mysql_native_passwordに変更。

③firewall設定
mysql 使用ポート確認

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.02 sec)
コード

サービスで使用しているポート確認
mysql.xml

[root@tk2-229-24163 ~]# cat /usr/lib/firewalld/services/mysql.xml
<?xml version="1.0" encoding="utf-8"?>
<service>
  <short>MySQL</short>
  <description>MySQL Database Server</description>
  <port protocol="tcp" port="3306"/>
</service>

firewallでサービス許可

[root@tk2-229-24163 ~]# firewall-cmd --list-all --zone=public
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0
  sources:
  services: dhcpv6-client http https mysql ssh
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

【外部からmysqlにアクセスする際のコマンド】
mysql -h <DBサーバIP>　-u admin -p
【エラー内容】
ERROR 2003 (HY000): Can't connect to MySQL server on 'DBサーバIP' (10060)

よろしくお願い致します。

Comments

[sazi]

bind-addressをコメントアウトではなく、個別のアドレス指定した場合を確認されましたか？

[yabe0919]

個別のアドレスを指定した場合も確認致しました。

[sazi]

修正後にMySQLを再起動して確認していますか？

[IkumiMashiba]

環境の構成は
1)internet --- [CentOS7.4 レンタルサーバ Mysql]
2)internet --- [CentOS7.4 レンタルサーバ] --- [Sakura DB Server Mysql]

どちらでしょうか？

[yabe0919]

@sazi 様
はい、修正後にMySQLの再起動を行っております。

@IkumiMashiba様
構成は１かと思います。
【自身の自宅PC】----【Centos7.4 MySQL】
自宅のPCから直接レンタルサーバ上のDBにアクセスを行っています。
踏み台は挟んでおりません。

[yabe0919]

@sazi様、IkumiMashiba様
本件ですが、新規にDBサーバを構築したところ無事外部から接続することが出来ました。
前回との差異は、MysqLを構築する際にバックアップファイルからデータを移行しなかった点となります。
目的としての、【外部からMySQLに接続すること】は達成することが出来ましたので、本件クローズさせていただきます。
※bind-addressはコメントアウトした状態でも、問題無く通信致しました。
お二方、ご協力ありがとうございました。

[IkumiMashiba]

```shell
# mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

```

当てずっぽうですが、他のmy.cnfの存在の可能性

[yabe0919]

他の設定ファイルの影響も考えたのですが、
今回は、Windowsで構築したDBのダンプファイルをLinuxで復元したことが原因の様でした。
お時間頂きありがとうございました！！

Answer 1

以下参考
データベースに接続しようとするとエラーが出て接続に失敗します。

[mysql]mySQLへ他ホストから接続できない

AWSで、RDSに接続できない

Answer 2

【原因】
Windows上で構築したMySQLのバックアップから、Linux上で構築したMySQLに復元したことが原因？と思われる。
【解決策】
復元しないで、テーブル定義などのSQLのみを抽出し手動で復元する。

Comments

[sazi]

多分、セキュリティグループ

[yabe0919]

確かに貼ってくださったリンクの様な状況でした。
詳しくないんですが、復元すると自宅PCのセキュリティグループに登録された？
そのあたりも今度調べてみようかと思います！
お時間頂きありがとうございました！！

Answer 3

#bind-address = *********自身のIPの為、*にしています。

先頭の # はコメント行になりますよ

Comments

[yabe0919]

ご指摘有難うございます。
bind-addressをコメント行にすると、接続IPの指定をしなくなるという記事をみかけたため、
記載したcnfではコメント行にしています。
コメントを外した状態でも試しましたが、解決には至っていない状況です。