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

MySQL/MariaDB SQL チートシート