Setting Up a MySQL Database and User

Last update: 06-05-2024

If you're working on a project that requires a MySQL or MariaDB database, here’s a quick guide to help you set up a database and create a user with the necessary privileges.

mysql -u root -p
CREATE DATABASE dbname;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
exit

Steps:

1. Log in to MySQL or MariaDB as the root user. There are a few options for that:
• Option 1: Use sudo. This is the default way to access MySQL/MariaDB in Debian-based systems. It will only work if you have root privileges:

sudo mysql

• Option 2: If the root user is configured to use a password, you'll need to provide your MySQL/MariaDB password. This method does not require root access:

mysql -u root -p

• Option 3: For XAMPP on Windows, use the following command. (MySQL/MariaDB does not have a password on XAMPP by default, so when prompted for the password, just hit enter). This assumes you have XAMPP installed in the default location:

c:xamppmysqlin.mysql.exe -u root -p

2. Create a new database (replace dbname with the actual name you want to use):

CREATE DATABASE dbname;

3. Create a new user and assign a password (replace username and password with the actual username and password you want to use):

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

4. Grant all privileges on the new database to the new user (replace dbname and username with the actual database name and username):

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

5. Exit MySQL/MariaDB:

exit

And that's it! You now have a new database and a user with full privileges on it. This setup is ideal for development environments or when setting up a new project.