test_mysqld_with_slave.pl

#!/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;