Search Our Database

How to create a MySQL database and user with privileges in MySQL 8.0

Last updated on |
under
Database, MySQL, Access Control & Account Management
|
by

Introduction

This guide is intended for system administrators, database managers, and developers who need to create new databases and manage user privileges in MySQL 8.0. It provides step-by-step instructions for logging into the MySQL client, creating a database, and assigning user privileges. The guide also includes instructions for creating a new user if one does not already exist. These steps are useful when setting up new databases for applications or granting appropriate access levels to different users. The instructions are applicable when managing MySQL databases on Linux-based servers. The solution is implemented through the MySQL command-line interface and is most effective when configuring database environments or setting up new projects.

 

Prerequisites

  • Access to MySQL 8.0 or higher installed on a Linux-based server.
  • Root or administrative access to MySQL.
  • Familiarity with using the command-line interface (CLI).
  • Access to your server via SSH to log into the MySQL client.

 

Steps-by-steps Guide

There are two methods covered in this guide: creating a new database and assigning privileges to an existing user, and creating a new database user if one does not already exist. Each section will provide detailed instructions to help you complete these tasks in MySQL 8.0.

 

Create a New Database

Create a New Database

Follow the steps below to create a new database and assign privileges to a user in MySQL 8.0. If the user doesn’t exist yet, instructions on creating a new user are provided further down.

Step 1: Log in to MySQL Client

First, log in to the MySQL client after accessing your server via SSH using the following command:

mysql -u root -p

 

Step 2: Create a New Database

Once logged in, use the following command to create a new database:

CREATE DATABASE 'databasename';

 

Step 3: Grant Privileges to a Database User

If the user already exists, you can grant the necessary privileges to that user using the following command:

GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'hostname' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

If the database user doesn’t exist yet, proceed to the tutorial below to create a new user.

 

Create a New Database User

Create a New Database User

Step 1: Log in to MySQL Client

Log in to the MySQL client with the following command:

mysql -u root -p

 

Step 2: Create a New User

Use the following command to create a new user:

CREATE USER 'new-username'@'localhost' IDENTIFIED BY 'password-for-new-account';

 

Conclusion

By following these steps, you have successfully created a new MySQL database, assigned user privileges, and optionally created a new user if needed. This process ensures secure access to the database and provides a structured method for managing MySQL databases within your server environment.

For additional assistance or if you encounter any issues, please contact our support team at support@ipserverone.com.

 

Article Posted on 23 February 2020 by Louis

Related Article

Related Posts