Mysql主从扩展之问题及解决方法

qq524061227

一、配置Mysql主从遇到的问题及解决方法：
　　主从复制是Mysql内建的复制功能，它是构建高性能应用程序的基础，技术成熟，应用也很广泛。其原理就是通过将Mysql主库的sql语句复制到从库上，并重新执行一遍来实现的。在进行主从复制时，所有命令都必须在主库上进行，从库不做操作。否则，会引起主从库之间的数据不同步，复制会中断。
　　

**1、）mysql主从配置uuid相同错误解决**　　

　　配置mysql主从时，由于是拷贝的mysql目录，导致主从mysql uuid相同， Slave_IO无法启动，报错信息如下：
　　The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
　　解决办法:修改mysql data 目录下auto.cnf 文件中uuid的值，使两台mysql不同即可，修改后重启mysql服务。
　　问题: 从数据库无法同步
　　2）、Slave_SQL_Running 值为 NO，或 Seconds_Bebind_Master 值为 Null
　　原因:

• 　　程序有可能在 slave 上进行了写操作
  
  
• 也有可能是 slave 机器重启后，事务回滚造成的
  

　　解决方法一:
　　msyql> stop slave;
　　msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
　　msyql> start slave;
　　解决方法二:
　　msyql> stop slave;
　　#查看主服务器上当前的 bin-log 日志名和偏移量
　　msyql> show master status;
　　#获取到如下内容:
　　+------------------+----------+--------------+------------------+
　　| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000005 |      286 |              |                  |
　　+------------------+----------+--------------+------------------+
　　#然后到从服务器上执行手动同步
　　msyql> change master to
　　

->master_host="192.168.10.1",　　

　　
->master_user="user",
　　

　　
->master_password="123456",
　　

　　
->master_post=3306,
　　

　　
->master_log_file="mysql-bin.000005",
　　

　　
->master_log_pos=286;
　　

　　msyql> start slave;
　　4）、场景1：主库上用系统命令复制或删除表数据文件
　　【模拟异常】：
　　主库上直接copy表数据文件，或直接rm表数据文件主库上拷贝test表数据文件后，执行insert into test values('111');或主库上执行rm -f test05.*后，执行create table test05(a int(11));
　　【错误日志】：
　　从库日志：SHOW SLAVE STATUS \G;
　　Last_Errno: 1146
　　Last_Error:Error 'Table'testdb.test 'doesn't exist'on query' insertinto test values('111') '.
　　Default database: 'testdb'. Query: 'insert into testvalues('111')'
　　或者如下：
　　Last_Error: Error 'Table’test05 'already exists' on query.
　　【错误原因】：
　　表的创建或删除不是通过执行sql，未写入binlog，从库上没有相关表；
　　【解决方案】：
　　在从库上手动创建此表（建表语句可参考主库）；
　　以后，主库上对表的操作请通过sql完成，避免使用系统命令拷贝或删除
　　5）、场景2：数据不一致：包括删除失败、主键重复、更新丢失
　　【问题1】：
　　主键重复：在slave已经有该记录，又在master上插入了同一条记录。
　　从库日志：SHOW SLAVE STATUS \G;
　　Last_Errno: 1062
　　Last_Error: Error 'Duplicate entry 'xxxn-66-77' for key1' on query. Default database: 'guild'. Query: 'insert into pynpcrecord setMapCode = 'xxxn', UpdateTime = '2015-08-07 00:00:32''
　　

【解决方案】：　　

　　方案1：在从库上将重复的主键记录删除，再次重启主从；
　　deletefrom xxxx where 主键=yyyy;
　　stopslave;start slave;
　　

   方案2：停掉主从同步，忽略一次错误，再开启同步：　　

　　
stop slave;
　　

　　set global sql_slave_skip_counter=1;startslave;
　　

　　若是新配主从，忽略3次还报此错，还可以在my.cnf里加
　　

  一 行： slave-skip-errors=1062　　

　　然后重启实例，再重启主从同步；
　　

　　stop slave; start slave;
　　【问题2】删除失败：在master上删除一条记录，而slave上找不到。
　　从库日志：SHOW SLAVE STATUS \G;
　　Last_Errno: 1032;
　　Last_Error: Could not execute Delete_rows event ontable hcy.t1;
　　Can't find record in 't1',
　　【解决方案】：
　　由于master要删除一条记录，而slave上找不到而报错，这种情况主库都将其删除了，从库可以直接跳过。
　　可用命令：
　　stop slave;
　　set global sql_slave_skip_counter=1;startslave;
　　【问题3】：更新丢失：在master上更新一条记录，而slave上找不到，丢失了数据。
　　从库日志：SHOW SLAVE STATUS \G;
　　Last_Errno: 1032;
　　Last_Error: Could not execute Update_rows event ontable hcy.t1; Can't find record in 't1',
　　

【解决方案】：　　

　　把丢失的数据在slave上填补，然后跳过报错即可。
　　6）、场景3：字段不一致：包括字段丢失、不够长等
　　【问题1】
　　从库日志：SHOW SLAVE STATUS \G;
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: No
　　Last_Errno: 1264
　　Last_Error: Error 'Out of range value for column 'JFNow' at row 1' onquery. Default database: 'guild'. Query: 'update pyPHBWS set JFNow =JFNow -1 wherePlayerName = '狂魔''
　　虽然从库该字段和主库的一致，但从库还是报错：
　　guild> desc pyPHBWS;
　　+------------+---------------------+------+-----+---------+-------+
　　| Field      |Type                | Null | Key |Default | Extra |
　　+------------+---------------------+------+-----+---------+-------+
　　| PlayerName | varchar(30)         | NO  | PRI | NULL    |       |
　　| JFNow   | int(10)unsigned    | YES  |     |NULL    |       |
　　| JFAll   |int(10) unsigned    | NO   |    | NULL    |       |
　　

【解决方案】　　

　　修改字段：
　　ALTER TABLE guild.pyPHBWS MODIFY JFNowbigint(20) unsigned;
　　重启主从：stop slave;start slave;
　　【问题2】
　　从库日志：
　　SHOW SLAVE STATUS \G;
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: No
　　Last_Errno: 1054

　　Last_Error:Error 'Unknown column 'qdir' in 'field list''on query. Default database: 'club'. Query: 'insert into club.question_del (id,pid, ques_name,>　　【解决方案】
　　主库：查询 desc club.question_del，发现club.question_del表里面没有qdir这个字段；

　　从库：执行>　　7）、场景4：超出MyISAM数据表大小限制
　　【错误日志】
　　从库日志：
　　SHOW SLAVE STATUS \G;
　　Slave_IO_Running: No
　　Slave_SQL_Running: Yes
　　Last_Errno : 1114
　　Last_Error : Error 'The table   'tbleventlog' is full' onquery. Default database: 'dblog'. Query: 'insert into tbleventlog(PlayerName, ACTION, VALUE, PARAM, TIME) values ('ĺɫ', '־', '620',':2,:397842703', '2015-07-28 06:56:04')'
　　找到该实例所在的目录，发现该表大小超过4GB；
　　【解决方案】
　　对于MyISAM数据表,单个.MYD和.MYI默认4GB。
　　利用AVG_ROW_LENGTH和MAX_ROWS建表选项可以把这个最大值扩大到800万TB.
　　max_rows主要对myisam生效.
　　从库：调整max_rows并重启slave.

　　use dblog;>　　stop slave; start slave;
　　8）、场景5：slave的中继日志relay-bin损坏
　　【模拟异常】
　　SLAVE在宕机，或者非法关机，例如电源故障、主板烧了等，造成中继日志损坏，同步停掉。
　　【错误日志】
　　从库日志：SHOW SLAVE STATUS \G;
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: No
　　Last_Errno: 1593

　　Last_Error: Error initializing>　　【解决方案】
　　在主库上找到同步的binlog和POS点，然后重新做同步，这样就可以有新的中继日志了。
　　mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821;
　　场景6：binlog index记录不一致
　　【模拟异常】
　　主数据库突然停止或问题终止，更改了mysql-bin.xxx日志，slave服务器找不到这个文件。
　　【错误日志】
　　从库日志：SHOW SLAVE STATUS \G;
　　Master_Log_File: mysql-bin.000029
　　Last_Errno: 1594

　　Last_Error:>　　【解决方案】
　　找到同步的点和日志文件，然后chage master即可：
　　change master to master_log_file='mysql-bin.000025',master_log_pos=1010663436;
　　9）、场景7：时区不一致导致主从数据不一致
　　【模拟异常】：主从服务器设置不同的时区
　　主库：show variables like '%timezone%';      #.看到变量timezone值为EDT
　　从库：showvariables like '%timezone%';       #.看到变量timezone值为 CST
　　【错误日志】：主库执行insert into tbname(dtime)values(now());
　　主库：  select* from tbname;  #.看到字段dtime值为 2013-05-08 18:40:18
　　从库：  select* from tbname;  #.看到字段dtime值为 2013-05-09 06:40:18
　　【解决方案】：
　　设置主从为相同时区，并尽量使用相同时间服务器
　　如果对时间字段用now()写入，在删除时候用delete * from tbname where dtime='xxx',由于主从时间是不一致的，就会造成删除的非同一条记录，也会引起主键冲突问题。
　　10）、场景8：字段集不一致
　　【模拟异常】
　　1.主库：版本MySQL 4.0.18，字符集gb2312，主键字段PlayerName
　　show create table pybcsltscore;
　　PRIMARY KEY (PlayerName)  ENGINE=MyISAM DEFAULT CHARSET=gb2312
　　2.从库：版本MySQL 4.1.22，字符集latin1，主键字段PlayerName
　　show create table pybcsltscore;
　　PRIMARY KEY (PlayerName)  ENGINE=MyISAM DEFAULT CHARSET=latin1
　　3.主库：执行sql:
　　mysql> insert into pybcsltscore set PlayerName = '怒☆斩', PT = 'pchg.c8';
　　mysql> insert into pybcsltscore set PlayerName = '怒★斩', PT = 'pchg.c8';

• 主库：查询正常
  

　　select * from pybcsltscore where playername='怒☆斩' or playername='怒★斩';
　　【错误日志】
　　从库：查询异常，查询实心星号，结果却出现空心星号
　　select * from pybcsltscore_bak where playername='怒★斩';
　　从库: 从库状态:
　　Last_Errno: 1062
　　Last_Error: Error 'Duplicate entry '怒★斩' for key 1' on query. Defaultdatabase: 'test0505'. Query: 'insert into pybcsltscore set PlayerName = '怒★斩', PT = 'pchg.cs68'‘
　　从库：插入playname=’怒★斩’ 的记录，会提示主键冲突
　　insert into score_bak set PlayerName = '怒★斩', PT = 'pchg.cs68';
　　ERROR 1062 (23000): Duplicate entry '怒★斩' for key 1
　　【解决方案】
　　方案1. 从库：去掉主键
　　alter table pybcsltscore_test0513 drop primary key;
　　stop slave sql_thread; start slave sql_thread;
　　

   方案2. 从库：修改默认编码为gb2312　　

　　mysql --default-character-set=gb2312 -S mysql3307.sock
　　

   从库：再次查询：　　

　　select * from pybcsltscore where playername='怒☆斩' or playername='怒★斩';
　　场景9：max_allowed_packet太小
　　【模拟异常】

• 主库：设置max_allowed_packet为特小值，比如12K：
  

　　mysql> set global max_allowed_packet=12*1024;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show variables like 'max_allowed_packet';
　　+--------------------+-------+
　　| Variable_name      | Value |
　　+--------------------+-------+
　　| max_allowed_packet | 12288 |
　　+--------------------+-------+

• 重启slave io thread
  

　　#说明：slave如果不重启的话，个人觉得主从关系所使用的主库的变
　　不会改变，重启以便重新加载一些变量

• 　　主库：导入r2.txt（仅一行记录
  　　#du -sh r2.txt    80K    r2.txt）
  　　./bin/mysql test0505 -e "load data infile'/tmp/r2.txt' into table test2;"
  
  
• 查看从库状态
  

　　Slave_IO_Running: No
　　Slave_SQL_Running: Yes

• 从库：
  

　　mysql> show variables like 'max_allowed_packet';
　　+--------------------+----------+
　　| Variable_name      | Value    |
　　+--------------------+----------+
　　| max_allowed_packet | 16776192|
　　+--------------------+----------+
　　【错误日志】
　　查看从数据库的错误日志，找到如下信息:
　　[ERROR] Got fatal error 1236: 'log event entry exceededmax_allowed_packet;
　　Increase max_allowed_packet on master' from master whenreading data from binary log
　　或类似：
　　[ERROR] Error reading packet from server: Got packetbigger than          'max_allowed_packet' bytes (server_errno=2020)
　　应该是master上的dump线程在从binlog读取数据时，读取的结果集超出了max_allowed_packet限制，造成往slave发送失败。
　　【解决方案】
　　修改max_allowed_packet的大小，然后重启slave。建议主从一致
　　mysql> set global max_allowed_packet=1610241024;
　　重启slave, stop salve;start slave;
　　场景10：临时表太大导致磁盘写满
　　【错误日志】：从库日志
　　Last_Errno: 3
　　Last_Error: Error 'Error writing file '/tmp/FeqMc' (Errcode: 28)'on query.
　　Default database: 'evt'. Query: 'delete from goodslogwhere OpTime