Cannot find or open table wp_kstats_raw

This error usually occurs because the table has crashed or some other INNODB error. Usually its best to just recreate the table from scratch

DROP TABLE IF EXISTS `wp_kstats_raw`;
CREATE TABLE `wp_kstats_raw` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `ip` int(10) unsigned NOT NULL,
  `url` varchar(1024) NOT NULL,
  `referrer` varchar(1024) NOT NULL,
  `user_agent` varchar(1024) DEFAULT NULL,
  `os` varchar(255) DEFAULT NULL,
  `browser` varchar(255) DEFAULT NULL,
  `search_engine` varchar(255) DEFAULT NULL,
  `search_terms` varchar(255) DEFAULT NULL,
  `spider` varchar(255) DEFAULT NULL,
  `feed` enum('','ATOM','COMMENT ATOM','COMMENT RSS','RDF','RSS','RSS2') DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `preserved` enum('1') DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `ip` (`ip`),
  KEY `url` (`url`(255))
) ENGINE=InnoDB AUTO_INCREMENT=262808 DEFAULT CHARSET=utf8;

Show Number Of MySQL Databases Per User On cPanel

cd /var/lib/mysql
ls -d */ | grep '_' | cut -d '_' -f 1 | uniq -c | sort -rn

How To Fix [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Time to time on new installs of MySQL I encounter this error which is luckily very easy to fix.

From the command line simply run:

mysql_install_db --user=mysql

You may also encounter this error in part when trying to start/stop/restart the service and see:

ERROR! MySQL server PID file could not be found!

...which usually reveals itself in the err log in /var/lib/mysql

Sortable Output MySQL Show Processlist

This will sort the output by username. Substitute select's and order's as needed.

SELECT id,user,db,command,time,state FROM INFORMATION_SCHEMA.PROCESSLIST order by user;

Live Auto Update Refresh MySQL Processlist

watch -n 1 mysqladmin processlist 

Resolve cPanel Update Now Failing MySQL RPM Dependancies

If cPanel is failing nightly UpdateNow based on RPM errors:

error: Failed dependencies:
MySQL conflicts with mysql-5.0.77-4.el5_5.3.i386
W Exit Code: 254
***** FATAL: Test install failed: error: Failed dependencies:
MySQL conflicts with mysql-5.0.77-4.el5_5.3.i386

You can easily fix it using the following:

[~]# rpm -qa | grep mysql-5
mysql-5.0.77-4.el5_5.3

[~]# rpm -e --nodeps --allmatches --justdb mysql-5.0.77-4.el5_5.3

[~]# /scripts/upcp --force

This essentially does the following:
1) Determines the RPM package causing the conflict
2) Removes RPM database entry for package
3) Forces an update of the control panel

Select Query Last Month Unix Timestamp In MySQL

Change [fields], [table] and [datefield] to suite your needs.

If your dates are not stored as Unix timestamps then you can remove the "from_unixtime" function wrapping [datefield]

select [fields] from [table] where from_unixtime([datefield]) >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

Determine Version Of MySQL From Command Line

mysql -V

Detect Orphaned MySQL Databases On Linux Running cPanel

This script relies on databases being in the following format:
username_database-name

...and located in:
/var/lib/mysql

cd /var/lib/mysql
for I in `ls -d */ | grep '_' | cut -d '_' -f 1 | uniq`
do
if [ ! -d "/home/$I" ]; then
    echo User $I doesnt exist - databases to be cleaned up!
fi
done

This should be used with caution and user existence manually verified but will give you a starting point.

Tune MySQL On Linux Running cPanel

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

Fixing MySQL Management Timeout In DirectAdmin

This is due to DirectAdmin calculating the size of the assigned databases on the fly to inform the user. Users with large databases are negatively affected by this and as such this "feature" should be limited or removed via the following.

Edit:
/usr/local/directadmin/conf/directadmin.conf

Show_db_usage = 0

You can also set this value to the maximum number of tables it will read before giving up.

Restore Database Ownership In cPanel

/usr/local/cpanel/bin/setupdbmap

Import MySQL Database From Command Line Interface "CLI" On Linux

mysql -u username -p databasename < /path/to/filename.sql

Export MySQL Database From Command Line Interface "CLI" On Linux

mysqldump -u username -p databasename > /path/to/filename.sql