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://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: