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

note:

-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 backup-mysql.sh , 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.


#!/bin/bash

PATH==/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export $PATH;

###command for sent email#######
echo "Database Backup will start `date`" |mailsend -smtp 10.14.99.206 -d yourmailserver.jagolinux.com -t maildestination@jagolinux.com -f mailsender@jagolinux.com -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/backupmysql.sh > /mnt/backup/backupmysql.log 2>&1

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

Facebook Comments