How to Backup Mysql

backup mysql

In the current post, I will explain about how to backup mysql with mysql dump. I will create a backup script that will run every day. The first step you should know the password for the mysql user that will be backup. We will back up with a simple command like the following.

¬†¬† mysqldump -u root -pyourpassword yourdatabase --routines > /mnt/backup/backup-mysql `date +’%Y%m%d’`.sql


-u : Option -u for database username  (root is my database username)
-pyourpassword : Please fill for password database(example: -pthismypassword)
yourdatabase :Please fill in the name of the database you want to backup
–routines : This option includes procedures and functions on the database
/mnt/backup/backup-mysql : Destination directory
`date +’%Y%m%d’` : File output with time stamp (example:backup-mysql 20180630.sql)
.sql : name of extention file

the next step is to create a script file and add the command as above, please use any existing text editor like vi, vim or nano. example nano , I will add a mailsend command on the script, mailsend is used to send emails automatically. Please visit this post to see how to install mailsend.


export $PATH;

###command for sent email#######
echo "Database Backup will start `date`" |mailsend -smtp -d -t -f -sub "Backup mysql database `date`";

name="/mnt/backup/backup-mysql`date +'%Y%m%d'`.sql"
mysqldump -u root -pkomponen dbmobile --routines > /mnt/backup/backupdb-dms`date +'%Y%m%d'`.sql
compressed="/mnt/backup/backup-mysql`date +'%Y%m%d'`.tar.gz"
tar cfz $compressed $name;

the next step add on crontab as needed

30 23 * * * /mnt/backup/ > /mnt/backup/backupmysql.log 2>&1

The script will run at 23:30 or 11:30 PM

Facebook Comments