mysql数据库双向同步热备

mysql数据库双向同步热备环境：Masterserver:39Slave

server:37步骤：1.分别在Master/Slaver

\t"/database/201108/_blank"mysql

db创建backupuser：GRANTFILEON*.*TObackup@39IDENTIFIEDBY'pass';GRANTREPLICATIONSLAVEON*.*TObackup@39IDENTIFIEDBY'pass';

GRANTFILEON*.*TObackup@37IDENTIFIEDBY'pass';GRANTREPLICATIONSLAVEON*.*TObackup@37IDENTIFIEDBY'pass';2.在Masterserver配置/etc/my.cf文件：server-id=1binlog-do-db=testbinlog-ignore-db=mysql

replicate-do-db=testreplicate-ignore-db=mysql

master-host=37master-user=backupmaster-password=passmaster-port=3306master-connect-retry=60

slave-skip-errors=all3.在Masterserver配置/etc/my.cf文件：server-id=2

binlog-do-db=testbinlog-ignore-db=mysql

replicate-do-db=testreplicate-ignore-db=mysql

master-host=39master-user=backupmaster-password=passmaster-port=3306master-connect-retry=60

slave-skip-errors=all4.重启mysql\t"/database/201108/_blank"数据库，验证命令如下：查看Master状态showmasterstatus;mysql>showmasterstatus;+-----------------+----------+--------------+------------------+|File

|Position|Binlog_Do_DB|Binlog_Ignore_DB|+-----------------+----------+--------------+------------------+|mysqllog.000003|

301|test

|mysql

|+-----------------+----------+--------------+------------------+1rowinset(0.00sec)查看Slave状态来运棋牌showslavestatus\G;mysql>showslavestatus\G;***************************1.row***************************

Slave_IO_State:Reconnectingafterafailedmastereventread

Master_Host:39

Master_User:backup

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysqllog.000003

Read_Master_Log_Pos:301

Relay_Log_File:mysqlgsb-relay-bin.000082

Relay_Log_Pos:348

Relay_Master_Log_File:mysqllog.000003

Slave_IO_Running:No

Slave_SQL_Running:Yes

Replicate_Do_DB:test

Replicate_Ignore_DB:mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0

Last_Error:

Skip_Counter:0

Exec_Master_Log_Pos:301

Relay_Log_Space:650

Until_Condition:None

Until_Log_File:

Until_Log_Pos:0

Master_SSL_Allowed:No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:No

Last_IO_Errno:0

Last_IO_Error:

Last_SQL_Errno:0

Last_SQL_Error:1rowinset(0.00sec)

ERROR:Noqueryspecified查看同步进程：mysql>showprocesslist\G;***************************1.row***************************

Id:1

User:systemuser

Host:

db:NULLCommand:Connect

Time:4186

State:Waitingformastertosendevent

Info:NULL***************************2.row***************************

Id:2

User:systemuser

Host:

db:NULLCommand:Connect

Time:3745

State:Hasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit

Info:NULL捕鱼游戏***************************3.row***************************

Id:5

User:root

Host::28293

db:NULLCommand:Query

Time:0

State:NULL

Info:showprocesslist***************************4.row***************************

Id:6

User:backup

Host