MySQL Management in cPanel: Create Users, Assign Privileges and Backup Easily

Managing MySQL databases in cPanel is a fundamental task for every website owner or developer. If your hosting provider uses cPanel, the good news is that it offers an easy-to-use interface to create databases, add users, assign privileges, import databases and take backups — all without needing to write a single line of code.

In this step-by-step blog, we will walk through the essential MySQL database operations in cPanel:

• Creating a MySQL database
• Creating a MySQL user
• Assigning user privileges to the database
• Importing the database
• Taking a backup of the database

Step 1: Log in to cPanel

To begin, log in to your cPanel account using the credentials provided by your hosting provider.

Step 2: Create database and database user in MySQL

To create database and users, cPanel provides two method, using Manage My Databases section and Database Wizard section.

We will see both of the methods here

Method 1: Using Manage My Databases in cPanel

In Manage My Databases, you can create database and users separately and then assign user to the specific database with privileges.

Create Database

1. To manage databases, click on Manage My Databases icon under the Databases section of cPanel.

2. Add unique database name in New Database input box.

3. Click on Create Database. A successful message will appear on screen.

4. Click on Go Back to check database created successfully.

Create Database User

1. In Manage My Databases, under Add New User section, add following details
   • Username: Choose a unique username
   • Password: Set a strong password and retype in Password (Again).

Alternatively, you can use Password Generator button to create new password.

2. Click On Create User button. A ‘successfully created a database user’ message will appear on screen.

3. Click on Go Back to check database user created successfully.

Assign User to Database

1. Under Add User To Database section, select the User and Database from dropdown menu and click Add.

2. In Manage User Privileges section, select ALL PRIVILEGES.

3. Click on Make Changes to apply changes to database.
4. After success message, user will be assigned to the database. Click on Go Back to check user assigned to the database.

Method 2: Using Database Wizard in cPanel for MySQL

1. To access database wizard, click on Database Wizard icon under the Databases section of cPanel.

2. Add database name in the given input box, click on Next Step.

3. Add database username, Password and retype in Password (Again) in the given form, alternatively you can generate password with Password Generator button. Store the generated password in safe place.

4. Click on Create User.
5. Select privileges for the user, click on Make Changes to apply changes.

6. After changes applied, click on Next Step.
7. A success message will appear on screen.

Using any method mentioned above, user can create database along with user assigned.

Step 3: Import MySQL database in cPanel using phpMyAdmin

1. To use phpMyAdmin, click on phpMyAdmin icon under Databases section in cPanel. It will be redirected to phpMyAdmin.

2. Select the database where you want to import the database file.
3. Click on Import tab in menu bar.
4. Under File to import section, click on Choose File and select the file from your system.

5. Leave the default settings as-is unless you have a specific format or charset requirement.
6. Lastly, click on Import button.

You’ll receive a success message once the database is imported.

Step 4: Export (Backup) MySQL database in cPanel

Backup database in cPanel has two methods. One is using export tab in phpMyAdmin and another is Backup section in cPanel.

Export method using phpMyAdmin has custom features where you can export the data in required formats, select the tables and compression.

Method 1: Using export feature in phpMyAdmin

1. To use phpMyAdmin, click on phpMyAdmin icon under Databases section. It will be redirected to phpMyAdmin.
2. Select database for backup.
3. Click on Export tab in menu bar.
4. Select export method from Export Method section.
   • Quick: Best for basic backup needs.
   • Custom: Allows table selection, output format, and more options.

5. In Output Section, select the Compression, select None for .sql file

6. Click on Export to download the backup file.

Method 2: Using Backup feature in cPanel

1. Click on Backup under Files section in tools.

2. Scroll down for Partial Backups section.

3. Just click on the Database under Download a Database Backup section.
4. Backup file will be automatically download to the computer.

Conclusion

Managing MySQL databases via cPanel is straightforward process. With just a few clicks, you can create a database, set up a user, assign permissions, and take a secure backup. Using phpMyAdmin, you can import the database, create a backup file of database. These steps are crucial for website performance, security, and data integrity