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”
3. In the next page under “Current Databases” you can see the list of databases you have created
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.
Click on “Delete Database” to delete the database and click on “cancel” to keep the database instead of deleting.
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.
Click on “delete” to delete the mySQL user. You will see the below confirmation before deleting the mySQL database.
Click on “Delete User” to delete the 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 -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)
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;
4. Run the below command to drop/delete the database
DROP DATABASE [databasename];
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]
Run the below command to delete the mySQL user :
DROP USER ‘username’@’localhost’;
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
2. Enter cPanel username and password to access cPanel
3. In the cPanel home page Search for “MySQL Databases”. Click on “MySQL Databases” under “Databases”
cPanel Home >> Databases >> MySQL Databases
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.
5. Always take a backup before renaming the mySQL database.
You can take mySQL database backup through cPanel or from server backend.
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
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”
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
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
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
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
Enter the database name and click on “Create Database”. Your database name begins with your cPanel username.
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.
Click on “Create User”
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’
In the next page you can tick what all privileges you want to add for the mySQL user.
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.