MySql backup and Truncate the table

Question

How can I take the mysql database backup as csv file and truncate the table after that on regular basis. how can I achieve this using mysql only.

Any type of help will be appreciated.

Answer 1

Instead of using csv files it's better to backup tables into .sql files, which again helps you while importing into database.

You can use bash script and cron jobs for regular backups if you are a Linux user.

Sample script :

#!/bin/bash
_user="root"
_password="YourMySQLRootPassword"

mysqldump -u${_user} -p${_password} DB_Name TableName > Table_name_`date +%Y%m%d`.sql  # here date command is executed for present date
mysql -u${_user} -p{_password} -e "truncate table DB_Name.TableName"

Before you can truncate the table you can check if the dump command was successful then execute table Truncation using "if" conditions. For quick introduction you can check it out this link https://www.youtube.com/watch?v=hwrnmQumtPw