How to take backup of specific databases in MySQL or MariaDB

mysqldump is used to take logical backups of database/databases in MairaDB and Mysql.If you would like to exclude some of the databases you can change the query "Select distinct Table_schema from information_Schema.tables" according to your requirement. Let's say if I only want to take the backup of databases which start with Tech%, I can use below script.


# Provide the user name that you want to use for backups
MYSQL_USER="backupuser"
#Provide the Password for the backup user
MYSQL_PASS="Test123"
#Provide the backup directory path in which you would like to create new direcoty and backup databases. In my case I have directory /mysqlbackup
BACKUP_DIR=/mysqlbackup/$(date +%Y-%m-%dT%H_%M_%S);
#Check and create new directory if not exits
test -d "$BACKUP_DIR" || mkdir -p "$BACKUP_DIR"
# Get the database list
for db in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS  -e 'Select distinct Table_schema from information_Schema.tables where Table_schema like "Tech%";')
do
  # backup each database in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS "$db" --skip-lock-tables> "$BACKUP_DIR/$db.sql"
done
How to take backup of some database in MariaDB or MySQL

1 comment: