Shulou(Shulou.com)05/31 Report--

Editor to share with you what to do when init_command leads to the failure of mysql 5.6 master-slave connection. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Mysql 5.6 scheduled a reboot from the library host, and after rebooting the host, there was a problem.

The instance cannot connect to master.

The error is as follows:

[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 amp 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 Slave 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 thread exiting O thread exiting, read up to log 'FIRST', position 4

[/ code]

What's the matter? my colleague reset slave or reported an error.

Because gtid is enabled, the parameters of the gtid are consistent on both sides. It also eliminates the problem of gtid.

Because before the restart is OK, there is no data problem. I wonder.

If you look at the log, it's like this.

I didn't get it at the moment. Check the main library.

Here's the problem.

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

A sql statement is added to the init_command parameter on the main library.

Corresponds to the error report of init_command in the log.

Look at the permissions

[code]

Mysql.sock@ (none) > use mysql

Database changed

Mysql.sock@mysql > select * from tables_priv

+-+ +

| | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |

+-+ +

| |% | db_monitor | user_app | accesslog | root@localhost | 0000-0000: 00:00 | Insert |

+-+ +

1 row in set (0.02 sec)

Mysql.sock@mysql >

[/ code]

After commenting out the init_command, everything is ok.

It turns out that because of an event, a little audit function was added, and it was implemented with init_command.

It turns out that I forgot to authorize repl.

When causing repl to log in again, you have no right to operate this insert statement to report an error. I quit. Unable to connect to master.

The above is all the contents of the article "what to do if init_command causes mysql 5.6 master-slave connection failure". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.