Creating MySQL/MariaDB database and user

This seems to come up frequently for me, so thought I’d keep a note up here. Usually, this comes from making a backup some where else via a command like “mysqldump dbname -uusername -ppassword > somebackupfile.sql”. To create a new DB, create a user, grant privileges to the DB, and import the back data, do something like this…

1. Log into MySQL/MariaDB via root user.

mysql -uroot -p

2. Create the new database.

create database newdbname;

3. Add a user and grant privileges to the DB.

CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON newdbname.* TO 'newusername'@'localhost';

4. Remember to flush privileges (this never matters until it’s really frustrating to figure out what you forgot to do).

flush privileges;

5. Populate the DB from some backup file…

mysql newdbname -unewusername -psomepassword < somebackupfile.sql

Yes, this info is all over the Internet. And now it’ here too. ?

Leave a Comment