yoshinorim/mha4mysql-manager — pull request #30 by kaiwangchen (2013-06-21 08:25:55+00:00)
Title: add helper to block/release user in Shlomi Noach's way

master_ip_online_change script suggests the following steps in freezing phase:

## Gracefully killing connections on the current master
# 1. Set read_only= 1 on the new master
# 2. DROP USER so that no app user can establish new connections
# 3. Set read_only= 1 on the current master
# 4. Kill current queries
# * Any database access failure will result in script die.

In step 2, what we need is blocking users rather than dropping users. Dropping users is one way to block and requires management effort such as dump and import. Why not change the password which effectively disable login? In Shlomi Noach's way as described in this link http://code.openark.org/blog/mysql/blocking-user-accounts

So add two helpers to easy hook script writers.

Test cases snippet:

set_password($dbh, '');
$ret = _block_release_user_by_regexp($dbh,'abc','localhost',1);
ok( $ret == 0 && is_blocked('abc', ''), 'block empty');
_block_release_user_by_regexp($dbh,'abc','localhost',0);
ok( $ret == 0 && is_released('abc', ''), 'release empty');

set_password($dbh, '123');
$ret = _block_release_user_by_regexp($dbh,'abc','localhost',1);
ok($ret == 0 && is_blocked('abc', '123'), 'block new');
$ret = _block_release_user_by_regexp($dbh,'abc','localhost',0);
ok($ret == 0 && is_released('abc', '123'), 'release new');

sub is_blocked {
my ($user,$password) = @_;
DBI->connect('dbi:mysql:', $user, $password, {PrintError=>0});
return 1 if $DBI::err == 1045;
}
sub is_released {
my ($user,$password) = @_;
DBI->connect('dbi:mysql:', $user, $password, {PrintError=>0});
return 1 if $DBI::err == 0;
}
sub set_password {
my ($dbh, $plain) = @_;
my $rs = $dbh->do("set password for 'abc'\@'localhost' = password('$plain')");
}