日向夏特殊応援部隊

俺様向けメモ

MySQL Replication with Test::mysqld

やってみたかったからついやってみた。

#!/usr/bin/perl
 
use strict;
use warnings;
 
use Data::Dump qw(dump);
use DBI;
use Test::More;
use Test::Exception;
use Test::mysqld;
use Test::TCP;
 
sub setup_master {
 
# http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html
    my $mysqld = Test::mysqld->new(
        auto_start => 2,
        mysqld     => '/usr/sbin/mysqld',
        my_cnf     => +{
            'port'      => empty_port(),
            'log-bin'   => 'mysql-bin',
            'server-id' => 1,
        },
    ) or die($Test::mysqld::errstr);
 
    note( $mysqld->dsn );
 
    # http://dev.mysql.com/doc/refman/5.1/en/replication-howto-repuser.html
    my $dbh = DBI->connect( $mysqld->dsn, 'root', '' );
    $dbh->do(
        sprintf(
            q|CREATE USER '%s'@'%s' IDENTIFIED BY '%s'|,
            'repl', '127.0.0.1', 'replpass'
        )
    ) or die( $dbh->errstr );
    $dbh->do(
        sprintf(
            q|GRANT REPLICATION SLAVE ON *.* TO '%s'@'%s'|,
            'repl', '127.0.0.1'
        )
    ) or die( $dbh->errstr );
 
    return $mysqld;
}
 
sub setup_slave {
    my $master_mysqld = shift;
 
 # http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slavebaseconfig.html
    my $mysqld = Test::mysqld->new(
        auto_start => 2,
        mysqld     => '/usr/sbin/mysqld',
        my_cnf     => +{
            'port'      => empty_port(),
            'server-id' => 2,
        },
    ) or die($Test::mysqld::errstr);
 
    note( $mysqld->dsn );
 
    my $dbh_master = DBI->connect( $master_mysqld->dsn, 'root', '' );
 
    my $master_status = $dbh_master->selectrow_hashref( 'SHOW MASTER STATUS' );
 
    my $dbh = DBI->connect( $mysqld->dsn, 'root', '' );
 
    # http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slaveinit.html
    $dbh->do(
        sprintf(
q|CHANGE MASTER TO MASTER_HOST='%s', MASTER_PORT=%d, MASTER_USER='%s', MASTER_PASSWORD='%s', MASTER_LOG_FILE='%s', MASTER_LOG_POS=%d|,
            '127.0.0.1', $master_mysqld->my_cnf->{port},
            'repl', 'replpass', $master_status->{File}, $master_status->{Position},
        )
    );
    $dbh->do(q|START SLAVE|);
 
    note(
        explain(
            $dbh->selectall_arrayref( 'SHOW SLAVE STATUS', +{ Slice => +{} } )
        )
    );
 
    return $mysqld;
}
 
my $master_mysqld;
 
lives_ok(
    sub {
        $master_mysqld = setup_master;
    },
    'setup_master() is success'
);
 
my $slave_mysqld;
 
lives_ok(
    sub {
        $slave_mysqld = setup_slave($master_mysqld);
    },
    'setup_slave() is success'
);
 
my $dbh_master =
  DBI->connect( $master_mysqld->dsn, 'root', '',
    +{ RaiseError => 1, AutoCommit => 0, } );
 
isa_ok( $dbh_master, 'DBI::db' );
 
$dbh_master->do(q|CREATE DATABASE hidek|) or die($dbh_master->errstr);
$dbh_master->do(q|USE hidek|) or die($dbh_master->errstr);
$dbh_master->do(
q|CREATE TABLE hidek ( id int not null primary key auto_increment, name varchar(32) ) ENGINE=InnoDB|
) or die($dbh_master->errstr);
$dbh_master->do( q|INSERT INTO hidek(name) VALUES(?)|, undef, 'yakatabune' ) or die($dbh_master->errstr);
$dbh_master->commit or die($dbh_master->errstr);
 
note( explain( $dbh_master->selectall_arrayref(q|SHOW DATABASES|) ) );
 
sleep 10;
 
my $dbh_slave =
  DBI->connect( $slave_mysqld->dsn, 'root', '',
    +{ RaiseError => 1, AutoCommit => 0, } );
 
note( explain( $dbh_slave->selectall_arrayref(q|SHOW DATABASES|) ) );
$dbh_slave->do(q|USE hidek|);
note( explain( $dbh_slave->selectall_arrayref(q|SHOW TABLES|) ) );
note( explain( $dbh_slave->selectall_arrayref(q|SELECT * FROM hidek|) ) );
 
done_testing;

要約すると my.cnf で言うところの mysqld で設定出来る内容なら何でも出来ると思って良いと。