Cheat Sheet

Most of the commands i use at work which used for my reference

Show Database by Size
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Show Size consumed by Tables in Database (Change database_name)
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Disk Space Usage
ncdu / or df -h

Copy, Move, Sync Files to S3 Bucket
Note – Attach S3 Write Access Role to EC2 Instance
aws s3 cp database-backup.sql s3://bucketname
aws s3 mv database-backup.sql s3://bucketname
aws s3 sync database-backup.sql s3://bucketname

Encryption & Decryption
gpg --batch --passphrase-file key.txt -c data.txt
gpg --batch --passphrase-file key.txt -d data.txt.gpg > b.txt

Compression
Works only on files
-9 is compression Ratio
gzip -k -9 filename

On Directories
GZIP=-9 tar cvzf log.tar.gz /path/to/directory
tar cv catalina.out-20180722 | gzip –best > file.tar.gz

Decompression
tar -xvzf file.tar.gz

Mysql
Create User
CREATE USER 'john'@'%' IDENTIFIED BY 'secret';
Note Grant Access After User Creation
Readonly Access
GRANT SELECT ON *.* TO 'john'@'%' IDENTIFIED BY 'secret';

All Access
GRANT ALL PRIVILEGES ON *.* TO 'john'@'%' IDENTIFIED BY 'secret';

Get All Users
SELECT User FROM mysql.user;

Delete User
DELETE FROM mysql.user WHERE user = 'john';

Get List of Active Connections
show processlist