別サーバの MySQLにアクセスする設定・ファイアウォール設定、ポート設定、MySQL設定、テーブルへ権限付与の方法
2017/03/11
別サーバへの MySQLにアクセスする設定の方法
MySQLに接続する方法
同じサーバ内に設置した MySQLにアクセスするのは下記のコマンドを入力するだけでアクセスできます。
1 |
# mysql -u root -p password |
【外部サーバからデータベースサーバにアクセスするイメージ】
ですが、別のサーバにある MySQLにアクセスをする場合は、アクセスを受ける側のサーバの
- データベース・テーブルへのアクセス権限設定
- MySQLへのアクセス制限設定
- MySQLが利用するポートの開放設定
- ファイアウォールの制限設定
が適切に行われている必要があります。
いずれか一つでも設定できていなければ、アクセスができません。
別サーバからアクセスして接続を確認
まず最初に、別サーバからアクセスできるか確認をしてみます。
また、MySQLコマンドを実行するには、アクセスをする側(外部サーバ側)にも MySQLが入っている必要があります。
1 |
# mysql -h 192.168.1.1 -u root -p |
ポートを指定する場合は、下記のようになりますね。
1 |
# mysql -h 192.168.1.1 -u root -p -P 3306 |
ちなみに、「mysql」が MySQLに接続するためのコマンドで、「-h 192.168.1.1」は接続する MySQLのホストの IPの指定で、「-u root」は接続するユーザの指定で、「-p」はパスワード認証方式でアクセスする指定で、「-P 3306」は接続するポート番号の指定です。
MySQL接続のエラーが発生
接続コマンドを実行し、MySQLにアクセスできればいいのですが、下記のようにエラーメッセージが出る場合は、何かしらの制限によってアクセスできないということになります。
1 2 3 |
# mysql -h 192.168.1.1 -u root -p -P 3306 Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.1' (110) |
Windowsの XAMPP環境から接続を試してみることもできます。
GRANT文でデータベース、テーブルに権限を付与する
接続できなかった場合は、データベースサーバ側の MySQLの設定状況を確認します。
そのため、データベース側の MySQLに接続して作業を行います。
1 2 3 4 5 6 7 8 9 10 11 12 |
# mysql -u root -p Enter password: mysql> select user, host from mysql.user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | localhost | | user | localhost | +------+-----------------------+ 3 rows in set (0.00 sec) |
上記の内訳は、下記のとおりです。
・root@127.0.0.1
IPアドレス「127.0.0.1(ローカルホスト)」からユーザ名「root」でアクセス可能
・root@localhost
ホスト「localhost(ローカルホスト)」からユーザ名「root」でアクセス可能
・user@localhost
ホスト「localhost(ローカルホスト)」からユーザ名「user」でアクセス可能
この状態では、ローカルでしかアクセスができませんので、下記のように GRANTコマンドを実行し、アクセスできるユーザ権限を付与します。
1 |
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.168.%' IDENTIFIED BY 'password' WITH GRANT OPTION; |
上記の GRANTコマンドは、「ON *.*」の個所で、「*.*」としてあります。
これは、データベースもテーブルも指定しない、ということになります。
外部からアクセスするデータベースを指定する場合は、「ON db_name1.*」のようにアクセスできる「データベース名(db_name1)」を指定します。
また、「TO root@’192.168.%’」の部分では、権限を付与する「ユーザ名(root)」と「接続元(192.168.%)」を指定しています。
ユーザ名はそのままユーザ名を指定するわけですが、接続元は、いろいろな設定方法があります。
・すべてのアクセスを許可する場合は「TO root@’%’」となります。
・IP 192.168.1.2からのみ許可する場合は「TO root@’192.168.1.2’」となります。
・ドメイン example.comからのアクセスを許可数場合は「TO root@’example.com’」となります。
また、上記の例は、「192.168」で始まる IPアドレスの接続元の場合許可する、ということになり、ローカルネットワーク上からは許可する、といったイメージになります。
ちなみに「BY ‘password’」の「password」は、設定するユーザのパスワードを編集します。
上記の権限を付与するコマンドを実行すると、下記のように rootユーザで 192.168.から始まる IPからのアクセスに権限が付与されたことが確認できます。
1 2 3 4 5 6 7 8 9 10 |
mysql> select user, host from mysql.user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | 192.168.% | | | localhost | | user | localhost | +------+-----------------------+ 4 rows in set (0.00 sec) |
MySQLの設定ファイル my.cnfの bind-addressを設定
MySQLの設定ファイル「my.cnf」を確認し、外部からのアクセスが制限されていないか、確認をします。
MySQLの設定ファイルである「my.cnf」開きます。
「my.cnf」がある場所については「MySQLの設定ファイル my.cnfがある場所と読み込み順序」を参照してください。
1 2 3 4 5 6 |
# vi /etc/my.cnf : bind-address = 127.0.0.1 bind-address = 192.168.1.1 : |
「my.cnf」に、外部からのアクセス制限を設定する「bind-address」が指定されている場合は、この制限によって外部からアクセスができない原因になっています。
MySQLのバージョンによっては、デフォルトで「bind-address = 127.0.0.1」が設定されている場合もあり、この場合はローカルからしかアクセスできません。
アクセスする IPが決まっているのであれば、「bind-address = 192.168.1.1」のように IPを指定してアクセスを許可する IPを追加する方法があります。
また、アクセスする元の IPが決まっていないようであれば、「# bind-address = 127.0.0.1」のようにコメントアウトして、アクセス元を制限する処理をなくしてしまう方法もあります。
MySQLのバージョンによっては「bind-address」の設定がないものもありますので、コメントアウトで問題ないでしょう。
「my.cnf」の設定を変更したら、下記のコマンドで MySQLを再起動します。
1 |
# service mysqld restart |
サーバのポート番号、ファイアウォールの設定で MySQLの 3306番を解放
MySQLが利用するポートは「3306番」の確認
MySQLが利用するポートは「3306番」です。
MySQLが利用するポート「3306番」が開いているかを確認し、開いていない場合は利用できるように設定します。
ポートが開いているかどうかを確認するのは、下記のコマンドを実行します。
1 2 |
# netstat -tlpn | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1858/mysqld |
上記のように「3306」番が「LISTEN」になっていると、ポートが開放されていることになります。
ファイアウォールの MySQL接続制限を解除
ファイヤーウォールに MySQLのアクセスが許可されているかを確認します。
ファイヤーウォールの調査は下記の「iptables -L」コマンドで行います。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ftp ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:http ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:https REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain FORWARD (policy ACCEPT) target prot opt source destination REJECT all -- anywhere anywhere reject-with icmp-host-prohibited Chain OUTPUT (policy ACCEPT) target prot opt source destination |
上記の状態では MySQLが設定されていません。
そのため、下記の MySQLが利用する 3306番を解放するコマンドを実行します。
1 |
# iptables -I INPUT 8 -m state --state NEW -p tcp --dport 3306 -j ACCEPT |
このコマンドの「INPUT 8」の「8」は、「8番目」という意味です。
そのため、「ACCEPT tcp」の「7番目」の次に追加するということを指定しています。
「REJECT」の前に入れる必要がありますので、各自の環境に合わせて追加する順番の数値を指定します。
MySQLの設定を追加した後は、その設定を iptablesに保存し、再起動を行います。
保存をしない場合は、OSを再起動すると設定内容がクリアされてしまいます。
1 2 |
# /etc/init.d/iptables save # service iptables restart |
再起動ができた後、改めて「iptables -L」コマンドを実行し、下記の 1行が追加されていることを確認します。
1 |
ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:mysql |
ちなみに、iptablesの再起動を行うと、接続していたコンソール(TeraTermなど)は接続が切れる可能性がありますので、その場合はコンソールを接続しなします。
外部サーバからデータベースサーバへのアクセスを許可する方法のまとめ
外部サーバからデータベースサーバへのアクセスを許可するには、
- データベース・テーブルへのアクセス権限設定
- MySQLへのアクセス制限設定
- MySQLが利用するポートの開放設定
- ファイアウォールの制限設定
の設定を全て利用できるようにする必要があります。
接続ができない場合は、一つずつ設定を確認し、どこで制限がかかってるか、制限がある場合はその設定を解放していきましょう。
この設定ができれば、データベースサーバ側に phpMyAdminや Adminerなどの MySQL管理ツールを入れなくても、ローカルの XAMPP環境の phpMyAdminから直接アクセスすることも可能になります。
GoogleAdwords
GoogleAdwords
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです!
関連記事
-
MySQLの「tinyint(1)」の悲劇 Boolean型になるとは...
MySQLで tinyint(1)を指定すると Boolean型を指定したことになります。意外な誤解があります。
-
MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認
MySQL+CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。
-
MySQLの設定ファイル my.cnfがある場所と読み込み順序
MySQLの設定はmy.cnfファイルに記述されています。my.cnfを設置する場所は複数あり予め決められた順番で読み込み、その順番通りに設定内容が反映されます。
-
MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も
MySQLのタイムゾーンテーブル(mysql.time_zone)の中身を確認し、からの場合は追加する方法を解説。LinuxとWindows・XAMPPとで異なる対応方法についてそれぞれ解説。
-
MySQLで SQLSTATE[42000] Row size too large(> 8126)のエラーが出たときの対処方法
MySQLで入力する文字数が多い場合「SQLSTATE[42000] Row size too large(> 8126)」のエラーが発生することがある。その対処方法の解説を解説。
-
MySQLのInnoDBでUniqueキーは最大767バイト
MySQLの Uniqueキーには InnoDBでは 767バイト、MyISAMでは 1000バイトの最大サイズの制限があります。
-
Insert On Duplicate Key Update構文の使い方
レコードの有無で Insert、Updateを切り替える On Duplicate Key構文の使い方の説明です。
-
MySQLの数値型(int、tinyint、bigint、decimal、number、float)の解説
MySQLの数値型についてテーブル設計のたびに調べているような気がしたので、調べてまとめてみた。数値型には整数型、固定小数点型、浮動小数点型がある。