Skip to content

SQL

Information

SQL (Structured Query Language) is the standard programming language used for managing relational databases and performing various operations on the data they contain. The SQL ecosystem includes a wide array of database systems such as MySQL and PostgreSQL, which are known for their robustness, scalability, and support for complex queries. These systems are integral to many applications, providing transactional support, ACID compliance, and advanced data management capabilities to ensure data integrity and performance across numerous industries.

Cheatsheet

MySQL/MariaDB Cheatsheet

  • Admin Commands

    • Drop Database (Be extremely careful when running this)

      • DROP DATABASE {$db_name};
        • Replace {$db_Name} with the database that you wish to DROP.
        • Remember that all the data will be removed and can not be recovered.

    • List All Users

      • SELECT user, host FROM mysql.user;
        • This will display all the users within the database instance.

    • Create User

      • CREATE USER {$user[@'host']} IDENTIFIED BY 'plain-text-password';
        • {$user[@'host']} can be replaced by an example like this 'root'@'localhost' or 'root'@'10.%.%.%'
          • 10.%.%.% - The % is a wildcard for the IP Address subnet.

    • Drop User

      • DROP USER {$user[@'host']}
        • This will only remove the user from the mysql instance.

    • Create Database

      • CREATE DATABASE {$database_name}
        • {$database_name} can be replaced as database_name_example , thus creating a statement like CREATE DATABASE database_name_example.

    • Grant permissions / privilegages.

      • GRANT ALL ON ${database_name}.* TO {$user[@'host']}
        • There are a couple situations that this statement creates, first it gives ALL permissions to the database, ${database_name} with the .* being a wildcard for all the tables inside of the database. Finally the {$user[@'host']} represents the user connecting via the IP Address.

MySQL

MariaDB

PostgreSQL

PlanetScale

Supabase

Backup

If you need a quick way to backup the mysql database, then use this command below:

sudo docker exec [$mysql_container_name] /usr/bin/mysqldump -u [$mysql_username] --password=[$mysql_password] [$database_name] > [$destination_path]

You could save the execution command as a shell file and/or reference it inside of your AWX stack.

More information on AWX and Docker

Notes

Videos

References

Q&A

  • What to do if you just installed mysql-server on Ubuntu on WSL and it never even prompted you for a password?

    • Well here’s a cool link that tells you exactly what to do.
    • Long story short it’s like ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' once you actually get in

  • What to do if you’ve never used this foreign and vaguely antiquated technology before and you wish you had a time machine that would let you go back in time so you could sit with the pioneers of this dying technology and learn from them what drugs they were smoking when they decided on the syntax?