MySQLのセットアップ
はじめに
データベース管理の勉強のため、MySQLをインストールしてみました。
そのときのセットアップログです。
環境
パソコン ： 普通のデスクトップパソコン(自作)
ＯＳ ： Ubuntu 20.04
手順
１．aptを使ってMySQLをインストールする
sudo apt install mysql-server mysql-client
２．バージョンを確認する
mysql --version
私の環境では、↓のような感じでした。
mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
３．MySQLサービスの動作を確認する
sudo service mysql status
↓こんな感じに表示されればOK
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-04-10 21:53:35 JST; 4min 0s ago
Main PID: 8403 (mysqld)
Status: "Server is operational"
Tasks: 37 (limit: 9123)
Memory: 332.8M
CGroup: /system.slice/mysql.service
mq8403 /usr/sbin/mysqld
4月 10 21:53:34 kyohei-server systemd[1]: Starting MySQL Community Server...
4月 10 21:53:35 kyohei-server systemd[1]: Started MySQL Community Server.
４．セキュリティの設定
sudo mysql_secure_installation
↓のように設定しました。
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
５．rootユーザーで初期設定をする
sudo mysql -u root
↓のように表示される
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 'validate_password%';
↓のように表示される。
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.01 sec)
mysql> CREATE USER 'kyohei'@'localhost' IDENTIFIED BY 'your_password';
Query OK, 0 rows affected (0.02 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| kyohei | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR 'kyohei'@'localhost';
+--------------------------------------------+
| Grants for kyohei@localhost |
+--------------------------------------------+
| GRANT USAGE ON . TO kyohei@localhost |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> GRANT all ON *.* TO 'kyohei'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'kyohei'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for kyohei@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO kyohei@localhost |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON . TO kyohei@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
データベースを使用してみる
データベースを表示するには↓のコマンド。
SHOW DATABASES;
CUI操作を熟知している人は、きっとコマンドラインでなんでもやってしまうのでしょうが、今の私には難しそうです...
GUI環境で操作できる便利なソフトがあるようですので、まずはそちらから取り掛かってみようと思います。
終わりに
Ubuntu上でMySQLサーバーを構築することができました。
データベースの操作の仕方はこれから勉強が必要ですが、いろいろできそうです。
