Performing Partial Backups Manually vs. Using Backup Ninja

Performing Partial Backups Manually vs. Using Backup Ninja

Profile picture for user LukasVileikis
Lukas Vileikis
20 May 2021

If you are a DBA, chances are you have performed backups. If you have backed up your data, you probably already know that there are a couple of backup methods available for you to use - you can perform full backups, partial backups etc. In this blog post we are going to go through the differences and similarities between performing partial backups manually and performing them while using Backup Ninja.

Partial Backups Explained

As the name already suggests, partial backups are such backups that do not consist of the full data set. Partial backups enable you to back up specific data - in the database world, they are frequently used to back up only specific databases or specific tables.

The database world usually has a few backup methods:

  • MySQL has mysqldump, xtrabackup or mariabackup (depending on the flavor of the database you elect to use - MySQL, Percona or MariaDB)
  • PostgreSQL has pg_dumpall and pg_dump. Do note that pg_dumpall is only available for PostgreSQL 12.
  • MongoDB has mongodump.

As you might already tell, these backup methods are useful for a host of different use cases. Here’s how some of those methods might be used:

  1. To use mysqldump when taking a partial backup, invoke it like so to take a backup of a specific table:
    mysqldump -u root -p[password] [database_name] [table_name] > backup.sql

    Or like so if you want to take a backup of a specific database:
    mysqldump -u root -p[password] [database_name] > backup.sql

     

  2. To use xtrabackup when taking a partial backup (do note that you must have the innodb_file_per_table option enabled) you can invoke it like so (this command will only back up tables in the test database):
    $ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.].*"

    For more examples on how xtrabackup might be used to achieve your backup goals, take a look at Percona’s documentation.
  3. If you are using PostgreSQL, you can also use pg_dump to achieve your partial backup goals (this command would back up a demo_table inside of a demo_database):
    pg_dump -t demo_table -f demo_backup.sql demo_database

     

  4. If you find yourself using MongoDB, you can use a command like so to back up a specific database for example:
    mongodump --db=db_name

     

Partial Backups with Backup Ninja

With that being said, Backup Ninja can also help you achieve your partial backup goals. Backup Ninja also supports all of the backup methods mentioned above (excluding pg_dumpall because that dumps all of the data) - simply select “Partial” underneath the Backup Type, select the backup method and include (or exclude) the tables and databases you want in the Details section:
 

DetailsDetails - 2

Then proceed with the installation - create a database user:

Creating a database user

Finally, install the agent and discover your servers:
 

Installing and Discovering ServersServer discovery

Summary

Partial backups can be a really useful tool in your shed - especially if they are used correctly. Use them wisely according to your business needs, but remember that using partial backups manually can be really time-consuming, so it might be useful to look into tools that can automate the process for you - if you are looking for a reliable backup partner, consider giving Backup Ninja a try today.

Tags