ローカルエリアネットワーク内にあるデータベースサーバーへ別端末からアクセス出来なくて困ってます。
やりたいこと
端末からmysql -h 192.168.100.160 -P 3306 -u dbuser -p
でデータベースへアクセスできるようにしたいです。
環境
データベースサーバー:192.168.100.160
接続したい端末:192.168.100.16
各設定
MariaDBの出力
console
1MariaDB [(none)]> show variables like 'port'; 2+---------------+-------+ 3| Variable_name | Value | 4+---------------+-------+ 5| port | 3306 | 6+---------------+-------+ 71 row in set (0.002 sec) 8 9MariaDB [(none)]> select user, host from mysql.user; 10+--------+---------------+ 11| user | host | 12+--------+---------------+ 13| dbuser | 192.168.100.% | 14| root | localhost | 15+--------+---------------+ 162 rows in set (0.001 sec) 17
ポート開放の設定
console
1ryou@dbserver:~$ sudo ufw status 2Status: active 3 4To Action From 5-- ------ ---- 622 ALLOW 192.168.100.0/24 73306 ALLOW 192.168.100.0/24 8
この状態でSSHのアクセスは出来ているので問題は無いと思います…
追記
/etc/mysql/mariadb.conf.d/50-server.cnf に設定ファイルらしきものを発見して、回答いただいた記述を追加しました。
bind-address = 127.0.0.1は元々あったのでその下に一行追加した感じです。
file
1# 2# These groups are read by MariaDB server. 3# Use it for options that only the server (but not clients) should see 4# 5# See the examples of server my.cnf files in /usr/share/mysql 6 7# this is read by the standalone daemon and embedded servers 8[server] 9 10# this is only for the mysqld standalone daemon 11[mysqld] 12 13# 14# * Basic Settings 15# 16user = mysql 17pid-file = /run/mysqld/mysqld.pid 18socket = /run/mysqld/mysqld.sock 19port = 3306 20basedir = /usr 21datadir = /var/lib/mysql 22tmpdir = /tmp 23lc-messages-dir = /usr/share/mysql 24#skip-external-locking 25 26# Instead of skip-networking the default is now to listen only on 27# localhost which is more compatible and is not less secure. 28bind-address = 127.0.0.1 29bind-address = 192.168.100.1 30 31# 32# * Fine Tuning 33# 34#key_buffer_size = 16M 35#max_allowed_packet = 16M 36#thread_stack = 192K 37#thread_cache_size = 8 38# This replaces the startup script and checks MyISAM tables if needed 39# the first time they are touched 40#myisam_recover_options = BACKUP 41#max_connections = 100 42#table_cache = 64 43#thread_concurrency = 10 44 45# 46# * Query Cache Configuration 47# 48#query_cache_limit = 1M 49query_cache_size = 16M 50 51# 52# * Logging and Replication 53# 54# Both location gets rotated by the cronjob. 55# Be aware that this log type is a performance killer. 56# As of 5.1 you can enable the log at runtime! 57#general_log_file = /var/log/mysql/mysql.log 58#general_log = 1 59# 60# Error log - should be very few entries. 61# 62log_error = /var/log/mysql/error.log 63# 64# Enable the slow query log to see queries with especially long duration 65#slow_query_log_file = /var/log/mysql/mariadb-slow.log 66#long_query_time = 10 67#log_slow_rate_limit = 1000 68#log_slow_verbosity = query_plan 69#log-queries-not-using-indexes 70# 71# The following can be used as easy to replay backup logs or for replication. 72# note: if you are setting up a replication slave, see README.Debian about 73# other settings you may need to change. 74#server-id = 1 75#log_bin = /var/log/mysql/mysql-bin.log 76expire_logs_days = 10 77#max_binlog_size = 100M 78#binlog_do_db = include_database_name 79#binlog_ignore_db = exclude_database_name 80 81# 82# * Security Features 83# 84# Read the manual, too, if you want chroot! 85#chroot = /var/lib/mysql/ 86# 87# For generating SSL certificates you can use for example the GUI tool "tinyca". 88# 89#ssl-ca = /etc/mysql/cacert.pem 90#ssl-cert = /etc/mysql/server-cert.pem 91#ssl-key = /etc/mysql/server-key.pem 92# 93# Accept only connections using the latest and most secure TLS protocol version. 94# ..when MariaDB is compiled with OpenSSL: 95#ssl-cipher = TLSv1.2 96# ..when MariaDB is compiled with YaSSL (default in Debian): 97#ssl = on 98 99# 100# * Character sets 101# 102# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full 103# utf8 4-byte character set. See also client.cnf 104# 105character-set-server = utf8mb4 106collation-server = utf8mb4_general_ci 107 108# 109# * InnoDB 110# 111# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. 112# Read the manual for more InnoDB related options. There are many! 113 114# 115# * Unix socket authentication plugin is built-in since 10.0.22-6 116# 117# Needed so the root database user can authenticate without a password but 118# only when running as the unix root user. 119# 120# Also available for other users if required. 121# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ 122 123# this is only for embedded server 124[embedded] 125 126# This group is only read by MariaDB servers, not by MySQL. 127# If you use the same .cnf file for MySQL and MariaDB, 128# you can put MariaDB-only options here 129[mariadb] 130 131# This group is only read by MariaDB-10.3 servers. 132# If you use the same .cnf file for MariaDB of different versions, 133# use this group for options that older servers don't understand 134[mariadb-10.3]
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。