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

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

general_log=1
general_log_file="/var/log/mysql-general.log"

Restart MySQL the end:

/usr/local/cpanel/scripts/restartsrv_mysql

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;

Strict mode

Check Strict Mode

mysql -e 'select @@GLOBAL.sql_mode;'
mysql -u root -e "SHOW VARIABLES LIKE 'sql_mode';"

It will show output like this:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

Disable Strict Mode

To disable strict mode, first check the strict mode as shown above, then copy the output and add it to the my.cnf without STRICT_TRANS_TABLES

sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

At the end, restart mysql:

/usr/local/cpanel/scripts/restartsrv_mysql

Database Map Tool

If database exists in /var/lib/mysql/ but not in cPanel, map the db to the user in WHM >> Database Map Tool or:

/usr/local/cpanel/bin/dbmaptool USER --type 'mysql' --dbs 'DBNAME'

If you get "db is already owned by user", run this and then the above again:

/usr/local/cpanel/bin/dbindex