MariaDB Cheatsheet
A long time ago, I used to know all MySQL commands by heart. Now I am re-learning them, but this time with MariaDB, and I learn best by writing things down. So here is the cheatsheet to have a way of looking up commands quickly.
The commands below can be used, e.g. in the MariaDB console. You start the console like so:mysql -u root -p and then enter the password for the root user. It's also interesting to try it out with the user you created, to confirm the (limited) permissions you gave it. Start the console like so:mysql -u username -p.
Permissions/Granting (useful for a Django DB)
Also MariaDB has users, those have privileges (or rights) that can be given depending on host, database, even table level. So the first thing is to create a user, and grant them some privileges. NOTE: I believe a good hint is always to start out as restrictive as possible. If you find no other argument for that just see it as security from the start. It does not make a DB secure per se, but its a good start.
| Command | What it does |
|---|---|
DROP USER 'username'@'%'; |
The user is always a combination of user+host, also grants can only be given to user+host combinations. So sometimes you need to drop the user. And re-create it, see below. |
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; |
Creates a user that is for any host. |
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'%'; |
Remove all grants, this is mostly a good idea, if afterwards you want to grant only special privileges, see next line. |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON db.* TO 'username'@'%'; |
Grant only privileges for some statements on all tables of a database for "username" on any server. A django app needs only these privileges, normally. |
FLUSH PRIVILEGES; |
After you have granted privileges, you need to flush them, so they are active. |
SHOW GRANTS FOR 'username'@'%'; |
See if the grant command did what it should. |
Database Management
Once you have set up all the permissions as needed, you can start to create databases and tables. Here are some commands that I always need again.
| Command | What it does |
|---|---|
SHOW DATABASES; |
Shows all databases, this user can see, on the server. |
CREATE DATABASE myprojectdb; |
|
USE myprojectdb; |
For any statement working on a certain DB, you need to say "which one" first, this is done with the USE statement. |
SHOW TABLES; |
Lists all tables in the DB "in use". |
DESCRIBE mytable; |
Shows all the columns and their definitions for the given table. |
User Management
| Command | What it does |
|---|---|
SELECT user, host, password, super_priv, select_priv, update_priv, insert_priv, default_role FROM mysql.user; |
Show all the users and their basic privileges |
desc mysql.user; |
See all the columns in the mysql user table |
SHOW GRANTS; |
|
SHOW GRANTS FOR 'root'@'localhost'; |
|
SHOW GRANTS FOR 'root'@'%'; |
Show grants for root on all servers |