Once in a while we all need to keep backup of all the MySQL databases in our system. We can easily do that with mysqldump, but one small issue with mysqldump is that it generate a dump of all databases into a huge single file. Here i’ll show you how to keep backup of every database in separate file using the same tool. I will breakdown the process in steps.
In step 1 i will generate a list of available database and save them in a file called db-list.txt
In step 2 i will read the database name from that file (
db-list.txt) one by one and generate a sql dump for that database.
Let’s do it.
mysql -u root -p -e "show databases" > db-list.txt
for /f %a in (db-list.txt) do (mysqldump -u root -p %a > %a.sql)