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
连接到从上,从是只读的,报错
