（slave不停restart)mysqld got signal 11

2节点的mysql，M-S架构，通过VIP连接，出现有的时候连接成功，有的时候连接异常，排查问题：
1、分别查看M、S节点mysql的errlog,发现slave的errlog有如下信息：
。。。。。。。
（以下只列出一次start ——crashed的日志）
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
150827 13:57:59 mysqld_safe Number of processes running now: 0
150827 13:57:59 mysqld_safe mysqld restarted
2015-08-27 13:57:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-08-27 13:57:59 1733 [Warning] You need to use --log-bin to make --binlog-format work.
2015-08-27 13:57:59 7effe99a2720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-08-27 13:57:59 1733 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-27 13:57:59 1733 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-27 13:57:59 1733 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-08-27 13:57:59 1733 [Note] InnoDB: CPU does not support crc32 instructions
2015-08-27 13:57:59 1733 [Note] InnoDB: Using Linux native AIO
2015-08-27 13:57:59 1733 [Note] InnoDB: Initializing buffer pool, size = 6.0G
2015-08-27 13:57:59 1733 [Note] InnoDB: Completed initialization of buffer pool
2015-08-27 13:57:59 1733 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-27 13:57:59 1733 [Note] InnoDB: The log sequence numbers 329998793384 and 329998793384 in ibdata files do not match the log sequence number 329998794070 in the ib_logfiles!
2015-08-27 13:57:59 1733 [Note] InnoDB: Database was not shutdown normally!
2015-08-27 13:57:59 1733 [Note] InnoDB: Starting crash recovery.
2015-08-27 13:57:59 1733 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-27 13:58:00 1733 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-27 13:58:00 1733 [Note] InnoDB: from the doublewrite buffer...
2015-08-27 13:58:00 1733 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-27 13:58:00 1733 [Note] InnoDB: Waiting for purge to start
2015-08-27 13:58:00 1733 [Note] InnoDB: 1.2.10 started; log sequence number 329998794070
2015-08-27 13:58:00 1733 [Note] Server hostname (bind-address): '*'; port: 3306
2015-08-27 13:58:00 1733 [Note] IPv6 is available.
2015-08-27 13:58:00 1733 [Note]   - '::' resolves to '::';
2015-08-27 13:58:00 1733 [Note] Server socket created on IP: '::'.
2015-08-27 13:58:00 1733 [Warning] 'user' entry 'root@ygserver' ignored in --skip-name-resolve mode.
2015-08-27 13:58:00 1733 [Warning] 'user' entry '@ygserver' ignored in --skip-name-resolve mode.
2015-08-27 13:58:00 1733 [Warning] 'proxies_priv' entry '@ root@ygserver' ignored in --skip-name-resolve mode.
2015-08-27 13:58:01 1733 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2015-08-27 13:58:01 1733 [Note] Slave I/O thread: connected to master 'yougourp@192.168.211.245:3306',replication started in log 'mysql-bin.000285' at position 1051656170
2015-08-27 13:58:01 1733 [Note] Event Scheduler: Loaded 0 events
2015-08-27 13:58:01 1733 [Note] Execution of init_file 'PATH_TO_FILE.sql' started.
2015-08-27 13:58:01 1733 [Note] Event Scheduler: scheduler thread started with id 3
2015-08-27 13:58:01 1733 [Note] Execution of init_file 'PATH_TO_FILE.sql' ended.
2015-08-27 13:58:01 1733 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/home/data/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2015-08-27 13:58:01 1733 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2015-08-27 13:58:01 1733[Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000285' at position 1044957771, relay log './nor.000002' position: 1702
05:58:01 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.


key_buffer_size=536870912
read_buffer_size=8388608
max_used_connections=0
max_threads=1000
thread_count=3
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 25113803 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
。。。。。。。。。。。。。。
2、根据errlog中得知，slave在同步master的  'mysql-bin.000285' at position 1044957771时 ，mysql接着got signal 11 ;
3、查看master上binlog（ 'mysql-bin.000285' at position 1044957771 ）
~]# mysqlbinlog -vv --base64-output=DECODE-ROWS --start-position=1044957771  mysql-bin.000285> ~/285.sql
~]# more  ~/285.sql
# at 1044957771
#150807 13:24:43 server id 20  end_log_pos 1044957984   Query   thread_id=13235616      exec_time=0     error_code=0
use `report_mms_db1`/*!*/;
注释省略*/;
grant all on report_mms_db2.tbl_merchant_report_commodity_analysis to 'yujx'@'%' identified by 'yujx'
/*!*/;
# at 1044957984
注释省略 */;
grant all on report_mms_db3.tbl_merchant_report_commodity_analysis to 'yujx'@'%' identified by 'yujx'
/*!*/;
发现就是2条grant的操作
4、 接着手动的在slave上尝试grant授权和create user，发现mysql都会开始不停的restart，除非把slave停止（不再执行grant命令 ）
 mysql> GRANT ALL PRIVILEGES ON `report_mms_db1`.* TO 'yujx'@'%'  identified by 'yuruoyi';

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> stop slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

。。。。。。。。。。。。。。。
 mysql> CREATE USER 'yujx'@'%' identified by  'yujx';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> stop slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/data/mysql/mysql.sock' (2)
ERROR: 
Can't connect to the server

。。。。。。。。。。
尝试更换grant的database、user、passwd，以及create user 发现都会触发相同的slave restart现象
5、查看slave的 mysq l . user 权限 表 状态
mysql> show table status like 'user'\G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 18
 Avg_row_length: 99
    Data_length: 1892
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time:2013-04-18 14:21:20
    Update_time: 2013-04-18 14:23:20
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)
发现create_time和update-time都是2013年的，考虑mysql.user(权限表）是否异常

6、从master上dump出mysql.user > slave的mysql下
]# mysqldump -uroot -p mysql user  --quick --single-transaction > user.sql
Enter password: 
]# mysql -uroot -p -h slave_ip -Dmysql
Enter password: 
7、 尝试grant和create用户，发现能成功授权和创建，并且不会导致之前的slave不停的restart
8、start slave 复制也正常（不会再触发slave 不停的restart）
     show slave status\G   #查看状态 正常，正在追赶master

综述：从master重新dump权限表mysql.user，导入到slave的mysql库下 解决了mysql.user 表异常导致无法grant和create user的异常

mark： http://blog.itpub.net/22664653/viewspace-756097/  -------- 另外一个mysqld got signal 11 案例

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27000195/viewspace-1784172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27000195/viewspace-1784172/