Linux 4 All

Best Linux tricks source

Posts in the MySQL category

Here is how:

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE index_type LIKE ‘FULLTEXT%’;

This is important as MySQL prior to 5.6 does not support FULLTEXT InnoDB tables.

I admit that is pretty lame, but until today I had no idea how to kill a specific MySQL query. It is quite easy.

First we need to get the queries:

mysql> show processlist;
| Id   | User | Host      | db   | Command | Time | State | Info             |
| 5585 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
1 row in set (0.00 sec)

Then kill the id:

mysql> kill 5585;
ERROR 1317 (70100): Query execution was interrupted

This query will display both the total size and number of tables for each MySQL engine.

SELECT engine,ROUND(SUM(index_length+data_length)/1024/1024,2) AS 'size in MB',count(engine) FROM information_schema.tables GROUP BY engine;

A nice MySQL query to get all FTP users and passwords from Plesk:

# mysql -u admin -p$(cat /etc/psa/.psa.shadow) -D psa
SELECT REPLACE(sys_users.home,'/home/http/vhosts/','') AS domain,
sys_users.login, accounts.password
FROM sys_users
LEFT JOIN accounts on
ORDER BY sys_users.home ASC;

If you need to get all databases and login credentials from Plesk, the following MySQL query would do the trick:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -D psa
SELECT AS domain_name, AS database_name,
FROM data_bases, db_users, domains, accounts
WHERE data_bases.dom_id =
AND db_users.db_id =
AND db_users.account_id =
ORDER BY domain_name;

From Plesk 11.5 passwords are encrypted in the database, so this query will no longer return the password, but the hashed version.

Getting Plesk email accounts and passwords is quite easy. Here are few ways of doing that:

mysql -u admin -p$(cat /etc/psa/.psa.shadow) -D psa
SELECT, mail.mail_name, accounts.password, FROM domains LEFT JOIN mail ON = mail.dom_id LEFT JOIN accounts ON mail.account_id


mysql -uadmin -p$(cat /etc/psa/.psa.shadow) -D psa 
SELECT mail_name,name,password FROM mail 
LEFT JOIN domains on mail.dom_id = 
INNER JOIN accounts where mail.account_id =;"




mysql -u admin -p$(cat /etc/psa/.psa.shadow) -D psa
SELECT CONCAT(mail_name,"@",name) as email_address,accounts.password FROM mail
LEFT JOIN domains on
LEFT JOIN accounts on;

Some queries might not work on all Plesk versions. From 11.5 upwards the passwords are encrypted, thus only /usr/local/psa/admin/bin/mail_auth_view will work.

Reset MySQL password

Standard method is to stop MySQL server, start it with –skip-grant-tables, update root password and restart again the server.

You can do it without ski-grant-tables and with one MySQL restart.

1. Add this line to /etc/my.cnf


2. Create /var/lib/mysql/mysql.init with contents:

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('mynewpassword'); FLUSH PRIVILEGES;

3. Restart MySQL service

4. Delete /var/lib/mysql/mysql.init and /etc/my.cnf entry

Get a MySQL database size

It’s pretty easy to get a MySQL database size. All you need to do is running the following MySQL command:


Try to replace DB_NAMES with actual Database name to get some values.