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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql -uroot -p
mysql -uroot -p
mysql -uroot -p

2. Create the new database.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
create database newdbname;
create database newdbname;
create database newdbname;

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON newdbname.* TO 'newusername'@'localhost';
CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'somepassword'; GRANT ALL PRIVILEGES ON newdbname.* TO 'newusername'@'localhost';
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).

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
flush privileges;
flush privileges;
flush privileges;

5. Populate the DB from some backup file…

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql newdbname -unewusername -psomepassword < somebackupfile.sql
mysql newdbname -unewusername -psomepassword < somebackupfile.sql
mysql newdbname -unewusername -psomepassword < somebackupfile.sql

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

Leave a Comment