What happens when a MySQL database runs out of disk space?
If you are conversant with MySQL databases, you’ll notice that the MySQL will become unresponsive, and the application will return Error 28.
When this happens, you’ve some options to clear up the disk space to enable MySQL to continue writing to the disk and resume operation.
You could:
- Remove unnecessary files from the database.
- Delete old and unwanted tables from the database, and run the “OPTIMIZE TABLE” SQL query command to move the freed space to the OS.
- Purge the binary logs; that’s if the MySQL is still responsive and you enabled the binary option in your MySQL server.
- Add more space to the disk or partition.
Or you could even empty the entire database.
The article guides you through all you need to know on how to empty a MySQL database.
So, let’s get started.
Method 1: Via phpMyAdmin
You could empty a MySQL database by dropping the tables, and luckily, the phpMyAdmin web interface makes this more effortless.
The phpMyAdmin is free software that helps in the administration of MySQL and MariaDB over the web. It’s like a control panel that streamlines MySQL management to eliminate the need to memorize and write MySQL command codes.
It helps users manage:
- Databases
- Tables
- Columns
- Relations
- Indexes
- Users
- Permissions, etc
To get started with phpMyAdmin, log in to your Spanel account.
The Spanel is Scalahosting’s all-in-one control panel that enables you to manage your entire hosting account without paying any additional licensing fees.
In the Spanel dashboard, click on the phpMyAdmin tool under the Databases section.
Spanel will redirect you to the phpMyAdmin web console. On the interface, click Databases on the top menu bar.
You’ll see a list of your databases; click on the name of the database you intend to empty. Doing this will open the database, and you’ll see all its tables.
Click on Check all to select all the tables at a go.
Or, you could opt to select the tables individually if there are specific tables you don’t intend to delete.
After checking all the tables you want to drop, click on With selected below the tables and select Drop 0n the menu list.
This command will execute the “DROP TABLE” SQL query to drop the selected tables at once.
Click on the Go button in the next step to confirm and finally execute the task.
Method 2: Via the Command Line
Unlike the phpMyAdmin that makes emptying a database pretty straightforward, using the command line to empty a MySQL database will require you to drop the database first and recreate it.
Dropping a MySQL database via the command line is irreversible. So, ensure that you have the drop and create database privilege assigned to your user account. If not, you’d only drop the database but won’t be able to recreate it.
And when you sort these out, here’s how to empty a MySQL database via the command line.
Log in to your server remotely via an SSH client to connect to the database you intend to drop. Of course, make sure to enable SSH for your hosting account.
If you’re a Windows computer user, you could consider installing Putty.
Enter your hostname or IP address in the Host Name field, fill the Port field and select SSH as your connection type. Click on the Open button.
If you are a macOS user, you don’t have to bother installing a third-party SSH client. It comes with an in-built Terminal.
When you establish an SSH connection and access the command line, run this command to log in as the MySQL root or admin user.
$ mysql -u root -p
Or you could use this command to log in to the user assigned to the database you intend to drop.
Mysql -uUsername -pPassword
Replace Username and Password placeholder with the MySQL username and user’s password, respectively.
Now, run the DROP DATABASE query on the command prompt to drop the intended database.
mysql> DROP DATABASE dbname;
Replace the dbname placeholder with the name of the database you intend to drop.
That’s it.
Wrapping It Up
We’ve made emptying a MySQL database elementary and intuitive. Follow the outlined steps to run this task any time you feel like doing so.
But always ensure you’ve got the “drop and create database” privileges.
You could always contact the Support team if you need help. Scalahosting provides free support for database issues.
