Analyze issues related to Mysql container startup failure recovery

Mysql container startup failure recovery case

After restarting the NAS yesterday, I found that the NAS crashed again this morning. You can only force shutdown and restart again.
Before starting the docker container, I adjusted the memory of the mysql container to 512M. Then, tragically, I discovered that it could not be started. Later I found out that the failure to start was not due to me adjusting the memory.

View the log, the display is as follows:

2020-12-27T02:43:53.375776Z 0 mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.
2020-12-27T02:43:53.375776Z 0 mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
2020-12 -27T02:43:53.375776Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var /lib/mysql-files
2020-12-27T02:43:53.376005Z 0 [ERROR] [MY-010119] [Server] Aborting

Baidu, the solution is to re-run a mysql container , and add -v /mnt/md0/User/wzp/home/www/mysql-files:/var/lib/mysql-files/ to the previous configuration.

Now here comes the problem. When I ran the mysql container before, I did not record the complete run command. Fortunately, you can view it through the rekcod tool. Even better, this tool can be run through docker. Because my NAS system is not a Linux distribution, many tools like npm and yum cannot be installed.

Regarding the use of rekcod, please refer to linuxea: How to reproduce the command to view docker run parameters

In short, the usage is as follows:

1 Docker install rekcod

$ docker pull nexdrew/rekcod
$ alias rekcod="docker run --rm -v /var/run/docker.sock:/var/run/docker.sock nexdrew/rekcod"

2 How to use:

[root@TNAS-012664 ~]# rekcod mysql ==>mysql是我的容器名
docker run --name mysql --runtime runc -v /mnt/md0/User/wzp/home/www/mysql/:/var/lib/mysql -p 3306:3306/tcp --net bridge --restart no -h 39964e9e508a --expose 3306/tcp --expose 33060/tcp -e 'MYSQL_ROOT_PASSWORD=123456' -e 'PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin' -e 'GOSU_VERSION=1.12' -e 'MYSQL_MAJOR=5.7' -e 'MYSQL_VERSION=5.7.30-1debian10' -d -t -i --entrypoint "docker-entrypoint.sh" mysql 'mysqld'

You can see that my database files are placed in the /mnt/md0/User/wzp/home/www/mysql/ directory, I check After downloading the directory file, the data should not be lost.

So, I created a new mysql2 container, the command is as follows
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql2 -v /mnt/md0/User/wzp /home/www/mysql/:/var/lib/mysql -v /mnt/md0/User/wzp/home/www/mysql-files:/var/lib/mysql-files/ mysql

Then, enter mysql2 and find that the database cannot be connected
docker exec -it mysql2 /bin/bash

Check the mysql status

root@0e83698acbfb:/# mysqld status
mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/docker.cnf' is ignored.
mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.
2020-12-27T02:39:41.865252Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-12-27T02:39:41.865455Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 105
2020-12-27T02:39:41.871715Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2020-12-27T02:39:41.872541Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-12-27T02:39:41.872776Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.
root@0e83698acbfb:/# Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!^C

Use the following method to Start mysqld in root mode

root@0e83698acbfb:/# mysqld --user=root
mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/docker.cnf' is ignored.
mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.
2020-12-27T02:40:57.169719Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-12-27T02:40:57.169896Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 116
2020-12-27T02:40:57.184807Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-27T02:40:57.241048Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2020-12-27T02:40:58.241783Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2020-12-27T02:40:59.242983Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2020-12-27T02:41:00.244290Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2020-12-27T02:41:01.245762Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2020-12-27T02:41:02.247539Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

The screen keeps printing Unable to lock ./ibdata1 error: 11, and can only be forced to interrupt by CTRL-C. After searching on Baidu, there are two ways to solve this problem

1 Refer to mysql [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
Analysis of this article shows that the error is mainly caused by the following Two reasons. Unfortunately, the method in the article cannot solve my problem.

1. Insufficient disk space directory
2. The ibdata1 file is occupied by other processes

2 Refer to mysqld report InnoDB: Unable to lock ./ibdata1 error: 11This article perfectly solved my problem. The following operations were mainly performed.

2.1 First, enter the directory where my mysql database is located
cd /mnt/md0/User/wzp/home/www/mysql

2.2 Then change ibdata1 After the file is renamed, cp it back again. I don't quite understand why exactly this is done.

[root@TNAS-012664 www]# cd /mnt/md0/User/wzp/home/www/mysql/
[root@TNAS-012664 mysql]# ls
 auto.cnf          binlog.index      client-key.pem   dci            '#ib_16384_0.dblwr'   ib_logfile0     mautic               nianbao    performance_schema   robot             shangbiao          undo_001         zeng
 baike             ca-key.pem        company_works    dianzicaipiao  '#ib_16384_1.dblwr'   ib_logfile1     mysql                niuwan     private_key.pem      sara_wiki         sjzt_ry6           undo_002
 bigdatapaltfrom   ca.pem            copyrightdata    gs_data         ib_buffer_pool      '#innodb_temp'   mysql.ibd            pachong    public_key.pem       server-cert.pem   sys                wenshu
 binlog.000001     client-cert.pem   db_huayun        hy_dci_admin    ibdata1              integrate       mysql_upgrade_info   pachong2   qianliu_wiki         server-key.pem    ucenter_huayunyy   yuanqixiaoshuo
[root@TNAS-012664 mysql]# mv ibdata1 ibdata1.bak
[root@TNAS-012664 mysql]# mv ib_logfile0 ib_logfile0.bak
[root@TNAS-012664 mysql]# mv ib_logfile1 ib_logfile1.bak
[root@TNAS-012664 mysql]# cp -a ibdata1.bak ibdata1
[root@TNAS-012664 mysql]# cp -a ib_logfile0.bak ib_logfile0
[root@TNAS-012664 mysql]# cp -a ib_logfile1.bak ib_logfile1

2.3 Then I deleted the previous mysql2 container, ran a mysql2 container again with the following command, and found that I could connect to the mysql database.

[root@TNAS-012664 ~]# docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456  --name mysql2 -v /mnt/md0/User/wzp/home/www/mysql/:/var/lib/mysql  -v /mnt/md0/User/wzp/home/www/mysql-files:/var/lib/mysql-files/    mysql
1e031247ea46e82f6205db68e7fb1b55389c87e5e2cb13517f9e1ac17d514509
[root@TNAS-012664 ~]# docker exec -it mysql2 /bin/bash
root@1e031247ea46:/# mysql -uroot -p
mysql: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

---

Think that everything will be fine once the mysql container is ready? No, too naive! There is another episode waiting for me. I just solved the mysql container here, and suddenly I found that my wiki container was missing for no reason. Fortunately, the database files are there and everything can be restored as before.

1 Use the following commands to create two new wikis

docker run --name sarawiki --link mysql2:mysql -p 8086:80 -d sarawang85/mediawiki:1.0.0
docker run --name qianliuwiki --link mysql2:mysql -p 8083:80 -d sarawang85/mediawiki:1.0.0

2 Then copy the backed up LocalSettings.php file and Logo image into the container, and you're done.

PS, if you edit the stop state container in the docker gui interface, such as setting the memory limit, a new container with the same name will actually be created. Because I found that after I did this, I was prompted to reinstall when I accessed my wiki.

PS again, mysql backup must be put on the agenda immediately. The memory limit of mysql cannot be limited by the container alone, my.cnf must also be changed accordingly.

Recommended study: "mysql video tutorial"

The above is the detailed content of Analyze issues related to Mysql container startup failure recovery. For more information, please follow other related articles on the PHP Chinese website!