MySQL

My Structured Query Language

Information

  • MySQL is a structured collection of data with a relational database management system (RDBMS) that operates within a structured query language (SQL).

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.

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


MariaDB

  • MariaDB is a drop-in replacement for MySQL and should be able to execute the same SQL statements as MySQL.

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?


Licenses

  • The license break down for the different applications that are referenced in this document.

  • MariaDB License

    • MariaDB Community Server / Community Edition is released under GPL license v2.
    • MariaDB Enterprise Edition is a proprietary license that is available through a subscription from MariaDB.
    • MariaDB SKYSQL is a cloud-first database solution that is available through MariaDB and operates under existing cloud infrastructure, GCP / AWS.
  • MySQL License

    • MySQL Community Edition is released under GPL licenses v2.
    • MySQL Enterprise Edition and higher is under a proprietary license through Oracle and is considered premium software.