mysql keepalived测试_mysql 结合keepalived测试

vip:192.168.32.66

192.168.32.6 主库:

mysql> show variables like '%read_only%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| innodb_read_only | OFF |

| read_only | OFF |

| tx_read_only | OFF |

+------------------+-------+

3 rows in set (0.00 sec)

可读可写

192.168.32.116 从库:

mysql> show variables like '%read_only%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| innodb_read_only | OFF |

| read_only | ON |

| tx_read_only | OFF |

+------------------+-------+

3 rows in set (0.02 sec)

只读

测试账户:

test:/root# mysql -uzjzc_app -p1234567

从库:

slave:/root# mysql -uzjzc_app -p'1234567' --socket=/data01/mysql/mysql.sock

Warning: Using a password on the command line interface can be insecure.

mysql> use zjzc;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table test(id int);

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql>

mysql> insert into SystemRole(roleName,roleCode,status,createdTime) values('aaaa','aaa','1',now);

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql> \

开始测试；

/root/sbin# cat test.pl

use DBI;

my $database='zjzc';

my $user="zjzc_app";

my $passwd="1234567";

my @arr2=();

my $dbh = DBI->connect("dbi:mysql:database=$database;host=192.168.32.66;port=3306",$user,$passwd,{

RaiseError => 1,

AutoCommit => 0

} ) or die "can't connect to database ". DBI-errstr;

my $hostSql = qq{select id,name from scan; };

my ($a1, $a2, $a3,$a4,$a5,$a6,$a7,$a8,$a9);

my $selStmt = $dbh->prepare($hostSql);

$selStmt->execute();

$selStmt->bind_columns(undef, \$a1, \$a2);

$selStmt->execute();

while( $selStmt->fetch() ){ push (@arr2, "$a1 $a2 $a3\n" );

};

print "\@arr2 is @arr2\n";

eval{

$dbh->do("insert into scan values(2,'cccddeqe')") ;

$dbh->commit();};

if( $@ ) {

print "Database error: $DBI::errstr\n";

$dbh->rollback(); #just die if rollback is failing

};

$dbh->disconnect;

slave:/root/sbin# perl test.pl

@arr2 is 1 aaabbb

2 cccddeqe

2 cccddeqe

2 cccddeqe

2 cccddeqe

此时连接到master上,可以读写

重启master的keepalive,此时；

slave:/root/sbin# perl test.pl

@arr2 is 1 xxxxyyyy

DBD::mysql::db do failed: The MySQL server is running with the --read-only option so it cannot execute this statement at test.pl line 22.

Database error: The MySQL server is running with the --read-only option so it cannot execute this statement

连接到从上,从是只读的,报错