★★★mysql主从备份故障,求指点★★★
本帖最后由 zztmj 于 2015-12-4 13:33 编辑新手,希望打开帖子的朋友能耐心看一下,给我指点迷津。我先说一下怎么做的主从备份:
两台服务器系统是win2008 R2 EN,Mysql版本都是5.6,同步前先将主服务器数据通过Navicat for mysql软件转储为一个文件,然后在从机里通过Navicat for mysql恢复;
主机IP:192.168.0.15
从机IP:192.168.0.16
1:设置主机my.ini,如图
2:设置从库my.ini,如图
3:主库添加备份用户
grant replication slave on *.* to 'root'@'%' identified by 'Aa1234';
4:主库查询binglog
show master status;(显示日志名称和位置)
5:在从库运行以下代码
change master to
master_host='192.168.0.15',
master_user='root',master_password='Aa1234',
master_log_file='日志名称',
master_log_pos=位置;
6:最后测试从库,show slave status \G后(请看下面红字部分)
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.15
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-log.000028
Read_Master_Log_Pos: 397392887
Relay_Log_File: backup-relay-bin.000003
Relay_Log_Pos: 654
Relay_Master_Log_File: mysql-log.000027
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table 'wins.wind_pdupdate_log' doesn't exi
st' on query. Default database: 'wins'. Query: 'UPDATE WIND_PDUPDATE_LOG SET SERVERFILE='SCFReturnDistribution_D_20151203.xml.gz',SERVERFILETIME='2015-12-03 00:
06:43',OPDATE='2015-12-03 15:35:39' ,OPMODE='1' WHERE TABLENAME='SCFReturnDistribution''
Skip_Counter: 0
Exec_Master_Log_Pos: 402336239
Relay_Log_Space: 1078561757
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'wins.wind_pdupdate_log' doesn't exi
st' on query. Default database: 'wins'. Query: 'UPDATE WIND_PDUPDATE_LOG SET SER
VERFILE='SCFReturnDistribution_D_20151203.xml.gz',SERVERFILETIME='2015-12-03 00:
06:43',OPDATE='2015-12-03 15:35:39' ,OPMODE='1' WHERE TABLENAME='SCFReturnDistribution''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 15
Master_UUID: 3129c03a-6d98-11e5-982c-98be9446655a
Master_Info_File: E:\MySQL Server 5.6\Data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 151203 15:51:07
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
我认为因为Last_Error的提示导致Slave_SQL_Running是NO,网上用了各种办法,跳过错误(set global sql_slave_skip_counter=1;)也不行,重新做主从也不行。。实在没招了,请知道的请给小弟指点指点,我这个情况,Slave_SQL_Running=NO,到底怎么办。具体怎么操作,不胜感激了!
找找从库上wins有wind_pdupdate_log表吗? 或者同步的用户是否有此权限...
页:
[1]