一:首先你在从库检查从库进程状态是否正常 如下红字所示,都为YES是正常。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.106
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.000466
Read_Master_Log_Pos: 20256923
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 4293096
Relay_Master_Log_File: log.000466
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: search.%,bid.%,en_US_bid.%,common.%,goods.%,jsprun.%,jsprunnew.%,jspxcms304.%,mkyongjava.%,shopxx.%,booth.%,discuz_test.%,zixun_en_test.%,zixun_ar_test.%,zixun_cn_test.%,huizhan_test.%,help_test.%,search_test.%,bbs.%,search_lj.%,huizhan_ar_test.%,huizhan_de_test.%,huizhan_en_test.%,huizhan_es_test.%,huizhan_fr_test.%,huizhan_in_test.%,huizhan_it_test.%,huizhan_ja_test.%,huizhan_ko_test.%,huizhan_ms_test.%,huizhan_pt_test.%,huizhan_ru_test.%,huizhan_th_test.%,huizhan_tw_test.%,huizhan_vi_test.%,zixun_de_test.%,zixun_es_test.%,zixun_fr_test.%,zixun_in_test.%,zixun_it_test.%,zixun_ja_test.%,zixun_ko_test.%,zixun_ms_test.%,zixun_pt_test.%,zixun_ru_test.%,zixun_th_test.%,zixun_tw_test.%,zixun_vi_test.%,search_testqa.%,bidtest_JA_JP.%,bidtest_DE_DE.%,bidtest_IN_ID.%,bidtest_ES_ES.%,bidtest_KO_KR.%,bidtest_TH_TH.%,bidtest_FR_FR.%,bidtest_AR_SA.%,bidtest_ZH_TW.%,bidtest_IT_IT.%,bidtest_EN_US.%,bidtest_MS_MY.%,bidtest_RU_RU.%,bidtest_PT_PT.%,bidtest_VI_VN.%,bidtest_ZH_CN.%,boothtest_JA_JP.%,boothtest_DE_DE.%,boothtest_IN_ID.%,boothtest_ES_ES.%,boothtest_KO_KR.%,boothtest_TH_TH.%,boothtest_FR_FR.%,boothtest_AR_SA.%,boothtest_ZH_TW.%,boothtest_IT_IT.%,boothtest_EN_US.%,boothtest_MS_MY.%,boothtest_RU_RU.%,boothtest_PT_PT.%,boothtest_VI_VN.%,boothtest_ZH_CN.%,search_testqa_ar_sa.%,search_testqa_de_de.%,search_testqa_en_us.%,search_testqa_es_es.%,search_testqa_fr_fr.%,search_testqa_id_id.%,search_testqa_it_it.%,search_testqa_ja_jp.%,search_testqa_ko_kr.%,search_testqa_ms_my.%,search_testqa_pt_pt.%,search_testqa_ru_ru.%,search_testqa_th_th.%,search_testqa_vi_vn.%,search_testqa_zh_cn.%,search_testqa_zh_tw.%,search_test_ar_sa.%,search_test_de_de.%,search_test_en_us.%,search_test_es_es.%,search_test_fr_fr.%,search_test_id_id.%,search_test_it_it.%,search_test_ja_jp.%,search_test_ko_kr.%,search_test_ms_my.%,search_test_pt_pt.%,search_test_ru_ru.%,search_test_th_th.%,search_test_vi_vn.%,search_test_zh_cn.%,search_test_zh_tw.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 20256923
Relay_Log_Space: 4293096
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: 0
1 row in set (0.00 sec)
如果人一个出现问题,可以在从库执行:
mysql> stop slave;
mysql> start slave;
再检查是否正常,如果不成功再执行以下语句:
mysql>stop slave #关掉slave服务 也就是关闭主从同步
mysql>change master tomaster_host='192.168.4.106',master_user='root',master_password='~1~#dev',master_port=3306,master_log_file='log.000456',master_log_pos=4939863; #重新设置master库信息
mysql>start slave #开启slave服务
红字部分是在从库执行 以下红字部分:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.106
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.000456
Read_Master_Log_Pos: 20256923
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 4939863
Relay_Master_Log_File: log.000466
二:可能是网络不通导致不同步,可以检查是否能互相ping通?如果有问题,应该请求网络工程师了。
三:必须保证主从数据库所有表的所有字段属性一致,否则这个库就不同步,例如:
master库中的一个库中的一个表:Product
mysql> desc Product;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| prdId | bigint(20) | NO | | NULL | |
| prdTitle | varchar(1024)| YES | | NULL | |
| 1stCtgId | int(11) | NO | | NULL | |
| 2ndCtgId | int(11) | NO | | NULL | |
| 3rdCtgId | int(11) | YES | | NULL | |
slave库中的一个库的这个表:Product
mysql> desc Product;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| prdId | bigint(20) | NO | | NULL | |
| prdTitle |varchar(1000) | YES | | NULL | |
| 1stCtgId | int(11) | NO | | NULL | |
| 2ndCtgId | int(11) | NO | | NULL | |
| 3rdCtgId | int(11) | YES | | NULL | |
改成一致即可;
四:如果由于某种原因导致slave库数据多于master库,解决办法使主备数据一致:
方法1:mysqldump
先在master库:mysqldump -uroot -p密码 数据库名 > 导出的文件名
然后把导出的文件传给slave 然后关掉
然后再在slave上:mysql>source /opt/导出的文件名
方法2:冷备
先把master上要操作的库给锁定,直接把主库的数据文件scp到备库相应位置,然后再把slave库的相应的文件给移动到别的目录或者重命名,
mysql>flush tables with read lock; ##登录上mysql后直接执行这个语句,就把所有的库的所有表修锁定了
mysql>unlock tables; ###解锁
五:还可以尝试在master上重新给slave授权。
GRANT replication slave ON *.* TO ‘root’ identified by '123';
flush privileges;
六 有时候需要跳过某个无法执行的命令,需要在slave处于stop状态下,执行 set global sql_slave_skip_counter=N以跳过命令;
mysql>stop slave
mysql>set global sql_slave_skip_counter=1 ####会连续跳过若干个event,直到当前所在的事务结束
解释何为event :MySQL从库从主库上复制binlog文件内容到本地执行。在binlog上命令以event的形式存在,并非一个命令对应一个event。以一个insert语句为例(引擎InnoDB、binglog_format=statement), 在binlog中实际上有三个event,分别为begin\insert\commit 。 命令类型都是Query_log_event.
然后再执行:
mysql>stop slave