Mysql/sql文DDL

rootパスワード設定

mysqladmin -u root password '任意のパスワード'

ユーザ権限追加

-- mysql-5.7からはCREATE USERしてから
CREATE USER user1@"localhost";
-- 全dbに指定sql分を許可
GRANT USAGE ON *.* TO user1@"localhost" IDENTIFIED BY 'パスワード';
GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER ON *.* TO user1@"localhost";
-- 個別dbのみ
GRANT USAGE ON db1.* TO user1@"localhost" IDENTIFIED BY 'パスワード';
GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER ON db1.* TO user1@"localhost";
-- 権限確認
SHOW GRANTS FOR user1@localhost;

以下エラーが出た場合

ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user'
$ sudo mysql_upgrade -u root -p
$ sudo service mysql restart

権限削除

-- db指定権限削除
REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON db1.* FROM 'user1'@'localhost';
-- db全指定権限削除
REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* FROM 'user1'@'localhost';
-- 全権限削除
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';

ユーザ削除

DELETE FROM mysql.user WHERE user='ユーザ名';

mysqlに接続する方法

mysql -A -u [ユーザ名] -p
password入力のプロンプトが出るので、パスを入力
-Aを入力するとログイン時データベース一覧を取得しないので早くなる

my.cnf場所

var/lib/mysql/my.cnf

～から～のＤＢへ接続できるかテストする

mysql -u root -p -h 接続先IP

復元

mysql -uroot1 -ppass1 database1 < xxx.dump

ダンプ

mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > xxx.log

テーブルのみを指定する時は
--tables [table]を追加する

ロック中でも強制的にダンプするとき
--skip-lock-tables

データのみダンプ
-t

テーブル定義のみダンプ
-d

文字コードがeuc-jpのときはujisとする

create文のみバックアップ

$ mysqldump -uroot1 -ppass1 -d database1 > create1.dmp

再起動

# /etc/init.d/mysqld start
                     stop

または

# /etc/rd.d/init.d/mysqld start

gzipのバックアップとリストア

バックアップ

mysqldump --opt --quote-names --databases xxx | gzip -c1 > xxx.dump.gz

リストア

gzip -dc xxx.dump.gz | mysql -D xxx

utf8でdb作成

create database database1 default character set utf8;

my.cnfの以下設定

[mysqld]
default-character-set = utf8
skip-character-set-client-handshake
show variables like 'char%'でutf8確認

innodbのデッドロックを見つける

/var/lib/mysql/のinnodb.status.[dddd]の中を見る

puttyコンソールから文字コード変更

プッティ側の設定を変更することで可能 （ウィンドウ/変換）

puttyコンソールからデータ表示枠の拡大は

ウィンドウ/スクロールバックの数を増やすしかない

現在発行中のsql一覧

mysqladmin processlist

クライアントツールでutf8を表示

set names utf8;

テーブルと一緒にuniqueキー作成

CREATE TABLE sample(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
unique1 varchar(10),
unique2 int(11),
PRIMARY KEY(id),
UNIQUE KEY `uniq_url` (`unique1`, `unique2`)
);

テーブルと一緒にindexキー作成

KEY `idx_custom_created` (`code`,`created`),

auto_increment確認＆設定

--確認
SHOW TABLE STATUS;
--設定
ALTER TABLE table1 AUTO_INCREMENT=4;

strage変更

ALTER TABLE table1 ENGINE = InnoDB;

現在のプロセス確認

mysql> show processlist;
+----------+------+-----------------------+-----------------+---------+------+-------+------------------+
| Id       | User | Host                  | db              | user1mand | Time | State | Info             |
+----------+------+-----------------------+-----------------+---------+------+-------+------------------+
| 23719307 | user1 | localhost             | NULL            | Query   |    0 | init  | show processlist |
| 23722193 | user1 | localhost:37333       | test          | Sleep   |    1 |       | NULL             |
| 23722199 | user1 | localhost:37343       | test          | Sleep   |    0 |       | NULL             |
| 23722093 | user1 | localhost:37170       | test          | Query   |    0 | FULLTEXT initialization | (SELECT `tests`.`name`, `tests`.`created`, `tests`.`updated`, `s |