mysql作为A的从同时为C的主

hjjie2006

由于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