Dump all banks for a specific user

I noticed that you used the mysqldump tag. Since you have not informed the operating system you are using, I will assume it is Linux - which is the most common - if not, you can use the content of that response to adapt to your environment. You will need to login with a user and password using the mysqldump tool 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'm not going to advise you to do this. But if you have multiple banks to be backed up and this is properly separated into their respective users, using the root credentials will allow you to run the script once for all banks. As you mentioned that the 54 banks belong to plant652 user, I recommend that you run as such. Remembering, of course, that for this you will need the password.

I do not know of any magic commands to back up 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, let me explain. So that the user and password are not stored in the shell history (and do not need to use hacks like putting space in front of the command, which is no guarantee of anything or other techniques out of standard or even be counting on memory to delete the% I have done what every time the script is executed, it asks the user and the password to be used (in this case I opt for bash_history that will see all the schemas ) and defines the location where backups will be saved, in this case root .

It creates the folders and organizes the backup by YYYY-MM-DD, compact with the tool gunzip and then creates the largest files (which in my case is important) separated by year to be able to do a simple year-round download. It overwrites the files, does not optimize the use of space, and commits some other sins that are not relevant to me.

At the end of the script you can see that it discards the values of the variables storing the user and password in an attempt to keep it as safe as possible within the simple scope of the script.

If you do not want to type this every time, your only solution is probably to store plain text inside the script and save it to disk. But we can all imagine that this idea is not very cool ...

DISCLAIMER: USE AT YOUR OWN RISK .

It's worth mentioning the most important part of it 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 has its visibility affected by the user whose credentials are being used by the tool), then loop and delete the possible schemas that you do not want to include in the backup, most notably the protected schemas or MySQL defaults. From this final list it then makes a /backups/mysql for each schema in> and save / compact as explained above.

Important to remember: The mysqldump user that I am referring to in this response at no time is root operating system , but < database . I found it important to point this out to internet visitors who may come here in the future.