Mysql-从库只读设置

主从设置中，如果从库在my.cnf中使用init_connect来限制只读权限的话，从库使用非超级用户（super权限）登陆数据时，无法进行任何操作，仅可维持主从复制。

init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
init_connect='SET autocommit=0'
init_connect='SET global read_only=1

如果需要从库可进行查询类的只读操作，需要在配置文件中通过配置read_only=on来实现

从库my.cnf示例

[mysql]
default-character-set=utf8
[mysqld]
autocommit=OFF
skip_name_resolve
skip-external-locking
datadir=/usr/mysql/5.6/data
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin
server-id=215
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=mixed
lower_case_table_names=1
#主从配置


pid_file=/var/lib/mysql/mysql.pid
#init_connect='SET collation_connection = utf8_general_ci'
#init_connect='SET NAMES utf8'
#init_connect='SET autocommit=0'
#init_connect='SET global read_only=1'
read_only=on
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
log_bin_trust_function_creators=1
#user=
port=3306
#innodb
default_storage_engine=InnoDB
innodb_buffer_pool_size=512M
innodb_log_buffer_size=16M
innodb_log_file_size=64M
innodb_file_per_table=1
innodb_autoextend_increment=128M
#innodb_flush_method=0_DIRECT
#other
key_buffer_size=512M
log_error=/usr/mysql/5.6/mysql-error.log
slow_query_log_file=/usr/mysql/5.6/mysql-slow.log
tmp_table_size=200M
max_heap_table_size=200M
table_open_cache = 1024
sort_buffer_size = 1024K
max_allowed_packet = 100M
query_cache_type=0
query_cache_size=0
open_files_limit=65535
[client]
socket=/var/lib/mysql/mysql.sock
port=3306
default-character-set=utf8