How do I create a MySQL/MariaDB backupset via cli

Operatingsystem

Linux

Software

8.1 and higher

Cause

You have a Linux server without a graphical environment like GNOME or KDE and would like to create a MySQL/MariaDB backupset.

Solution

The backup set must be created first. We do this via the web interface of the backup server.

Creating the Backupset

1. Go to the backup server where the backup account is located (e.g. backup09.mindtime.nl).
2. Log in to your backup account.
3. Go to the user section.
   
4. Go to the Backup set option in the left menu.
5. Click on the to add a new backup set.
6. Choose the Backup set type: MySQL Backup or MariaDB Backup.
7. Provide a meaningful name for the set, eg: MySQL Backup.
8. For "Platform" choose: Linux.
9. Enter the Login ID of the user who is allowed make the backup.
   • By default this is: root.
10. We will enter the password later via the software client.
11. Check whether the Host and Port are entered correctly
    • Default Host: localhost.
    • Default Port: 3306.
12. Enter the Path to mysqldump.
    • You can find the path with the command: root@server:~# which mysqldump
    • Usually this is the path: /usr/bin/mysqldump
13. Click at the bottom right to proceed to the source selection.
14. Click on to add a source selection
    • MySQL ensures that all databases are included in the backup.
    • MySQL/employees ensures, for example, that only the employees database is included in the backup
15. Click on to add a de-selected source.
    It is wise to always exclude the following databases as these are system databases that are not allowed in the backup.
    • information_schema
    • performance_schema
    
16. Click at the bottom right to proceed to the schedule.
17. You can now adjust / delete or add the backup schedule.
    • Leave the * in "Run scheduled backup on computers named" laten staan
    • If you add a new schedule, do not forget to check the box "Run Retention Policy after backup".
18. Click at the bottom right to proceed to the destination.
19. You can now specify a destination. By default, the online backup server is already entered here.
20. You can leave "Windows User Authentication" blank
21. Done, you can now at the bottom right to save the set.

Complete backup set

You can now log into the machine via e.g. ssh to do the last few steps.
If you still need to install the software, you can use the article How do I install the software on a linux machine without gui.
If you have already installed the software, you can run the configuration script:

cd /usr/local/obm
sh RunConfigurator.sh

1. Choose (1). Login.
2. Enter the login details.

Once you are logged in, the script will detect that there is a new backup set.

Encryption setting has not been defined for backup set "MySQL Backup"
Do you want to set the encryption setting for this backup set? (Y/N) ?

1. Select Y if the correct set is mentioned
2. Enable Encryption (Y/N) ? Y
3. You will now see a selection menu for the type of encryption
   • (1). Default
     With this option, the machine generates a strong random code. You can request this code via "1. List backup sets"
   • (2). User password
     The current password of the backup account is used. If the password is changed in the future, the key does NOT change!
   • (3). Custom
     You can fill in what the key should be.
4. Attention! The following applies to all options: it cannot be changed afterwards and must be properly documented.

Enter SQL password

As a last step we now have to enter the password for access to the MySQL server.
We assume that you are still logged in and in the main menu.

1. Export the backup set settings to an XML file by choosing (3). Export Backup Set Setting to XML.

2. Choose your backup set to generate XML file
   -------------------------------------------
   (1). MySQL Backup
   -------------------------------------------
   Your Choice: 1

   XML file successfully exported to /root/.obm/config/backupSet.xml

3. Close the software

Now go to the folder containing the XML file and edit it in your favorite text editor

# cd /root/.obm/config
# vi backupSet.xml

Find and edit the following parameters in the XML file

<!-- MySQL Password -->
<Value data="abc123$%" name="Password" type="string" />

Delete the Key "Destination Settings" in the XML file

<!-- This is the collection of destinations -->
<Key name="Destination Settings">
  <Value data="1" name="concurrency-level" type="integer" />
</Key>

We now go back to the software:

cd /usr/local/obm
sh RunConfigurator.sh

1. Choose (1). Login.
2. Log in to your backup account.
3. Import the just edited XML by choosing (4). Import Backup Set Setting from XML.
   • When you get the prompt, Overwrite the existing backup set.

     Backup Set 'MySQL Backup' already exist. Confirm overwrite? (Y/N) ? Y
     XML imported, uploading to server...
     XML successfully uploaded to server

4. You can now close the CLI interface.

Check Password and Destination

1. Go to the backup server where the backup account is located (e.g. backup09.mindtime.nl).
2. Log in to your backup account.
3. Go to the user section.
4. Go to the Backup set option in the left menu.
5. Choose the created backupset: MySQL Backup.
6. The password field should now contain ******.
7. Select Destination in the left menu.
   • If there is no more destination available, click on to add a destination.
     Choose from the list the CBS destination with the most colorful icon (The other is a clone for technical reasons).
   • If you have added a destination, then click at the bottom right on: .
8. If you have made changes do not forget to click on at the bottom right to save them.

Attention! Do not forget to delete the XML file because it now contains an unprotected password.