1. Home
  2. Docs
  3. Backup Ninja
  4. How-To
  5. Point-in-time Recovery (PITR)
  6. MySQL Server/Percona Server/MariaDB Server

MySQL Server/Percona Server/MariaDB Server

Taking Point-in-time Recovery (PITR) for MySQL Server/Percona Server/MariaDB Server is simply straight forward just like how you schedule your backup for MySQL Server/Percona Server/MariaDB Server.

A PITR in Backup Ninja using MySQL Server/Percona Server/MariaDB Server works once you are performing a full backup using mysqldump and by enabling PITR during the scheduling of backup.

Prerequisites before taking a PITR backup

To perform a PITR capable backup, you are required to at least enable the following configuration variables by MySQL-based database. These are the following:

  • Enabling Binary Logging for MySQL:
    • server-id must be specified, otherwise the server is not allowed to start. Set unique replication ID in the range from 1 to 4294967295.(eg.: server-id = 1)
    • set log_bin as the path that should be used as Binary Logs Location.(eg.: log_bin = /var/log/mysql/mysql-bin.log)
    • restart MySQL server.

This can be seen in our configuration wizard during enabling PITR.

Creating your PITR backup

In creating your PITR backup, it is expected that you have already created or added your Server. Let’s go over the steps on how to do this.

1.) Schedule a Backup

From the Backup Ninja dashboard, go to Schedules → Schedule Backup and follow the configuration wizard. You can check our How-To page on how to schedule a backup for MySQL/Percona Server/MariaDB

2.) Choose mysqldump as your backup method

At this point, you are now in the Details tab under the configuration wizard. Make sure that you have selected correctly your target MySQL/Percona Server/MariaDB server that you have already created or added. Afterwards, simply choose the mysqldump as the backup method just like as what is shown below:

Enable PITR backup

Note

When enabling PITR, you are required to at least specify the configuration variables for MySQL-based databases which are server-id and log-bin. Please check our user guide page regarding PITR.

3.) Specify the binary log location

Once you have enabled the required parameters or configuration variables as stated in our user-guide for PITR, you have to determine and use that binary log location where it is stored. You can verify this by running the following query,


mysql> show global variables like 'log_bin_basename';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| log_bin_basename | /opt/mysqlbinlogs/log_bin |
+------------------+---------------------------+
1 row in set (0.00 sec)

In the example above, the location of my binary logs are stored in /opt/mysqlbinlogs. Keep in mind that the log_bin is the base filename. For example, here are the list of binary logs in that path:

$ ls -alth /opt/mysqlbinlogs/
total 20K
-rw-r----- 1 mysql mysql 156 May 24 16:36 log_bin.000002
drwxr-xr-x 2 mysql mysql 4.0K May 24 16:36 .
-rw-r----- 1 mysql mysql 66 May 24 16:36 log_bin.index
-rw-r----- 1 mysql mysql 179 May 24 16:36 log_bin.000001
drwxr-xr-x 4 root root 4.0K May 24 16:35 ..

To specify the binary log location, simply copy paste the directory or path where your binary logs are stored. For example,

Path of binary logs location

4.) Clear Binary Logs location after each backup to save space

Lastly, this option is optional for you to decide. This means that, whenever the Backup Ninja finishes taking a backup of the binary log, Backup Ninja will clear or remove that binary log from its original location to free up space. That means, if you have log_bin.000001 up to log_bin.000010, if these binary logs have been finished backup by the Backup Ninja, it will be removed from its original location, for example, from /opt/mysqlbinlogs. Since it shall remove the file, you have to think of this carefully unless you have decided that is the desired choice for your binary logs once it has been back up. To do this, simply check the checkbox as follows,

Clear binary logs location after each backup to save space

Now that you are good to go, just hit the [keybtn]Continue[/keybtn] and proceed on the next tab Storages.

Determining and viewing your PITR enabled backups

To determine your PITR enabled backups, go over to the Schedule List of your backups. Now choose the name of your scheduled backup for which the PITR is enabled. See the example scheduled backup below,

Click the scheduled PITR enabled backup

The enabled PITR backup in the list is tagged with PITR label so it’s easy to identify which one of your backup has PITR enabled.
Once clicked, you’ll get the view of your PITR enabled backups
PITR enabled backup list view

You get the full list of your backups and these are paginated. If you have lots of backup listed, you can navigate in accordance to its page selected.

Alternatively, your PITR backups will be listed by going to Backup NinjaBackups in the left panel navigation. See our user guide to know how to list your backups. To determine the PITR backups among the full backup or other backups you have, the backup name shall be prefixed with bin. See the screenshot below,

 

Determining your PITR backups in the list

There are times that your MySQL-based database is not performing any large writes or say your MySQL/Percona Server/MariaDB server is place into a dormant state for some time, then any Backup Ninja actions has to be made shall be skipped since there’s no any writes or no previous changes have been identified from your MySQL-based database server. If you have checked to clear your binary logs after it has been backup, then simply the action to take the backup can be skipped since there are no binary logs to be back up. See screenshot below:

Skipped PITR backup action

Was this article helpful to you? Yes No