{"id":13340,"date":"2019-04-01T20:50:16","date_gmt":"2019-04-01T20:50:16","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=13340"},"modified":"2019-04-01T20:50:16","modified_gmt":"2019-04-01T20:50:16","slug":"how-to-backup-restore-mysql-mariadb-and-postgresql-using-automysqlbackup-and-autopostgresqlbackup-tools","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/04\/01\/how-to-backup-restore-mysql-mariadb-and-postgresql-using-automysqlbackup-and-autopostgresqlbackup-tools\/","title":{"rendered":"How to Backup\/Restore MySQL\/MariaDB and PostgreSQL Using \u2018Automysqlbackup\u2019 and \u2018Autopostgresqlbackup\u2019 Tools"},"content":{"rendered":"

If you are a database administrator (DBA<\/strong>) or are responsible for maintaining, backing up, and restoring databases, you know you can\u2019t afford to lose data. The reason is simple: losing data not only means the loss of important information, but also may damage your business financially.<\/p>\n

\n

\"MySQL\/MariaDB<\/a><\/p>\n

MySQL\/MariaDB & PostgreSQL Backup\/Restore<\/p>\n<\/div>\n

For that reason, you must always make sure that:<\/p>\n

1.<\/strong>\u00a0your databases are backed up on a periodic basis,
\n2.<\/strong>\u00a0those backups are stored in a safe place, and
\n3.<\/strong>\u00a0you perform restoration drills regularly.<\/p>\n

This last activity should not be overlooked, as you don\u2019t want to run into a major issue without having practiced what needs to be done in such situation.<\/p>\n

In this tutorial we will introduce you to two nice utilities to back up\u00a0MySQL<\/strong>\u00a0\/\u00a0MariaDB<\/strong>\u00a0and\u00a0PostgreSQL<\/strong>databases, respectively:\u00a0automysqlbackup<\/strong>\u00a0and\u00a0autopostgresqlbackup<\/strong>.<\/p>\n

Since the latter is based on the former, we will focus our explanation on\u00a0automysqlbackup<\/strong>\u00a0and highlight differences with\u00a0autopgsqlbackup<\/strong>, if any at all.<\/p>\n

It is strongly recommended to store the backups in a\u00a0network share mounted<\/a>\u00a0in the backup directory so that in the event of a system-wide crash, you will still be covered.<\/p>\n

Read following useful guides on MySQL:<\/p>\n

MySQL Basic Database Administration Commands<\/a><\/div>\n
MySQL Backup and Restore Commands for Database Administration<\/a><\/div>\n
phpMyBackupPro \u2013 A Web Based MySQL Backup Tool<\/a><\/div>\n
MySQLDumper: A PHP and Perl Based MySQL Database Backup Tool<\/a><\/div>\n
15 Basic MySQL Interview Questions for Database Administrators<\/a><\/div>\n

Installing MySQL \/ MariaDB \/ PostgreSQL Databases<\/h3>\n

1.<\/strong>\u00a0This guide assumes the you must have\u00a0MySQL<\/strong>\u00a0\/\u00a0MariaDB<\/strong>\u00a0\/\u00a0PostgreSQL<\/strong>\u00a0instance running, If not, please install the following packages:<\/p>\n

Fedora-based distributions:<\/h4>\n
# yum update && yum install mariadb mariadb-server mariadb-libs postgresql postgresql-server postgresql-libs\r\n<\/pre>\n
Debian and derivatives:<\/h4>\n
# aptitude update && aptitude install mariadb-client mariadb-server mariadb-common postgresql-client postgresql postgresql-common\r\n<\/pre>\n
2.<\/strong>\u00a0You have a testing\u00a0MySQL<\/strong>\u00a0\/\u00a0MariaDB<\/strong>\u00a0\/\u00a0PostgreSQL<\/strong>\u00a0database that you can use (you are advised to\u00a0NOT<\/strong>\u00a0use either\u00a0automysqlbackup<\/strong>\u00a0or\u00a0autopostgresqlbackup<\/strong>\u00a0in a production environment until you have become acquainted with these tools).<\/p>\n
Otherwise, create two sample databases and populate them with data before proceeding. In this article I will use the following databases and tables:<\/p>\n
MySQL\/MariaDB<\/div>\n
CREATE DATABASE mariadb_db;\r\nCREATE TABLE tecmint_tbl (UserID INT AUTO_INCREMENT PRIMARY KEY, \r\nUserName VARCHAR(50), \r\nIsActive BOOL);\r\n<\/pre>\n
\n
\"Create<\/a><\/p>\n
Create MySQL Database<\/p>\n<\/div>\n
PostgreSQL<\/div>\n
CREATE DATABASE postgresql_db;\r\nCREATE TABLE tecmint_tbl (\r\nUserID SERIAL PRIMARY KEY,\r\nUserName VARCHAR(50),\r\nIsActive BOOLEAN);\r\n<\/pre>\n
\n
\"Create<\/a><\/p>\n
Create PostgreSQL Database<\/p>\n<\/div>\n
Installing automysqlbackup and autopgsqlbackup in CentOS 7 and Debian 8<\/h3>\n
3.<\/strong>\u00a0In\u00a0Debian 8<\/strong>, both tools are available in the repositories, so installing them is as simple as running:<\/p>\n
# aptitude install automysqlbackup autopostgresqlbackup\r\n<\/pre>\n
Whereas in\u00a0CentOS 7<\/strong>\u00a0you will need to download the installation scripts and run them. In the sections below we will focus exclusively on installing, configuring, and testing these tools on\u00a0CentOS 7<\/strong>\u00a0since for\u00a0Debian 8<\/strong>\u00a0\u2013 where they almost work out of the box, we will make the necessary clarifications later in this article.<\/p>\n
Installing and configuring automysqlbackup in CentOS 7<\/h4>\n
4.<\/strong>\u00a0Let us begin by creating a working directory inside\u00a0\/opt<\/code>\u00a0to download the installation script and run it:<\/p>\n
# mkdir \/opt\/automysqlbackup\r\n# cd \/opt\/automysqlbackup\r\n# wget http:\/\/ufpr.dl.sourceforge.net\/project\/automysqlbackup\/AutoMySQLBackup\/AutoMySQLBackup%20VER%203.0\/automysqlbackup-v3.0_rc6.tar.gz\r\n# tar zxf automysqlbackup-v3.0_rc6.tar.gz\r\n# .\/install.sh\r\n<\/pre>\n
.<\/p>\n
\n
\"Installing<\/a><\/p>\n
Installing AutoMysqlBackup in CentOS-7<\/p>\n<\/div>\n
5.<\/strong>\u00a0The configuration file for\u00a0automysqlbackup<\/strong>\u00a0is located inside\u00a0\/etc\/automysqlbackup<\/strong>\u00a0under the name\u00a0myserver.conf<\/strong>. Let\u2019s take a look at most relevant configuration directives:<\/p>\n
myserver.conf \u2013 Configure Automysqlbackup<\/div>\n
# Username to access the MySQL server\r\nCONFIG_mysql_dump_username='root<\/strong>'\r\n# Password\r\nCONFIG_mysql_dump_password='YourPasswordHere<\/strong>'\r\n# Host name (or IP address) of MySQL server\r\nCONFIG_mysql_dump_host='localhost<\/strong>'\r\n# Backup directory\r\nCONFIG_backup_dir='\/var\/backup\/db\/automysqlbackup<\/strong>'\r\n# List of databases for Daily\/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )\r\n# set to (), i.e. empty, if you want to backup all databases\r\nCONFIG_db_names=(AddYourDatabase Names Here<\/strong>)\r\n# List of databases for Monthly Backups.\r\n# set to (), i.e. empty, if you want to backup all databases\r\nCONFIG_db_month_names=(AddYourDatabase Names Here<\/strong>)\r\n# Which day do you want monthly backups? (01 to 31)\r\n# If the chosen day is greater than the last day of the month, it will be done\r\n# on the last day of the month.\r\n# Set to 0 to disable monthly backups.\r\nCONFIG_do_monthly=\"01<\/strong>\"\r\n# Which day do you want weekly backups? (1 to 7 where 1 is Monday)\r\n# Set to 0 to disable weekly backups.\r\nCONFIG_do_weekly=\"5<\/strong>\"\r\n# Set rotation of daily backups. VALUE*24hours\r\n# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.\r\nCONFIG_rotation_daily=6<\/strong>\r\n# Set rotation for weekly backups. VALUE*24hours. A value of 35 means 5 weeks.\r\nCONFIG_rotation_weekly=35<\/strong>\r\n# Set rotation for monthly backups. VALUE*24hours. A value of 150 means 5 months.\r\nCONFIG_rotation_monthly=150<\/strong>\r\n# Include CREATE DATABASE statement in backup?\r\nCONFIG_mysql_dump_create_database='no'<\/strong>\r\n# Separate backup directory and file for each DB? (yes or no)\r\nCONFIG_mysql_dump_use_separate_dirs='yes'<\/strong>\r\n# Choose Compression type. (gzip or bzip2)\r\nCONFIG_mysql_dump_compression='gzip'<\/strong>\r\n# What would you like to be mailed to you?\r\n# - log   : send only log file\r\n# - files : send log file and sql files as attachments (see docs)\r\n# - stdout : will simply output the log to the screen if run manually.\r\n# - quiet : Only send logs if an error occurs to the MAILADDR.\r\nCONFIG_mailcontent='quiet'<\/strong>\r\n# Email Address to send mail to? (user@domain.com)\r\nCONFIG_mail_address='root'<\/strong>\r\n# Do you wish to encrypt your backups using openssl?\r\n#CONFIG_encrypt='no'\r\n# Choose a password to encrypt the backups.\r\n#CONFIG_encrypt_password='password0123'\r\n# Command to run before backups (uncomment to use)\r\n#CONFIG_prebackup=\"\/etc\/mysql-backup-pre\"\r\n# Command run after backups (uncomment to use)\r\n#CONFIG_postbackup=\"\/etc\/mysql-backup-post\"\r\n<\/pre>\n
Once you have configured\u00a0automysqlbackup<\/strong>\u00a0as per your needs, you are strongly advise to check out the\u00a0README<\/strong>\u00a0file found in\u00a0\/etc\/automysqlbackup\/README<\/strong>.<\/p>\n
MySQL Database Backup<\/h4>\n
6.<\/strong>\u00a0When you\u2019re ready, go ahead and run the program, passing the configuration file as argument:<\/p>\n
# automysqlbackup \/etc\/automysqlbackup\/myserver.conf\r\n<\/pre>\n
\n
\"Configure<\/a><\/p>\n
Configure Automysqlbackup on CentOS 7<\/p>\n<\/div>\n
A quick inspection of the\u00a0daily<\/strong>\u00a0directory will show that\u00a0automysqlbackup<\/strong>\u00a0has run successfully:<\/p>\n
# pwd\r\n# ls -lR daily\r\n<\/pre>\n
\n
\"MySQL<\/a><\/p>\n
MySQL Daily Database Backup<\/p>\n<\/div>\n
Of course you can add a crontab entry to run\u00a0automysqlbackup<\/strong>\u00a0at a time of day that best suits your needs (1:30<\/strong>am every day in the below example):<\/p>\n
30 01 * * * \/usr\/local\/bin\/automysqlbackup \/etc\/automysqlbackup\/myserver.conf\r\n<\/pre>\n
Restoring a MySQL Backup<\/h4>\n
7.<\/strong>\u00a0Now let\u2019s drop the\u00a0mariadb_db<\/strong>\u00a0database on purpose:<\/p>\n
\n
\"Drop<\/a><\/p>\n
Drop MariaDB Database<\/p>\n<\/div>\n
Let\u2019s create it again and restore the backup. In the MariaDB prompt, type:<\/p>\n
CREATE DATABASE mariadb_db;\r\nexit\r\n<\/pre>\n
Then locate:<\/p>\n
# cd \/var\/backup\/db\/automysqlbackup\/daily\/mariadb_db\r\n# ls\r\n<\/pre>\n
\n
\"Locate<\/a><\/p>\n
Locate MariaDB Database backup<\/p>\n<\/div>\n
And restore the backup:<\/p>\n
# mysql -u root -p mariadb_db < daily_mariadb_db_2015-09-01_23h19m_Tuesday.sql\r\n# mysql -u root -p\r\nMariaDB [(none)]> USE mariadb_db; \r\nMariaDB [(none)]> SELECT * FROM tecmint_tb1;\r\n<\/pre>\n
\n
\"Restore<\/a><\/p>\n
Restore MariaDB Backup<\/p>\n<\/div>\n
Installing and configuring autopostgresqlbackup in CentOS 7<\/h3>\n
8.<\/strong>\u00a0In order for\u00a0autopostgresql<\/strong>\u00a0to work flawlessly in\u00a0CentOS 7<\/strong>, we will need to install some dependencies first:<\/p>\n
# yum install mutt sendmail\r\n<\/pre>\n
Then let’s repeat the process as before:<\/p>\n
# mkdir \/opt\/autopostgresqlbackup\r\n# cd \/opt\/autopostgresqlbackup\r\n# wget http:\/\/ufpr.dl.sourceforge.net\/project\/autopgsqlbackup\/AutoPostgreSQLBackup\/AutoPostgreSQLBackup-1.0\/autopostgresqlbackup.sh.1.0\r\n# mv autopostgresqlbackup.sh.1.0 \/opt\/autopostgresqlbackup\/autopostgresqlbackup.sh\r\n<\/pre>\n
Let’s make the script executable and\u00a0start<\/strong>\u00a0\/\u00a0enable<\/strong>\u00a0the service:<\/p>\n
# chmod 755 autopostgresqlbackup.sh\r\n# systemctl start postgresql\r\n# systemctl enable postgresql\r\n<\/pre>\n
Finally, we will edit the value of the backup directory setting to:<\/p>\n
autopostgresqlbackup.sh – Configure Autopostgresqlbackup<\/div>\n
BACKUPDIR=\"\/var\/backup\/db\/autopostgresqlbackup\"\r\n<\/pre>\n
After having through the configuration file of\u00a0automysqlbackup<\/strong>, configuring this tool is very easy (that part of the task is left up to you).<\/p>\n
9.<\/strong>\u00a0In\u00a0CentOS 7<\/strong>, as opposed to\u00a0Debian 8<\/strong>,\u00a0autopostgresqlbackup<\/strong>\u00a0is best run as the\u00a0postgres<\/strong>\u00a0system user, so in order to do that you should either switch to that account or add a cron job to its crontab file:<\/p>\n
# crontab -u postgres -e\r\n<\/pre>\n
30 01 * * * \/opt\/autopostgresqlbackup\/autopostgresqlbackup.sh\r\n<\/pre>\n
The backup directory, by the way, needs to be created and its permissions and group ownership must be set recursively to\u00a00770<\/strong>\u00a0and\u00a0postgres<\/strong>\u00a0(again, this will NOT be necessary in\u00a0Debian<\/strong>):<\/p>\n
# mkdir \/var\/backup\/db\/autopostgresqlbackup\r\n# chmod -R 0770 \/var\/backup\/db\/autopostgresqlbackup\r\n# chgrp -R postgres \/var\/backup\/db\/autopostgresqlbackup\r\n<\/pre>\n
The result:<\/p>\n
# cd \/var\/backup\/db\/autopostgresqlbackup\r\n# pwd\r\n# ls -lR daily\r\n<\/pre>\n
\n
\"PostgreSQL<\/a><\/p>\n
PostgreSQL Daily Database Backup<\/p>\n<\/div>\n
10.<\/strong>\u00a0Now you can restore the files when needed (remember to do this as user postgres after recreating the empty database):<\/p>\n
# gunzip -c postgresql_db_2015-09-02.Wednesday.sql.gz | psql postgresql_db\r\n<\/pre>\n
Considerations in Debian 8<\/h3>\n
As we mentioned earlier, not only the installation of these tools in\u00a0Debian<\/strong>\u00a0is more straightforward, but also their respective configurations. You will find the configuration files in:<\/p>\n
    \n
  1. Automysqlbackup<\/strong>: \/etc\/default\/automysqlbackup<\/li>\n
  2. Autopostgresqlbackup<\/strong>: \/etc\/default\/autopostgresqlbackup<\/li>\n<\/ol>\n
    Summary<\/h3>\n
    In this article we have explained how to install and use\u00a0automysqlbackup<\/strong>\u00a0and\u00a0autopostgresqlbackup<\/strong>\u00a0(learning how to use the first will help you master the second as well), two great database back up tools that can make your tasks as a DBA or system administrator \/ engineer much easier.<\/p>\n
    Please note that you can expand on this topic by setting up\u00a0email notifications<\/strong>\u00a0or sending backup files as attachments via email \u2013 not strictly required, but may come in handy sometimes.<\/p>\n
    As a final note, remember that the permissions of configuration files should be set to the minimum (0600<\/strong>\u00a0in most cases). We look forward to hearing what you think about this article. Feel free to drop us a note using the form below.<\/p>\n
    Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    If you are a database administrator (DBA) or are responsible for maintaining, backing up, and restoring databases, you know you can\u2019t afford to lose data. The reason is simple: losing data not only means the loss of important information, but also may damage your business financially. MySQL\/MariaDB & PostgreSQL Backup\/Restore For that reason, you must … <\/p>\n
    Continue reading “How to Backup\/Restore MySQL\/MariaDB and PostgreSQL Using \u2018Automysqlbackup\u2019 and \u2018Autopostgresqlbackup\u2019 Tools”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-13340","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13340","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=13340"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13340\/revisions"}],"predecessor-version":[{"id":13341,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13340\/revisions\/13341"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=13340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=13340"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=13340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}