mysql – Dump all banks for a specific user

Question:

I have the plant652 user who has 54 databases and I need to backup them all. How to do all at once on CentOS 6 with cPanel?

Answer:

I noticed that you used the mysqldump tag. Since you haven't informed which operating system you're using, I'll assume it's Linux – which is the most common – if not, you can use the contents of this answer to adapt to your environment. You will need to login with a username and password through the mysqldump tool if you want to do any operations.

It is not ideal to run any scripts that a stranger on the internet wrote as root . 🙂
So I'm not going to advise you to do that. But if you have several banks to be backed up and this is properly separated into their respective users, using root credentials will allow you to run the script once for all banks. As you mentioned that the 54 banks belong to the plant652 user, I recommend that you run it as such. Remembering, of course, that you will need the password for this.

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 mine) 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 explain. So that the username and password are not stored in the shell history (and not need to use hacks like putting space in front of the command, which is no guarantee of anything or other non-standard techniques or even relying on memory to erase bash_history ) I did so that every time the script was executed, it asks for the username and password to be used (in this case I choose root which will see all the schemas ) and defines the location where the backups will be saved, in this case /backups/mysql .

It creates the folders and organizes the backup by YYYY-MM-DD, compresses it with the gunzip tool and then creates the larger archives (which in my case is important) separated by year in order to make a simple download for the whole year. It overwrites files, doesn't optimize space usage and commits some other possible sins that aren't relevant to me.

At the end of the script you can see that it discards the values ​​of the variables that stored the username and password, in an attempt to keep this as secure as possible within the simple scope of the script.

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

DISCLAIMER: USE AT YOUR OWN RISK .

It is worth mentioning the most important part of all this:

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 it takes the name of all schemas (which of course have their visibility affected by the user whose credentials are being used by the tool), then loops and excludes any schemas you don't want to include in the backup , most notably the schemas MySQL protected or defaults. From this final list it then does a mysqldump for each schema and saves/compresses as explained earlier.

Important to remember: The root user I refer to in this answer at no time is the root of the operating system , but the root of the database . I thought it was important to point this out to internet visitors who may come by in the future.

Scroll to Top