Learn about Centmin Mod LEMP Stack today
Become a Member

Wordpress mysqld.log fills up fast

Discussion in 'Blogs & CMS usage' started by Daniel J. Lewis, Oct 23, 2014.

  1. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    I followed Wordpress - Wordpress Multisite + FastCGI_Cache + Pagespeed + Cloudflare | Centmin Mod Community to setup my WordPress sites, Multisite, and caching.

    The problem is that my server is filling its hard drive with junk extremely quickly, and it crashes after a few hours. It looks like about 1–2 GB per hour are being generated by something—probably the caching.

    What's happening? Any idea what I can do to prevent this, or at least track it down?

    I do have a big database, but that's not inflating itself. I run a find command and only find my expected large files.

    A complete server reboot (not just service restarts for Nginx, PHP-FPM, and MySQL) does clear whatever is filling up.

    Please help!
     
  2. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    I learned about du -hsx * | sort -rh | head -10 and used it until I discovered the culprit—/var/log/mysqld.log

    It's currently 8 GB and growing.

    I'm sure this is because I have a large database for my bbPress and a lot of activity. But I don't need logs of this detail, so how can I make it stop?
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,144
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,132
    Local Time:
    5:47 PM
    Nginx 1.13.x
    MariaDB 5.5
    troubleshooting guide at How to troubleshoot Centmin Mod initial install issues | Centmin Mod Community will outline where all your log files are

    for mysql, did you make any /etc/my.cnf changes to mysql settings to enable logging ? it could be mysql errors so you will need to deal with them yourself as Centmin Mod is provided as is without app specific mysql troubleshooting or optimisation support.

    You can also post an excerpt of your mysql error logs too using this command below - use gist.github.com or pastebin.com to share the output or use [CODE]youroutput[/CODE] tags on the forums

    the sed part is to mask your hostname for privacy
    Code:
    tail -40 /var/log/mysqld.log | sed -e "s/`hostname`/yourserverhostname/g"
     
  4. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    Thanks for that! The DB is full of messages like these.
    Code:
    141023  2:35:40  InnoDB: Error: page 99 log sequence number 1256640570
    InnoDB: is in the future! Current system log sequence number 170599977.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
    InnoDB: for more information.
    Before Centminmod, I was running MariaDB 10.1. I dumped that database and imported into Centminmod's MariaDB 5.5. I know many of those tables are in InnoDB (yes, I commented out the skip-innodb line).

    So any idea what I need to do to fix this?
     
  5. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    Reading around, it looks like I should dump my databases, delete the databases, and then re-import. Should I do anything to the MySQL storage files?
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,144
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,132
    Local Time:
    5:47 PM
    Nginx 1.13.x
    MariaDB 5.5
    Unfortunately Centmin Mod is provided as is so there's no support for troubleshooting issues like this which maybe due to MariaDB 10.1 vs MariaDB 5.5 differences in InnoDB and how you imported/downgraded the data.

    Whatever you do before hand, make sure to have a full mysqldump backup sql of ALL your databases on your server in a safe place first before you try any remedies you would like to try.

    How did you do mysql backup and restore for your MariaDB 10.1 to MariaDB 5.5 data ? exact commands used ?

    Easiest way probably is for every database and database table that is InnoDB, convert them back to MyISAM engine tables. Then only after all converted back to MyISAM engine tables, then with MySQL server shutdown only then will you remove ibdata1 and iblogfile* files from /var/lib/mysql which are you InnoDB tablespace and redo logs. Only remove them after a backup is made via mysqldump to sql files and only when you're sure all tables that are InnoDB are converted back to MyISAM. Otherwise, removing ibdata1 and iblogfile* before all InnoDB tables are converted back to MyISAM, will result in corruption and loss of data. Then convert them back from MyISAM to InnoDB again.

    Or yes mysql dump to sql files ALL databases on your server not just wordpress database, then do proper drop mysql database and restore them all. You should never touch actual mysql data files in /var/lib/mysql unless you know what you're doing.
     
    Last edited: Oct 23, 2014
  7. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    I think I did once try an rsync of the mysql folder, but that didn't work. The last thing I actually did was a vanilla mysqldump and mysql import. But it's likely I had already corrupted the MySQL folder.

    So I should dump, delete, and restore even the information_schema, performance_schema, and mysql tables?
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,144
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,132
    Local Time:
    5:47 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah rsync might have messed up your data...

    yes ALL databases on the server need mysqldump backup to sql file and then delete, restore.
     
  9. Daniel J. Lewis

    Daniel J. Lewis Award-winning podcaster and consultant

    77
    8
    8
    Oct 20, 2014
    Ratings:
    +11
    Local Time:
    3:47 AM
    1.8.0
    5.6
    *REMOVE* I figured it out, I think.
     
    Last edited: Oct 23, 2014