Mariadb is part of the Ubuntu Official Repositories so perhaps it's time to start using it.
Contents of ~/.my.cnf
# Contents of ~/.my.cnf
# protected by 600 permissions rw-------
[client-tgn]
host = localhost
user = root
password = "new_password"
[client-backup]
host = localhost
user = backup_user
password = "YourSecurePasswordHere"
Backup script
#!/bin/bash
# crontab
# 31 13 * * * /home/user/admin-scripts/backup.sh
# Send mariadb backups to local disk
cd `dirname $0`
MY_CNF=~/.my.cnf
GROUP_SUFFIX=-backup
BACKUP_DIR=/home/user/backups
if [ ! -f "$MY_CNF" ];
then
echo Missing $MY_CNF ;
exit 1
fi
echo Making sure perms for $MY_CNF are right
chmod 600 $MY_CNF
DAY=`date +%a`
DAY_NUM=`date +%u`
# get all the DB's but skip the ones you don't want to backup by adding them to the NOT IN clause
DBS=`mariadb --defaults-group-suffix=$GROUP_SUFFIX \
--skip-column-names \
-e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');"`
for DB in $DBS
do
echo BACKUP $DB DAY_NUM ${DAY_NUM} DAY ${DAY}
mariadb-dump --defaults-group-suffix=$GROUP_SUFFIX \
--no-tablespaces \
$DB | gzip > ${BACKUP_DIR}/${DAY_NUM}-${DB}-${DAY}-db.sql.gz
done
Create a user with sufficient backup privileges
-- Create the dedicated backup user
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'YourSecurePasswordHere';
-- Grant permissions for logical dumps
GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
-- Apply the new privilege rules
FLUSH PRIVILEGES;

0 Comments