Common reasons for MySQL startup failure

author ： Yao yuan
Focus on Oracle、MySQL Database for years ,Oracle 10G and 12C OCM,MySQL 5.6,5.7,8.0 OCP. Now, Dingjia technology is a technical consultant , Provide database training and technical support services for colleagues and customers .
In this paper, the source ： Original contribution
* Produced by aikesheng open source community , Original content is not allowed to be used without authorization , For reprint, please contact the editor and indicate the source .

MySQL There are two most common causes of startup failure , They are caused by inaccessible system resources and parameter setting errors , The analysis is as follows .

One 、 Unable to access system resources

MySQL Unable to access the resources needed for startup is caused by MySQL A common reason for not starting , Such as ： file , Port, etc . because linux To start mysqld Process mysql Users are usually not allowed to log in , You can use commands like the following to check file access .

sudo -u mysql touch /var/lib/mysql/b

After finding out the problem , Modifying the permissions or ownership of the corresponding file or directory can usually solve the problem . But sometimes mysql Users have access to files and directories , But still be denied access , Take the following example ：

mysql> system sudo -u mysql touch /home/mysql/data/a
mysql> create table t1 (
id int primary key,n varchar(10
) data directory
ERROR 1030 (HY000): Got error 168 from storage engine

Test instructions mysql Users have access to this directory , But the creation of the file still failed , This situation confuses a lot of people , This time is usually mysqld Process access is linux Of selinux or apparmor To stop , You can see that the created table is not in mysql Under the default directory of , therefore selinux or apparmor Of policy There is no access to this directory , In this case, only the corresponding modification policy That's it , Of course selinux or apparmor It's OK to stop .

Sometimes, although you have access to system resources , But system resources have been taken up ：

mysqld --no-defaults --console --user mysql
2020-11-03T03:36:07.519419Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 21171
2020-11-03T03:36:07.740347Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

The cause of this failure is another mysqld The process has started and occupied the corresponding file .

Two 、 Parameter setting error

Wrong parameter setting results in MySQL The reason why it can't start is very common , At this point, first check MySQL Parameters that will be called at startup , The following command can be used to query MySQL The order in which parameter files are called at startup ：

$ mysqld --verbose --help | grep "Default options " -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

got it MySQL The calling order of the parameter file , We can check the corresponding parameter file , Find out the mistakes , If you think the readability of the parameter file is not strong , You can use the following command to display mysqld The parameters that the program will call ：

$ mysqld --print-defaults
/usr/sbin/mysqld would have been started with the following arguments:
......

Note that the command exits after displaying the parameters , It doesn't really run mysqld. This command and my_print_defaults mysqld It's completely equivalent , It's just that the latter is displayed with one parameter per line .

Then start debugging the suspicious parameters , My favorite parameters and order are as follows ：

stay mysqld Then add the first parameter --no-defaults , The function of this parameter is to inform mysqld Don't read any parameter files at boot time ;
The second parameter is --console, This parameter will output the error message to the screen , One drawback of this parameter is that all information is output to the screen , Make the screen look messy , But it's very convenient for us to debug ;
The third parameter is --log-error-verbosity=3, This parameter will display the detailed log ;
And then add a certain parameter to the back , You can add only one parameter at a time , Then start mysqld, Use the elimination method to find out the wrong parameters step by step .

Look at this example ：

mysqld --no-defaults --console --log-error-verbosity=3 --user mysql --gtid_mode=on
2020-11-03T07:14:20.384223Z 0 [Note] [MY-010949] [Server] Basedir set to /usr/.
2020-11-03T07:14:20.384254Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 22617
2020-11-03T07:14:20.400221Z 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
……
2020-11-03T07:14:21.632851Z 0 [ERROR] [MY-010912] [Server] GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON.
2020-11-03T07:14:21.634183Z 0 [ERROR] [MY-010119] [Server] Aborting
……
2020-11-03T07:14:23.026551Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.
[email protected]:~#

Look at this example , It's easy to know that we need to set parameters at the same time GTID_MODE and ENFORCE_GTID_CONSISTENCY Also for on Talent .