Create Zip Backup files for each Database by using mysqldump in MySQL or MairaDB

mysqldump is used to take logical backups of database/databases in MairaDB and Mysql. Below script will create a directory with timestamp and then create zip backup file for each database in MySQL or MairaDB.

# 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 | gzip > "$BACKUP_DIR/$db.sql.gz"
done

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

Take backup of each DB in newly created timestamp directory by using mysqldump for MairaDB /MySQL

mysqldump is used to take logical backups of database/databases in MairaDB and Mysql. In this post we are going to cover the scenario in which you have to write the script that should create a directory by using date time and then take the backup of all the databases in newly created directory. Each database backup will be saved to it own .sql file.


# 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;')
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 perform backup of a database in MairaDB/MySQL by using mysqldump

mysqldump is used to take logical backup of database/s in MairaDB and MySQL. It works great if the database size is small. If your database size is huge, consider taking hot backups.
Let's follow below steps to take the backup of TechBrothers Database.

1) Create the directory for backups

First of all create the directory in which you would like to save MairaDB Database/s backups. I am going to create mysqlbackup direct under root directory.
sudo mkdir /mysqlbackup

2) Provide Full Permission to mysql on backup directory

chown-R mysql:mysql /mysqlbackup

3) Create backupuser in MairaDB or Mysql

Create the user backupuser that we will use to perform backups of MariaDB databases. You can have any name you like for your backup user and provide strong password.
MariaDB [(none)]>CREATE USER 'backupuser'@'localhost' identified BY 'Test123';

4) Provide Permissions to backups user

You have to provide permissions to backup user so it can perform backups for databases. I used below statement to provide required permissions to backup user in MariaDB. You can further refine the permissions for backup user..
MariaDB [(none)]GRANT reload, lock tables, REPLICATION client, 
CREATE TABLE, 
             process, 
             super, 
             CREATE, 
INSERT, 
SELECT 
ON *.* TO 'backupuser'@'localhost';

5) Run mysqldum to perform backup of a database in MariaDB

Let's say I have database called TechBrothers in MairaDB and I would like to perform the backup. I can use below command. Change the below command as per your Database Name, backup user name  , password and backup directory.
mysqldump --user=backupuser --password=Test123 TechBrothers > /mysqlbackup/TechBrothers.sql 

How to Apply and Search Labels in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to Apply and Search Labels in TFS 2015? 2. Why is it important o apply Labels in TFS 2015? 3. Searching using Labels in TFS 2015 4. Limitations in applying labels in TFS 2015? 5. Applying Labels using Visual Studio 2015