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 で設定出来る内容なら何でも出来ると思って良いと。