因为init_command 导致的 mysql 5.6 主从连接失败的案例一则。

最新推荐文章于 2024-07-21 03:44:52 发布

cudi7618

最新推荐文章于 2024-07-21 03:44:52 发布

阅读量449

点赞数

文章标签：数据库

mysql 5.6 从库主机安排了计划性重启，重启主机后，问题来了。
实例无法连接master 了。

报错如下：

[code]
2018-09-17 12:21:50 28554 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.8.42', master_port= 3307, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.8.42', master_port= 3307, master_log_file='', master_log_pos= 4, master_bind=''.2018-09-17 12:21:58 28554

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended.

Please consider using the USER and PASSWORD connection options for START SLAVE;

see the 'START SLAVE Syntax' in the MySQL Manual for more information.2018-09-17 12:21:58 28554

[Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './relay-bin.000001' position: 42018-09-17 12:21:58 28554

[Note] Slave I/O thread: connected to master 'repl@192.168.8.42:3307',replication started in log 'FIRST' at position 42018-09-17 12:21:58 28554

[Warning] "SELECT UNIX_TIMESTAMP()" failed on master, do not trust column Seconds_Behind_Master of SHOW SLAVE STATUS. Error: Aborted connection 12505106 to db: 'unconnected' user: 'repl' host: '192.168.8.43' (init_connect command failed) (1184)2018-09-17 12:21:58 28554 [ERROR] Slave I/O: The slave I/O thread stops because a fatal error is encountered when it tries to SET @master_heartbeat_period on master. Error: , Error_code: 15932018-09-17 12:21:58 28554

[Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
[/code]

咋回事呢，同事重置了slave 还是报错。

因为启用了gtid ，两边对照了gtid 的各个参数都是一致的。也排除了gtid 的故障。

因为重启之前都是OK 的，也不存数据问题。纳闷了。

看日志就上上面这样了。

一时没明白过来。去主库上看看。

问题找到了。

#init_connect='set names gbk;insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user());commit;'

主库上的init_command 参数加了一条sql 语句。

对应到了日志里的 init_command 的报错。

看看权限

[code]

mysql.sock@(none)> use mysql

Database changed

mysql.sock@mysql> select * from tables_priv ;

+------+------------+--------------+------------+----------------+---------------------+------------+-------------+

+------+------------+--------------+------------+----------------+---------------------+------------+-------------+

+------+------------+--------------+------------+----------------+---------------------+------------+-------------+

1 row in set (0.02 sec)

mysql.sock@mysql>