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.
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.
Now that you’re in you need to create some users. Within the MySQL console run these commands:
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:
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:
Creating databases is essential to working with a relational database system. Here are the commands to do it:
Create a database
To add a table to a database you must first be using the database.
After naming your table the syntax within the parenthesis are as follows:
- Column name
- Column type and options (like
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.