Daily backup of mysql-databases under windows
The task that stood before me:
There is a server with mysql running windows server 2008 R2, on which, among other things, mysql runs with several dozens of databases, the number and composition of which periodically change. It is necessary to organize a daily backup of these databases without stopping the mysql server, and in such a way that each database falls into a separate archive. This, at first glance, the simplest task (perhaps the way it is) for me was quite difficult.
What does Google tell us?
The fact that there is mysqlhotcopy and mysqldump. The first works directly with the database files, the second - makes dumps using queries.
I couldn’t get mysqlhotcopy to work even with the simplest parameters, and when I did, I came to the conclusion (correct me if I am mistaken) that this script is not adapted for windows.
')
I started it with mysqlhotcopy, because it is easier to work directly with files in my case - I just slipped the files into my muscle and work. The dump, though weighs less due to the lack of indexes, still needs to be imported. But, since I did not find another way without stopping mysql , we will do dumps.
Here I found an excellent guide to script keys. So, he can make a general dump of all the bases into one file (extremely inconvenient), or make dumps of the listed bases. Since the number and the names of the bases change with us, then writing a list is not an option - every time we rule it out. So you need a script that will substitute in turn each database in mysqldump.
I implemented it this way - the batch file looks at which folders are in the mysql data directory and in the loop substitutes the name of each of them (which is the name of the database) in the parameter string mysqldump.
The resulting dump is immediately archived using 7-zip in gzip format (so that the resulting file can be fed to the muscle without unpacking). Well, eachfile utility will remove outdated backups.
In the process of googling, I also came across the MySQL Backup Tool program to test which, however, I did not dare.
There is a server with mysql running windows server 2008 R2, on which, among other things, mysql runs with several dozens of databases, the number and composition of which periodically change. It is necessary to organize a daily backup of these databases without stopping the mysql server, and in such a way that each database falls into a separate archive. This, at first glance, the simplest task (perhaps the way it is) for me was quite difficult.
What does Google tell us?
The fact that there is mysqlhotcopy and mysqldump. The first works directly with the database files, the second - makes dumps using queries.
I couldn’t get mysqlhotcopy to work even with the simplest parameters, and when I did, I came to the conclusion (correct me if I am mistaken) that this script is not adapted for windows.
')
I started it with mysqlhotcopy, because it is easier to work directly with files in my case - I just slipped the files into my muscle and work. The dump, though weighs less due to the lack of indexes, still needs to be imported. But, since I did not find another way without stopping mysql , we will do dumps.
Here I found an excellent guide to script keys. So, he can make a general dump of all the bases into one file (extremely inconvenient), or make dumps of the listed bases. Since the number and the names of the bases change with us, then writing a list is not an option - every time we rule it out. So you need a script that will substitute in turn each database in mysqldump.
I implemented it this way - the batch file looks at which folders are in the mysql data directory and in the loop substitutes the name of each of them (which is the name of the database) in the parameter string mysqldump.
SET SOURCEDIR=E:\xampp\mysql\data\ set hour=%TIME:~0,2% set minute=%TIME:~3,2% set second=%TIME:~6,2% set HHMMSS=%hour%-%minute% for /d %%i in (%SOURCEDIR%\*) do "E:\xampp\mysql\bin\mysqldump.exe" -uusername -hlocalhost -ppassword -c -n %%~ni | "c:\Program Files\7-Zip\7z.exe" a -tgzip -si"%%~ni_%DATE%_%HHMMSS%.sql" "D:\backups\data\%DATE%_%HHMMSS%\%%~ni.sql.gzip" eachfile.exe -purge -r -w -e -d 13 -l 0 -dir D:\backups\data\ exit The resulting dump is immediately archived using 7-zip in gzip format (so that the resulting file can be fed to the muscle without unpacking). Well, eachfile utility will remove outdated backups.
In the process of googling, I also came across the MySQL Backup Tool program to test which, however, I did not dare.
Source: https://habr.com/ru/post/205964/
All Articles