MySql monitoring & optimzing commands

MySql monitoring & optimzing commands

Some of the terms used in the article below refer to
USERNAME = your username
MYSQL_PASSWORD = your password
DATABASE_1 = name of the database
LOCAL_DOMAIN.COM = local domain name
DESTINATION_DOMAIN.COM = remote domain name
Restart MySql Server

sudo service mysql restart

Check database size

mysql> SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%DATABASE_1%';

Check if mysql needs any repairs on any database

mysqlcheck -uzalam -p --quick --all-databases

Check if mysql needs any repairs on any database

mysqlcheck --analyze -uUSERNAME -p --all-databases

Check specific mysql database

mysqlcheck --analyze -uUSERNAME -p DATABASE_1

mysql database repair command on all databases

mysqlcheck --repair -uUSERNAME -p --all-databases

mysql database repair command on specific database

mysqlcheck --repair -uUSERNAME -p DATABASE_1

Command to optimize mysql databases

mysqlcheck -uUSERNAME -p --optimize --all-databases --verbose

Login in mysql shell and execute the command to see current working mysql processes.

mysql> show full processlist;

login to mysql and check mysql cache status.

mysql> SHOW STATUS LIKE '%cache%';

If your host is blocked by too many connections. You can empty them by the command below.

sudo mysqladmin flush-hosts -uUSERNAME -p;

Flush privileges

sudo mysqladmin flush-privileges -uUSERNAME -p

install database internal files

sudo mysql_install_db

show all permissions for a users

mysql> SHOW GRANTS FOR 'USERNAME'

Get number of Wordpres posts status

mysql> select wp_posts.post_status, count(id) from wp_posts group by wp_posts.post_status;

You can delete WordPress revisions by the follwing command.

mysql> DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';

Check query cache working

mysql> SHOW VARIABLES LIKE 'have_query_cache';

Get all cache related settings information.

mysql> show variables like '%cache%';

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *