The MySQL server is running with the --read-only option so it cannot execute this statement

I was in a meeting when a colleague called to report that the development library could not be written. The error message was as follows:

1209 - The MySQL server is running with the --read-only option so it cannot execute this statement

Generally, there are two reasons for this error:

1. Connect to the slave database. The slave library is generally set to read-only.

2. The read_only parameter of the master database is changed to 1

Developers are ordinary users and should not have permission to modify the value of this parameter.

The DBA will not actively modify this parameter. So what exactly is the reason why the development library cannot be written?

First, we confirmed that the problem was not with the developers, because more than 200 R&D personnel in the department encountered this problem.

To solve the problem first, query the value of the read_only parameter on the master database. Sure enough, read_only is set to 1.

After manually changing it to 0, the problem is solved. The question is why is read_only set to 1?

The solution steps are as follows:

mysql> select @@read_only;

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

| @@read_only |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> set global read_only=0;

Query OK, 0 rows affected (0.00 sec)

Checking the mysql error log, I found the following information:

151231 13:55:11 mysqld_safe Number of processes running now: 0

151231 13:55:11 mysqld_safe mysqldrestarted

This shows that MySQL has been restarted. What is the reason for the restart?

I checked the system log and found the following error:

#tail -100f /var/log/message

Dec 31 13:55:11 mysql2dev kernel: [8680] 500 8680 27084 92 3 0 0 bash

Dec 31 13:55:11 mysql2dev kernel: Out ofmemory: Kill process 12805 (mysqld) score 964 or sacrifice child

Dec 31 13:55:11 mysql2dev kernel: Killedprocess 12805, UID 500, (mysqld) total-vm:13146848kB, anon-rss:7870704kB,file-rss:16kB

Dec 31 13:55:11 mysql2dev kernel: rsyslogdinvoked oom-killer: gfp_mask=0x201da, order=0, oom_adj=0, oom_score_adj=0

Dec 31 13:55:11 mysql2dev kernel: rsyslogdcpuset=/ mems_allowed=0-1

Dec 31 13:55:11 mysql2dev kernel: Pid:21035, comm: rsyslogd Not tainted 2.6.32-358.el6.x86_64 #1

Dec 31 13:55:11 mysql2dev kernel: CallTrace:

From this error, we can see that MySQL restarted due to memory overflow.

Out of memory: Kill process 12805 (mysqld)score 964 or sacrifice child

So what caused the memory leak?

After checking the system's historical commands, I found that a colleague was doing a backup. The system was under great pressure at the time, and the secondary system did not have a swap partition set up. The above reasons led to the restart of MySQL.

Swap: 0 0 0

Why does restarting result in read_only=1? Maybe read_only is set in the configuration file. Check the configuration file.

#grep read_only my.cnf

read_only = on

At this time, the reason why the development environment suddenly could not be written was finally revealed.

You may ask why the main library sets read_only=on, because it was originally a MMM environment.

Now that the MMM environment has been removed, set read_only in the configuration file to 0. The problem of the development library not being able to be written is now solved.

MySQL reports an error: The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement, etc.

1. Log in to mysql: mysql –u root –p

mysql> set global read_only=0;
(Turn off the read-only attribute of the new master database)

flush privileges;

2. Modify the MySQL configuration file my.cnf, which is in the /etc directory

Restart the mysql service: service mysqld restart

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot exe

This error was reported when resetting the password during the installation of MySQL 8.0.3. The reason is that if the password is not set, you need to add this section to /etc/my.cnf to operate mysql

#Skip password verification

skip-grant-tables

But after adding this sentence, MySQL reported this error again, which became an infinite loop. Finally, I found a solution.

This is because the permissions have been set but not refreshed.

Execute first

flush privileges;

Execute the SQL statement again and it succeeds

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';