How to schedule backup Job for MariaDB or MySQL by using crontab

Scenario:

you are working as MySQL or MariaDB admin and you need to schedule daily backups for a database.How will you schedule backup for MariaDB or MySQL database?

Solution:

Crontab can be used to schedule the backups for MySQL or MariaDB , Crontab entry consist of six below fields.

minute(s) hour(s) day(s) month(s) weekday(s) command(s)

Field Value Description
minute 0-59
The exact minute that the command sequence executes
hour 0-23
The hour of the day that the command sequence executes
day 31-Jan
The day of the month that the command sequence executes
month 12-Jan
The month of the year that the command sequence executes
weekday 0-6
The day of the week that the command sequence executes (Sunday = 0, Monday = 1, Tuesday = 2, and so forth)
Command special Provide the Command you like to execute

Let's make changes to our crontab file to run backup every minutes, if you like to run backup every minute, you will be providing * * * * * for all the time fields 

open crontab file by sudo vim /etc/crontab
* * * * * root mysqldump -u [User]-p[Password] DBName> /BackupDirectory/DatabaseName_`date +\%m_\%d_\%YT\%H_\%M_\%S`.sql

Let's say if I am taking backup of abc database to mysqlbackup directory every minute, I will be using below command.

* * * * * root mysqldump -u root -pDBA321 abc > /mysqlbackup/abc_`date +\%m_\%d_\%YT\%H_\%M_\%S`.sql
Video Demo : How to schedule backup for MySQL database by using Crontab job

2 comments: