mysql5.1双机热备记录

linux下mysql双机热备份

MYSQL双机备份记录

李新君

1版本说明

Mysql:5.1.56,Fedora10;A:192.168.1.200B:192.168.1.201

2创建数据库

首先在A机器创建要备份的数据库和表，然后用mysqldump导出并在B机器部署，保证两台机器数据库、表、数据等都一致；然后对两个数据库用户进行授权，保证双方可以正常访问；A机器：

mysql>grantallonbackup.*to'backup'@'192.168.1.201'identifiedby'backup'withgrantoption;mysql>grantallonbackup.*to'backup'@'localhost'identifiedby'backup'withgrantoption;mysql>flushprivileges;B机器：

mysql>grantallonbackup.*to'backup'@'192.168.1.200'identifiedby'backup'withgrantoption;mysql>grantallonbackup.*to'backup'@'localhost'identifiedby'backup'withgrantoption;mysql>flushprivileges;

linux下mysql双机热备份

3数据库配置文件修改

安装过程略：A服务器http://doc.guandang.netf:

####==============================#####synchronizationconfigstart

#####==============================#MasterIP:192.168.1.200##SlaveIP:192.168.1.201

##synchronizationdatabase:backup##user:backuppassword:backupserver-id=1

binlog-do-db=backuplog-bin

max_binlog_size=104857600replicate-same-server-id

#========================###undersettingslave

###========================master-host=192.168.1.201master-user=backup

master-password=backupmaster-port=3306

master-connect-retry=60replicate-do-db=backup

B服务器http://doc.guandang.netf:

####==============================####synchronizationconfigstart

####==============================#MasterIP:192.168.1.200#SlaveIP:192.168.1.201

#synchronizationdatabase:backup#user:backuppassword:backupserver-id=2

binlog-do-db=backuplog-bin

max_binlog_size=104857600replicate-same-server-id

#========================##undersettingslave

##========================master-host=192.168.1.200master-user=backup

master-password=backupmaster-port=3306

master-connect-retry=60replicate-do-db=backup

linux下mysql双机热备份

binlog-ignore-db=mysql

log-error=/var/log/mysqld.log

###============================####synchronizationconfigend

####===========================binlog-ignore-db=mysql#log-slave-updates

log-error=/var/log/mysqld.log

###===========================###synchronizationconfigend

###============================

4备份授权

A服务器授权

mysql>grantreplicationslaveon*.*to'backup'@'192.168.1.201'identifiedby'backuppwd';mysql>flushprivileges;B服务器授权

mysql>grantreplicationslaveon*.*to'backup'@'192.168.1.200'identifiedby'backuppwd';mysql>flushprivileges;

5配置测试

使用showmasterstatus和showslavestatus进行测试；保证没有错误发生，如果发生错误，请参考第七节；mysql>showmasterstatus\G;

***************************1.row***************************

File:localhost-bin.000001

linux下mysql双机热备份

Position:106

Binlog_Do_DB:backupBinlog_Ignore_DB:mysql1rowinset(0.00sec)

mysql>showslavestatus\G;

***************************1.row***************************

Slave_IO_State:

Master_Host:192.168.1.201Master_User:backupMaster_Port:3306Connect_Retry:60Master_Log_File:Read_Master_Log_Pos:4

Relay_Log_File:localhost-relay-bin.000053Relay_Log_Pos:4

Relay_Master_Log_File:mysql-bin.000019

Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:backupReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:

Last_Errno:0

linux下mysql双机热备份

Last_Error:Skip_Counter:0

Exec_Master_Log_Pos:106

Relay_Log_Space:0Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:

Seconds_Behind_Master:NULL

Master_SSL_Verify_Server_Cert:No

Last_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:

1rowinset(0.00sec)

6数据测试

两台机器分别进行插入、删除、更新等操作，看是否同步，我的没有同步，解决方法参见第七节；

linux下mysql双机热备份

查看命令：

mysql>showmasterstatus\G;mysql>showslavestatus\G;mysql>showprocesslist\G;

遇到的问题，只能单向备份，不能双向备份；

7常见问题

两台机器同时设置出错误跳过的步骤（下面表示同步如果出现错误跳过一次，可以设置n次）：

mysql>stopslave;

mysql>setGLOBALSQL_SLAVE_SKIP_COUNTER=1;mysql>startslave;

最好拿新部署的MySQL进行测试，否则由于日志的问题，极容易造成访问错误。

（1）错误信息：Last_IO_Error:errorconnectingtomaster'backup@192.168.1.200:3306'-retry-time:60retries:86400解决办法：

linux下mysql双机热备份

slave服务器上删除数据库目录下的http://doc.guandang.net、你的主机名-relay-bin.*，重启slave即可。（2）Slave_IO_Running:No

Slave_SQL_Running:No

本语句证明主备之间没有同步，最大的可能会出在日志上面，我的解决方案：-bash-3.2#http://doc.guandang.net

然后把其他日志也全部删除重启即可