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
mysql>

Get MySQL InnoDB tables

Run this query to get all InnoDB tables:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Change engine='innodb' to engine='myisam' to get MyISAM tables.

Here is a quick trick for using tcpdump to get all MySQL queries running on a server:

tcpdump -i $INTERFACE -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

I think I got this from Percona website

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 sys_users.account_id=accounts.id
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 domains.name AS domain_name,
data_bases.name AS database_name,
db_users.login,
accounts.password
FROM data_bases, db_users, domains, accounts
WHERE data_bases.dom_id = domains.id
AND db_users.db_id = data_bases.id
AND db_users.account_id = accounts.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.

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

init_file=/var/lib/mysql/mysql.init

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:

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

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