MySQL Cheat Sheet

MySQL is my production database of choice but I always forget a few commands like how to create new users and managing permissions for those users. That’s why I’m publishing my own personal MySQL cheat sheet that covers everything from creating new databases to managing users and permissions. I still haven’t switched over to MariaDB yet but seeing how it’s a drop-in replacement for MySQL this cheat sheet should work for MariaDB as well (their APIs are compatible, MariaDB just has more optimized internals). So here’s a MySQL cheat sheet. Enjoy.

Logging In

1

mysql -u <username> -p<password>

When you’re first getting started you’ll likely log in as root, set up your database, then be sure to run sudo mysql_secure_installation to turn off some insecure defaults.

If you’ve installed MySQL locally on a Mac using Homebrew then you don’t have a root password. You can just log in with mysql -u root -p.

If you installed MySQL on a VPS using something like sudo apt-get install mysql-server then you’ll be prompted to choose a password when you install the software. Please choose a strong password. Seriously. It’s easy. Make it long and give it some uppercase and lower case letters with numbers and throw in some special characters while you’re at it.

Adding Users

Now that you’re in you need to create some users. Within the MySQL console run these commands:

1

CREATE USER new_user@localhost IDENTIFIED BY 'a strong password here';

Granting user permissions

Once you’ve created your new user it has no permissions to do anything. You need to give your user permissions on any databases and database tables you want that user to have permissions on. Here we grant all privileges to the user on all databases:

1

GRANT ALL PRIVILEGES ON *.* TO new_user@localhost;

For more specific permissions you would need to replace those asterisks with database and table names. The list of possible permissions you can grant a user are:

• CREATE – Allow the creation of tables and databases
• DROP – Allow a user to delete entire tables
• DELETE – Allow a user to delete a table row
• INSERT – Allows user to insert a row into a table
• SELECT – Allows user to read data from a table
• UPDATE – Allows the user to update a database row
• GRANT OPTION – Allows a user to grant permissions to other users

The wise thing to do is always grant your users only the specific permissions they need to work with the database. If your app’s ORM has a user that only needs to read data then only grant read-only privileges.

When you’re done granting permissions to users you need to finalize them by flushing permissions. You do this by running:

1

FLUSH PRIVILEGES;

Creating Databases

Creating databases is essential to working with a relational database system. Here are the commands to do it:

Create a database

1

CREATE DATABASE my_database;

Adding tables

To add a table to a database you must first be using the database.

1
2

USE my_database;
CREATE TABLE my_table (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255), ...);

After naming your table the syntax within the parenthesis are as follows:

1. Column name
2. Column type and options (like AUTO_INCREMENT or PRIMARY KEY)

What did I miss?

If I missed something let me know and I’ll add it. As I come across commands I forget I’ll add them here as well. Hopefully this is a useful reference for you in your adventures with MySQL or MariaDB.