Write down the troubleshooting process of mysql service exception in FreeBSD system

With the monitoring assistant suddenly prompting many database connection errors:

Troubleshooting database errors is on the agenda.

Restart Dafa

I have to say that sometimes restarting Dafa is very helpful. So we also try to restart mysql

 $ /usr/local/etc/rc.d/mysql-server stop
$ /usr/local/etc/rc.d/mysql-server start

If you connect again, the data will not be connected directly. At this point, you need to come to the correct track: look at the content of the error report, check the cause according to the content of the error report, and solve the problem.

error log

Unfortunately, mysql will not report any error information even if the startup fails during the startup process. We need to use the mysql-server status command to check whether mysql is successfully started:

 root@YunzhiTest:/usr/home/panjie # /usr/local/etc/rc.d/mysql-server status
mysql is not running.

Whether to print the log, and where the log is placed, requires us to configure it manually. On the premise that the mysql service is successfully started, we can actually use the relevant commands of mysql to view the current configuration file location, but unfortunately the current mysql has not been successfully started, so at this time, we need to use some query software or install mysql to use it. tools (such as FreeBSD ports) to find the location of the mysql configuration file. In FreeBSD, the configuration file for mysql is located in /usr/local/etc/mysql :

 root@YunzhiTest:/usr/home/panjie # cd /usr/local/etc/mysql/
root@YunzhiTest:/usr/local/etc/mysql # ls
keyring        my.cnf        my.cnf.sample

Then we back up a configuration file cp my.cnf my.cnf.bak and then edit it:

 [mysqld]
log-error                       = /var/log/mysql/error.log
user                            = mysql
port                            = 3306

Add log-error under mysqld. At the same time, since the current mysql startup user is mysql, it is also necessary to ensure that the mysql user has absolute permissions on the relevant log paths:

 $ mdkir /usr/log/mysql
$ chown mysql:mysql /usr/log/mysql

View logs

At this point, we start the mysql service again, and you can view the error.log file generated under /var/log/mysql/:

 $ /usr/local/etc/rc.d/mysql-server start

The more important error messages are as follows:

 2022-07-11T14:22:25.946391Z 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2022-07-11T14:22:25.946435Z 0 [Note] InnoDB: Setting file '/var/db/mysql/ibtmp1' size to 128 MB. Physically writing the file full; Please wait ...
2022-07-11T14:22:25.947132Z 0 [Note] InnoDB: Progress in MB:
 1002022-07-11T14:22:26.085805Z 0 [Warning] InnoDB: Retry attempts for writing partial data failed.
2022-07-11T14:22:26.085855Z 0 [ERROR] InnoDB: Write to file /var/db/mysql/ibtmp1failed at offset 133169152, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2022-07-11T14:22:26.085940Z 0 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2022-07-11T14:22:26.085951Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2022-07-11T14:22:26.085968Z 0 [ERROR] InnoDB: Could not set the file size of '/var/db/mysql/ibtmp1'. Probably out of disk space

The above error is probably talking about a problem: the disk space is full, and this problem causes mysql to fail to start.

Sort data

Once the root cause of the problem is found, solving the problem becomes the easiest thing to do.

 root@YunzhiTest:/usr/local/etc/mysql # df -h
Filesystem                     Size    Used   Avail Capacity  Mounted on
/dev/ufsid/59a7effe7885633c     39G     36G    124M   100%    /
devfs                          1.0K    1.0K      0B   100%    /dev
zroot/mengyunzhi                48G     40G    8.4G    82%    /mengyunzhi
zroot                          8.4G     23K    8.4G     0%    /zroot

First we look at the database file configuration path in my.cnf :

 datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure

Then look at the space it occupies in turn:

 root@YunzhiTest:/var/db # du -h -d 1
180M    ./portsnap
3.1M    ./etcupdate
8.0K    ./zfsd
 36K    ./entropy
4.0K    ./ipf
4.0K    ./hyperv
 87M    ./pkg
688K    ./ports
1.5G    ./freebsd-update
 12K    ./ntp
148K    ./fontconfig
8.0K    ./sudo
 18G    ./mysql
4.0K    ./mysql_secure
4.0K    ./mysql_tmpdir
8.0K    ./redis
8.0K    ./colord
 20G    .

It was found that mysql took up 18G, but in fact there was not that much data. After entering the mysql folder, continue to view:

 root@YunzhiTest:/var/db/mysql # du -ah | sort -h
104M    ./log/log.ibd
105M    ./log
130M    ./mysql/slow_log.CSV
131M    ./mysql-bin.000108
136M    ./measurement/instrument.ibd
142M    ./mysql-bin.000113
145M    ./mysql-bin.000104
150M    ./mysql
190M    ./mysql-bin.000114
214M    ./mysql-bin.000111
224M    ./mysql-bin.000109
230M    ./mysql-bin.000103
256M    ./ib_logfile0
256M    ./ib_logfile1
256M    ./mysql-bin.000106
274M    ./mysql-bin.000107
287M    ./mysql-bin.000110
344M    ./mysql-bin.000102
346M    ./instrument
380M    ./mysql-bin.000112
404M    ./measurement/instrument_check_info_mandatory_instrument_check_ability_list.ibd
502M    ./mysql-bin.000120
658M    ./mysql-bin.000121
678M    ./mysql-bin.000125
786M    ./mysql-bin.000116
813M    ./mysql-bin.000123
900M    ./mysql-bin.000118
1.0G    ./measurement
1.0G    ./mysql-bin.000115
1.0G    ./mysql-bin.000117
1.0G    ./mysql-bin.000119
1.0G    ./mysql-bin.000122
1.0G    ./mysql-bin.000124
1.2G    ./switchgear1
1.2G    ./switchgear1/record_value.ibd
2.3G    ./ibdata1

In the end, it was found that the large space users are as above. We found that the .mysql-bin file in the system occupies a large space, and the mysql-bin file has two functions: 1 is used for data recovery; 2 is when the master-slave database is used. Guaranteed high availability.

Although the corresponding mysql-bin file can be deleted, it is still necessary to keep this document. But we can keep it shorter, for example, we only keep it for a week. Check the file's generation date:

 root@YunzhiTest:/var/db/mysql # ls -alh
-rw-r-----   1 mysql  mysql   344M Jun 13 17:02 mysql-bin.000102
-rw-r-----   1 mysql  mysql   229M Jun 14 13:53 mysql-bin.000103
-rw-r-----   1 mysql  mysql   145M Jun 14 20:44 mysql-bin.000104
-rw-r-----   1 mysql  mysql    56M Jun 15 00:11 mysql-bin.000105
-rw-r-----   1 mysql  mysql   256M Jun 15 22:34 mysql-bin.000106
-rw-r-----   1 mysql  mysql   274M Jun 16 11:29 mysql-bin.000107
-rw-r-----   1 mysql  mysql   131M Jun 16 17:38 mysql-bin.000108
-rw-r-----   1 mysql  mysql   224M Jun 17 04:00 mysql-bin.000109
-rw-r-----   1 mysql  mysql   287M Jun 17 17:26 mysql-bin.000110
-rw-r-----   1 mysql  mysql   214M Jun 18 03:29 mysql-bin.000111
-rw-r-----   1 mysql  mysql   380M Jun 18 21:19 mysql-bin.000112
-rw-r-----   1 mysql  mysql   142M Jun 20 17:02 mysql-bin.000113
-rw-r-----   1 mysql  mysql   189M Jun 21 00:09 mysql-bin.000114
-rw-r-----   1 mysql  mysql   1.0G Jun 22 19:35 mysql-bin.000115
-rw-r-----   1 mysql  mysql   785M Jun 24 00:16 mysql-bin.000116
-rw-r-----   1 mysql  mysql   1.0G Jun 25 19:06 mysql-bin.000117
-rw-r-----   1 mysql  mysql   900M Jun 27 08:14 mysql-bin.000118
-rw-r-----   1 mysql  mysql   1.0G Jun 29 11:30 mysql-bin.000119
-rw-r-----   1 mysql  mysql   502M Jul  1 13:09 mysql-bin.000120
-rw-r-----   1 mysql  mysql   657M Jul  5 01:38 mysql-bin.000121
-rw-r-----   1 mysql  mysql   1.0G Jul  6 21:05 mysql-bin.000122
-rw-r-----   1 mysql  mysql   813M Jul  8 09:05 mysql-bin.000123
-rw-r-----   1 mysql  mysql   1.0G Jul 10 10:36 mysql-bin.000124
-rw-r-----   1 mysql  mysql   677M Jul 11 21:28 mysql-bin.000125

It is found that the file has been saved for nearly a month. At this time, we delete two slightly larger historical files to free up some space, and then modify the retention date in my.cnf to shorten it to 10 days.

 root@YunzhiTest:/var/db/mysql # rm mysql-bin.000115
root@YunzhiTest:/var/db/mysql # rm mysql-bin.000124
root@YunzhiTest:/var/db/mysql # df -h
Filesystem                     Size    Used   Avail Capacity  Mounted on
/dev/ufsid/59a7effe7885633c     39G     34G    2.1G    94%    /
devfs                          1.0K    1.0K      0B   100%    /dev
zroot/mengyunzhi                48G     40G    8.4G    82%    /mengyunzhi
zroot                          8.4G     23K    8.4G     0%    /zroot

Set the retention days data for the bin file to 10:

 binlog_cache_size               = 16M
expire_logs_days                = 10

Finally try to start mysql

 root@YunzhiTest:/usr/local/etc/mysql # /usr/local/etc/rc.d/mysql-server start
Starting mysql.
root@YunzhiTest:/var/log/mysql # /usr/local/etc/rc.d/mysql-server status
mysql is running as pid 11633.

In fact, in addition to this method, if you have enough space on the second hard disk, you can also directly migrate the mysql data files to the second hard disk, but I did not do this because the remaining space of my second hard disk is only There is 8.4G, and this value is less than the current 18G occupied space of mysql. So even if I wanted to migrate, I couldn't. The fundamental reason is that there is a system that needs to upload a large number of large files, and I do not use storage to process these files. It is time to use storage to store resource files exclusively.

Tracking: Although the value of expire_logs_days is set to 10, mysql does not automatically delete historical logs when it is started. It may need to be triggered at a certain time node for follow-up tracking.