Technology sharing | common causes of MySQL startup failure

Author: Yao yuan

Focus on Oracle and MySQL databases for many years, Oracle 10G and 12C OCM, MySQL 5.6, 5.7, 8.0 OCP. Now Dingjia technology serves as a technical consultant to provide database training and technical support services for colleagues and customers.

Source of this article: original contribution * produced by aikesheng open source community. The original content cannot be used without authorization. Please contact Xiaobian for reprint and indicate the source.

There are two common reasons for MySQL startup failure: inability to access system resources and parameter setting errors. The following are analyzed respectively.

1, Unable to access system resources

MySQL cannot access the resources needed for startup, which is a common reason why MySQL cannot start, such as files, ports, etc. Since the MySQL user used to start the mysqld process in linux is usually unable to log in, you can use a command similar to the following to check the access rights of the file.

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

After finding the problem, you can usually solve the problem by modifying the permission or owner of the corresponding file or directory. However, sometimes mysql users have access to files and directories, but they will still be denied access. For example, the following example:

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

The test shows that mysql users have access rights to this directory, but the creation of files still fails. This situation puzzles many people. At this time, the access of mysql D process is usually blocked by selinux or apparmor of linux. You can see that the created table is not under the default directory of mysql, so the policy of selinux or apparmor does not contain the access rights to this directory, At this time, just modify the corresponding policy. Of course, stop selinux or apparmor.

Sometimes, although you have access to system resources, system resources have been occupied:

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

The reason for this failure is that another mysqld process has started and occupied the corresponding file.

2, Parameter setting error

It is also very common that MySQL cannot be started due to wrong parameter settings. At this time, first check the parameters that will be called when MySQL is started. The following command can query the order of calling parameter files when MySQL is started:

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

Knowing the call order of MySQL parameter files, we can check the corresponding parameter files and find out the errors. If we feel that the readability of the parameter files is not strong, we can use the following command to display the parameters to be called by mysqld program:

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

Note that this command will exit after displaying the parameters and will not really run mysqld. This command and my_print_defaults mysqld is completely equivalent, but the latter is displayed one parameter per line.

Then start debugging the suspicious parameters. The parameters and order I like to add are as follows:

1. Add the first parameter -- no defaults after mysqld. The function of this parameter is to inform mysqld not to read any parameter files when starting;

2. The second parameter is -- console, which will output error information to the screen. One disadvantage of this parameter is that all information is output to the screen, which makes the screen look messy, but it is very convenient for us to debug;

3. The third parameter is -- log error verbosity = 3, which will display detailed logs;

4. Then add confident parameters to the back. You can add only one parameter at a time, then start mysqld, and use the exclusion 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=on2020-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 226172020-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.root@scutech:~#
Looking at this example, we can easily know that we need to set the parameter gtid at the same time_ Mode and force_ GTID_ Only if consistency is on at the same time. Relevant recommendations:

Technology sharing | parsing and "tampering" MySQL's Binlog using Python

Technology sharing | MySQL uses MariaDB audit plug-in

Technology sharing | only frm and How to batch restore InnoDB tables when ibd files

Recent developments in the community

Keywords in this article: # troubleshooting ## startup failure#

Click "read the original" for more information

This article is shared from the wechat official account - ActiontechOSS.
In case of infringement, please contact Delete.
Article participation“ OSC source creation plan ”, you who are reading are welcome to join us and share with us.

Tags: Python Linux MySQL Oracle MariaDB SELinux innodb ocp

Posted by squalls_dreams on Sun, 08 May 2022 03:53:20 +0300