MySQL Database/User Creation & Privileges

mysql.md

Creating a user

CREATE USER user;

Creating a database

CREATE DATABASE database_name;

Granting privileges

Grants all privileges on database_name to user on the local machine.

GRANT ALL ON database_name.* to 'user'@'localhost' identified by 'aVeryStrongPassword';

Grants all privileges on database_name to user from any ip address.

GRANT ALL ON database_name.* to 'user'@'%' identified by 'aVeryStrongPassword';

For this to work, your MySQL server 'my.cnf' bind address needs to be set to a public facing ip address. bind-address = 0.0.0.0 You can change 0.0.0.0 to your server ip.

Grants all privileges on database_name to user from the ip address 123.456.789.10

This is a good practice for those wanting to allow remote access from a single server. If you plan to allow remote access, it is generally a good idea to at least use method to allow remote database access or properly configure your firewall.

GRANT ALL ON database_name.* to 'user'@'123.456.789.10' identified by 'aVeryStrongPassword';

Hopefully, now we have less people using the root user for every database on their machine.