由于mysql跨机房读写,因网络延时很容易出现以下类似错误,可使用主从同步的方式来解决此问题
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2006 (HY000) at line 1: MySQL server has gone away
以下为其中一台置于双线机房的配置
#############START#########################
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 1
event_scheduler = 1
expire_logs_days = 7
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
binlog-do-db=info_account
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
############END##########################################
以下为其中一台置于网通机房的配置/<电信相同>
############START########################################
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 1
event_scheduler = 1
expire_logs_days = 7
skip-name-resolve
log-bin=mysql-bin
binlog_format=mixed
slave-skip-errors=all
#####################
server-id = 20
# 主服务器的IP地址或者域名
master-host=x.x.x.x
# 主数据库的端口号
master-port=3306
# 同步数据库的用户
master-user=slave
# 同步数据库的密码
master-password=password
# 如果从服务器发现主服务器断掉,重新连接的时间差
master-connect-retry=60
# 需要备份的库
replicate-do-db=info_account
#
#####binlog#######
binlog-do-db=acc
binlog-do-db=act
binlog-do-db=fso
binlog-do-db=fso_log
binlog-do-db=fso2
binlog-do-db=info_account
binlog-do-db=info_game
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
############END##########################################
网通mysql节点配置,
其上层的mysql主机<此mysql的主>在双线机房 跨机房只同步一个info_account<帐号库> 由replicate-do-db参数指定;
其下层的mysql主机<此mysql的从>与此主机存放在同一机房 所需同步的库由binlog-do-db参数指定
对于server-id 要注意主ID号小于从ID号
可以参考下列ID设置规则
双线 1
电信 10 11 电信从 100
网通 20 21 网通从 200
阅读(1164) | 评论(0) | 转发(0) |