DB/Mysql

This is reference regarding mysql databases. It's worth to mention that MySQL open source project now is called MariaDB. These commands below are mainly tested with Amazon RDS instances.

Connect

mysql -h database-eu.example.com -u admin@database-eu -p
mysql -h database-eu.example.com -u admin@database-eu -pPassw0rd123

References

• Local_port_forwarding_-_straight_tunnel

Check MySQL current connections

watch -n1 "mysql -u root -pPASSWORD -te \"select id, command, host, time, left(info,60) from information_schema.processlist where info is not null or time > 300 order by time asc;\" 2>/dev/null"

Mysql client - prompt

How to show the name of the current database in the MySQL prompt If you need the name of the currently selected database in your MySQL prompt, use the following lines in your MySQL my.cnf configuration file entry like this [mysql] prompt='mysql(\d)> ' Now, when you connect, the MySQL prompt will look like what’s shown below:

mysql((none))> use test;
#Database changed mysql(test)> 
mysql(test)> use mysql;
#Database changed mysql(mysql)>

Create a user

Show users

# Show users and their main set of privileges
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv

FROM mysql.user

Create or delete a user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT USAGE ON *.* TO newuser@'%' IDENTIFIED BY 'password';
DROP USER 'newuser'@'localhost';

Grant a user privileges

# Limit to a specific table only
grant select on mysql.general_log to newuser@'%';

Show tables grants

mysql> select * from mysql.tables_priv;
+-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+
| Host      | Db    | User       | Table_name  | Grantor           | Timestamp           | Table_priv | Column_priv |
+-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+
| %         | mysql | bob        | general_log | root@10.10.11.10  | 0000-00-00 00:00:00 | Select     |             |
| %         | mysql | bob        | proc        | root@10.10.11.10  | 0000-00-00 00:00:00 | Select     |             |
| %         | mysql | bob        | slow_log    | root@10.10.11.10  | 0000-00-00 00:00:00 | Select     |             |
| %         | mysql | newuser1   | general_log | root@10.10.11.10  | 0000-00-00 00:00:00 | Select     |             |
| localhost | mysql | newuser1   | general_log | root@10.10.11.10  | 0000-00-00 00:00:00 | Select     |             |
+-----------+-------+------------+-------------+-------------------+---------------------+------------+-------------+

Queries

Show process list

Note in the info column there will be the current SQL query running.

mysql> show full processlist;
+----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+
| Id | User     | Host               | db          | Command | Time | State                | Info                  |
+----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+
| 34 | rdsadmin | localhost          | NULL        | Sleep   |    0 | delayed send ok done | NULL                  |
| 35 | rdsadmin | localhost          | NULL        | Sleep   |    5 | cleaned up           | NULL                  |
| 36 | rdsadmin | localhost          | NULL        | Sleep   |  151 | delayed send ok done | NULL                  |
| 38 | root     | 10.10.11.10:35357  | core_db     | Sleep   |  260 | cleaned up           | NULL                  |
| 39 | root     | 10.10.11.10:35439  | core_db     | Sleep   |    1 | cleaned up           | NULL                  |
| 40 | root     | 10.10.11.10:35675  | NULL        | Query   |    0 | init                 | show full processlist |
+----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+

Check size of all tables

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Check version

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.10-log |
+------------+

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| aurora_version          | 1.17.7                       |
| innodb_version          | 1.2.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+