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%';