How to backup of specific databases from MySQL Server by using mysqldump - MySQL DBA Tutorial

Scenario:

You are working as MySQL DBA, you have a lot of databases on MySQL Server, but you need to take backup of only databases those start with "Tech", each time you run the backup script , it should create a timestamp directory and then take backup of only databases which starts with "Tech". How would you perform backups for given scenario by using mysqldump?

Solution:

You can use below script to filter the databases for which you would like to run backup. 

#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 --user='backupuser' --password='MyPassword$123' -e 'Select distinct Table_schema from information_Schema.tables where Table_schema like "tech%";')
do
echo "Performing backup of Database : "$db
  # backup each database in a separate file
  mysqldump --user='backupuser' --password='MyPassword$123' "$db" --skip-lock-tables> "$BACKUP_DIR/$db.sql"
done

Items to change in above script according to your environment

• Replace "backupuser" to your user name that you are using to run the mysqldump
• Replace "MyPassword$123" password with password for your user.
• Replace "Tech" with your filter requirements in "Select distinct Table_schema from information_Schema.tables where Table_schema like "tech%"" query.

Once you will run above script , a new directory will be created as shown below and you will see the backups in that directory.

Backup specific databases from MySQL Server by using mysqldump

MySQL Training for Beginners - Backup specific database by using mysqldump