MySQL コマンド備忘録

MySQL5.6で動作確認済み(2017.10.16)

コマンド

#バージョン確認
mysqld -V

#初期設定
mysql_secure_installation
    Enter current password for root (enter for none):enter
    Set root password? [Y/n] Y
    New password:'password'
    Re-enter new password:'password'
    Remove anonymous users? [Y/n] Y
    Disallow root login remotely? [Y/n] Y
    Remove test database and access to it? [Y/n] Y
    Reload privilege tables now? [Y/n] Y

#ログイン
mysql -u 'user_name' -p
Enter password: 'password'
#mysql -u 'user_name' -p -h 'host_name'(リモートログイン)

# ダンプ
mysqldump -u 'user_name' -p 'database_name' > dumpfilename.sql
Enter password: 'password'

#リストア
mysql -u 'user_name' -p 'database_name' < dumpfilename.sql
Enter password: 'password'

入力キャンセル

mysql> SELECT * FROM hoge WHERE INNER machigaeta
    ->\c
mysql>

セッション

-- セッション一覧
 SHOW PROCESSLIST;
 
 -- 切断
 KILL `processId(一覧で表示されたid)`;

ユーザ操作

-- ログインユーザ表示
SELECT USER();

-- ユーザ一覧表示
SELECT host,user FROM mysql.user;

-- ユーザ権限表示
SHOW GRANTS FOR `user_name`@`host_name`;

-- ユーザ作成
CREATE USER `user_name`@`host_name` IDENTIFIED BY 'password';
-- host_name[`localhost`,`%`,`%.example.com`,`192.168.0.%`]

-- 権限付与
GRANT ALL PRIVILEGES ON `database_name`.`table_name` TO `user_name`@`host_name`;
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_name`.* TO `user_name`@`host_name`;
-- ALL PRIVILEGES, ALTER, CREATE, DROP, INDEX, SELECT, INSERT, UPDATE, DELETE

-- ユーザ作成と権限付与 （※MySQL8.0で使えなくなった）
GRANT ALL PRIVILEGES ON `database_name`.`table_name` TO `user_name`@`host_name` IDENTIFIED BY 'password';

-- ユーザ削除
DROP USER `user_name`@`host_name`;

-- パスワード変更
UPDATE mysql.user SET password=password('password') WHERE user = 'user_name';
FLUSH PRIVILEGES;

表示系

-- データベース一覧
SHOW DATABASES;

-- テーブル一覧
SHOW TABLES;

-- カラム一覧
SHOW FULL COLUMNS FROM `table_name`;

-- インデックス
SHOW INDEX FROM `table_name`;

-- テーブル作成SQL
SHOW CREATE TABLE `table_name`;

-- 文字コード確認
SHOW VARIABLES LIKE '%char%';

-- 同時接続数
SHOW STATUS LIKE 'Threads_connected';

DB操作

-- 切替え
USE `database_name`;

-- 作成
CREATE DATABASE `database_name`  /*!40100 COLLATE 'utf8mb4_general_ci' */;

-- 削除
DROP DATABASE `database_name`;

TABLE操作

-- テーブル作成
CREATE TABLE `message` (
    `messageID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `year` SMALLINT(5) UNSIGNED NOT NULL COMMENT '作成年(パーティション操作用)',
    `month` TINYINT(2) UNSIGNED NOT NULL COMMENT '作成月(パーティション操作用)',
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    `title` VARCHAR(100) NULL DEFAULT NULL COMMENT 'タイトル',
    `body` TEXT NOT NULL COMMENT '本文',
    `deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '論理削除フラグ',
    PRIMARY KEY (`messageID`,`year`,`month`,`created`)
)
COMMENT='メッセージテーブル'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

-- カラム追加
ALTER TABLE `message` ADD COLUMN `locked` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'ロックフラグ' AFTER `body`;

-- カラム削除
ALTER TABLE `message` DROP COLUMN `locked`;

-- インデックス追加
ALTER TABLE `message` ADD INDEX `messageID` (`messageID`);

-- テーブル削除
DROP TABLE `message`;

パーティション操作

--作成:RANGE COLUMNS(`created`)
ALTER TABLE `message` PARTITION BY RANGE COLUMNS(`created`) (
    PARTITION p201710 VALUES LESS THAN ('2017-11-01'),
    PARTITION p201711 VALUES LESS THAN ('2017-12-01'),
    PARTITION p201712 VALUES LESS THAN ('2018-01-01'),
   PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 前に追加:RANGE COLUMNS(`created`)
ALTER TABLE `message` REORGANIZE PARTITION p201710 INTO (
    PARTITION p201708 VALUES LESS THAN ('2017-09-01'),
    PARTITION p201709 VALUES LESS THAN ('2017-10-01'),
    PARTITION p201710 VALUES LESS THAN ('2017-11-01')
);

-- 後に追加:RANGE COLUMNS(`created`)
ALTER TABLE `message` REORGANIZE PARTITION pmax INTO (
    PARTITION p201801 VALUES LESS THAN ('2018-08-01'),
    PARTITION p201802 VALUES LESS THAN ('2018-09-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

--作成:RANGE(`year`)
ALTER TABLE `message` PARTITION BY RANGE (`year`) (
    PARTITION p2017 VALUES LESS THAN (2017),
    PARTITION p2018 VALUES LESS THAN (2018),
    PARTITION p2019 VALUES LESS THAN (2019),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

--作成:LIST(`month`)
ALTER TABLE `message` PARTITION BY LIST (`month`)(
    PARTITION m1 VALUES IN (1),
    PARTITION m2 VALUES IN (2),
    PARTITION m3 VALUES IN (3),
    PARTITION m4 VALUES IN (4),
    PARTITION m5 VALUES IN (5),
    PARTITION m6 VALUES IN (6),
    PARTITION m7 VALUES IN (7),
    PARTITION m8 VALUES IN (8),
    PARTITION m9 VALUES IN (9),
    PARTITION m10 VALUES IN (10),
    PARTITION m11 VALUES IN (11),
    PARTITION m12 VALUES IN (12)
 );

-- パーティション確認
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'message';

-- パーティション削除
ALTER TABLE `message` DROP PARTITION p201710;