Emptying a database without deleting it

You are about to reinstall the application and you want a clean start. The obvious route looks like deleting the database and creating another. It is the wrong route, and the bill arrives later: the reinstall fails saying it cannot connect, with the right name, the right user and the right password, and nobody can see why.

The difference nobody explains

On a hosting account there are three separate pieces, and only the first of them is the database itself.

The piece What happens to it if you delete the database
The database, with the tables inside it Gone, and everything stored in it with it.
The database user Still there, and still listed in cPanel. But with nothing left to reach.
The link between the two, with its privileges Gone as well, and this is the one nobody sees. Creating another database with exactly the same name does not bring it back: you have to attach the user to the database again and grant all privileges, by hand.

That is why the reinstall fails with everything apparently correct. The connection details are fine; what is missing is the authorisation, which left with the old database.

Before anything else, an export. Always, and especially when you are sure you will not need it. In phpMyAdmin, with the database open, the export tab hands you the file. If the database is large, export it over SSH: importing (and exporting) a large database over SSH. Keep the file off the server. What follows is instant and has no undo.

Emptying: drop the tables, leave the database standing

Emptying means deleting the tables. The database stays, the user stays, the privileges stay, and the application’s configuration file goes on working untouched. It is what you want in almost every case.

1 Take the export and keep it on your own computer. Never leave it inside the site folder: a loose database file there is the password and all your data available to anyone who guesses the name.
2 In phpMyAdmin, open the database and select every table. Below the list there is an action box for whatever is selected: choose the one that deletes, shown as Drop. It shows you the commands and asks for confirmation before running anything.
3 Confirm that it is empty, not that it is gone. The database has to still be in the left-hand list, now with no tables at all. If it vanished from the list, you deleted the database rather than the tables: go to the last section of this article.
4 Reinstall. The new application finds an empty database and creates its own tables in it.
If some tables refuse to go, it is because others depend on them. The way that always works is to run the lot in one go on the SQL tab: SET FOREIGN_KEY_CHECKS = 0; first, then the delete commands, then SET FOREIGN_KEY_CHECKS = 1; at the end. It really does have to be one submission: every submission to phpMyAdmin opens a fresh connection, and that setting does not carry from one connection to the next.

The list of commands, without typing it out

On a database with dozens of tables nobody types a command per table. Ask the engine for the list. On the SQL tab, swap account_db for your database’s full name, prefix included:

SELECT CONCAT('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'account_db';

The result is the list, ready to run. Copy it, paste it on the same tab between the two FOREIGN_KEY_CHECKS lines, and execute. Read the list before you run it: if a table from another site turns up in there, you typed the wrong database name.

When deleting the database really is what you want

What you want to do What you do
Reinstall the same application from scratch Empty it. The database and the user serve again, and you never touch the configuration file.
Swap to a different application on that domain Empty it. The new application creates its own tables in an empty database without complaining.
Stop using that database for good Delete it. And delete the user too, if nothing else needs it: a forgotten user with an old password is one more door.
Free up space and carry on using the site Neither. What you want is a clean-up, and that is done with the site running: cleaning and optimising the database.
The site stopped connecting and you do not know why Delete nothing. The list of causes is in when the site says it cannot connect to the database.

You already deleted the database. How to put it right

1 Create another database with exactly the same name. In cPanel you type only the part you chose; it adds the account prefix itself. The final name has to come out identical, letter for letter, to the one in the application’s configuration file.
2 Attach the user to the database again, with all privileges. This is the step everybody misses, and it is the one that makes the error go away. The walkthrough is in creating databases and using phpMyAdmin.
3 Import the export, if you have one. If the file is too big for phpMyAdmin, the route is importing over SSH.
4 If you have no export at all, there are still the panel’s own copies, and you should touch nothing else until you have looked: how to restore your data with JetBackup.
Check the database name before you delete anything. If the account carries several sites, the names all look alike and the same prefix sits in front of every one of them. A site that had nothing to do with this can be emptied in two seconds. If you are not sure which database belongs to that site, find out first.

Deleted the database and have no copy of your own? Send us the domain and the time, quickly, and we will look at what is kept.

Open a support ticket

SEE ALSO

Hosting plans and what each one includes

Support Policy: how far our help goes

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
MySQL or MariaDB: which one runs here, and does it matter Shared hosting here runs MariaDB, not Oracle’s MySQL Server. At the time of writing the version is 10.11.16. For almost...