How to use Automysqlbackup and Autopostgresqlbackup
Steps to Backup/Restore MySQL/MariaDB and PostgreSQL Using ‘ Automysqlbackup’ and ‘ Autopostgresqlbackup’ Tools
The main roles of a database administrator is to maintain, backup and restore databases. In this article, we will learn the two best utilities to back up MySQL / MariaDB and PostgreSQL databases respectively using automysqlbackup and autopostgresqlbackup.
To Install MySQL / PostgreSQL Databases
For RedHat based distributions
# yum update & & yum install mariadb mariadb-server mariadb-libs postgresql postgresql-server postgresql-libs
For Ubuntu/Debian and derivatives
# aptitude update & & aptitude install mariadb-client mariadb-server mariadb-common postgresql-client postgresql postgresql-common
To create two sample databases
Here we have used MySQL database to create two sample databases and add the data in the databases.
root@linuxhelp ~# mysql Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 43 Server version: 10.0.23-MariaDB-0ubuntu0.15.10.1 (Ubuntu) Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ' help ' or ' h' for help. Type ' c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE maria_db Query OK, 1 row affected (0.02 sec) MariaDB [(none)]> use maria_db Database changed. MariaDB [maria_db]> CREATE TABLE linux (ID AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(20), ACTIVE BOOL) MariaDB [maria_db]> SELECT * FROM linux +-----+-------+--------+ | ID | NAME | ACTIVE | +-----+-------+--------+ | 101 | user1 | 1 | | 102 | user2 | 0 | +-----+-------+--------+ 2 rows in set (0.00 sec) MariaDB [maria_db]> CREATE DATABASE postgresql_db Query OK, 1 row affected (0.00 sec) MariaDB [postgresql_db]> CREATE TABLE linux1 ( USERID SERIAL PRIMARY KEY, NAME VARCHAR(20), ACTIVE BOOLEAN) MariaDB [postgresql_db]> SELECT * FROM linux1 +--------+--------+--------+ | USERID | NAME | ACTIVE | +--------+--------+--------+ | 1 | user21 | 0 | | 2 | user41 | 4 | +--------+--------+--------+ 2 rows in set (0.01 sec)
To Install automysqlbackup and autopostgresqlbackup
To Install automysqlbackup and autopostgresqlbackup in Ubuntu/Debian
root@linuxhelp ~# aptitude install automysqlbackup autopostgresqlbackup
Create a directory inside /opt. and Download the installation script and run it
root@linuxhelp ~# mkdir /opt/automysqlbackup
root@linuxhelp ~# cd /opt/automysqlbackup/
root@linuxhelp /opt/automysqlbackup# wget http://ufpr.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
--2016-04-19 09:52:59-- http://ufpr.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
Resolving ufpr.dl.sourceforge.net (ufpr.dl.sourceforge.net)... 200.236.31.2, 2801:82:80ff:8000::3
Connecting to ufpr.dl.sourceforge.net (ufpr.dl.sourceforge.net)|200.236.31.2|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 39205 (38K) [application/x-gzip]
Saving to: ‘ automysqlbackup-v3.0_rc6.tar.gz’
automysqlbackup-v3. 100%[=====================> ] 38.29K 87.1KB/s in 0.4s
2016-04-19 09:53:00 (87.1 KB/s) - ‘ automysqlbackup-v3.0_rc6.tar.gz’ saved [39205/39205]
Enter the following the tar command to install the automysqlbackup in the terminal.
root@linuxhelp /opt/automysqlbackup# tar zxf automysqlbackup-v3.0_rc6.tar.gz root@linuxhelp /opt/automysqlbackup# ls ' automysqlbackup automysqlbackup-v3.0_rc6.tar.gz install.sh README automysqlbackup.conf CHANGELOG LICENSE root@linuxhelp /opt/automysqlbackup# ./install.sh
Configuration file
The configuration file is located inside /etc/automysqlbackup under the name myserver.conf for automysqlbackup. Open and check for the configuration directives
myserver.conf &ndash Configure Automysqlbackup
# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username=' root'
# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password=' linuxc'
# Host name (or IP address) of MySQL server e.g localhost
CONFIG_mysql_dump_host=' localhost'
# " Friendly" host name of MySQL server to be used in email log
# if unset or empty (default) will use CONFIG_mysql_dump_host instead
#CONFIG_mysql_dump_host_friendly=' '
# Backup directory location e.g /backups
CONFIG_backup_dir=' /var/backup'
CONFIG_db_names=(' maria_db' ' postgresql_db' )
# You can use
#declare -a MDBNAMES=( " ${DBNAMES[@]}" ' added entry1' ' added entry2' ... )
# INSTEAD to copy the contents of $DBNAMES and add further entries (optional).
# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=(' mariadb' ' postgresql_db' )
# Set to 0 to disable monthly backups.
CONFIG_do_monthly=" 01"
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
CONFIG_do_weekly=" 5"
# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today' s backups, you could choose 1, i.e. everything older than 24hours will be removed.
CONFIG_rotation_daily=6
# Set rotation for weekly backups. VALUE*24hours
CONFIG_rotation_weekly=35
# Set rotation for monthly backups. VALUE*24hours
CONFIG_rotation_monthly=150
# Notification setup
# What would you like to be mailed to you?
# - log : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
CONFIG_mailcontent=' quiet'
# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
#CONFIG_mail_maxattsize=4000
# Allow packing of files with tar and splitting it in pieces of CONFIG_mail_maxattsize.
#CONFIG_mail_splitandtar=' yes'
# Use uuencode instead of mutt. WARNING: Not all email clients work well with uuencoded attachments.
#CONFIG_mail_use_uuencoded_attachments=' no'
# Email Address to send mail to? (user@domain.com)
CONFIG_mail_address=' root'
To know more about the configuration file, verify the README file found in the path /etc/automysqlbackup/README.
To take MySQL Database Backup
Run the following command to take " automysql" Database Backup.
root@linuxhelp /etc/automysqlbackup# automysqlbackup /etc/automysqlbackup/myserver.conf
Parsed config file " /etc/automysqlbackup/automysqlbackup.conf"
# Checking for permissions to write to folders:
base folder /var ... exists ... ok.
backup folder /var/backup ... creating ... success.
Now move to the backup directory " /var/backup" to check the backup file.
root@linuxhelp /etc/automysqlbackup# cd /var/backup
root@linuxhelp /var/backup# ls -lR daily
daily:
total 8
drwxr-xr-x 2 root root 4096 Apr 19 10:03 maria_db
drwxr-xr-x 2 root root 4096 Apr 19 10:03 postgresql_db daily/maria_db:
total 8
-rw-r--r-- 1 root root 750 Apr 19 10:01 daily_maria_db_2016-04-19_10h01m_Tuesday.sql.gz
-rw-r--r-- 1 root root 750 Apr 19 10:03 daily_maria_db_2016-04-19_10h03m_Tuesday.sql.gz daily/postgresql_db:
total 8
-rw-r--r-- 1 root root 777 Apr 19 10:01 daily_postgresql_db_2016-04-19_10h01m_Tuesday.sql.gz
-rw-r--r-- 1 root root 776 Apr 19 10:03 daily_postgresql_db_2016-04-19_10h03m_Tuesday.sql.gz
To take automysql backup daily in a specific time set the entry in the crontab as shown below.
30 3 * * * /usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
To Restore MySQL Backup
Drop the maria_db database and create it again and restore it
root@linuxhelp /# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 58 Server version: 10.0.23-MariaDB-0ubuntu0.15.10.1 (Ubuntu) Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ' help ' or ' h' for help. Type ' c' to clear the current input statement. MariaDB [(none)]> show databases +--------------------+ | Database | +--------------------+ | information_schema | | maria_ | | maria_db | | mysql | | performance_schema | | postgresql_db | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> DROP DATABASE maria_db Query OK, 1 row affected (0.06 sec) MariaDB [(none)]> show databases +--------------------+ | Database | +--------------------+ | information_schema | | maria_ | | mysql | | performance_schema | | postgresql_db | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> CREATE DATABASE maria_db Query OK, 1 row affected (0.00 sec)
Then provide the location were the backup has to be restored
root@linuxhelp /# cd /var/backup/daily/mariadb_db
root@linuxhelp /var/backup/daily/maria_db# ls
daily_maria_db_2016-04-19_10h01m_Tuesday.sql.gz
daily_maria_db_2016-04-19_10h03m_Tuesday.sql.gz
Next, restore the backup file into the maria_db database.
root@linuxhelp /var/backup/daily/maria_db# mysql -u root -p maria_db < daily_maria_db_2016-04-19_10h01m_Tuesday.sql.gz Enter password: Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 58 Server version: 10.0.23-MariaDB-0ubuntu0.15.10.1 (Ubuntu) Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ' help ' or ' h' for help. Type ' c' to clear the current input statement. MariaDB [(none)]> show databases +--------------------+ | Database | +--------------------+ | information_schema | | maria_ | | maria_db | | mysql | | performance_schema | | postgresql_db | +--------------------+ 6 rows in set (0.00 sec)
To take PostgreSQL Backup
The postgresql backup process is similar to that of automysql backup. Run the yum command.
To Install and configure autopostgresqlbackup in CentOS 7 install the additional two packages called mutt and sendmail. Whereas for other distributions it will be installed by default at the initial stage.
# yum install mutt sendmail
Then Repeat the process.
root@linuxhelp /# mkdir /opt/autopostgresqlbackup root@linuxhelp /# cd /opt/autopostgresqlbackup/ root@linuxhelp /opt/autopostgresqlbackup# wget http://ufpr.dl.sourceforge.net/project/autopgsqlbackup/AutoPostgreSQLBackup/AutoPostgreSQLBackup-1.0/autopostgresqlbackup.sh.1.0 --2016-04-19 11:42:31-- http://ufpr.dl.sourceforge.net/project/autopgsqlbackup/AutoPostgreSQLBackup/AutoPostgreSQLBackup-1.0/autopostgresqlbackup.sh.1.0 Resolving ufpr.dl.sourceforge.net (ufpr.dl.sourceforge.net)... 200.236.31.2, 2801:82:80ff:8000::3 Connecting to ufpr.dl.sourceforge.net (ufpr.dl.sourceforge.net)|200.236.31.2|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 17028 (17K) [text/x-sh] Saving to: ‘ autopostgresqlbackup.sh.1.0’ autopostgresqlbacku 100%[=====================> ] 16.63K 44.1KB/s in 0.4s 2016-04-19 11:42:32 (44.1 KB/s) - ‘ autopostgresqlbackup.sh.1.0’ saved [17028/17028] root@linuxhelp /opt/autopostgresqlbackup# mv autopostgresqlbackup.sh.1.0 autopostgresqlbackup.sh
Change the mode and Enable the service to execute the script.
root@linuxhelp /opt/autopostgresqlbackup# chmod 755 autopostgresqlbackup.sh
root@linuxhelp /opt/autopostgresqlbackup# systemctl start postgresql
root@linuxhelp /opt/autopostgresqlbackup# systemctl enable postgresql
To edit the value of the backup directory
The postgremysql backup will be saved in " /var/backup/db/autopostgresqlbackup" path. This path can be changed in " myserver.conf" file
In Ubuntu/Debian, add a cron job to the crontab file:
root@linuxhelp:~# crontab -u postgres -e
30 03 * * * /opt/autopostgresqlbackup/ autopostgresqlbackup.sh
Create the backup directory and set permission to " 0770" and ownership to " postgres" .
root@linuxhelp ~# mkdir /var/backup/db/autopostgresqlbackup
root@linuxhelp ~# chmod -R 0770 /var/backup/db/autopostgresqlbackup/
root@linuxhelp ~# chgrp -R postgres /var/backup/db/autopostgresqlbackup/
The postgresql backup file is shown.
postgres@linuxhelp:/var/lib/autopostgresqlbackup/daily/postgres$ ls postgres_2016-04-19_12h29m.Tuesday.sql.gz
To Restore the file
Restore the file by executing the following command.
postgres@linuxhelp:/var/lib/autopostgresqlbackup/daily/postgres_globals$ gunzip -c postgres_globals_2016-04-19_12h29m.Tuesday.sql.gz | psql linux
Q
What is the command to run PREBACKUP?
A
By using this command you can take backup
Syntax: "PREBACKUP="/etc/automysqlbackup/mysql-backup-pre"
Syntax: "PREBACKUP="/etc/automysqlbackup/mysql-backup-pre"
Q
Where is the configuration file located for automysqlbackup in Centos?
A
The configuration file is located inside /etc/automysqlbackup under the name myserver.conf for automysqlbackup.
Q
How to install automysqlbackup in ubuntu?
A
By using this command you can download
Syntax: "apt-get install automysqlbackup autopostgresqlbackup"
Syntax: "apt-get install automysqlbackup autopostgresqlbackup"
Q
It is possible to set the weekly backup in automysqlbackup?
A
Yes, it is possible to set. Go to advanced settings and select the process
DOWEEKLY=6
Which day do you want weekly backups at? (1 to 7 where 1 is Monday)
DOWEEKLY=6
Which day do you want weekly backups at? (1 to 7 where 1 is Monday)
Syntax: "aptitude update && aptitude install mariadb-client mariadb-server mariadb-common postgresql-client postgresql postgresql-common"