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;