My MySQL Cheat Sheet

Software Engineer at BMW, Oracle Certified Professional Java SE 11 Developer
Search for a command to run...

Software Engineer at BMW, Oracle Certified Professional Java SE 11 Developer
No comments yet. Be the first to comment.
Two whole days of my life, down the drain! Thankfully, it was just my pet project, but I ended up sacrificing an entire weekend wrestling with this enigma. So, I'm writing this blog post to save you from the same fate. Problem: I am using Quarkus and...

Shebang Shebang is a combination of bash # and bang ! followed the the bash shell path. Shebang is simply an absolute path to the bash interpreter. #! /bin/bash Make a file executable To make a .sh file executable only for your user, use: chmod u+x...

1. Show tables The command \dt stands for describe tables and shows all tables in the database, equivalent to show tables in MySQL. \dt 2. Create user Run the following SQL command to create a user. Replace and 'password' with the desired username a...

How to set JAVA_HOME on macOS Monterey? First check which shell you are using (either bash or zsh). For zsh shell, we can put the environment variables at ~/.zshenv or ~/.zshrc.echo $SHELL /bin/zsh On Mac OS X 10.5 or later, we can use /usr/libexe...

Fork your open source project you want to contribute into your GitHub and clone your fork. git clone git@github.com:dcnis/mockito.git Show current configured remote repositories. The origin repo points to your fork on GitHub. git remote -v > origin ...

This is a collection of commonly used MySQL statements in my daily work. Mainly for myself to copy-paste and save time.

SELECT * FROM mysql.user;
CREATE USER 'read-only-user'@'%' IDENTIFIED BY 'aSecurePassword1';
GRANT SELECT ON MyDB.* TO 'read-only-user'@'%';
SHOW GRANTS FOR 'read-only-user'@'%';
SELECT
table_name AS 'Table'
round(((data_length + index_length) / 1024/1024/1024), 2) AS 'Size in GB'
FROM information_schema.TABLES
WHERE table_schema = '<db_name>'
AND table_name = '<table_name>';
SELECT
table_schema AS 'Database'
round(((data_length + index_length) / 1024/1024/1024), 2) AS 'GB'
FROM information_schema.TABLES
ORDER BY (data_length + index_length) desc;
SELECT
table_schema AS 'Database',
table_name AS 'Table'
round(((data_length + index_length) / 1024/1024/1024), 2) AS 'GB'
FROM information_schema.TABLES
WHERE table_schema = '<db_name>'
ORDER BY (data_length + index_length) desc;
cd /var/log/mysql
mysqlbinlog mysql-bin.00001 | grep -m 100 -C 5 'searchString or position'
-C: Prints additional x lines surrounding the matching line
-m: Max number of returned matchings
If we want to find all tables which reference parent_table, we can use following command.
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = '<parent_table>';