Dump from all banks to a specific user

Question

I have user plant652 who has 54 databases and need to back up all of them. How to do them all at once in Centos 6 with cPanel?

Answer 1

I noticed you used the tag mysqldump. Since you have not informed the operating system you are using, I will assume that it is Linux - which is the most common - if it is not, you can use the content of that response to adapt to your environment. You will need to log in with a user and password by the 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’m not gonna advise you to do that. But if there are several banks to be backed up and this is properly separated in their respective users, use the credentials of root will allow you to run the script once for all banks. As mentioned that the 54 banks belong to the user plant652, recommend to run as such. Remembering, of course, that for this 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 it. 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 non-standard techniques or even rely on the memory to delete the bash_history) I did what every time the script was executed, it asks the user and password to be used (in this case I choose root that will see all schemas) and sets the location where backups will be saved, in this case /backups/mysql.

It creates the folders and organizes the backup by YYYY-MM-DD, compact with the tool gunzip and then create the larger Rchives (which in my case is important) separated by 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 it in 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 highlighting 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 he takes the names of all the schemas (of course, have their visibility affected by the user whose credentials are being used by the tool), then loop and delete the possible schemas that you don’t want to include in the backup, most notably the schemas protected or Mysql defaults. From this final list it then makes a mysqldump for each schema and save/compact as explained above.

Important to remember: The user root to which I refer in that reply at no time is the root of operating system, rather that of the database. I found it important to highlight this for internet visitors who may pass through here in the future.