Ever needed to delete all databases in MySQL? I recently faced this challenge. With a vast number of databases, manually deleting each one was daunting and time-consuming. But don’t fret, I’ve discovered an efficient command that makes this task a breeze.
Streamlining MySQL Database Deletion:
The Lifesaver Command:
mysql -uroot -p<password> -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | gawk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -p<password>
Replace <password>
with your actual MySQL password.
This powerful command eradicates all databases on your server, sparing mysql
and information_schema
. If you wish to retain specific databases, simply append | grep -v your_database_name
after grep -v mysql| grep -v information_schema|
.
Note: For this command to work, it’s essential to have gawk
installed. If you’re on Ubuntu and don’t have it, here’s how to get it:
apt update
apt install gawk
Managing MySQL databases doesn’t have to be tedious. With the right commands, you can expedite processes and manage your databases efficiently.