Create a MySQL Database Backup Set for Linux
There are two options to create a MySQL Database Backup Set for Linux.
For backup set creation on AhsayCBS web console, most backup set preferences may be configured using AhsayCBS except the following:
- backup set encryption key settings
- MySQL login password
After the backup set preferences are setup on the AhsayCBS web console, to complete the backup set creation process the RunConfigurator.sh script will need to be used on the MySQL machine to do the following:
- to setup the backup set encryption key settings
- to export/import the backupSet.xml file to setup the MySQL login password
For backup set creation using command line, most backup set preferences may be configured using the backupSet.xml file.
To complete the backup set creation process after successfully importing the backupSet.xml file, the RunConfigurator.sh script will need to be used on the MySQL machine to setup the backup set encryption key settings.
Creating a MySQL Database Backup Set with AhsayCBS Web Console
Log in to AhsayCBS
- Go to User
Go to Backup Set then click the + button to create a backup set.
Input the “Name”, select the “Backup set type” which should be MySQL Backup and “Platform” which should be Linux. Update the “Host” and “Port”. Default settings for these are localhost and 3306 respectively. Input the “Path to mysqldump” then click the
right arrow /Next.
Add the backup source in the "Other Selected Source" by clicking the + button e.g. path is MySQL/sakila.
To back up all databases in the MySQL machine, use the path MySQL.
Input the path of the source to be backed up. Click the + button to add the source. Keep doing this until all the backup source are added.
To exclude a source, you can add it under "Deselected Source by clicking the "+" button.
Always add the “information_schema” and “performance_schema” databases under “Deselected Source” since they are MySQL virtual system databases. They are read-only and cannot be backed up.
Input the path of the source to be excluded in the backup. Click the "+" button to add the deselected source. Keep doing this until all source to be excluded are added. Click the right arrow/Next.
The “Run scheduled backup for this backup set” is turned on by default. If you do not want to create a backup schedule you can turn it off by sliding the lever to the left. This schedule may either be edited or deleted if you want to create your own backup schedule.
It is optional to input the computer name in the “Run scheduled backup on computers named” field since it will be updated once backup set creation is completed in the AhsayOBM client. In Step 12 the computer name can be checked as the Owner.
Click the "+" button to add a new backup schedule. Once done setting the schedule, click the "+" then the right arrow/Next to proceed.
Select the "Backup Mode" if Concurrent or Sequential. By default, Sequential is selected. In Sequential backup mode, if there are multiple destinations configured in the backup set, AhsayOBM will back up to one destination at a time.
In Concurrent backup mode, if there are multiple destinations configured in the backup set, AhsayOBM will backup to all destinations at the same time or concurrently.
For backup sets with multiple destinations, sequential backup mode will take longer compared with concurrent backup mode.
If Concurrent is selected, specify the maximum number of backup destinations.
Click the "+" button to add a standard and/or predefined destination/s.
Select your desired destination, it could be one or both displayed destinations. Tick the checkbox and click "+" to proceed.
Click the Save icon to create the Backup Set.
Click on the backup set you just created.
Go to Others and input the path of your temporary directory. It is recommended to check the box beside Remove temporary files after backup to make sure the spooled database files are cleaned up after each backup job to free up space on the temporary drive.
Otherwise, if the temporary drive runs out of space the database backup job will not run. You can also change settings for "Compressions". Click the Save icon once done.
Open ssh session in the Linux machine and execute the RunConfigurator.sh script. The Login Menu will be displayed. Select (1). Login then press Enter to login. Input your "Login Name" and "Password". Set the encryption setting for the backup set.
#cd /usr/local/obm/bin # sh RunConfigurator.sh Startup Ahsay Online Backup Manager ... Config file found Login Menu ---------- (1). Login (2). Change Network Settings (3). Forgot Password (4). Quit ---------- Your Choice: 1 Login Name : test Password : ******************* Please wait while verifying user account with server... Your profile has been downloaded and updated. Encryption setting has not been defined for backup set "MySQL Database Backup" Do you want to set the encryption setting for this backup set? (Y/N) ? Y Enable Encryption (Y/N) ? Y Choose Encryption Type ---------------------- (1). Default (2). User password (3). Custom ---------------------- Your Choice: 2Edit the backup set created from AhsayCBS to add the MySQL Password. Select (1). List Backup Sets to list the backup set.
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 1Select the backup set to show the details by selecting (2). MySQL Database 1.
Select a Backup Set to show more details ---------------------------------------- (1). MySQL Database (2). MySQL Database 1 ---------------------------------------- Your Choice: 2 Name : My SQL Database 1 Owner : localhost.localdomain Type : MySQL Selected Source : MySQL/test Selected Source : MySQL/world Destination Name : Local-1, Type: Local Encryption Key : 123 Encryption Algorithm : AES Encryption Mode : CBC Encryption Key Length: 256 Press Enter to continue...Export the backup set to XML by selecting (3). Export Backup Set Setting to XML
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 3 Choose your backup set to generate XML file ------------------------------------------- (1). MySQL Database Backup ------------------------------------------- Your Choice: 1 XML file successfully exported to /root/.obm/config/backupSet.xmlExit fro the Main Menu then edit the XML file by using an editor like vi. Add the value data for "MySQL Password" to the file then save.
# cd /root/.obm/config # vi backupSet.xml <!-- MySQL Password --> <Value data="Abcd123$%^" name="Password" type="string" />Please refer to the Troubleshooting - MySQL Backup Set XML Template for details on the field to be configured.
Import the backup set by selecting (4). Import Backup Set Setting from XML. Confirm overwrite of file.
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 4 Backup Set 'MySQL Database Backup' already exist. Confirm overwrite? (Y/N) ? Y XML imported, uploading to server... XML successfully uploaded to serverCheck in AhsayCBS web console if MySQL "Password" and "Encryption" setting was successfully added.
Creating a MySQL Database Backup Set using Command Line
To create a MySQL database backup set, select (5). Generate new Backup Set Settings Template from the menu.
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 5Select (2)> MySQL Database to generate a MySQL Database Backup Set template file to /root/.obm/config directory.
Choose a template from a backup set type ---------------------------------------- (1). File (2). MySQL Database (3). MariaDB (4). Oracle Database Server (5). IBM Domino ---------------------------------------- Your Choice: 2 XML file successfully exported to /root/.obm/config/backupSet.xml Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice:Configuring MySQL Backup Set Settings.
To configure the MySQL backup set setting you need to edit the /root/.obm/config/backupSet.xml file using a text editor, for example vi.
You can either quit the RunConfigurator.sh script or open a new ssh session to edit the backupSet.xml file.
For details and examples on how to create backup sets using the backupSet.xml file, please refer to the following Troubleshooting pages:
- MySQL Backup Set XML Template (Raw)
- MySQL Backup Set XML Template (With Explanation)
- Example of MySQL Database Backup Set with Normal Backup Schedule
- Example of MySQL Database Backup Set with Periodic Backup Schedule
Importing the updated backupSet.xml file to AhsayOBM
After you have edited the backupSet.xml file with your chosen backup settings you need to import the settings back to AhsayOBM, so they can be uploaded to the AhsayCBS backup server to create the backup set.
For example: to create a new MySQL backup set called “MySQL Database 1” with encryption enabled and default encryption settings.
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 4 New backup set created. Enable Encryption (Y/N) ? Y Choose Encryption Type ---------------------- (1). Default (2). User password (3). Custom ---------------------- Your Choice: 1 XML imported, uploading to server... XML successfully uploaded to serverVerify the Backup Set Settings
To verify the uploaded backup set settings are correct select (1). List Backup Sets and then select the backup set you wish to verify, for example backup set named “MySQL Database 1”.
Main Menu --------- (1). List Backup Sets (2). Delete Backup Set (3). Export Backup Set Settings to XML (4). Import Backup Set Settings from XML (5). Generate new Backup Set Settings Template (6). Change Language [English] (7). Update Profile Settings (8). Quit --------- Your Choice: 1 Select a Backup Set to show more details ---------------------------------------- (1). MySQL Database (2) MySQL Database 1 ---------------------------------------- Your Choice: 2 Name : My SQL Database 1 Owner : localhost.localdomain Type : MySQL Selected Source : MySQL/test Selected Source : MySQL/world Destination Name : Local-1, Type: Local Encryption Key : 123 Encryption Algorithm : AES Encryption Mode : CBC Encryption Key Length: 256- We would like to stress that it is very important to keep a separate record of your encryption key in a safe place, as you will not be able to restore your data without the correct key.
- If you re-install AhsayOBM or install AhsayOBM on another machine, the encryption key will be required for restoring data from the backup set.
Backup and Restore