1. Restart Mysql manually when overloaded

MySQL may have problems when it runs out of memory or because of some other problem in my case when the server runs many different sites. In most cases, we can solve this problem by manually restarting the MySQL service.

To check if MySQL is running, run the command:

 sudo service mysql status // kiểm tra trạng thái mysql

Or:

 sudo service mysqld status // kiểm tra trạng thái nếu sử dụng mariadb

If Mysql state is off, restart using command line:

 sudo service mysql restart // hoặc sudo service mysqld restart

If you do it manually, it's simple, but if the database is overloaded in the middle of the night, when we are sleeping, the website access will be interrupted, that's the problem, so how to solve the problem this ?

With linux/Centos, the operating system provides us with the Cron feature, which is a time-based automatic scripting job scheduler for Linux so that it can run the scripts without us manipulating them. crafts again.

2. Create a script (Bash script) to restart MySQL

You can configure cron to automatically check the status of the MySQL server and restart it if it crashes. Of course, this isn't a permanent fix for your dingy MySQL server, but it can save you time until you can investigate further.

First, we need to create a simple BASH script and store it in the home directory (or wherever you want), then instruct cron to run this BASH script once a minute.

You can put this script anywhere, but in this example, we will put it in the root directory. or, you can use bitvise to access the server and create a folder that simulates your command line, next, create a folder called scripts

 cd /root/ // change directory to root folder sudo mkdir scripts // make dir scripts with root permission

Go to scripts folder and create mysqlmon file with nano tool (data editor on linux terminal), or use bitvise to create this file

 cd scripts sudo nano mysqlmon.sh

Insert this code into the newly created mysqlmon.sh file depending on whether your service is mysql or mysqld:

 #!/bin/bash # Check if MySQL is running sudo service mysql status > /dev/null 2>&1 # Restart the MySQL service if it's not running. if [ $? != 0 ]; then echo -e "MySQL Service was down. Restarting now...n" sudo service mysql restart else echo -e "MySQL Service is running already. Nothing to do here.n" fi // sử dụng 1 trong 2 đoạn code này phù hợp server của bạn, chỉ khác nhau mysql và mysqld #!/bin/bash # Check if MySQL is running sudo service mysqld status > /dev/null 2>&1 # Restart the MySQL service if it's not running. if [ $? != 0 ]; then echo -e "MySQL Service was down. Restarting now...n" sudo service mysqld restart else echo -e "MySQL Service is running already. Nothing to do here.n" fi

If using nano, save and exit (press Ctrl + X , press Y(yes) then press enter)

Grant execute permission to the script:

 sudo chmod +x mysqlmon.sh

3. Check if the script file you just created is working properly

Run this command to execute the newly created script file:

 sudo ./mysqlmon.sh

If the Mysql service is still running, the terminal will display the line:

 MySQL Service is running already. Nothing to do here.

If you want to see what happens when the script detects if MySQL crashes, stop the MySQL service, keep in mind this step, your server will temporarily crash due to not being able to load the database:

 sudo service mysql stop

After stopping, try running the mysqlmon.sh file you just created again to see if it recognizes and restarts mysql:

 sudo ./mysqlmon.sh

If run correctly, this line of text will be displayed

 MySQL Service was down. Restarting now..

Check the status one last time to see if it was successful:

 sudo service mysql status

4. Automate script file execution with cron

Create cron files with nano or bitvise:

 cd ~ cd /etc/cron.d // chuyển tơi thư mục etc/cron.d nano -w auto_restart // khởi tạo file auto_restart với trình biên tập nano

Paste this command line into the file auto_restart

 */1 * * * * root /root/scripts/mysqlmon.sh >/dev/null 2>&1 // chạy file này 1 phút 1 lần, tăng số 1 lên nhiều nếu bạn muốn thời gian tự động chạy lâu hơn

Save the cron file to finish.

Note, when using this way, you won't be able to find the cron command with the command line crontab -l , to see if your command is recognized use the command line:

 head -n -0 /var/spool/cron/* /etc/crontab /etc/cron.d/* // xem các kịch bản tự động chạy trong thư mục cron.d ls /etc/cron.{hourly,daily,weekly,monthly}/ // xem các file cron tự động chạy trong các thư mục cron theo giờ, ngày, tuần, tháng ...

Good luck !