Create user and set privileges in MySQL/MariaDB

Connect to MySQL/MariaDB
sudo mysql -u root

Create database

Create database
mysql> CREATE DATABASE IF NOT EXISTS your_database_name;

Add User

Create user
CREATE USER 'set-your-username'@'%' IDENTIFIED BY 'set-your-password';

Note. ‘set-your-username’@’%’ equals ‘username’@’host/domain’. localhost eg. ‘username’@’localhost’ can be used if mysql is only accessed from the same server that’s running the script.

Grant permissions to user ( All databases )
GRANT ALL PRIVILEGES ON *.* TO 'your-username'@'%';

Note.1 *.* means all databases and all tables. use dbname.* to restrict the user to a single database and dbname.tablename to restrict both the database and a table.
Note.2 % is a wildcard matching all domains.

Grant permissions to user ( Single database )
GRANT ALL PRIVILEGES ON dbname.* TO 'your-username'@'%';

Flush privileges
FLUSH PRIVILEGES;

 

Sources

Helper commands

View databases
SHOW DATABASES;

 

View users
SELECT host, user, password FROM mysql.user;

 

View user privileges
SHOW GRANTS FOR 'your-username'@'localhost';

Kontakt

Confirmation icon

Har du spørgsmål til et projekt, eller ønsker du at tale en idé igennem?

I så fald er du velkommen til at ringe, eller sende mig en besked med dine spørgsmål.

  • Airplain icon Send besked

Mads Hansen
Vesterbro 13, baggård, stuen
5000, Odense C.

Tlf. 60 94 27 14
info@lmp.dk
cvr. 34 51 04 07