It was the upgrade of Ubuntu OS that broke the MySQL Server I was using. I believe the old version is 18.x and I was trying to bring it up to 20.04. The upgrade went smoothly, although took a long time. The problem only occurred with MySQL. After it got the latest update, MySQL server wouldn't start.
At first, I tried to restart the MySQL server service. Unfortunately it wouldn't start. And I was using the systemctl command with log output trying to determine what happened. It was telling me that there is a configuration issue. The commands to start/stop MySQL server are:
sudo systemctl start mysql --- Start MySQL server sudo systemctl stop mysql --- Stop MySQL server
To take a look at the log output of the server startup, use the following command:
sudo systemctl status mysql
The problem was that I was upgrading from a lower version (version 5 maybe) to version 8, and the cnf file for MySQL server had format changed. And I have made change the cnf file on my server in order for the server to handle some special queries I have. The new version just couldn't handle my cnf file.
The DB server down was making me very nervous. At one point, I thought my blog sites are completely toasted. I forgot where the actual log file is, I had no idea where the cnf file is and what I have done to it. So I had to google all of it. First, I had to find where the log file is. It is located at: /var/log/mysql/error.log. I opened it up and there is no useful information in it. I was disappointed. And that is when I thought my site is toasted.
I continued digging around. And found a way to manually restart MySQL server. I think I was using the following command:
mysqld --validate-config
If there is a configuration issue, this command will show you. Here is a mock configuration problem I have created just to show you what the error output would be:
2021-06-24T03:26:01.934202Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,XXX' to 'sql_mode'. 2021-06-24T03:26:01.934245Z 0 [ERROR] [MY-010119] [Server] Aborting
Here it is. I added the invalid ",XXX" at the very end of it. And it have messed up the configuration. This is how I found my problem. I created my own sql_mode configuration value. And I probably stuck it in the wrong file. It had the wrong format or something which is interfering with MySQL server start-up.
Anyways. Next I checked the server version. Here is the command I used:
mysqld --version
The output would be:
/usr/sbin/mysqld Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
This looked very familiar to me. Turned out that on my personal computer, the Linux Mint I have installed with MySQL had the same version. And I did the configuration change on this computer. So if I copy the same changes to the web server, it could solve my problem. All I have to do is finding where the config file is.
MySQL configuration is kinda hard to figure out. The reason: there are so many of them:
I had to open every single one to find the one I have modified. On the server, I modified /etc/mysql/my.cnf. And on my local, I have modified /etc/mysql/mysql.conf.d/mysqld.cnf. Since the local one is good and server one is bad. I deleted the change I made on the server one. Then did a configuration file validation. It was good. Then on the corresponding good file, and copied the change to the server file. Do another validation. Then I started the MySQL server backup. The blog sites was then fully functional.
I tend to forget things that I have done over 6 months ago, especially with things I am not too familiar with. In this case, I was able to make the MySQL server work with the queries I have in my application code. Then I forget all about it (how I have done it), I only remembered the many searches I have to do to get the queries working. This is definitely a wake-up call as what I do with my small set of data.
This simple tutorial is the first step to document various MySQL work I have done so that I have something I can review later. I should also do regular back up of the data from the server. And establish a local version of my blog sites so that I can restore quickly. In the next post I will probably write how to do back up from MySQL server. I hope this post can help you out if you faced similar situation.
Just a quick summary of all the commands I have used:
To start MySQL Service:
sudo systemctl start mysql
To stop MySQL Service:
sudo systemctl stop mysql
To check MySQL Service running status:
sudo systemctl status mysql
To check MySQL server version. It works even when MySQL server is stopped:
mysqld --version
To check MySQL configuration is valid or not without starting the server itself:
mysqld --validate-config
I hope these commands will help you with your issue diagnoses. Good luck to you!
There is no comments to this post/article.
