MySQL Backup Script in Bash

mysql auto backup script linux
mysql backup script ubuntu
mysql dump
mysql backup all databases script
mariadb backup bash script
automate mysql backup
mysql backup tools
mysql backup options

REF http://www.rsnapshot.org/howto/1.2/rsnapshot-HOWTO.en.html 4.3.9. backup_script

I need to backup ALL the mysql databases by dynamically should new ones be created. Is there an ideal way to do this in bash with minimal code?

Would I need to log in to mysql and get all the databases?

The mysqldump command has an --all-databases option to back up every single database in one pass.

The only down-side to this is you have to restore them all together, you don't have the luxury of picking and choosing.

Keep in mind that databases usually have an associated directory in your MySQL data directory, so you can always iterate through those to find out which databases exist.

A Simple Bash Script for MySQL Database Backup – TecAdmin, Step 2 – Configure Setup. Edit settings.conf file and update all required values as per your requirements. As a DBA, you must schedule the crontab script to take the backup of MySQL databases. It is important to configure backup of your MySQL databases either running on Linux or Windows so that you can recover your database in case of any problems occur. MYSQLDUMP is the command using which you can take the backup of mysql databases.

Seems that you are wanting a bash script to run backup on dynamic databases that are created in MySQL. You can add the mysql root user account information in my.cnf in the root directory or within the bash script under the # [ Define Variables ].

you will need to chmod the bash script with

$sudo chmod +x backupmysql.sh

This will allow you to run the script with the following command.

$sudo ./backupmysql.sh

You can name the script whatever you like. In this example, I named it backupmysql.sh.

Here is the bash script:

#!/bin/bash

# [ Define Variables ]
HOST=`hostname -s`
syslogtag=MySQL-Backup
DEST=/var/dba/backup/
DBS="$(mysql -u root -Bse 'show databases' | egrep -v '^Database$|hold$' | grep -v 'performance_schema\|information_schema')"
DATE=$(date +'%F')

#[ Individually dump all databases with date stamps ]
for db in ${DBS[@]};
do  

GZ_FILENAME=$HOST-$db-$DATE.sql.gz
mysqldump -u root --quote-names --opt --single-transaction --quick $db > $DEST$HOST-$db-$DATE.sql
ERR=$?

if [ $ERR != 0 ]; then
NOTIFY_MESSAGE="Error: $ERR, while backing up database: $db"
logger -i -t ${syslogtag} "MySQL Database Backup FAILED; Database: $db"
else
NOTIFY_MESSAGE="Successfully backed up database: $db "
logger -i -t ${syslogtag} "MySQL Database Backup Successful; Database: $db"
fi
echo $NOTIFY_MESSAGE
done

If you have large files for backup, you can replace the statement in the bash script for the mysqldump to compress the file using gzip.

mysqldump -u root --quote-names --opt --single-transaction --quick $db | gzip -cf > $DEST$HOST-$db-$DATE.sql.gz

you can use gunzip to uncompress the file.

MySQL Backup Script in Bash, Create MySQL Backup Script Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system. Use this link to download script. After than change some configuration values in section “Update below values” in the script as per your environment. To that end, I just created a MySQL shell script that I use on my Linux servers to make database backups with mysqldump, and I thought I’d share that script here. My MySQL database backup (dump) shell script. Here’s the source code for my MySQL database backup shell script. An explanation follows the shell script source code. #!/bin/sh

For mysql local system process I use:

#!/bin/bash

DEFAULTSFILE=/etc/my.cnf.d/.backups.cnf
BACKUPDIR=/root/db_backups
SAVEIFS=$IFS

# get list of dbs with field name (Database)

DBLIST=$(mysql -e "show databases" | cut -d ' '  -f 2 | grep -v Database)
IFS=$'\n'
DBLIST=($DBLIST)

# create backups directory if it doesn't exist

[ -d $BACKUPDIR ] || mkdir -p $BACKUPDIR

# make a single dump file for all databases in case entire server needs to be restored:

echo "Dumping all databases..."
mysqldump --defaults-file=$DEFAULTSFILE --all-databases > $BACKUP_DIR/$(date +%Y%m%d).all.sql
echo "Done dumping all databases."

# backup each database into it's own file to facilitate discrete restoration:

for (( i=0; i<${#DBLIST[@]}; i++ ))
do
  echo "starting dump of  ${DBLIST[$i]}..."
  mysqldump --defaults-file=$DEFAULTSFILE ${DBLIST[$i]} --skip-lock-tables > $BACKUPDIR/$(date +%Y%m%d).${DBLIST[$i]}.sql
  echo "done dumping ${DBLIST[$i]}."
done
IFS=$SAVEIFS

# gzip the sql files in-place to save space

gzip -f $BACKUPDIR/*.sql

# delete sql files older than 7 days

find $BACKUPDIR -type f -mtime +7 -name '*.gz' -execdir rm -- '{}' \;

For docker things are slightly different:

#!/bin/bash
DEFAULTSFILE=/etc/my.cnf.d/.backups.cnf
BACKUPDIR=/root/db_backups
SAVEIFS=$IFS

# get list of dbs with field name (Database) and information_schema excluded. information_schema hangs during dumps

DBLIST=$(docker exec mysql mysql --defaults-file=/etc/my.cnf.d/.backups.cnf -e "show databases" | cut -d ' '  -f 2 | grep -v Database)
IFS=$'\n'
DBLIST=($DBLIST)

# create backups directory if it doesn't exist

[ -d $BACKUPDIR ] || mkdir -p $BACKUPDIR

# backup each database into it's own file to facilitate discrete restoration:

for (( i=0; i<${#DBLIST[@]}; i++ ))
do
  echo "starting dump of  ${DBLIST[$i]}..."
  docker exec mysql mysqldump --defaults-file=$DEFAULTSFILE ${DBLIST[$i]} --skip-lock-tables > $BACKUPDIR/$(date +%Y%m%d).${DBLIST[$i]}.sql
  echo "done dumping ${DBLIST[$i]}."
done
IFS=$SAVEIFS

# gzip the sql files in-place to save space

gzip -f $BACKUPDIR/*.sql

# delete sql files older than 7 days

find $BACKUPDIR -type f -mtime +7 -name '*.gz' -execdir rm -- '{}' \;

I haven't been able to get --all-databases working for docker. I don't like all-in-one backups anyway...

I threw these directly into root's crontab and they work fine...

Bash Script to backup all MySQL databases � GitHub, Seems that you are wanting a bash script to run backup on dynamic databases that are created in MySQL. You can add the mysql root user account information in my. cnf in the root directory or within the bash script under the # [ Define Variables ]. This will allow you to run the script with the following command. A working mysql backup script - explains how to backup MySQL Database Server using a cron job under UNIX or Linux like operating systems.

How to Backup MySQL Database using Bash Script, #TITLE: mysql_backup.sh. #DESCRIPTION: script for automating the daily mysql backups on development computer. #AUTHOR: tleish. chmod 0700 vps-database-backup-script.sh. Run the script to create a backup: bash vps-database-backup-script.sh mysql automatic backup. One you are sure that the script is working as it should, you can create a cron job so that automatic backups of the mysql data is taken at periodic intervals. See the existing crontab entries with the command

Backing up MySQL Databases, This shell script will create a file named 'dblist' in '/usr/local' that will list all the databases in MySQL Server. The DB's are backed up in '/Backup/mysqlbackup'� To backup multiple MySQL databases with one command you need to use the --database option followed by the list of databases you want to backup. Each database name must be separated by space. mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql. The command above will create a dump file containing both databases

MySQL DB backup using Bash script - DEV, Mysqldump is a nifty little utility that lets you dump the contents of a database to a file, so I use this to create a little bash script that will backup� For a full list of control characters supported by the date command, see: Linux Shell Script Date Format. We can use these date control characters in crontab to give our sql.gz files unique filenames. In the next step we will show some examples and backup scenarios using crontab schedules and timestamps in filenames. 7. MySQL Automated Backup

Comments