0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

My SQLサーバー作成とクライアント接続までの設定

Posted at

MY SQLサーバーを立てたときにつまずいたので記録残します。

#mysqlインストール
apt install mysql-server mysql-client

#スタートアップに登録
systemctl enable mysql
systemctl start mysql

#DBとUSER作成
mysql -uroot
CREATE DATABASE [DB NAME] CHARACTER SET UTF8 COLLATE UTF8_BIN;
CREATE USER '[USER NAME]'@'%' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON [DB NAME].* TO '[USER NAME]'@'%';
FLUSH PRIVILEGES;
quit;

#リモート接続できるDBを確認
select User,Host, plugin FROM mysql.user; 
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| rakuten          | %         | caching_sha2_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           |
+------------------+-----------+-----------------------+
#認証方式をパスワードに変更
#auth_socketはログインしているユーザーと同じユーザーでログイン
ALTER USER '[USER NAME]'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

#リモート接続できるDBとUSERを設定
#どこからでもアクセスできるようにする場合はIPアドレスを"%"に設定
grant all privileges on [db_name].* to [user_name]@"[ip_address]" identified by '[password]' with grant option;

#そもそもmysqlの設定で外部接続を許可していない場合
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
抜粋-------------------------------------
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1 ←ここで接続元IPを制限
mysqlx-bind-address     = 127.0.0.1
#
-------------------------------------

#mysqlサービスを再起動
sudo systemctl restart mysql

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?