AWS, AWS Aurora

AWS Aurora | MySQL Cheat Sheet

show databases;
show tables;
connect MyDatabaseName;

Check Tables size

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;
SHOW FULL PROCESSLIST;

SHOW VARIABLES LIKE "general_log%";

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND INFO LIKE '%UPDATE %';

MySQL Table with Identity (auto increment)

CREATE TABLE study
( study_id INT(11) NOT NULL AUTO_INCREMENT,
  study_col VARCHAR(255) NOT NULL,
  CONSTRAINT study_pk PRIMARY KEY (study_id)
);

Insert into table with identity

INSERT INTO study(study_col)  VALUES ( 'new_study' );

MySQL Client install and running from CLI – user – password – database

sudo apt install -y default-mysql-client

mysql -u user -h 1.2.3.4 -pPassword someDB -e "select * from t"

Suppressing and removing the headers and lines in MySQL cli results set (notice the -sN, -s, -N argument)

mysql -N -u airflow -h 1.2.3.4
mysql -s -u airflow -h 1.2.3.4 -pairflow airflow  -e "select study_id from study where study='str4';"
mysql -sN -u airflow -h 1.2.3.4 -pairflow airflow  -e "select study_id from study where study='str4';"

change the encoding of MySQL table

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

Check the encoding of a MySQL table

SHOW CREATE TABLE myTable;

Convert encoding to MySQL column

SELECT column1, CONVERT(column2 USING utf8)
FROM my_table 
WHERE my_condition;

Query log mysql

https://tableplus.io/blog/2018/10/how-to-show-queries-log-in-mysql.html

Audit aurora logs in athena

https://aws.amazon.com/blogs/database/audit-amazon-aurora-database-logs-for-connections-query-patterns-and-more-using-amazon-athena-and-amazon-quicksight/

https://console.aws.amazon.com/rds/home?region=us-east-1#performance-insights:

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

Leave a Reply