MySQL/MariaDB SQL チートシート


MySQL/MariaDB のデータベースをコマンドラインから作成するときとかのよく使う CLI コマンドをまとめたものです。

完全に自分用です。

concrete5 でメンテに使うコマンドとかもひっそりと含んでいます。

# Create DB and user / DB を作成してユーザーを加える

## Non-RDS


```
CREATE DATABASE dev-c5db DEFAULT CHARACTER SET=utf8mb4;

GRANT ALL PRIVILEGES ON `dev-c5db`.* TO `dev-c5dbuser`@`127.0.0.1` IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `dev-c5db`.* TO `dev-c5dbuser`@`localhost` IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `dev-c5db`.* TO `dev-c5dbuser`@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `dev-c5db`.* TO `dev-c5dbuser`@`Additional IP` 

FLUSH PRIVILEGES;
```

## AWS RDS

AWS RDS だと `ALL PRIVILEGES` が許可されていないようなので、個別にすべての権限を与える必要がありました。

```
CREATE DATABASE dev-c5db DEFAULT CHARACTER SET=utf8mb4;
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES,TRIGGER, CREATE VIEW, SHOW VIEW, EVENT on `dev-c5db`.* TO `dev-c5dbuser`@`127.0.0.1` identified by 'password';
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES,TRIGGER, CREATE VIEW, SHOW VIEW, EVENT on `dev-c5db`.* TO `dev-c5dbuser`@`localhost` identified by 'password';
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES,TRIGGER, CREATE VIEW, SHOW VIEW, EVENT on `dev-c5db`.* TO `dev-c5dbuser`@'%' identified by 'password';
GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES,TRIGGER, CREATE VIEW, SHOW VIEW, EVENT on `dev-c5db`.* TO `dev-c5dbuser`@`Additional IP` identified by 'password';
```

# Root パスワードを変更する (MariaDB 等)

MariaDB 10.4 ぐらいから、ssh のログインユーザー認証で MariaDB の root で入ることができるようになっています。元のようにパスワード認証が良い人は、MariaDB サーバーインストール後に root パスワードを設定します。

```
$ mysql -u root
ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("パスワード")
FLUSH PRIVILEGES;
exit;
```


# Clear database / DB をクリアする。

DB を削除しないで中身をクリアする。

```
mysqldump -h [mysql_address] -u [mysql_username] -p --add-drop-table --no-data [database_name] | grep ^DROP | mysql -u [mysql_username] -p  -h [mysql_address] [database_name]

mysqldump -h [mysql_address] -u [mysql_username] -p --add-drop-table --no-data [database_name] | grep ^DROP | mysql -u [mysql_username] -p [database_name]

mysqldump -u${user} -p${pass} -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p${pass} -h ${host} ${database}
```

Foreign Key エラーが出た時

```
mysqldump -u${user} -p${pass}] -h ${host} --add-drop-table --no-data ${database} | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -u${user} -p${pass} ${database}
```

DB を削除、再度作成して同じユーザーに権限を与える。

```
DROP DATABASE `dev-db`;
CREATE DATABASE `dev-db` DEFAULT CHARACTER SET=utf8mb4;
GRANT ALL PRIVILEGES ON `dev-db`.* to `dev-dbuser`@`127.0.0.1` WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `dev-db`.* to `dev-dbuser`@`localhost` WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `dev-db`.* to `dev-dbuser`@`XXX.XXX.XXX.XXX` WITH GRANT OPTION;
FLUSH PRIVILEGES;
```

## mysqldump + gzip

mysqldump から 直接 gzip する

```
mysqldump -h [origin host] -u [origin DB User] --password="[origin Password]" [origin DB Name] | gzip > [Filename]
```

おまけ：ファイルの末尾に日時を追加する

```
mysqldump -h [origin host] -u [origin DB User] --password="[origin Password]" [origin DB Name] | gzip > [Filename]_$(date +%Y%m%d%H%M%S).gz
```

## MySQL コピー

ファイルを作成せず、パイプしてデータをコピーする

```
mysqldump -h [origin host] -u [origin DB User] --password="[origin Password]" [origin DB Name] | mysql -h [dest host] -u [dest DB User] --password="[dest Password]" [dest DB Name]
```

# Concrete CMS 関連

## File Storage Location from 2 to 1

すべてのファイルの保存場所を手動で変更する。

```
UPDATE `Files` SET fslID=REPLACE(fslID, 2,1);
```

ファイルのデフォルト保存場所をデフォルト位置に設定する

```
UPDATE `FileStorageLocations` SET fslIsDefault=0 WHERE fslName="S3";
UPDATE `FileStorageLocations` SET fslIsDefault=1 WHERE fslName="Default";
```


## Update user data

Batch change Concrete CMS User data

Concrete CMS ユーザーのメールアドレスを強制変更する。
本番環境から開発環境に移行する時に個人情報を削除する目的で。
ただし、ユーザー属性の情報は削除していないので気をつけて。

```
UPDATE Users SET uEmail=REPLACE(uEmail, "original", "example");
```


```
# メールアドレスから特定の文字列「original」だけを「example」に変更する
UPDATE Users SET uEmail=REPLACE(uEmail, "original", "example"); 
# メールアドレスを「ユーザー名@example.com」に変更する
UPDATE Users SET uEmail=CONCAT(uName, '@example.com');
# メールアドレスを「ユーザー名@example.com」に変更するけれど「@example.co.jp」のメールアドレスは変えない。（例：自分の会社メールアドレスは匿名化しない）
UPDATE Users SET uEmail=CONCAT(uName, '@example.com') WHERE uEmail NOT LIKE  "%@example.co.jp";
＃メールアドレスを「数字の連番@example.com」に変更するけれど「@example.co.jp」のメールアドレスは変えない。（例：自分の会社メールアドレスは匿名化しない）
UPDATE Users SET uEmail=CONCAT(uID, '@example.com') WHERE uEmail NOT LIKE  "%@example.co.jp";
# ユーザー名もスーパー管理者以外は連番だけにする
UPDATE Users SET uName=uID WHERE uID NOT LIKE  "1";

```

## Get latest entries of Log

```
use [CCM データベース名]
Select * From Logs ORDER BY logID DESC LIMIT 10;
```


## Replace URL

```
UPDATE atDefault SET value = REPLACE(value, 'http://example.com', 'http://example.net');
UPDATE btContentLocal SET content = REPLACE(content, 'http://example.com', 'http://example.net');
```



# MySQL ユーザー権限

mysqlに切り替え
```
USE mysql
```

## 登録されているユーザを確認
```
SELECT user, host FROM user;
```

## 権限を表示
```
SHOW GRANTS for 'user'@'%';
SHOW GRANTS for 'user'@'127.0.0.1';
SHOW GRANTS for 'user'@'localhost';
```

## ユーザー削除

```
DROP USER user1@127.0.0.1,[user2@127.0.0.1];
```

# MySQL 設定

設定情報を参照

```
SHOW GLOBAL VARIABLES;
use [db];
SHOW VARIABLES;
```

## Collation 情報を表示

```
SHOW VARIABLES LIKE "chara%";
USE db_name;
SELECT @@character_set_database, @@collation_database;
```

# MySQL システム

## Upgrade MySQL Client

```
sudo yum remove mysql-community-client mysql-community-common mysql-community-devel mysql-community-libs mysql-community-libs-compat
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql80-community
sudo yum install mysql-community-client mysql-community-common mysql-community-devel mysql-community-libs mysql-community-libs-compat MySQL-python net-snmp php-snmp postfix
```

## Copy MySQL from one to another

```
# Format
mysqldump -h [old HOST] -u [old USER] [old DB] | mysql -h [new HOST] -u [new USER] [new DB]
# Sample
mysqldump -h 127.0.0.1 -u root c5demo_802 | mysql -h 127.0.0.1 -u root c5demo_prb
```