Whenever you work with MySQL databases, it is a good idea to do backups. I have used
mysqldump --all-databases for ages for that.
But all the databases are dumped into one huge sql file. And when it comes to restoring of data, you are in trouble. You’d still get your backup, but it’ll take you a long time to separate out only one database (think about 20+ databases running on the server). So it is a good idea to separate databases into separate files and there is no solution out of the box for that. So one needs to come up with ways to do this. Thanks to Stackoverflow we have something already done for us. I’ve added some things for zipping and here we are:
@REM echo off @REM this script creates a backup of all the mysql databases in the designated directory. @REM Please provide day of a week as a parameter set file=d:\Mysql_backup\%1%_backup MKDIR %1% mysql.exe -uUSERNAME -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do mysqldump %%D -uUSERNAME --routines > %1\\%%D.sql 7z a %file%.zip %1% rmdir /s /q %1%
Where USERNAME is a dedicated backuping user in mysql. I set up my backuping user with select permissions on all databases and no password. None of my mysql databases are available to the outside world, so no problem with having no password there.
This script needs a parameter to run. I usually set up daily backup jobs that are giving day of the week as a parameter, so I get Monday_backup.zip, Tuesday_backup.zip, etc.