CentOS8でのmysqlインストール（過去記事）

インストール

dnf install mysql mysql-server -y
．．．
Complete!

バージョン確認

# mysql --version
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)

起動

 # systemctl start mysqld

自動起動設定

# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

文字コードの確認

utf8mb4で使いたいので確認する

 # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution
​
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)

ユーザーの作成

ユーザー「alice」を作成

mysql> CREATE USER 'alice'@'localhost' IDENTIFIED BY 'alice_pass';

ユーザー一覧

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| alice            | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

データベースの作成

mysql> CREATE DATABASE alice_db;
Query OK, 1 row affected (0.00 sec)

権限の設定

alice_dbの全テーブルに対して全権限を付与

mysql> GRANT ALL ON alice_db.* TO 'alice'@'localhost';
Query OK, 0 rows affected (0.00 sec)

確認

mysql> SHOW GRANTS FOR 'alice'@'localhost';
+-------------------------------------------------------------+
| Grants for alice@localhost                                  |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `alice`@`localhost`                   |
| GRANT ALL PRIVILEGES ON `alice_db`.* TO `alice`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

ユーザーの作成と権限付与

mysql8以前はユーザーの作成と権限の付与を同時に実行できた。
mysql8からはsyntax errorが出るので、ユーザーの作成と権限の付与は構文を分ける必要がある。

mysql8からはsyntax errorが出るので、ユーザーの作成と権限の付与は構文を分ける必要がある。

mysql> GRANT ALL ON alice_db.* TO 'alice'@'localhost' IDENTIFIED BY 'alice_pass';
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 'IDENTI
FIED BY 'alice_pass'' at line 1