[MySQLFAQ] series-enable the newly installed MySQL in GTID & binlog

Q: In the newly installed MySQL, the following message is displayed. I cannot log on. What is the problem? [Root @ imysqlmysql] # mysqlmysqlERROR1045 (28000): Accessdeniedforuserroot @ localhost (usingpassword: NO) answer: the database has been initialized by mysql_install_db. It should be reasonable to say that there is NO problem.

Q: In the newly installed MySQL, the following message is displayed. I cannot log on. What is the problem? [Root @ imysql mysql] # mysql mysqlERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO) answer: the database has been initialized by mysql_install_db. It should be okay.

Q: In the newly installed MySQL, the following message is displayed. I cannot log on. What is the problem?

[root@imysql mysql]# mysql mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

A: The database has been initialized by mysql_install_db. It should be okay. After investigation, we found that the permission mode of the $ datadir mysql database directory is:

drwx------ 2 mysql root        4096 Jan  9 20:22 mysql

It seems that there is no problem. Check the error log and find a row:

130109 21:04:23 [Warning] Bootstrap mode disables GTIDs. Bootstrap mode should only be used by mysql_install_db which initializes the MySQL data directory and creates system tables.ERROR: 1785  Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.130109 21:04:23 [ERROR] Aborting

In addition, the user tablespace file in the mysql database is displayed as follows:

-rw-rw---- 1 mysql mysql 10684 Jan  9 20:22 user.frm-rw-rw---- 1 mysql mysql     0 Jan  9 20:22 user.MYD-rw-rw---- 1 mysql mysql  1024 Jan  9 20:22 user.MYI

The size of the user tablespace data file is 0.
It seems that it has something to do with enabling GTID mode. After careful check, we found that GTID-related options are:

gtid_mode = ONbinlog_format = mixeddisable-gtid-unsafe-statements = 1log-bin=binlog

This is because when GTID mode is enabled, transaction consistency cannot be guaranteed when non-transaction tables are updated. Therefore, set disable-gtid-unsafe-statements = 1 to ensure master-slave data consistency.

Solution:
In this case, you can change the value of disable-gtid-unsafe-statements to 0. You can also disable the GTID and binlog options at the same time and enable them after initialization.

#gtid_mode = ON#log-bin=binlog

Or

disable-gtid-unsafe-statements = 0

Technical issues:

Basic MySQL knowledge

MySQL FAQ

Original article address: [MySQL FAQ] series-enable GTID & binlog newly installed MySQL prompts that you cannot log on. Thank you for sharing your thanks.