How to delete a mySQL database and MySQL user on a cPanel Server

How to delete/drop a mySQL database and MySQL user on a cPanel Server


 
You can delete the mySQL database either from server backend or through cPanel.

I. Steps to Delete the mySQL user and database from cPanel

1. Login to your cPanel using cPanel username and password

Link to access cPanel : https://YourDomain.com:2083
Username : Enter cPanel username
Password : Enter cPanel Password

2. Click on “MySQL Databases” Under “Databases”
 
mysql db cpanel
 
3. In the next page under “Current Databases” you can see the list of databases you have created
 
Remove mySQL database
 
4. Under “Actions”, click on “Delete” to delete the database.

Note : The database will be deleted when you click on ‘delete’, all the data will be lost. Please take a database backup if the data is important.

5. In the next page you will see confirmation before deleting the database.
 

delete mysql database

delete mysql database

Click on “Delete Database” to delete the database and click on “cancel” to keep the database instead of deleting.
 
mysql db deleted
 

Follow the below steps to delete the database user

Scroll down to the bottom of the page and you can see “Current Users”. Under “Actions” you can see the option to delete the mySQL user.
 
Delete mySQL user
 
Click on “delete” to delete the mySQL user. You will see the below confirmation before deleting the mySQL database.
 
Delete mySQL user cPanel
 
Click on “Delete User” to delete the mySQL user.
 
deleted mysql user
 

II. Steps to Delete the mySQL User and database from server backend

1. Log into linux server via SSH as ‘root’ user

2. Then login to mySQL server using the below command

mysql

OR

mysql -u root -p

Use the above command if you have set mySQL root password. Enter mySQL root password to access mySQL server.

[root@server ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2753
Server version: 5.6.34 MySQL Community Server (GPL)

mysql>

You can see the above mysql login prompt when you have successfully logged in mySQL prompt

3. Type “show databases;” to list all databases on the server

mysql> show databases;
 

mySQL show databases

mySQL show databases


4. Run the below command to drop/delete the database

DROP DATABASE [databasename];
 
Drop mysql Database command
 
On the above server I have deleted the database “accosdft_testing”. Type “show databases;” command to make sure it is deleted.

5. You can also use the below command to drop the database

mysqladmin drop [databasename]
 
mysqladmin drop database
 
Run the below command to delete the mySQL user :

DROP USER ‘username’@’localhost’;
 
DROP mySQL user
 

How to Rename a mySQL database on cPanel Server


 
You can Rename a mySQL database either through mySQL Databases section in cPanel or through phpMyAdmin

1. Open a new web browser and type the below link to log in to cPanel

http://Yourdomain.com:2082

2. Enter cPanel username and password to access cPanel
 

log in cpanel

log in cpanel


 
3. In the cPanel home page Search for “MySQL Databases”. Click on “MySQL Databases” under “Databases”

cPanel Home >> Databases >> MySQL Databases
 
cPanel MySQL db
 
4. Under “Current Databases” you can see all the databases in your cPanel account. Under “Actions” you can see “Rename” option for all databases. Click on “Rename” button next to corresponding database.
 
Rename mySQL database
 
5. Always take a backup before renaming the mySQL database.

You can take mySQL database backup through cPanel or from server backend.
 
Backup mySQL database cPanel
 

In the above picture click on the database and you can download database backup in sql.gz format (database.sql.gz)

Command to take mySQL database backup :

mysqldump databasename > databasename.sql

Backup is very important because you can restore the database if something goes wrong while changing database name.

6. Rename the database after taking the backup like shown in below image
 
change database name
 
7. Click on “Proceed” after entering the new database name
 


 
METHOD II :

You can also rename the mySQL database through “phpMyAdmin”

1. In the cPanel home page click on “phpMyAdmin” under “Databases”
 
Rename mySQL phpmyadmin
 
2. In the left you can see the list of databases. Click on the database you wish to rename.

3. Click on “operations” tab and there you can see “Rename database to” option
 
Change name of database
 
4. Enter the new database name and click on “Go”

The database name should be in format cPanelaccountname_newdbname

cPanelaccountname in front of the database should remain the same

5. The old database will be dropped and new database name will be created
 
drop and recreate database
 
Click on “OK” to rename databases
 

How to create mySQL database, MySQL user and add privileges on cPanel Server


 
1. Login to your cPanel account. You any of the below URL to access your cPanel account

https://YourServerIP:2083

OR

https://YourDomain.com:2083

OR

http://Yourdomain.com:2082

2. Enter cPanel username and Password to login. cPanel username and password will be provided by your webhositng company.

3. Click on “mySQL databases” under “DATABASES”

cPanel >> DATABASES >> mySQL databases

4. Here you can see the create database option like shown in below picture
 
Create mySQL database
 
Enter the database name and click on “Create Database”. Your database name begins with your cPanel username.
 
Created mySQL db
 
5. Click on “go back”, add MySQL user and mySQL user Password

In the username field enter the MySQL username. You can only enter 7 characters in the mySQL username field.

Password : In this field enter a strong mySQL password. You can use Password generator to generate a strong MySQL password.

You might see warnings if the mySQL password strength is less than 65. Strength should be above 65.
 
Create mySQL user password
 
Click on “Create User”
 
Created mySQL username
 
6. Add user to the database. (Add privileges for the database user)

Scroll down to the bottom and you can see the option “Add User To Database”

Select the database and mySQL user from the dropdown. Click on ‘Add’
 
Add privileges mysql user
 
In the next page you can tick what all privileges you want to add for the mySQL user.
 
User Privileges mySQL cPanel
 
Tick “ALL PRIVILEGES” and click on “Make Changes” to grant all privileges for the mySQL user.

Now you have created mySQL database, MySQL user + MySQL user Password and gave privileges on database to the user.
 
All privileges mySQL database