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
mysql -h database-eu.example.com -u admin@database-eu -pPassw0rd123 --ssl-mode=DISABLED # [1]

[1] ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol when connecting from Ubuntu 20.04 to Aurora engine 5.6.mysql_aurora.1.22.2

References

Local_port_forwarding_-_straight_tunnel

Ops queries

Show process list

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

mysql> SHOW GLOBAL STATUS;
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 |
+----+----------+--------------------+-------------+---------+------+----------------------+-----------------------+

Kill a process

In RDS you don't have access to all commands thus there is specific procedure to be called in:

kill 38                                # Mysql server
CALL mysql.rds_kill(38)                # RDS instance
CALL mysql.rds_kill_query($PROCESS_ID) # Aurora

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

Check 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"

Check a table size

SELECT COUNT(*) FROM `Table`; #count a table rows
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size(MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";

Check size of all tables

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

Watch database size

This is useful when loading data to a database.

RDS_HOSTNAME=localhost
RDS_PASS=***
DB=database1 # database name, watch all databases if empty
watch -d "mysql -h $RDS_HOSTNAME -u admin -p$RDS_PASS -e \"SELECT table_schema $DB, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) DBSizeinMB  FROM information_schema.tables  GROUP BY table_schema;\""

+---------------------------+------------+
| table_schema              | DBSizeinMB |
+---------------------------+------------+
| information_schema        |        0.2 |
| mysql                     |        6.0 |
| performance_schema        |        0.0 |
| sys                       |        0.0 |
+---------------------------+------------+

mysql client - change the command line 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

Note: If you use MySQL Workbench to see effect of the statements below, make sure to close and reopen the tab as the current state is read only on opening the tab.

Show users

SELECT * FROM mysql.user;

Mysql 5.7

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

Mysql 5.6. Notice usage lines, it creates a user without permissions if the user does not exist. It creates only '%' type of user.

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

-- grant a user all privilages apart 'GRANT OPTION' on 'newdb' schema
GRANT ALL PRIVILEGES ON `newdb`.* TO 'new.user'@'%';

Grant a user privileges per tables and column

MySQL 5.6

show grants for `username`;

MySQL 5.7

SHOW GRANTS username; SELECT * FROM mysql.user; -- show users table and their privilages SELECT * FROM mysql.tables_priv WHERE user LIKE '%prav%'; -- show table privileges table SELECT * FROM mysql.columns_priv where user like '%praveen%'; -- show columns privileges table

Grant permission to a specific table only

GRANT select ON mysql.general_log TO 'newuser'@'%'; GRANT type_of_permission ON db_name.table_name TO 'newuser'@'localhost';

Grant permission to a specific column only, eg. grant `UPDATE` permission

GRANT UPDATE (col) ON `<db_name>`.<table_name> TO '<user>'@'%';

Revoke table permission

REVOKE type_of_permission ON db_name.table_name FROM 'newuser'@'localhost';

Once finalised the permissions for the users, always reload/save all the privileges

FLUSH PRIVILEGES;

-- RO GRANT SELECT, SHOW VIEW ON ... -- RW GRANT SELECT, SHOW VIEW, INSERT, UPDATE, DELETE ON ... </syntaxhighlightjs>

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

CPU stress

Running below on db.t3.small aurora takes around 2-5 minutes, you may loose the connection but it will continue running in the background.

DELIMITER $$
DROP PROCEDURE IF EXISTS StressCPU;
CREATE  PROCEDURE StressCPU()
BEGIN
  DECLARE counter INT DEFAULT 1;
  DECLARE max_counter INT DEFAULT 50000000;
  DECLARE val float8;
  WHILE counter <= max_counter DO
    SET val = POW(70,70);
    SET counter = counter + 1;
  END WHILE;
END$$

CALL StressCPU();

SHOW FULL PROCESSLIST;

AWS - Aurora logs

You can control MySQL logging by using the parameters in this list:

slow_query_log: To create the slow query log, set to 1. The default is 0.
general_log: To create the general log, set to 1. The default is 0.
long_query_time: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query execution time to be logged, in seconds. The default is 10 seconds; the minimum is 0. If log_output = FILE, you can specify a floating point value that goes to microsecond resolution. If log_output = TABLE, you must specify an integer value with second resolution. Only queries whose execution time exceeds the long_query_time value are logged. For example, setting long_query_time to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
log_queries_not_using_indexes: To log all queries that do not use an index to the slow query log, set to 1. The default is 0. Queries that do not use an index are logged even if their execution time is less than the value of the long_query_time parameter.
log_output option: You can specify one of the following options for the log_output parameter.
- TABLE (default)– Write general queries to the mysql.general_log table, and slow queries to the mysql.slow_log table.
- FILE– Write both general and slow query logs to the file system. Log files are rotated hourly. Required to send logs to CloudWatch Log Group.
- NONE– Disable logging.

Advanced auditing

QUERY_DCL – Similar to the QUERY event, but returns only data control language (DCL) queries (GRANT, REVOKE, and so on).
QUERY_DDL – Similar to the QUERY event, but returns only data definition language (DDL) queries (CREATE, ALTER, and so on).

Publish Aurora logs to CloudWatch Log Group:

log_output - needs to be set to FILE, the mysql.general_log will stop receiving new updates
will be created automatically
- /aws/rds/cluster/<cluster_DB_identifier>/general | LogGroup
  - <db_instance_identifier>-<region> | LogStream
  - <db_instance_identifier>-<us-east-1a> | LogStream
  - <db_instance_identifier>-<us-east-1c2> | LogStream
can be created manually including Log Streams; use naming convention as above
Modify cluster by Exporting respective logs in the Log Export section; no reboot required

# Enable via Cluster Parameters in the Cluster Parameter Group
general_log     0     #disabled log
slow_log        1     #enabled log
long_query_time 10    #value in seconds, any query running for longer than time set will be logged to slow_log log
log_output      TABLE #redirect all logs to tables

# Read logs from a table
use mysql;
SELECT * FROM mysql.slow_log;
SELECT * FROM mysql.slow_log_backup;
SELECT * FROM mysql.general_log;
SELECT * FROM mysql.general_log_backup;
SELECT * FROM mysql.slow_log;
SELECT * FROM mysql.slow_log_backup;

#Rotate logs, will clear general_log and move data to general_log_backup table
#Running it twice will clear both tables
CALL mysql.rds_rotate_general_log;

#Check TABLE log size
SELECT COUNT(*) FROM `mysql.general_log`; #count a table rows
SELECT table_name AS `mysql.general_log`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "mysql";

Align SQL inserts by a comma

cat inserts.sql | sed 's/`,/`@,/g' | sed "s/',/'@,/g" | sed "s/,'/@,'/g" | column -t -s "@" > inserts-aligned.sql
# accounts for `,
# accounts for ',
# accounts for ,'

SSL/TLS connections

Note: MySQL doesn't use raw TLS on the wire for its encrypted connections. Instead, it performs a protocol handshake in the MySQL layer to enable TLS before it starts to be used. That's similar to FTP or SMTP which use AUTH TLS and STARTTLS respectively to enable TLS, and that's what the -starttls argument in the s_client command does. curl doesn't support the MySQL protocol so it can't do this.

RDS MySQL can use SSL to encrypt connection. More info read here:

Commands

SELECT * FROM information_schema.SESSION_STATUS;

show variables like '%ssl%';
'have_openssl', 'YES'
'have_ssl', 'YES'        # <- db supports ssl
'ssl_ca', '/rdsdbdata/rds-metadata/ca-cert.pem'
'ssl_capath', ''
'ssl_cert', '/rdsdbdata/rds-metadata/server-cert.pem'
'ssl_cipher', 'EXP1024-RC4-SHA:EXP1024-DES-CBC-SHA:...:EXP-EDH-DSS-DES-CBC-SHA'
'ssl_crl', ''
'ssl_crlpath', ''
'ssl_key', '/rdsdbdata/rds-metadata/server-key.pem'

JDBC secure connection

Listed below is a code example showing how to communicate with a MySQL database using SSL and JDBC. The useSSL=true property is added to the JDBC URL to attempt to communicate via SSL. The requireSSL=true property can be added to only connect if the database server supports SSL. The verifyServerCerticate=false property is set to bypass certificate validation.

import java.sql.*;
public class TestMySQLSSL
{
	public static void main (String[] args)
	{
		Connection con = null;
		try
		{
			String url = "jdbc:mysql://127.0.0.1:3306/sample"+
				"?verifyServerCertificate=false"+
				"&useSSL=true"+
				"&requireSSL=true";
			String user = "testuser";
			String password = "testpass";
				
			Class dbDriver = Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
		}
		catch (Exception ex)
		{

Setting up wso2 to use SSL when connecting to MySQL DB

mysql-ssl-connection-using-jdbc

Import client and server certificates to the client-truststore of WSO2 server. In case of using AWS you need to import rds-ca-2019-root.

keytool -import -alias rds-ca-2019-root -file /etc/mysql-ssl/rds-ca-2019-root.pem -keystore repository/resources/security/client-truststore.jks
# In case of managing your own mysql instance, import a cert issued by eg. rds-ca-2019-root 
keytool -import -alias wso2qamysqlserver -file  /etc/mysql-ssl/client-cert.pem -keystore repository/resources/security/client-truststore.jks

JDBC string enforcing SSL requires additional parameters:

jdbc:mysql://10.10.10.10:3306/ds21_carbon?autoReconnect=true&amp;useSSL=true&amp;requireSSL=true

Datasource in master-datasources.xml would look like below. ds21_carbon it's database schema name.

<configuration>
    <url>jdbc:mysql://10.10.10.10:3306/ds21_carbon?autoReconnect=true&amp;useSSL=true&amp;requireSSL=true</url>
    <username>root</username>
    <defaultAutoCommit>false</defaultAutoCommit>
    <password>root</password>
    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
    <maxActive>80</maxActive>
    <maxWait>60000</maxWait>
    <minIdle>5</minIdle>
    <testOnBorrow>true</testOnBorrow>
    <validationQuery>SELECT 1</validationQuery>
    <validationInterval>30000</validationInterval>
</configuration>

DB/Mysql

Contents

Connect

Ops queries

Show process list

Kill a process

Check version

Check current connections

Check a table size

Check size of all tables

Watch database size

mysql client - change the command line prompt

Create a user

Show users

Create or delete a user

Grant a user privileges per tables and column

CPU stress

AWS - Aurora logs

Align SQL inserts by a comma

SSL/TLS connections

Setting up wso2 to use SSL when connecting to MySQL DB

Navigation menu

DB/Mysql

Connect

Ops queries

Show process list

Kill a process

Check version

Check current connections

Check a table size

Check size of all tables

Watch database size

mysql client - change the command line prompt

Create a user

Show users

Create or delete a user

Grant a user privileges per tables and column

CPU stress

AWS - Aurora logs

Align SQL inserts by a comma

SSL/TLS connections

Setting up wso2 to use SSL when connecting to MySQL DB

Navigation menu

Search