Z
I noticed you used the tag mysqldump. Since you have not informed the operating system you are using, I will assume it is Linux - that is the most common - If it isn't, you can use the content of this response to adapt to your environment. You will need to log in with a user and password by the tool mysqldump if you want to do any operation.It is not ideal to run any script that a stranger on the internet wrote as root. So I won't advise you to do that. But if you have multiple banks to be backed up and this is properly separated in your respective users, use the credentials of root will allow you to run the script once for all banks. How did you mention that the 54 banks belong to the user plant652, recommend that you perform as such. Remember, of course, that for that you will need the password.I don't know of any magic command to backup all MySQL schemas by default.For example, I created this script to solve your (and my) problem:#!/bin/bash
read -p "Input the desired MySQL user for backup: " USER
export USER=${USER}
if [ -z "${USER}" ];
then
exit
fi
read -p "Input specified user password: " PASSWORD
export PASSWORD=${PASSWORD}
if [ -z "${PASSWORD}" ];
then
exit
fi
export MYSQL_BACKUP_DIR="/backups/mysql"
mkdir -p ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d
#rm "$MYSQL_BACKUP_DIR/*gz" > /dev/null 2>&1
databases=mysql -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
mysqldump -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD --databases $db > ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql
gzip ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql
tar -cvf ${MYSQL_BACKUP_DIR}/date +%Y.tar ${MYSQL_BACKUP_DIR}/date +%Y
fi
done
unset PASSWORD;
unset USER;
Now, I'll explain. So that the user and password are not stored in the shell history (and don't need to use hacks like putting space in front of the command, which is no guarantee of anything or other techniques out of default or even relying on the memory in deleting the bash_history) did what every time the script was executed, it prompts the user and the password to be used (in case I opt for the root that will see all schemas) and defines the location where backups will be saved, in this case /backups/mysql.It creates folders and organizes backup by YYY-MM-DD, compact with the tool gunzip and then create the largest archives (which in my case is important) separated per year to be able to make a simple download of the whole year. It overwrites the files, does not optimize the use of space and commits some other possible sins that are not relevant to me.At the end of the script you can see that it discards the values of the variables that stored the user and password, in an attempt to keep this as safe as possible within the simple scope of the script.If you don't want to type this every time, probably your only solution is to store in plain text within the script and save it on the disk. But we can all imagine that this idea is not very legal..._.It is worth mentioning the most important part of this all:databases=mysql -h 127.0.0.1 -P63306 -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
mysqldump -h 127.0.0.1 -P3306 -u $USER -p$PASSWORD --databases $db > ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql
gzip ${MYSQL_BACKUP_DIR}/date +%Y/%m/%d/$db-date +%Y-%m-%d.sql
tar -cvf ${MYSQL_BACKUP_DIR}/date +%Y.tar ${MYSQL_BACKUP_DIR}/date +%Y
fi
done
Here he takes the name of all schemas (which of course have its user-affected visibility whose credentials are being used by the tool), then loops and deletes the possible schemas that you do not want to include in the backup, most notably the schemas protected or MySQL standards. From that final list he then makes a mysqldump for each schema and save/package as explained earlier.Important remember: The user root what I mean in this answer at no time is root the operating system, and yes database. I found it important to highlight this for internet visitors who can go through here in the future.
