Linux shell script for database backup

Question

I tried many scripts for database backup but I couldn t make it  I want to backup my database every hour  I added files to   etc cron hourly   folder  changed its chmod to 755  but it didn t run  At least I write my pseudo code   I would be happy if you can write a script for this operation and tell me what should I do more   After adding this script file to  etc cron hourly  folder    Get current date and create a variable  date date d m y H M S  Create a variable for the file name  filename   date  gz Get the dump of my database like this mysqldump --user my user --password my pass --default-character-set utf8 my database   gzip  gt    var www vhosts system example com httpdocs backups    filename   Delete all files in the folder  var www vhosts system example com httpdocs backups  that are older than 8 days To the file   var www vhosts system example com httpdocs backup log txt   this text will be written  Backup is created at    date   Change the file owners  chown  from root to  my user   Because I want to open the backup and log files from the  my user  FTP account  I don t want an email after each cron   gt  dev null 2 gt  amp 1 will be added

User · Accepted Answer

After hours and hours work  I created a solution like the below  I copy paste for other people that can benefit   First create a script file and give this file executable permission     cd  etc cron daily    touch  etc cron daily dbbackup-daily sh   chmod 755  etc cron daily dbbackup-daily sh   vi  etc cron daily dbbackup-daily sh   Then copy following lines into file with Shift Ins     bin sh now    date    d  m  Y  H  M  S    filename  db backup  now  gz backupfolder   var www vhosts example com httpdocs backups  fullpathbackupfile   backupfolder  filename  logfile   backupfolder  backup log    date    Y  m    txt echo  mysqldump started at   date    d- m- Y  H  M  S     gt  gt    logfile  mysqldump --user mydbuser --password mypass --default-character-set utf8 mydatabase   gzip  gt    fullpathbackupfile  echo  mysqldump finished at   date    d- m- Y  H  M  S     gt  gt    logfile  chown myuser   fullpathbackupfile  chown myuser   logfile  echo  file permission changed   gt  gt    logfile  find   backupfolder  -name db backup   -mtime  8 -exec rm       echo  old files deleted   gt  gt    logfile  echo  operation finished at   date    d- m- Y  H  M  S     gt  gt    logfile  echo                      gt  gt    logfile  exit 0   Edit  If you use InnoDB and backup takes too much time  you can add  single-transaction  argument to prevent locking  So mysqldump line will be like this   mysqldump --user mydbuser --password mypass --default-character-set utf8           --single-transaction mydatabase   gzip  gt    fullpathbackupfile

User · Answer

I got the same issue  But I manage to write a script  Hope this would help       bin bash   Database credentials user  username  password  password  host  localhost  db name  dbname    Other options backup path   DB DB Backup  date   date    d- b- Y     Set default file permissions umask 177   Dump database into SQL file mysqldump --user  user --password  password --host  host  db name  gt  backup path  db name- date sql    Delete files older than 30 days find  backup path   -mtime  30 -exec rm          DB backup log echo -e    date    d- b- y   r    ALERT Database has been Backuped      gt  gt  var log DB Backup log

User · Answer

bin sh  Procedures   For DB Backup  Scheduled at   Every Day 22 00  v path  etc database jobs db backup logfile path  etc database jobs v file name DB Production v cnt 0  MAILTO  abc as in  touch   logfile path kaka db log log    DB Backup mysqldump -uusername -ppassword -h111 111 111 111 ddbname  gt   v path  v file name date   Y- m- d  sql  if        -eq 0     then    v cnt  expr  v cnt   1    mail -s  DB Backup has been done successfully   MAILTO  lt   logfile path db log log  else    mail -s  Alert   kaka DB Backup has been failed   MAILTO  lt   logfile path db log log    exit fi

User · Answer

As a DBA  You must schedule the backup of MySQL Database in case of any issues so that you can recover your databases from the current backup   Here  we are using mysqldump to take the backup of mysql databases and the same you can put into the script    orahow oradbdb DB Backup   cat  backup script sh     bin bash   Database credentials user  root  password  1Loginxx  db name  orahowdb  v cnt 0 logfile path  DB Backup touch   logfile path orahowdb backup log    Other options backup path   DB Backup  date   date    d- b- Y- H- M- p     Set default file permissions   Continue Reading      MySQL Backup

User · Answer

bin bash    Add your backup dir location  password  mysql location and mysqldump        location DATE   date   d- m- Y  BACKUP DIR   var www back  MYSQL USER  root  MYSQL PASSWORD    MYSQL   usr bin mysql  MYSQLDUMP   usr bin mysqldump  DB  demo    to empty the backup directory and delete all previous backups rm -r  BACKUP DIR      mysqldump -u root -p   demo   gzip -9  gt   BACKUP DIR demo date format sql  DATE gz   changing permissions of directory  chmod -R 777  BACKUP DIR

User · Answer

I have prepared a Shell Script to create a Backup of MYSQL database  You can use it so that we have backup of our database s           bin bash     export PATH  bin  usr bin  usr local bin     TODAY  date    d b Y  I  M  S p                                                                                                Update below values                               DB BACKUP PATH   backup dbbackup      MYSQL HOST  localhost      MYSQL PORT  3306      MYSQL USER  auriga      MYSQL PASSWORD  auriga 123      DATABASE NAME   Project O2 o2      BACKUP RETAIN DAYS 30      Number of days to keep local backup copy  Enable script code in end of th script                                                                              mkdir -p   DB BACKUP PATH    TODAY          echo                                     TODAY    gt  gt    DB BACKUP PATH  Backup-Report txt                    echo  Can not make Directry          echo  Possibly Path is wrong              if   mysql -u   MYSQL USER  -p  MYSQL PASSWORD  -e  exit   then         echo  Failed  You may have Incorrect PASSWORD USER    gt  gt    DB BACKUP PATH  Backup-Report txt         exit 1     fi          for DB in    DATABASE NAME       do             if   mysql -u   MYSQL USER  -p  MYSQL PASSWORD  -e  use    DB   then                 echo  Failed  Database   DB  Not Found on   TODAY    gt  gt    DB BACKUP PATH  Backup-Report txt              else                   echo  Backup started for database -   DB                                 mysqldump -h localhost -P 3306 -u auriga -pauriga 123 Project O2        use gzip                    mysqldump -h   MYSQL HOST  -P   MYSQL PORT  -u   MYSQL USER  -p  MYSQL PASSWORD                              --databases   DB    gzip  gt    DB BACKUP PATH    TODAY    DB -  TODAY  sql gz                  if      -eq 0    then                     touch   DB BACKUP PATH  Backup-Report txt                     echo  successfully backed-up of   DB  on   TODAY    gt  gt    DB BACKUP PATH  Backup-Report txt                       echo  Database backup successfully completed                   else                     touch   DB BACKUP PATH  Backup-Report txt                     echo  Failed to backup of   DB  on   TODAY    gt  gt    DB BACKUP PATH  Backup-Report txt                       echo  Error found during backup                      exit 1                 fi             fi         done                    echo  Failed during backup          echo  Failed to backup on   TODAY    gt  gt    DB BACKUP PATH  Backup-Report txt             myshellsc sh 2 gt    DB BACKUP PATH  Backup-Report txt                  Remove backups older than  BACKUP RETAIN DAYS  days               DBDELDATE  date    d b Y  --date    BACKUP RETAIN DAYS  days ago          if     -z   DB BACKUP PATH     then             cd   DB BACKUP PATH              if     -z   DBDELDATE     amp  amp    -d   DBDELDATE     then                   rm -rf   DBDELDATE              fi       fi          End of script        In the script we just need to give our Username  Password  Name of Database or Databases if more than one  also Port number if Different   To Run the script use Command as   sudo   script sc   I also Suggest that if You want to see the Result in a file Like  Failure Occurs or Successful in backing-up  then Use the Command as Below   sudo   myshellsc sh 2 gt  gt  Backup-Report log   Thank You

User · Answer

Now  copy the following content in a script file  like   backup mysql-backup sh  and save on your Linux system          bin bash      export PATH  bin  usr bin  usr local bin     TODAY  date    d b Y        DB BACKUP PATH   backup dbbackup      MYSQL HOST  localhost      MYSQL PORT  3306      MYSQL USER  root      MYSQL PASSWORD  mysecret      DATABASE NAME  mydb      BACKUP RETAIN DAYS 30         mkdir -p   DB BACKUP PATH    TODAY      echo  Backup started for database -   DATABASE NAME        mysqldump -h   MYSQL HOST       -P   MYSQL PORT       -u   MYSQL USER       -p  MYSQL PASSWORD         DATABASE NAME    gzip  gt    DB BACKUP PATH    TODAY    DATABASE NAME -  TODAY  sql gz  if      -eq 0    then   echo  Database backup successfully completed  else   echo  Error found during backup    exit 1 fi         Remove backups older than  BACKUP RETAIN DAYS  days         DBDELDATE  date    d b Y  --date    BACKUP RETAIN DAYS  days ago    if     -z   DB BACKUP PATH     then       cd   DB BACKUP PATH        if     -z   DBDELDATE     amp  amp    -d   DBDELDATE     then             rm -rf   DBDELDATE        fi fi   After creating or downloading script make sure to set execute permission to run properly     chmod  x  backup mysql-backup sh   Edit crontab on your system with crontab -e command  Add following settings to enable backup at 3 in the morning   0 3       root  backup mysql-backup sh

User · Answer

Create a script similar to this      bin sh -e  location    date   Y m d  H M S  db  mysqldump -u root --password  lt your password gt  database name  gt   location  gzip  location   Then you can edit the crontab of the user that the script is going to run as     gt  crontab -e   And append the entry  01           script path sh   This will make it run on the first minute of every hour every day   Then you just have to add in your rolls and other functionality and you are good to go

User · Answer

Here is my mysql backup script for ubuntu in case it helps someone    Mysql back up script  start time    date -u   s    now    date   d- B- Y  H- M- S    ip     sbin ifconfig eth0 2 gt  dev null awk   inet addr    print  2   sed  s addr        filename   now   zip backupfolder  path to any folder fullpathbackupfile  backupfolder  filename db user xxx db password xxx db name xxx  printf   n n  printf                                 n  printf  Started Automatic Mysql Backup n  printf                                 n  printf  TIME   now  n  printf  IP ADDRESS   ip   n  printf  DB SERVER NAME  DB-SERVER-1 n   printf   sBACKUP FILE PATH  fullpathbackupfile n   printf  Starting Mysql Dump  n   mysqldump -u  db user -p db password  db name  pv   zip  gt   fullpathbackupfile  end time    date -u   s    elapsed     end time- start time    printf   sMysql Dump Completed In  elapsed seconds n   printf                                 n    PS  Rememember to install pv and zip in your ubuntu  sudo apt install pv sudo apt install zip   Here is how I set crontab by using crontab -e in ubuntu to run every 6 hours  0   6       sh  path to shfile backup-mysql sh  gt  gt   path to logs backup-mysql log 2 gt  amp 1   Cool thing is it will create a zip file which is easier to unzip from anywhere

User · Answer

You might consider this Open Source tool  matiri  https   github com AAFC-MBB matiri which is a concurrent mysql backup script with metadata in Sqlite3  Features    Multi-Server  Multiple MySQL servers are supported whether they are co-located on the same or separate physical servers  Parallel  Each database on the server to be backed up is done separately  in parallel  concurrency settable  default  3  Compressed  Each database backup compressed Checksummed  SHA256 of each compressed backup file stored and the archive of all files Archived  All database backups tar ed together into single file Recorded  Backup information stored in Sqlite3 database   Full disclosure  original matiri author

[mysql] Linux shell script for database backup

Examples related to mysql

Examples related to linux

Examples related to shell

Examples related to cron