Deprecated: Hook custom_css_loaded is deprecated since version jetpack-13.5! Use WordPress Custom CSS instead. Jetpack no longer supports Custom CSS. Read the WordPress.org documentation to learn how to apply custom styles to your site: https://wordpress.org/documentation/article/styles-overview/#applying-custom-css in /srv/www/srvfail.com/public_html/wp-includes/functions.php on line 6078
MySQL Archives ⋆ SysAdminStuff

How to clear or disable eximstats on cPanel

What is eximstats

Eximstats, on WHM/cPanel servers, is used to maintain statistics and information about email messages processed by Exim mail service.

If the data in it is not cleared often enough it can grow, and cause issues with disk space, or MySQL resource usage, as the size of the database can cause higher memory and disk consumption.

Disabling eximstats

If you do not have any need for Exim statistics, which are used for Mail Delivery Reports on  Home »Email »Mail Delivery Reports, gathering exim statistics can be disabled from WHM or command line.

In WHM it can be disabled by going to Home »Service Configuration »Service Manager, and unchecking the service, and then clicking on the Save button on the bottom.

This will stop the database from being populated with new data.

Eximstats in Service Manager
Service Manager

From command line, you can disable the database from being populated by running a following command:
/usr/local/cpanel/bin/tailwatchd --disable=Cpanel::TailWatch::Eximstats

Lowering Eximstats retention time

Database is periodically cleaned, and by default Exim stats are retained in database for 10 days, which can be changed in WHM by going to Home »Server Configuration »Tweak Settings and changing the “The interval, in days, to retain Exim stats in the database” setting on the “Stats and Logs” tab.

Setting can also be changed by altering the “exim_retention_days” value in “/var/cpanel/cpanel.config” file.

Empty eximstats database

Database can be cleared either by deleting from its four tables, defers, failures, sends and smtp, or by dropping the database completely, and creating a fresh one with empty tables from “/usr/local/cpanel/etc/eximstats_db.sql” file.

To delete all of the data from the tables following command can be used:

mysql -e "use eximstats;delete from defers;delete from failures;delete from sends;delete from smtp;delete from smtp;"

To drop the database and recreate it again, following commands can be used:

mysqladmin drop eximstats
mysqladmin create eximstats
mysql eximstats < /usr/local/cpanel/etc/eximstats_db.sql
References:

https://forums.cpanel.net/threads/problem-in-eximstats.363382/

https://confluence2.cpanel.net/display/ALD/Service+Manager#ServiceManager-tailwatchd(TailWatchDrivers)

https://documentation.cpanel.net/display/ALD/Tweak+Settings+-+Stats+and+Logs#TweakSettings-StatsandLogs-Theinterval,indays,toretainEximstatsinthedatabase

blob data length is greater than 10% of the total redo log size

On MySQL 5.6.20, and above you might be getting errors when trying to import tables, databases, if you innodb_log_file_size is too small.

If you are seeing error message like this, you will need to increase your innodb_log_file_size inside your my.cnf or my.ini MySQL configuration file.

The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
2015-09-07 17:29:33 12298 [ERROR] InnoDB: The total blob data length (10066357) is greater than 10% of the total redo log size (100663296). Please increase total redo log size.

As of MySQL version 5.6.20 changes were implemented in regards to InnoDB and BLOB data size in you tables, and you InnoFB log should be at least 10 times higher than the largest BLOB data size found in the rows of your tables, plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields).

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html#mysqld-5-6-20-innodb

InnoDB Notes

Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Note
In MySQL 5.6.22, the redo log BLOB write limit is relaxed to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).

Once you increase innodb_log_file_size inside your my.cnf or my.ini file, error should be resolved.

2015-09-07 17:29:38 5345 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-07 17:29:38 5345 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*65536 pages, LSN=702949287837
2015-09-07 17:29:38 5345 [Warning] InnoDB: Starting to delete and rewrite log files.
2015-09-07 17:29:38 5345 [Note] InnoDB: Setting log file ./ib_logfile101 size to 1024 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
2015-09-07 17:29:42 5345 [Note] InnoDB: Setting log file ./ib_logfile1 size to 1024 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
2015-09-07 17:29:46 5345 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-09-07 17:29:46 5345 [Warning] InnoDB: New log files created, LSN=702949287837

Reference:

http://stackoverflow.com/questions/25277452/how-to-configure-mysql-5-6-longblob-for-large-binary-data

MySQL failing to start with message “Can’t find file: ‘./mysql/plugin.frm’ (errno: 23)”

This is a repost of a post from an old blog, made on December 28, 2013, that used to be on:

http://adminramble.com/mysql-failing-start-message-cant-find-file-mysqlplugin-frm-errno-23/

Original post:

Recently I had a problem, where MySQL service was failing to start.
When tailing the MySQL log the following would be recorded while service was being started.

131224 06:04:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 23)
131224 6:04:53 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
/usr/libexec/mysqld: Can't create/write to file '/tmp/ibqcFQMW' (Errcode: 23)
131224 6:04:53 InnoDB: Error: unable to create temporary file; errno: 23
131224 6:04:53 [ERROR] Plugin 'InnoDB' init function returned error.
131224 6:04:53 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131224 6:04:53 [ERROR] Can't create IP socket: Too many open files in system
131224 6:04:53 [ERROR] Aborting

131224 6:04:53 [Note] /usr/libexec/mysqld: Shutdown complete

131224 06:04:53 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Now, the message ‘Can’t find file: ‘./mysql/plugin.frm’ (errno: 23)‘ at the begging of the startup process might make you think that the problems is a missing file, but you can see that at the end of a startup process  this message is logged[ERROR] Can’t create IP socket: Too many open files in system.

This suggest that there is a problem with the number of files that is open on the system.
You can confirm this by using a perror utility, which prints system error messages.

If we check the error number 23, which is reported in the error message, we see that the cause of the failure is not a missing file, but that the file can’t be open because to many file handles are in use.

# perror 23
OS error code 23: Too many open files in system

You can check the current maximum number of file descriptors by checking the fs.file-max value in /etc/sysctl.conf, or use the sysctl command to check the current value.

# sysctl -a | grep file-max
 fs.file-max = 65536

To increase the maximum number of file handlesm you can edit /etc/sysctl.conf, change the value of fs.file-max to 200000 or some other value higher then the one you currently have, and then run sysctl -p to apply the new value to the system.

Now, after the file handle number has been increased, you should be able to start the MySQL service normally.

How to install MySQL through YaST

This is a repost of a post from an old blog, made on January 9, 2012, that used to be on:

http://adminramble.com/install-mysql-yast/

Original post:

I was looking into Novell Service Desk as solution for our Help-desk department today, so i decided to test it by installing it on a single machine together with a MySQL server to act as its database. It’s been a few years since the last time I installed MySQL, last few years I’m mostly working with Informix and MS SQL.

I just wanted it installed as soon as possible so I decided to install the it through YaST thinking it would be faster, but neglected the fact that YaST scatters the installation through several directories so I spend some time locating the files. So this are the short instructions for those who decided to do it with YaST. I was installing on SUSE Linux Enterprise Server 11 SP1

HOW TO INSTALL MYSQL THROUGH YAST ON SLES 11
  1. Open YaST(just type yast in terminal)>Software>Software management(use Tab button to move between sections), or go straight to Install Software on main menu if you’re working through GUI
  2. In the search filed type mysql and press Enter or Search
  3. Select mysql package, mysql-client will be selected also automatically also, and press Accept to install them.
    (mysql user and mysql group will be created automatically, so you don’t need to do that)If you want to find out where did YaST installed all the files, you can do that either through the terminal with rpm -qal mysql command, or if you’re working through the GUI by opening YaST, typing mysql i search box and selecting mysql package. On the File List tab you can see where all the files are installed.

  4. Edit the /etc/my.cnf file, by adding user=mysql after the [mysqld]
    your file should look something like this:

    [mysqld]
     user = mysql
  5. Crate a mysql database with the following command:
    /usr/bin/mysql_install_db –user=mysql
  6. Start your server with:
    /usr/bin/mysqld_safe –user=mysql &
  7. Check if the server is running with:
    /usr/bin/mysqladmin version
    You should get information about the software version.
  8. Connect to your MySQL server with:
    /usr/bin/mysql -u root
    You should get the mysql> prompt if everything is okay.

 

There are other ways to install MySQL besides YaST, if you would like to install MySQL from source you can check this blog

MySQLTuner Couldn’t find mysqladmin in your $PATH

If you try to run MySQLTuner on a default RHEL/CentOS 7 minimal install, you will receive an error due to “which” command not being found.

In order to resolve the error you need to add “which” to your CentOS/RHEL installation, or use the “–mysqladmin” switch to point to mysqladmin executable when running mysqltuner.pl.

When you try to execute MySQLTuner, you will receive a message like this:

# perl mysqltuner.pl
Can't exec "which": No such file or directory at mysqltuner.pl line 905 (#1)
(W exec) A system(), exec(), or piped open call could not execute the
named program for the indicated reason. Typical reasons include: the
permissions were wrong on the file, the file wasn't found in
$ENV{PATH}, the executable in question was compiled for another
architecture, or the #! line in a script points to an interpreter that
can't be run for similar reasons. (Or maybe your system doesn't support
#! at all.)

Use of uninitialized value $mysqladmincmd in scalar chomp at mysqltuner.pl line
907 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
anid the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.

Use of uninitialized value $mysqladmincmd in -e at mysqltuner.pl line 908 (#1)
Use of uninitialized value $mysqladmincmd in -e at mysqltuner.pl line 913 (#1)
[!!] Couldn't find mysqladmin in your $PATH. Is MySQL installed?

If you check the reported lines in mysqltuner.pl file, you will see that reported variable mysqladmincmd is found by looking for path of mysqladmin with “which mysqladmin” command.

# sed -n '905p;908p;913p' mysqltuner.pl
        $mysqladmincmd = `which mysqladmin`;
    if ( !-e $mysqladmincmd && $opt{mysqladmin} ) {
    elsif ( !-e $mysqladmincmd ) {

You can install “which” with following command.

yum -y install which

After installing the rpm, you will be able to execute mysqltuner.pl file.

Alternative to installing “which” is to run MySQLTuner with “–mysqladmin” switch and define the path to mysqladmin executable.

# perl mysqltuner.pl --help
   MySQLTuner 1.6.0 - MySQL High Performance Tuning Script
   Bug reports, feature requests, and downloads at http://mysqltuner.com/
   Maintained by Major Hayden (major@mhtx.net) - Licensed under GPL

   Important Usage Guidelines:
      To run the script with the default options, run the script without arguments
      Allow MySQL server to run for at least 24-48 hours before trusting suggestions
      Some routines may require root level privileges (script will provide warnings)
      You must provide the remote server's total memory when connecting to other servers

   Connection and Authentication
      --host     Connect to a remote host to perform tests (default: localhost)
      --socket     Use a different socket for a local connection
      --port         Port to use for connection (default: 3306)
      --user     Username to use for authentication
      --pass     Password to use for authentication
      --mysqladmin   Path to a custom mysqladmin executable
      --mysqlcmd     Path to a custom mysql executable

      --noask              Dont ask password if needed

   Performance and Reporting Options
      --skipsize           Don't enumerate tables and their types/sizes (default: on)
                           (Recommended for servers with many tables)
      --skippassword       Don't perform checks on user passwords(default: off)
      --checkversion       Check for updates to MySQLTuner (default: don't check)
      --forcemem     Amount of RAM installed in megabytes
      --forceswap    Amount of swap memory configured in megabytes
      --passwordfile Path to a password file list(one password by line)
   Output Options:
      --silent             Don't output anything on screen
      --nogood             Remove OK responses
      --nobad              Remove negative/suggestion responses
      --noinfo             Remove informational responses
      --debug              Print debug information
      --dbstat             Print database information
      --idxstat            Print index information
      --nocolor            Don't print output in color
      --buffers            Print global and per-thread buffer values
      --outputfile   Path to a output txt file

      --reportfile   Path to a report txt file

      --template     Path to a template file

On CentOS 7 path to mysqladmin executable is “/usr/bin/mysqladmin”.

This is an example of a command which runs MySQL tuner, by manually specifying MySQL user and password, and path to mysqladmin executable.

perl mysqltuner.pl --user root --pass yourpassword --mysqladmin /usr/bin/mysqladmin