Free Linux Backup with ©XSIBackup-App: MySQL/ MariaDB databases

How to backup MySQL/ MariaDB databases in Linux servers with ©XSIBackup-App

Linux backup

©XSIBackup was conceived as a tool to back virtual machines up in ©VMWare ©ESXi hosts. We developed a deduplication engine that works over any regular file system and a delta differential algorithm based on the use of the SHA1 hash. All of the latter works at block level and is optimized to achieve high speed in backing up data by code optimization and the use of pthreads technology.

It was just a matter of time that we would dedicate some time to enable the latent Linux backup capabilities of ©XSIBackup, and so we did while preparing the launch of ©XSIBackup-App 1.0.1.0.

©XSIBackup-App can now connect to the root of any Linux host and backup or replicate it's data somewhere else locally or over IP. If what you want is to backup data with de-duplication and achieve a near 99% compression ratio, then ©XSIBackup-App is your choice. Also if the files to replicate are very big ©XSIBackup's algorithm will work better than other tools.

You can connect to a host and backup its data by choosing some folder, file or list of folders via the File() source argument method, which reads a list of directories from a text file. Obviously, any open files will be skipped. You can use the built-in --prev-script and --post-script arguments to run some script in the remote host.

Pre nd post scripts will allow you to do virtually anything, from taking an LVM2 snapshot and mounting some volume to run some backup server command that will generate a database dump per instance. The post script can take care to return whatever remote service to its original state.

Backing up big MySQL/ MariaDB databases with deduplication.

When your database is big, in the order of hundreds of gigabytes, as it's rather common in well known enterprise software like SAP, then you will start to have trouble by using mere scripts, not only because the dump will take time to complete, but because storing a few restore points will take all your backup space rapidly. Compressing will take time and it won't be enough most of the times.

In cases like the above is when ©XSIBackup-App will come in handy, as it can deduplicate your data at block level to compress it up to 99%, depending on the amount of new daily data that you generate each day.

XSIFS FUSE file system, which comes pre-installed in the ©XSIBackup-App appliance will allow you to mount each restore point to extract individual files very easily.

Backing some MySQL database up is rather simple, you just have to use the mysqldump command to generate a dump with all the data as INSERT SQL sentences. We provide two sample scripts to dump MySQL/MariaDB databases with or without compression in the etc/jobs dir.

You can place any of the above scripts inside the etc/scripts directory and invoke them via the --prev-script argument. The scripts are copied to the backup source server and executed there, thus you can easily try them out in the source server's command line to make sure they work before using them.

Of course you will have to chage the name of the database and target to suit your needs. You will be using the target dir in the backup command in a while.

As you will have noticed there isn't any authentication info in the mysqldump commands above. You can set implicit authentication by using the [Client] section in the my.cnf file.

You will also be wondering whether using compression will allow to achieve a good block deduplication compression ratio. The answer is YES, as gzip produces deterministic compressed results, namely: the compressed data will also yield the same string of bytes, except for some small variable data like dates. You will usually have to count with one or two MB of data that will change on every backup cycle even if the data remains the same, still it's worth to compress.

Even if you don't compress the data there will still be headers containing the creation date in the SQL dump anyway.

Backing up the data

Backing up data with ©XSIBackup-App is rather simple: you need to set the source and the target for your backup. As the appliance can connect to multiple servers you will need to add them first, as well as any other backend server you want to store your backups to.

The link between the source servers, namely: the ones that contain the data to be backed up, the back end servers and the appliance is made by means of RSA keys. Once the source and target servers are added to the appliance scheme you will be able to use them.

Adding a source server.

Linking to a source of backup data server is really easy, you just have to use the --add-host command and enter the password. You will normally use the root user, you can use any other user name though.

The above command is self explanatory, you have the user name, the @ sign followed by an IPv4 a colon and the port number which is usually 22 for the SSH service. Confirm and the appliance will be linked to the a.b.c.d. server by its RSA key.

Adding a backend server.

You can store your backups locally to any kind of device: VHD, RAW disk, NFS, SSHFS, Samba, etc... that can appear as local to the Linux file system. Nonetheless in this case we are going to use a remote IP target which will be bound to the appliance by its RSA key in a similar way as we did before for the source server. Instead of --add-host, we will use the --add-key argument.

Enter the remote server's root password when prompted to and the e.f.g.h backend backup server will be bound to the appliance.

Building the backup job.

Now that you have linked the appliance to the source and target servers you can reference those IPs in the backup jobs. In this case we want to grab the remote folder /home/backup/sqldumps at server a.b.c.d and backup its contents, that is the Test-DB.sql.gz backup tar ball to some backup repository at e.f.g.h

Arguments explained.

1. The first argument is the action --backup. This is always a deduplicated backup to the location pointed to in the target argument.
2. The source argument. This is what we are backing up at the source --backup-host. In this case it is a folder.
3. The target argument. This is where we are copying the data to. In this case it is a remote location at root@e.f.g.h:22:/home/backup/repo01.

1. In fourth position we have the --backup-host argument, which tells ©XSIBackup which server the data to backup is sitting at.
2. This is the "Previous Script" argument (--prev-script), which points at a script we want to run before running the backup job. In this case the script will dump the Test-DB database to the /home/backup/sqldumps directory at the source host. Then the backup job will backup the resulting dump to a deduplicated repository at root@e.f.g.h:22:/home/backup/repo01