【MySQL】データベースへの接続・作成【基本】

こんにちは、あっきー(IwswAkht)です。

本日はデータベースについてです。
中でもシェアの高いMySQLについての記事を書いて行こうと思います。

近年はフレームワークでの開発が主流でプログラミングを始めたての方などでフレームワークしかさわってないなんてこともあるのではないでしょうか？

フレームワークは非常に便利でデータベース言語がわからなくてもWebアプリの開発をすることができます。

便利ではある反面、Webアプリは結局のところデータベースありきだと僕は思っています。フレームワークのように便利なメソッドでデータベースを操作できるのは素晴らしいですが、このメソッドをつかった時に裏側ではどんなSQLが実行されたからこの結果が取得できたということを理解しておくことは後々の理解に非常に役が立ちます。

今回は中でもデータベースへの接続やデータベースの作成など基本的な部分について解説していきます。

基本をおさえることが結局のところ良い学習効率につながります。それではやっていきましょう。

MySQLへの接続
データベースの作成・削除
MySQLの初期構成
さいごに

MySQLへの接続

MySQLに接続するための基本的な方法を確認します。

MySQLに接続

ターミナル

$ mysql -u root -p

1

$ mysql -u root -p
MySQLを終了

ターミナル

$ exit;

1

$ exit;

MySQLに接続できない時の対処

新規でMySQLを導入した時に接続できないというケースのほとんどは単にMySQLが起動していないことがほとんどです。

ターミナルに以下のエラーが表示されているかと思います。

ターミナル

$ ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

1	$ ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

その場合は以下のコマンドでMySQLを起動してあげましょう。

ターミナル

$ mysql.server start

1	$ mysql.server start

逆にMySQLを終了する場合は以下のコマンドで停止できます。

ターミナル

$ mysql.server stop

1	$ mysql.server stop

パスワードを設定する

MySQLを初期インストールした状態では、rootユーザーにパスワードが設定されていません。このユーザーはMySQLのすべての操作が行うことができるので、パスワードが未設定だとセキュリティ的に危険です。

ローカルの開発環境だからパスワードなくてもいいでしょ！って意見もあるかと思いますが、万が一そのまま忘れてアプリケーションを公開なんてしてしまった日には完全に死亡フラグです。乗っ取られて深刻なセキュリティ障害が発生してしまうかもしれないです。

なので、MySQLを入れたらまずはパスワードを設定するということを意識するためにも最初の段階でやり方を理解して癖づけておきましょう。

MySQLでのパスワードの設定方法はバージョンによってはエラーになったりするので、まず初めにバージョンを確認してみましょう。

ターミナル

$ mysql --version

#結果
mysql  Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

$ mysql --version

#結果

mysql Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

mysql Ver 5.6.7より低い場合は以下の方法で設定することができます。

ターミナル

# MySQLに接続
$ mysql -u root

#パスワードの設定
mysql > update mysql.user set password=password('任意のパスワード') where user = 'root';
mysql > flush privileges;
#結果
Query OK, 0 rows affected (0.00 sec)

# MySQLに接続

$ mysql -u root

#パスワードの設定

mysql > update mysql.user set password=password('任意のパスワード') where user = 'root';

mysql > flush privileges;

#結果

Query OK, 0 rows affected (0.00 sec)

mysql Ver 5.6.7以降ですと上記のコマンドを実行すると以下のようなエラー分が表示されます。

ターミナル

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('設定したパスワード')' at line 1

1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('設定したパスワード')' at line 1

原因としてはmysql Ver 5.6.7以降ではpassword()関数が廃止されたことでそもそも文法が存在しないからです。

ですので、新しいバージョンでのMySQLでは以下の方法でパスワードを設定していきましょう。

ターミナル

# MySQLに接続
$ mysql -u root

#パスワードの設定
mysql > use mysql;
mysql > alter user 'root'@'localhost' identified by '任意のパスワード';
#結果
Query OK, 0 rows affected (0.00 sec)

# MySQLに接続

$ mysql -u root

#パスワードの設定

mysql > use mysql;

mysql > alter user 'root'@'localhost' identified by '任意のパスワード';

#結果

Query OK, 0 rows affected (0.00 sec)

これで設定が完了します。

試しにexitでいったんmysqlクライアントを終了して、再度パスワードなしのrootユーザでアクセスしてみてください。以下のような結果になることが確認できます。

ターミナル

$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

1 2	$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

接続ができないことが確認できますね。

ユーザの作成

ここまで管理者権限であるrootユーザで操作を行ってきました。ですが実際の開発や運用では常に管理者権限を利用することはセキュリティ的にあまりよろしいことではないです。

また、rootユーザはMySQLの全ての操作ができてしまうためユーザによって操作の制限などをかけたい場合もあるでしょう。

そこでここでは、作成した特定のデータベースへの操作をするためのユーザを作成する方法をみてみましょう。

こちらもパスワードを設定すると同様バージョンによってエラーが発生しますので2パターンでみていきましょう。

mysql Ver 5.6.7より低い場合は以下の方法で設定することができます。

ターミナル

mysql > grant all privileges on データベース名.* to '任意のユーザ名'@localhost identified by '任意のパスワード';

1	mysql > grant all privileges on データベース名.* to '任意のユーザ名'@localhost identified by '任意のパスワード';

mysql Ver 5.6.7以降ですと上記のコマンドを実行すると以下のようなエラー分が表示されます。

ターミナル

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '設定したパスワード'' at line 1

1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '設定したパスワード'' at line 1

mysql Ver 5.6.7以降では以下の方法で設定しましょう。

ターミナル

mysql> create user '任意のユーザ名'@'localhost' identified by '任意のパスワード';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on データベース名.* to '設定したユーザ名'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> create user '任意のユーザ名'@'localhost' identified by '任意のパスワード';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on データベース名.* to '設定したユーザ名'@'localhost';

Query OK, 0 rows affected (0.00 sec)

試しにexitでいったんMySQLクライアントを終了して、操作の権限が付与されたか確認してみましょう。

ターミナル

$ mysql -u 設定したユーザ名 -p
Enter password: 設定したパスワード

mysql> use mysql;
ERROR 1044 (42000): Access denied for user '設定したユーザ名'@'localhost' to database 'mysql'

$ mysql -u 設定したユーザ名 -p

Enter password: 設定したパスワード

mysql> use mysql;

ERROR 1044 (42000): Access denied for user '設定したユーザ名'@'localhost' to database 'mysql'

MySQLの初期構成で解説しますが、mysqlは管理者であるrootユーザが操作できるデータベースです。新規で作成したユーザではAccess deniedと接続が拒否されたことで権限が設定されたことが確認できますね。

少し複雑なコマンドですので解説もしますね。

all privileges
ユーザの操作の権限レベルを設定しています。
全権限・作成のみ・更新のみなどの操作レベルを設定できます(公式ページ)
データベース名.*
データベース名.テーブル名の構成で設定する
ここでは.*なので指定したデータベース内の全テーブルですが、特定のテーブル名を指定することでより詳細に設定することもできます
'任意のユーザ名'@'localhost'
作成したユーザ名とURLのホスト名で構成します。
開発環境ではlocalhostですが本番ではexample.comのように実際のホスト名を指定します。
identified by '任意のパスワード'
mysqlクライアントを起動するさいのパスワードをここで設定しています

データベースの作成・削除

MySQLに繋ぐ方法が理解できましたら実際にデータベースを作成して削除する流れを確認していきます。ここではsampleというデータベースを作成・削除してみます。

MySQLへの接続の手順でmysqlクライアントを起動します
データベースを作成する

ターミナル

mysql> create database sample;

1

mysql> create database sample;

データベースを確認する

ターミナル

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sample |

| sys |

+--------------------+

sampleが作成されていることが確認できます。

データベースを削除する

ターミナル

mysql> drop database sample;

1

mysql> drop database sample;

データベースを確認する

ターミナル

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+