I have multiple MySQL databases on WIndows Server and I would like to backup all these databases by creating a folder with the name of the database and inside the .SQL file folder for each table of the concerned database. The idea is to make it easier to restore information because sometimes I just need to restore a table instead of the whole database.
I've come up with some research on this and found a shell script that does just that. This script creates a folder for each database on the server and inside it creates a .SQL file for each table, then it compacts all those folders into a single .ZIP file and still sends it to an FTP server (this is not necessary for me).
Below is the shell script I found.
#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------
#########################
######TO BE MODIFIED#####
### System Setup ###
BACKUP=YOUR_LOCAL_BACKUP_DIR
### MySQL Setup ###
MUSER="MYSQL_USER"
MPASS="MYSQL_USER_PASSWORD"
MHOST="localhost"
### FTP server Setup ###
FTPD="YOUR_FTP_BACKUP_DIR"
FTPU="YOUR_FTP_USER"
FTPP="YOUR_FTP_USER_PASSWORD"
FTPS="YOUR_FTP_SERVER_ADDRESS"
######DO NOT MAKE MODIFICATION BELOW#####
#########################################
### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
FTP="$(which ftp)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
### Today + hour in 24h format ###
NOW=$(date +"%d%H")
### Create hourly dir ###
mkdir $BACKUP/$NOW
### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
### Create dir for each databases, backup tables in individual files ###
mkdir $BACKUP/$NOW/$db
for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
do
FILE=$BACKUP/$NOW/$db/$i.sql.gz
echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
done
done
### Compress all tables in one nice file to upload ###
ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW
$TAR -cvf $ARCHIVE $ARCHIVED
### Dump backup using FTP ###
cd $BACKUP
DUMPFILE=$NOW.tar.gz
$FTP -n $FTPS <<END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
cd $FTPD
mput $DUMPFILE
quit
END_SCRIPT
### Delete the backup dir and keep archive ###
rm -rf $ARCHIVED
Finally, I could use this script, however I have no Linux machine here and I can not create any VM with Linux either. I tried to convert this script to a batch file (.bat) but I did not succeed because I'm not very knowledgeable about advanced scripting.
Is there a solution for Windows that meets my need? Or is it possible to convert this script to work in Windows?
Thank you in advance.
If your requirement is only to export data tablewise for all the databases in your database instance, following script will do the magic for you and it will work in windows. The code snippet you have mentioned in question does lot more the your requirement like sending to FTP/zipping and what not. It doen't seems your requirement, but you could achieve that as well by modifying the basic short script, I'm providing here.
Logic is simple.
Make all the variables declaration appropriately.
##Export all databases names to file
mysql -h your-host -u username -pPassword your-database-name --skip-column-names --execute="SHOW DATABASES;" > databases.tmp
echo "Start!"
#### Look over database names
#### Outter loop to get table names
for /F %%A in (databases.tmp) do (
echo "Exporting table Names for %%A"
mysql -h your-host -u username -pPassword %%A --skip-column-names --execute="SHOW TABLES;" >"%%A_tables.tmp"
mkdir %%A
#### nested loop to create table name files per table
for /F %%B in (%%A_tables.tmp) do (
echo "Exporting data for %%B of %%A"
mysqldump -h your-host -u username -pPassword %%A %%B> "%%A\%%A_%%B.sql"
)
)
del *.tmp
Hope works for you.