文字コードの変更(utf8mb4)
以下の手順で文字コード変更した後は、今後新規作成するデータベースの文字コード設定が反映される。
※既に存在するデータベースの文字コードは変更されないことに注意。
- 設定ファイル【/etc/my.cnf/mariadb-server.cnf】
[mariadb]
character-set-server = utf8mb4- 設定ファイル【/etc/my.cnf/client.cnf】
[client-mariadb]
default-character-set = utfmb4- 文字コードの確認
SHOW VARIABLES LIKE 'char%';
SHOW VARIABLES LIKE 'coll%';インストール直後の場合
MariaDB [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.001 sec)文字コード変更後
MariaDB [(none)]> 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/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'coll%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)テーブル関連コマンド
- テーブルのカラム表示
MariaDB [test]> SHOW COLUMNS FROM table_01;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| value | text | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.002 sec)
MariaDB [test]> DESCRIBE table_01;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| value | text | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.018 sec)ユーザ関連のコマンド
ユーザの表示
MariaDB [test]> SELECT user,host FROM mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
3 rows in set (0.013 sec)ユーザの作成
MariaDB [test]> CREATE USER `user01`@`localhost` IDENTIFIED BY 'password01';
Query OK, 0 rows affected (0.002 sec)ユーザ権限の表示
MariaDB [test]> SHOW GRANTS FOR `root`@`localhost`;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.003 sec)ユーザに権限の付与
MariaDB [test]> SHOW GRANTS FOR `user01`@`localhost`;
+---------------------------------------------------------------------------------------------------------------+
| Grants for user01@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`localhost` IDENTIFIED BY PASSWORD '*8B4C9476A1542A79AA4AE86926B0344C4C4E2082' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> GRANT ALL PRIVILEGES ON test.* TO `user01`@`localhost`;
Query OK, 0 rows affected (0.003 sec)
MariaDB [test]> SHOW GRANTS FOR `user01`@`localhost`;
+---------------------------------------------------------------------------------------------------------------+
| Grants for user01@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`localhost` IDENTIFIED BY PASSWORD '*8B4C9476A1542A79AA4AE86926B0344C4C4E2082' |
| GRANT ALL PRIVILEGES ON `test`.* TO `user01`@`localhost` |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)ユーザから権限の消去
MariaDB [test]> REVOKE ALL ON test.* FROM `user01`@`localhost`;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SHOW GRANTS FOR `user01`@`localhost`;
+---------------------------------------------------------------------------------------------------------------+
| Grants for user01@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`localhost` IDENTIFIED BY PASSWORD '*8B4C9476A1542A79AA4AE86926B0344C4C4E2082' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [test]>ユーザのパスワード変更
MariaDB [(none)]> SET PASSWORD FOR `user01`@`localhost` = password('password01');
Query OK, 0 rows affected (0.013 sec)ユーザの削除
MariaDB [test]> DROP USER `user01`@`localhost`;
Query OK, 0 rows affected (0.003 sec)【参考URL】
MariaDB のユーザーのパスワードを変更する
