I know, man.  No, I mean I know I could use ‘man pages’!  Or I could just ‘google it’ but then it isn’t mine.  Since I do not have time for a complete brain-dump this MySql “cheat sheet” will grow over time.  Feel free to add your favorite MySql commands in the comments, if their really useful I’ll add them to the list!

If you don’t know what MySql is…look it up!  And, who are you?!  Seriously…

Create a DB & Assign to a User:

Create a New DB, Create a User and Grant them permissions to the New DB.

mysql> create database someDB_name;
Query OK, 1 row affected (0.13 sec)

mysql> create user 'someUser_name'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON someDB_name.* to someUser_name@localhost;
Query OK, 0 rows affected (0.05 sec)

The above should be pretty self explanatory but for thoroughness sake…  The first line creates an empty database.  At that point only the root or admin user can use this database.  The Second command, creates a user account and assigns it a password.  This user account has NO privileges at this point.  The Third line is the most important.  When you grant permissions you can grant global permissions *.* meaning you can access ALL databases (not a good idea).  OR you can set Database permissions like I did above; database_name.*.  That .* after the database name means you have full privileges to that database only.  OR you can refine the permissions even further and grant permissions to a specific table in the database: database_name.some_table. Hope that clarifies things.  To state it in a more succinct way use this framework:

 GRANT [type of permission] ON [database_name].[table_name] TO ‘[username]’@'localhost’;

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.  I always like to test the account out before giving the account to the user.  To test out your new user, log out and log back in as the user:

mysql> quit 
mysql -u [username]-p

Revoke User Access or Delete a whole DB:

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

You delete databases with DROP, you can also use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

 Recover Access when you have forgotten the root password:

Not that, that ever happens…

mysqld_safe --skip-grant-tables
mysql --user=root mysql

    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit;

That’s it for now.  More to follow…

---