MySQL Tutorial - Backup each table to separate file for MySQL Databases by using mysqldump

Scenario:

You are working as MySQL DBA, you need to write the script that should create a new directory each time you run the script , the directory name should be "Database_TimeStamp" and then take backup of each of table from a database to this directory as separate file. 

Solution:

The below script can be used to take backup of all the tables from a databases in separate file for each of the table by using mysqldump.

# Change the database name 
MYSQL_DB="techbrotheritnew"
#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=/mysqlbackup/$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 --user='backupuser' --password='MyPassword$123'  -e 'select table_name from information_Schema.tables where table_schema="'""$MYSQL_DB""'";')
do
echo "Taking backup of Table : "$tb
  # backup each table in a separate file
  mysqldump --user='backupuser' --password='MyPassword$123' "$MYSQL_DB" "$tb" --skip-lock-tables> "$BACKUP_DIR/$tb.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.
backup every table from MySQL Database to separate files by using mysqldump


MySQL DBA Tutorial - Backup each table to separate file from MySQL Database by using mysqldump

6 comments:

  1. thankyou for sharing all details about backup of each table to separate file for mysql database.
    regards
    eagleapk

    ReplyDelete
  2. Suppose you are searching for a fixed blade knife to use for household works and many other outdoor activities like hunting, agriculture, and other activities. visit website to read full article of best condor bushcraft knife

    ReplyDelete
  3. If you are looking for the best medical laboratory then labuncle is always there. Get laboratory tests done easily with our Home Sample Collection. If you are looking for a lab that will meet your needs with best customer service this is the place to go. Fully satisfied with their results and all the services total diagnostic kirti nagar. Definitely recommended for everyone.

    ReplyDelete
  4. The respect for the value of this natural resource continues as Bassett BED SALE artisans craft the lumber into beautiful sustainable furniture. The varied grain patterns, range of color and natural markings seen in BenchMade products tell the story of the tree's life.

    ReplyDelete