久しぶりにMySQLを使ってテーブルを作ろうと思ったところ、いろいろと忘れていることにショック中です。
自分用にログインしてDB作成からテーブル作成し、CREATE文等の基本的な操作を一連にまとめました。
-R7.2.23 ユーザの追加方法も追記しました。
環境
MySQL 8.0.39 (追加分はMYSQL 8.0.41)
windowsのPowershellからSSH接続
インストール
最新のmysqlを検索して、インストール、初期設定
●インストール
$ > sudo apt update # おまじない
$ > apt search mysql-serve # 最新のversionを検索
apt search mysql-serve
default-mysql-server/oracular,oracular 1.1.1 all
:
default-mysql-server-core/oracular,oracular 1.1.1 all
:
mysql-server/oracular-updates,oracular-updates,oracular-security,oracular-security 8.0.41-0ubuntu0.24.10.1 all
:
mysql-server-8.0/oracular-updates,oracular-security,now 8.0.41-0ubuntu0.24.10.1 amd64
:
mysql-server-core-8.0/oracular-updates,oracular-security,now 8.0.41-0ubuntu0.24.10.1 amd64:
:
$ > sudo apt install mysql-server-8.0 # 8.0系をインストール
$ > mysql --version # versionの確認
mysql Ver 8.0.41-0ubuntu0.24.10.1 for Linux on x86_64 ((Ubuntu))
最初の設定
インストール後最初にすること
●初期設定
$ > cd /etc/mysql # インストールフォルダに移動して
$ > sudo mysql_secure_installation # 初期設定
* パスワードポリシーの設定、anonimasuユーザを受け入れるか、rootのリモート接続を許可するか
$ > sudo service mysql start # DBの起動
$ > sudo mysql -u root # rootユーザの設定
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '********';
* パスワードは8桁以上にしないと叱られます
mysql> bye # 一回終了させる
$ > mysql -u root -p # ログインできるか確認
EnterPassword : ********
mysql>
# ログイン成功していれば準備OKユーザの追加作成
運用するにはroot以外のユーザを登録する必要がある（実務的に）
$ > mysql -u root -p # mysqlをrootで起動
EnterPassword : ********
mysql> SELECT user, host FROM mysql.user WHERE user = 'second_user'; # 追加したいユーザがいないか確認
Empty set (0.00 sec) # Empty ならOK
● ユーザ追加
# ローカル接続専用
mysql> CREATE USER 'local_user'@'localhost' IDENTIFIED WITH mysql_native_password BY '********';
# IP指定接続
mysql> CREATE USER 'lan_user'@'192.168.1.x' IDENTIFIED WITH mysql_native_password BY '********';
# どこからでもアクセスできるユーザ
mysql> CREATE USER 'remort_user'@'%' IDENTIFIED WITH mysql_native_password BY '********';
● ユーザの権限追加(どのDBが使えるのか）
# my_database だけ使える
mysql > GRANT ALL PRIVILEGES ON my_database.* TO '対象のユーザ'@'%';
# すべてのDBが使える
mysql > GRANT ALL PRIVILEGES ON *.* TO '対象のユーザ'@'%' WITH GRANT OPTION; ‘%’ はワイルドカードと同じ意味 ‘192.168.1.%’という使い方ができる
‘local_user’@’localhost’ と ’local_user’@’%’ は別ユーザとして扱われる
ログインからDB作成まで
パスワードを利用したログイン
仮 user test_user
pass password1234
# ログインするまで
$ > mysql -u test_user -p
Enter password:********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1065
Server version: 8.0.39-0ubuntu0.24.04.2 (Ubuntu)
：
mysql>
# コンソールが、"mysql>"になっていればログインが成功しています現在のユーザに対する権限を確認する
mysqlではユーザ毎に権限が付与されており、できることとできないことが決まっています。
次のコマンドを使ってログインユーザにどのような権限が付与されているかを確認します。
# ログインユーザにどのような権限が付与されているか確認する
mysql> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------+
| Grants for test_user@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%` | # 通常の操作は行うことができる
| GRANT ALL PRIVILEGES ON `a_db`.* TO `test_user`@`%` | # a_db に対してすべての操作が可能
+--------------------------------------------------------+この状態では、a_dbというデータベースだけが使える状態となっています。
DBの作成
次のコマンドでDBを作成することができます。
mysqlは通常インストールすると、dbの作成権限はrootだけに許可されているようですので、rootユーザでログインしなおします。
※rootユーザのパスワードはOSログイン時のパスワードと違うので、注意してください。
# DBの作成（権限が足りない）
mysql> CREATE DATABASE b_db;
ERROR 1044 (42000): Access denied for user 'test_user'@'%' to database 'test_db'
mysql>exit
# rootでログインしなおす
$> mysql -u root -p
Enter password: ****(一般ユーザと同じになっていたので後でコッソリ直しますw)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1069
:
mysql>
# DBの作成（権限が足りている）
mysql>CREATE DATABASE b_db; # ↑ボタンを押すと前のユーザで入力したコマンドが出るようです
Query OK, 1 row affected (0.02 sec)
# test_userにb_dbの権限を追加する
mysql> GRANT ALL PRIVILEGES ON b_db.* TO 'test_user'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql>exit # root から 一旦出る
# test_userでログインしなおす
ub-axis@ubaxis-virtual-machine:~$ mysql -u remort_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1070
:
mysql> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------+
| Grants for remort_user@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `remort_user`@`%` |
| GRANT ALL PRIVILEGES ON `a_db`.* TO `remort_user`@`%` |
| GRANT ALL PRIVILEGES ON `b_db`.* TO `remort_user`@`%` | # b_dbが追加されている
+--------------------------------------------------------+テーブルの作成
作成したDBにログインし、簡単なテーブルを作成します。
データの挿入、削除、選択、カラムの追加についてメモしておきます。
# b_dbデータベースを使う
mysql>use b_db;
Database changed
# item_masterテーブルを作成する
mysql>mysql> CREATE TABLE `item_master` (
`no` int NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT便利です
`name` varchar(20) NOT NULL,
`dist` text,
`cost` int ,
PRIMARY KEY (`no`)
);
Query OK, 0 rows affected (0.09 sec)
# データを挿入する
mysql> insert into item_master(name,dist,cost) values("赤い薬","HPを少し回復する",50);
Query OK, 1 row affected (0.01 sec)
mysql> insert into item_master(name,dist,cost) values("青い薬","MPを少し回復する",70);
lues("緑の薬","HPとMPを少し回復する",100);Query OK, 1 row affected (0.00 sec)
mysql> insert into item_master(name,dist,cost) values("緑の薬","HPとMPを少し回復する",100);
Query OK, 1 row affected (0.00 sec)
# データを更新する
mysql> update item_master set name="緑色の薬" where no=3;
Query OK, 1 row affected (0.00 sec)
# データを表示する
mysql> select * from item_master;
+----+-----------+------------------------------+------+
| no | name | dist | cost |
+----+-----------+------------------------------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 |
| 2 | 青い薬 | MPを少し回復する | 70 |
| 3 | 緑の薬 | HPとMPを少し回復する | 100 |
+----+-----------+------------------------------+------+
3 rows in set (0.00 sec)
# データを削除する
mysql> DELETE FROM item_master WHERE no = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from item_master;
+----+-----------+-------------------------+------+
| no | name | dist | cost |
+----+-----------+-------------------------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 |
| 2 | 青い薬 | MPを少し回復する | 70 |
+----+-----------+-------------------------+------+
2 rows in set (0.00 sec)
# カラムを追加する
mysql> ALTER TABLE item_master ADD kind varchar(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from item_master;
+----+-----------+-------------------------+------+------+
| no | name | dist | cost | kind |
+----+-----------+-------------------------+------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 | NULL |
| 2 | 青い薬 | MPを少し回復する | 70 | NULL |
+----+-----------+-------------------------+------+------+
2 rows in set (0.00 sec)
# CREATE文を再出力する
mysql> SHOW CREATE TABLE item_master;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item_master | CREATE TABLE `item_master` (
`no` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`dist` text,
`cost` int DEFAULT NULL,
`kind` varchar(10) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)表示系
テーブルの一覧やデータベースの一覧を確認したいとき
# dbの一覧を確認したいとき
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| a_db |
| b_db |
+--------------------+
# テーブルの一覧を確認したいとき
mysql> use b_db
Database changed
mysql> show tables;
+----------------+
| Tables_in_b_db |
+----------------+
| item_master |
+----------------+
1 row in set (0.00 sec)データ移行
作成したDBを本番環境に複写する方法
コンソール画面から次のコマンド(mysqldump)を使用します。
実行するにはroot権限が必要です。
# b_db.item_master の全データを生成するSQLを発行する
$> mysqldump -u root -p --no-create-info --complete-insert b_db item_master > output.sql
Enter password: ****
$> ls
output.sql
# 内容を表示
$> cat output.sql
-- MySQL dump 10.13 Distrib 8.0.39, for Linux (x86_64)
--
-- Host: localhost Database: b_db
-- ------------------------------------------------------
-- Server version 8.0.39-0ubuntu0.24.04.2
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `item_master`
--
LOCK TABLES `item_master` WRITE;
/*!40000 ALTER TABLE `item_master` DISABLE KEYS */;
INSERT INTO `item_master` (`no`, `name`, `dist`, `cost`, `kind`) VALUES (1,'赤い薬','HPを少し回復する',50,NULL),(2,'青い薬','MPを少し回復する',70,NULL);
/*!40000 ALTER TABLE `item_master` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-10-01 21:16:26
# データの挿入方法 ※挿入先のデータベースで実行
$>mysql -u username -p new_database < output.sql
最後に
CREATE系のSQLってプロジェクトの開始時にはよく使うのですが、開発中はあまり使わなくなるので、うっかり忘れがちです（個人的な見解）
件局、何度も構文を検索しているので、よく使う命令を自分用にまとめました。
