開発メモ

開発用のメモです。

MySQL ユーザー削除を Drop User で実行しない場合のSQL

権限情報が残ってるからみたいです。

DROP   TEMPORARY TABLE IF EXISTS     `duser`;
CREATE TEMPORARY TABLE IF NOT EXISTS `duser` 
      AS SELECT `Host`,`User` FROM `mysql`.`user` Where `User` = 'dbuser2';
DELETE x FROM `user` x, `duser` d WHERE x.Host = d.Host AND x.User = d.User;
DROP   TEMPORARY TABLE IF EXISTS     `duser`;

DROP   TEMPORARY TABLE IF EXISTS     `ddb`;
CREATE TEMPORARY TABLE IF NOT EXISTS `ddb`
     AS SELECT Host,User,Db FROM db WHERE 
        NOT EXISTS (
            SELECT 1 FROM user WHERE user.User = db.User AND user.Host = db.Host
        );
DELETE x FROM `db` x, `ddb` d WHERE x.Host = d.Host AND x.User = d.User AND x.Db = d.Db;
DROP   TEMPORARY TABLE IF EXISTS     `ddb`;

FLUSH PRIVILEGES;

例は dbuser2 を削除したい場合

Twitter: @asahina_alice