MySQL cheatsheet

Sometimes I derp out and forget some essential commands on MySQL. This is for me to reference if that ever happens. :D

Commands

If you need to create a new user:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Now to create a specific database:

CREATE DATABASE [IF EXISTS]* new_database;

Showing ALL the databases on MySQL:

SHOW DATABASES;

Showing ALL the users on MySQL:

SELECT User FROM mysql.user;

To grant ALL the privileges on the table to a specific user:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

You can also choose which privilege to give to a user when assigning a table, which include the following:

ALL PRIVILEGES - as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system).
CREATE - allows them to create new tables or databases.
DROP - allows them to them to delete tables or databases.
DELETE - allows them to delete rows from tables.
INSERT - allows them to insert rows into tables.
SELECT - allows them to use the Select command to read through databases.
UPDATE - allow them to update table rows.
GRANT OPTION - allows them to grant or remove other users' privileges.

Format if you choose to give specific permissions:

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

Revoke any permissions which might have been given to a user for a specific database:

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

Removing a database for whatever reason you need to:

DROP DATABASE [IF EXISTS]* new_database;

If at any time you change a setting related to users or databases, make sure to flush for new changes:

FLUSH PRIVILEGES;

It's fairly easy to add tables and individual data but is not the scope of this entry.

+ [IF EXISTS] will avoid an error relating to said functions already existing.

Remote Access

In order to allow remote access to a MySQL server, you'll need to allow MySQL to bind to a public address.

You'll need to visit /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL 5.7+) and look for the following in the file:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address  = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Simply comment out bind-address = 127.0.0.1 and restart MySQL.

WARNING! WARNING! WARNING! A reminder to myself really, but always make sure to secure your MySQL installation with the following commands:

mysql_install_db 
mysql_secure_installation

The first one will clean up the structure of all the MySQL databases and their tables. The second one will ask you a series of questions which will secure your MySQL instance.

If you do plan on giving access to some MySQL databases, the commands above still apply, but with a minor change:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@‘192.168.1.254’ IDENTIFIED BY '[password]';

Change 192.168.1.254 to reflect the remote IP you're wanting to connect from. To revoke access from this remote connection would follow the same template as above (see REVOKE). It also seems the password needs to be addressed in order for the changes to occur. Don't forget to flush the privileges!

If one wants to be super secure, you could whitelist only certain IP addresses to communicate over the firewall for the specific public MySQL port (3306), change the port all together, etc. However, it is not in the scope of this entry.