How to backup every table from a database to separate file by using mysqldump in mysql or mairadb

How to backup all the tables form a database to separete files in mysql or mairadb by using mysqldump


The below script can be used to backup all the tables from a database in mysql or mairadb. The script will create a new directory "DatabaseName_Date_T_Time" everytime you will run. Inside the directory single file will be created for each of the table.

# Provide the user name that you want to use for backups
MYSQL_USER="backupUserName"
#Provide the Password for the backup user
MYSQL_PASS="Password for BackupUserName"
#Provide DBName from which you want to backup all tables
MYSQL_DB="YourDatabaseName"
#Provide the backup directory path in which you would like to create new direcoty and backup tables from a Database. In my case I have directory /tmp
BACKUP_DIR=/tmp/$MYSQL_DB"_"$(date +%Y-%m-%dT%H_%M_%S);
#Check and create new directory if not exists
test -d "$BACKUP_DIR" || mkdir -p "$BACKUP_DIR"
# Get the Table List for the database you have provide above
for tb in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS  -e 'select table_name from information_Schema.tables where table_schema="'""$MYSQL_DB""'";')
do
  # backup each table in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS "$MYSQL_DB" "$tb" --skip-lock-tables> "$BACKUP_DIR/$tb.sql"
done

How to take a backup of single table in MySQL or MairaDB by using mysqldump

How to backup single table in MariaDB by using  mysqldump

Often as DBAs have to run delete, update or insert statements on a table, it is always good idea to take the backup of a table before running any DML statements. Below syntax can be used to take the backup of a table in Mysql or MairaDB. mysqldump will  create the file which will have drop table, create table and then insert statements for each line. If your table is huge, it can take long time to take the backup and also restore can be lengthy process.

Syntax:

mysqldump --user=username --password --lock-tables DatabaseName TableName > /tmp/FileName.sql

Example:

If I have a database name TechBrothers and one of the table is Emp that I need to take backup in tmp direcotry, I can use below script.
mysqldump --user=root --password --lock-tables DBA Emp > /tmp/Emp.sql
In above example, I have used root user, you can use the any user which has permission to take backup. you will be prompted for password after running above script.

How to see create table definition of a table in mySQL or mairaDB

Sometime you need to see the definition of table in Mysql or mairadb to understand the data types which the table is using for different columns to store the data OR you might need to create a new table exactly like the one you have already created, To see the definition of table in mairadb or mysql, you can use below statement.

MySQL > show create table TableName;

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



Work Items Overview in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. Work Items available in TFS 2015? 2. Different types of Team project template overview in TFS 2015? 3. What are the work item types available in Scrum Template of TFS 2015? 4. What are the work Item types available in CMMI Template of TFS 2015? 5. What are the work item types available in Agile Template of TFS 2015? 6. How to view work items in TFS 2015 using Visual Studio 2015?



How to configure Team Project Collection Report Folder in TFS 2015 - TFS 2015 Tutorial

In this video we will learn following: 1. How to configure Team Project Collection Report Folder in TFS 2015? 2. How to check report folder configuration using TFS 2015 Administration Console? 3. Prerequisites to configure Report Folder in TFS 2015? 4. Step by step Configuration of Report Folder in TFS 2015? 5. How to check Reports using Reporting Server URL in TFS 2015?



How to Configure a SharePoint Website to use as Team Project Portal in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to Configure a SharePoint Website as Team Project Portal in TFS 2015? 2. What are the prerequisites to configure SharePoint Team Project Portal in TFS 2015? 3. How to create a portal site on SharePoint Server to be used as Team Project Portal in TFS 2015? 4. What are the permission required to configure Team Project Portal in TFS 2015? 5. How to use Team portal using Visual Studio 2015?



How to Configure Site Collection for the Team Project Collection in TFS 2015-TFS 2015 Tutorial

In this video we will learn following: 1. How to create site collection site to be used in TFS 2015 Web Portal? 2. Purpose of creating site collections in TFS 2015? 3. Site collection on Sharepoint Server? 4. Permissions required to configure Site collection for TFS 2015?




How to Compare Source Code in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to compare source code in TFS 2015? 2. How to compare Code between TFS 2015 server and Local repository (Work space) 3. How to Compare code between two version of work space in Visual Studio 2015? 4. Different filters of code compare overview 5. Option to Synch code between source and destination files



How to get latest version in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video you are going to learn how to get latest version in Team Foundation Server 2015.

How to add New File or Project into TFS 2015 Source Control - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to create new project in Visual Studio 2015? 2. How to add new Project to the TFS 2015 source control? 3. Different ways to add the code to source control in TFS 2015 4. Prerequisites to add the file or code to TFS 2015 Source Control



How to Checkin and Checkout Projects in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to Check in and Check out code in TFS 2015? 2. What is local repository in TFS 2015? 3. What is work space in TFS 2015? 4. How to check if code is checked out or checked in TFS 2015?



How to configure work space in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. What is work space in TFS 2015? 2. How to configure Work Space in TFS 2015 using Visual Studio 2015? 3. What is local repository in TFS 2015? 4. Requirement to Make changes to your code in TFS 2015?




How to create Relational Branch in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. What is relational Branch in TFS 2015? 2. How to create relational branch in TFS 2015? 3. Why do we create relational branch in TFS 2015? 4. What are the advantages of creating relational branch in TFS 2015? 5. How to view the relational branch properties? 6. What is Parent-Child relationship in relational branches ?



How to create a branch in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following: 1. How to create Folder in TFS 2015 using Visual studio 2015? 2. How to convert Folder into Branch in TFS 2015 using VS 2015? 3. How to check-in changes in TFS 2015 using Visual Studio 2015?



How to create Team Project in TFS 2015 - Team Foundation Server 2015 Tutorial

In this video we will learn following:
1. How to create Team project in TFS 2015 using Visual Studio 2015?
2. How to connect to Team Project Collection using Visual Studio 2015?
3. Permissions required to create a Team Project in TFS 2015



How to create Team project collection in TFS 2015 - TFS 2015 Tutorial

In this video we will learn following:
1. How to create Team Project Collection in TFS 2015 using Administration Console?
2. How to open TFS 2015 administration Consol
3. Navigation to the Application Tier
4. Permission required to create Team Project Collection
5. Configuration of Data Tier in TFS 2015
6. Configuration of SharePoint and Reports in TFS 2015 for each Team Project Collection

How to Grant Select Permission on Table/s to User in MariaDB - MariaDB Tutorial

How to Grant Select Permission on Table/s to User in MariaDB provide all the steps from creating a brand new user and then provide select permission on single table or all the tables in a database in MariaDB.




How to install Visual Studio 2015 and connect with TFS 2015 - Team Foundation Server (TFS) 2015 Tutorial

In this video, we will learn following:
1. How to install Visual Studio 2015 Enterprise step by step
2. Installation of Visual Studio prerequisites
3. Configuring Visual studio for right environment
4. Team Viewer in Visual Studio
5. How to connect to Team Foundation Server 2015 using Visual Studio 2015
6. Adding TFS 2015 servers in Team Viewer list


How to Grant Full Permission on a Database to a User in MariaDB

How to Grant Full Permission on a Database to a User in MariaDB explains all the steps how you can create a user in MariaDB and then provide full permission such as create, drop, select etc. to a user account in MariaDB.