MySQL

Check MySQL Version

mysql -V

Check/repair/optimize

mysqlcheck -c DBNAME
mysqlcheck -r DBNAME
mysqlcheck -o DBNAME

Repair all databases on the server

mysqlcheck -A --auto-repair

Slow Query Enable

cp /etc/my.cnf /etc/my.cnf-bak
touch /var/log/mysql-slow.log
chown mysql:root /var/log/mysql-slow.log
chmod 640 /var/log/mysql-slow.log

Then Add the following to the [mysqld] section of /etc/my.cnf

slow_query_log= 1
slow_query_log_file="/var/log/mysql-slow.log"
long_query_time = 60;

Restart MySQL

/usr/local/cpanel/scripts/restartsrv_mysql

General query log

cp /etc/my.cnf /etc/my.cnf-bak
touch /var/log/mysql-general.log
chown mysql:root /var/log/mysql-general.log
chmod 640 /var/log/mysql-general.log

Database Export

Backup All Databases

mysqldump --all-databases > backup.sql

Backup database using User

mysqldump -u dbuser -p dbname > dbname.sql

Exclude a table from MySQL Dump

--ignore-table=dbname.tablename

Syntax: 
mysqldump -u DBuser -p DBname --ignore-table=DBname.tablename > dbname.sql

Export Single Table

mysqldump -u username -p database-name table-name > backup.sql

Export Table Structure Only

mysqldump -u DBUser -p --no-data dbname > schema.sql

Import Databases

Import a database

mysql -u DBuser -p DBname < dbdump.sql

Import Zip backup

gunzip < Backup.sql.gz | mysql -u USER -p DATABASE

Import .tar.gz gzipped

tar -xzOf your_db_dump.sql.tar.gz | mysql -u USERNAME -pPASSWORD your_database

Check the size of tables

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Check MySQL Max Connections

SHOW VARIABLES LIKE "max_connections";

cPanel showing incorrect MySQL size

/scripts/update_db_cache

Delete Selected Rows

mysql
use DBName;
delete from tablemame where Column_ID between 200 and 25134;