Backup MySQL (Ubuntu) - Revision history

Sstrutt: /* The Script */ Updated URL in script

2021-01-18T15:06:33Z

The Script: Updated URL in script

← Older revision		Revision as of 15:06, 18 January 2021
Line 31:		Line 31:
	# Simon Strutt (Apr 2012)		# Simon Strutt (Apr 2012)
	#		#
	# See ~~http~~://~~www~~.~~sandfordit.com/vwiki/index~~.~~php~~/Backup_MySQL_(Ubuntu)		# See https://vwiki.co.uk/Backup_MySQL_(Ubuntu)

	### MySQL Server Login and local backup info ###		### MySQL Server Login and local backup info ###

Sstrutt: /* The Script */ Updated script

2012-04-25T07:12:59Z

The Script: Updated script

← Older revision		Revision as of 07:12, 25 April 2012
Line 84:		Line 84:
	if [ -f ${STAMPS}/${db} ]		if [ -f ${STAMPS}/${db} ]
	then		then
	BAKDATE=`~~more~~ ${STAMPS}/${db}`		BAKDATE=`cat ${STAMPS}/${db}`
	BAKDATEU=`date --date "${BAKDATE}" +%s`		BAKDATEU=`date --date "${BAKDATE}" +%s`
	#Logger "$db last backup was $BAKDATE"		#Logger "$db last backup was $BAKDATE"

Sstrutt: /* Set-up */ Updated proc

2012-04-23T14:01:14Z

Set-up: Updated proc

← Older revision		Revision as of 14:01, 23 April 2012
Line 14:		Line 14:
	#* <code> chmod +x /backup/mysql.sh </code>		#* <code> chmod +x /backup/mysql.sh </code>
	# Perform a test run of the backup		# Perform a test run of the backup
	~~#* A <code>1044: Access denied for user 'root'@'localhost' to database 'information_schema'</code> error is normal and is due to a longstanding MySQL bug~~
	# Schedule the script to run with crontab		# Schedule the script to run with crontab
	#* <code> crontab -e </code>		#* <code> crontab -e </code>

Sstrutt: /* The Script */ Typo in script - fixed

2012-04-23T13:41:50Z

The Script: Typo in script - fixed

← Older revision		Revision as of 13:41, 23 April 2012
Line 80:		Line 80:
	else		else
	# Is backup required?		# Is backup required?
	DBUPDATE=`mysql -u ~~root~~ -h ~~localhost~~ -~~psyndrome~~ -Bse "SELECT max(update_time) FROM information_schema.tables WHERE table_schema='${db}'"`		DBUPDATE=`mysql -u $MUSER -h $MHOST -p$MPASS -Bse "SELECT max(update_time) FROM information_schema.tables WHERE table_schema='${db}'"`
	DBUPDATEU=`date --date "${DBUPDATE}" +%s`		DBUPDATEU=`date --date "${DBUPDATE}" +%s`
	#Logger "$db last update was $DBUPDATE"		#Logger "$db last update was $DBUPDATE"

Sstrutt: Added GRANT for backup user

2012-04-23T13:37:15Z

Added GRANT for backup user

← Older revision		Revision as of 13:37, 23 April 2012
Line 6:		Line 6:
	** Can use [[Acronyms#F\|FTPS]] for secure backup transfer		** Can use [[Acronyms#F\|FTPS]] for secure backup transfer

	~~To set the script~~ up~~...~~		=== Set-up ===
	# Create the required folders using...		# Create the required folders using...
	#* <code> mkdir backup </code>		#* <code> mkdir backup </code>
Line 19:		Line 19:
	#* <code> 30 1 * * * /bin/bash /backup/mysql.sh </code>		#* <code> 30 1 * * * /bin/bash /backup/mysql.sh </code>

			To create a MySQL user with limited privileges to run the backup with use...
			<source lang="mysql">
			GRANT SELECT,LOCK TABLES ON . TO backup@'localhost' IDENTIFIED BY 'password';
			</source>

			=== The Script ===
	<source lang="bash">		<source lang="bash">
	#!/bin/bash		#!/bin/bash
Line 29:		Line 35:

	### MySQL Server Login and local backup info ###		### MySQL Server Login and local backup info ###
	MUSER="~~root~~" # MySQL user		MUSER="backup" # MySQL user
	MPASS="password" # MySQL password		MPASS="password" # MySQL password
	MHOST="localhost"		MHOST="localhost"

Sstrutt: typo

2012-04-23T12:54:31Z

typo

← Older revision		Revision as of 12:54, 23 April 2012
Line 30:		Line 30:
	### MySQL Server Login and local backup info ###		### MySQL Server Login and local backup info ###
	MUSER="root" # MySQL user		MUSER="root" # MySQL user
	MPASS="~~syndrome~~" # MySQL password		MPASS="password" # MySQL password
	MHOST="localhost"		MHOST="localhost"
	MYSQL="$(which mysql)"		MYSQL="$(which mysql)"
Line 42:		Line 42:

	## FTP info		## FTP info
	FTPDIR="/~~Backup~~/~~monocle~~/db" # Folder path on FTP server		FTPDIR="/backup/server1/db" # Folder path on FTP server
	FTPUSER="backup" # FTP username		FTPUSER="backup-user" # FTP username
	FTPPASS="backup" # FTP password		FTPPASS="backup-pass" # FTP password
	FTPSERVER="~~nas~~.~~home~~.~~int~~" # FTP server name/IP		FTPSERVER="ftp.domain.com" # FTP server name/IP
	FTPS=1 # Set to 1 enable FTP over SSL/TLS		FTPS=0 # Set to 1 enable FTP over SSL/TLS

	## Functions		## Functions

Sstrutt: Initial creation

2012-04-23T12:52:13Z

Initial creation

New page

This backup script is a more elaborate version of the one to found [[MySQL_(Ubuntu)#Backup|here]], this backup script...
* Backs up all databases on a server
** You can exclude databases if required
* Only backs up databases that have changed since the last backup
* FTP's backup's off to server (uses [http://lftp.yar.ru/ <code>lftp</code>], install with <code>apt-get install lftp</code>)
** Can use [[Acronyms#F|FTPS]] for secure backup transfer

To set the script up...
# Create the required folders using...
#* <code> mkdir backup </code>
#* <code> mkdir backup/mysql </code>
# Create the file below (editing as required) as <code> /backup/mysql.sh </code>
# Make the file executable
#* <code> chmod +x /backup/mysql.sh </code>
# Perform a test run of the backup
#* A <code>1044: Access denied for user 'root'@'localhost' to database 'information_schema'</code> error is normal and is due to a longstanding MySQL bug
# Schedule the script to run with crontab
#* <code> crontab -e </code>
#* <code> 30 1 * * * /bin/bash /backup/mysql.sh </code>

<source lang="bash">
#!/bin/bash

# MySQL Database backup script
#
# Simon Strutt (Apr 2012)
#
# See http://www.sandfordit.com/vwiki/index.php/Backup_MySQL_(Ubuntu)

### MySQL Server Login and local backup info ###
MUSER="root" # MySQL user
MPASS="syndrome" # MySQL password
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql/data" # Where backups will go
STAMPS="/backup/mysql/stamps" # Where last backup timestamps go
LOG="/backup/mysql.log"
GZIP="$(which gzip)"
NOW=$(date -u +%Y%m%d)
DBS2EXCLUDE="Syslog" # Databases to exclude (; seperated, eg db1;db2)

## FTP info
FTPDIR="/Backup/monocle/db" # Folder path on FTP server
FTPUSER="backup" # FTP username
FTPPASS="backup" # FTP password
FTPSERVER="nas.home.int" # FTP server name/IP
FTPS=1 # Set to 1 enable FTP over SSL/TLS

## Functions
Logger()
{
echo `date "+%a %d/%m/%y %H:%M:%S"`: $1 >> $LOG
}

## Main Script
Logger "Started backup script..."

# Create folders that don't exist
[ ! -d $BAK ] && mkdir -p $BAK
[ ! -d $STAMPS ] && mkdir -p $STAMPS
[ ! -d $BAK/prev ] && mkdir -p $BAK/prev

# Move previous backups to tmp dir
#mv $BAK/*.gz $BAK/tmp

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
# Check db shouldn't be excluded from backup
if echo "${DBS2EXCLUDE}" | grep -q ${db}
then
Logger "$db backup excluded, skipping"
else
# Is backup required?
DBUPDATE=`mysql -u root -h localhost -psyndrome -Bse "SELECT max(update_time) FROM information_schema.tables WHERE table_schema='${db}'"`
DBUPDATEU=`date --date "${DBUPDATE}" +%s`
#Logger "$db last update was $DBUPDATE"
if [ -f ${STAMPS}/${db} ]
then
BAKDATE=`more ${STAMPS}/${db}`
BAKDATEU=`date --date "${BAKDATE}" +%s`
#Logger "$db last backup was $BAKDATE"
else
BAKDATEU=0
Logger "$db no last backup stamp"
fi
if [ "${DBUPDATEU}" -gt "${BAKDATEU}" ]
then
# Backup db (move previous to prev, and write stamp file first)
rm -f $BAK/prev/$db.*.gz
[ -f $BAK/$db.*.gz ] && mv $BAK/$db.*.gz $BAK/prev
FILE=$BAK/$db.$NOW.gz
Logger "$db backing up to $FILE"
date +'%F %T' > $STAMPS/$db
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --skip-lock-tables $db | $GZIP -9 > $FILE
RETURNVALUE=$?
if [ ! "${RETURNVALUE}" == "0" ]; then
Logger "$db backup experienced error (${RETURNVALUE})"
rm $STAMPS/$db
fi
else
Logger "$db backup not required (last backup $BAKDATE, last db update $DBUPDATE)"
fi
fi
done

Logger "Completed local backup"

## FTP to remote server
if [ ${FTPS} == 0 ]
then
lftp -u ${FTPUSER},${FTPPASS} -e "set net:max-retries 5; cd ${FTPDIR}; mput ${BAK}/*.gz; exit" ${FTPSERVER}
else
lftp -u ${FTPUSER},${FTPPASS} -e "set ftp:ssl-force true,ftp:ssl-protect-data true,net:max-retries 5; cd ${FTPDIR}; mput ${BAK}/*.gz; exit" ${FTPSERVER}
fi
RETURNVALUE=$?

if [ "${RETURNVALUE}" == "0" ]; then
Logger "FTP upload completed successfully"
else
Logger "FTP upload failed !!!"
fi

echo "----------------------------------------------" >> $LOG
</source>

[[Category:MySQL]]
[[category:Ubuntu]]