MySQL suddenly started failing

September 4, 2019 238 views
MySQL CentOS

Hi guys,

I’ve ran into trouble and I want to fix it on my own however I would pretty much need some pointers.

My MySQL service went down and I’m unsure on how to start troubleshooting it.

Can you guys assist?

2 Answers

Hi @Remdore,

Your questions is quite generic but I’ll try and lend you some pointers on which you can start.

Check Disk Space

If your droplet is full, services like mysql,apache,nginx etc. will start failing as they can’t create the proper sockets to start themselves.

Check the current disk usage

df -h

That will show you all your partions and the size they are using. If your server is full let me know and I’ll provide you with pointers on what to check and clear.

Now onto the good stuff

*Your MySQL might have been corrupted. *

In order to check that, you can check your mysql error log. Your error log should be defined in your my.cnf file. If you are not sure where it is, you can do the following

locate my.cnf

It’s usually at /etc/my.cnf

Once you find it, open it with your favourable editor and see if there is a line like

 [mysqld]
 log_error = /var/log/mysql/error.log

If there isn’t try adding the line and restarting MySQL.

Once you do, the error log should appear and it should say what the problem is.

If you run into trouble, post the contents of it and I’ll help out.

Kind regards,
Kalin D.

  • Wow, thanks for the fast answer!

    I’ll check out what you’ve pointed out and try to debug this myself.

    If I have any troubles, I’ll come back.

    • I’ve just had the time to check what’s going on and it does seem like the server is indeed full:

      Filesystem            Size  Used Avail Use% Mounted on
      /dev/mapper/vg_centos_home
                            30G  30G   0  100% /home
      
      

      I’ve checked my website but I can’t find anything to delete. I don’t want to mess anything up, can you let me know what would be okay to remove?

      • Hi @Remdore,

        Oh I see. I’ll try provide you with some useful commands to search your server and website for some big files.

        Firstly, let’s check the server files

        There is a possibility some log files to have grown a bit large if you don’t have log rotation. Go to

        cd /var/log
        

        once in the directory perform the following task

        du -sh *
        

        This will list all files in the directory and their size. Some files like secure/messages/maillog etc. can become quite large if you don’t truncate/compress them from time to time.

        They contain information about logging, system messages etc. If you don’t need them you can just truncate them like

        echo > nameoffile
        

        Another option would be to gzip them for future use however and your current state, you won’t be able to as the server is full.

        Additionally, do you by any chance have some cron jobs configured to create some backups? I find it’s most of the time something like that taking all the space.

        To check your cron jobs type

        crontab -l
        

        There you’ll see all cron jobs and which scripts they execute. If you do, you can either store your backups somewhere offsite or configure the cron job to remove older backups.

        Now let’s say it’s actually somewhere in the website files

        Firstly, go to your website directory and type the following command

        ionice -n 3 -c 3 find /path/to/website/ -type f -size +50M -exec du -hs {} \;
        

        Maybe you’ve forgotten you have a backup plugin or something else. You’ll see.

        Another thing that is really a long shot, you might have configured your sessions to be saved in a file located in your website dir. If you don’t remove files there manually, they become millions. You can check that from your php configuration/php.ini

        I hope I was extensive however this topic is pretty broad and I might’ve left something out.

        Kind regards,
        Kalin

        • Thank you so much!

          I’ve had some plugins for backups which related in the server being full. I’ve removed them however when I tried to start MySQL (now the server has 10 GB of space), it still failed.

          • Hi @Remdore,

            Did you remember to enable the mysql log? The error would be mentioned there.

            Reference :

            It’s usually at /etc/my.cnf

            Once you find it, open it with your favourable editor and see if there is a line like

             [mysqld]
             log_error = /var/log/mysql/error.log
            

I managed to start MySQL but in the log it says that a table has crashed on my DB.

Should I just go and try to repair it or is there something else to be done?

  • From the command line, enter this command, replacing [username] with your username and without the brackets:

    mysql -u [username] -p
    
    

    Enter your password.

    Again, without the brackets, enter the command:

    use [databasename];
    

    Enter the command:

    show tables;
    
    

    Unlike phpMyAdmin, there is no way to repair multiple tables with one command. Instead, you must issue one command per table:
    To check a table for errors, enter:

    check table [yourtablename];
    

    To repair a table, enter:

    repair table [yourtablename];
    

    That’s it.

    Kind regards,
    Kalin

Have another answer? Share your knowledge.