Adding MySQL database backup to rsnapshot for Linux Servers

Subject: Adding MySQL database backup to rsnapshot for Linux Servers

Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL)

Country: Singapore

Date: 22 August 2021 Sunday Singapore Time

Type of Pulication: Plain Text

Document Version: 20210822.01

DETAILED INSTRUCTIONS

=====================

Looking for mysqldump

=====================

# which mysqldump

/usr/bin/mysqldump

Testing if mysqldump works

==========================

# /usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql

Check the filesize of MySQL dump

================================

# du -h mysqldump_all_databases.sql

23M     mysqldump_all_databases.sql

Backup rsnapshot configuration file first

=========================================

# cd /etc/

# cp rsnapshot.conf rsnapshot.conf.backup20082021

Deploying MySQL backup script

=============================

# cp /usr/share/doc/rsnapshot-1.4.3/utils/backup_mysql.sh /usr/local/bin/

# chown root.root /usr/local/bin/backup_mysql.sh

# chmod o-w /usr/local/bin/backup_mysql.sh

# chmod +x /usr/local/bin/backup_mysql.sh

# cat /usr/local/bin/backup_mysql.sh

===BEGIN of backup_mysql.sh===

#!/bin/sh

##############################################################################

# backup_mysql.sh

#

# by Nathan Rosenquist <nathan@rsnapshot.org>

# http://www.rsnapshot.org/

#

# This is a simple shell script to backup a MySQL database with rsnapshot.

#

# The assumption is that this will be invoked from rsnapshot. Also, since it

# will run unattended, the user that runs rsnapshot (probably root) should have

# a .my.cnf file in their home directory that contains the password for the

# MySQL root user. For example:

#

# /root/.my.cnf (chmod 0600)

#   [client]

#   user = root

#   password = thepassword

#   host = localhost

#

# This script simply needs to dump a file into the current working directory.

# rsnapshot handles everything else.

##############################################################################

# $Id: backup_mysql.sh,v 1.6 2007/03/22 02:50:21 drhyde Exp $

umask 0077

# backup the database

/usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql

# make the backup readable only by root

/bin/chmod 600 mysqldump_all_databases.sql

===END of backup_mysql.sh===

Edit rsnapshot configuration file

==================================

# nano /etc/rsnapshot.conf

Under BACKUP POINTS / SCRIPTS add the following line

====================================================

backup_script           /usr/local/bin/backup_mysql.sh          secret.teo-en-ming-corp.com/mysqldump/

Test the rsnapshot configuration file for syntax errors

=======================================================

# rsnapshot configtest

require Lchown

Lchown module loaded successfully

Syntax OK

Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 22 August 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast.

REFERENCES

==========

[1] https://www.mail-archive.com/maria-discuss@lists.launchpad.net/msg06041.html