Advanced Configuration - MySQL iDataAgent

Table of Contents

Understanding the CommCell Console

The MySQL Server iDataAgent uses the following logical entities to manage backup and restore operations from the CommCell Console.

Agent

Facilitates MySQL instance discovery.

Instance

Defines the MySQL Server instance to be backed up.

Subclient

Defines the MySQL databases to be backed up.

Managing Instances

In the CommCell Console, each instance refers to a MySQL server. Therefore, you must create an instance for each MySQL Server that you need to back up.

This topic describes how to create an instance, and how to manage the instance once it is created.

Creating a New MySQL Server Instance

Whenever a new MySQL server is created on the client, you must add the server to the CommCell. To create a MySQL instance, the MySQL user should have DBA and SYSDBA privileges.

  1. From the CommCell Browser, navigate to Client Computers | <Client>.

    Right-click MySQL, point to All Tasks, and then click New MySql Server.

  2. In the Server Name box, type a name for the new MySQL instance that you are creating.
  3. In the SA User Name box, enter the MySQL administrator user name.

    In the Account Password box, enter the account password for the administrator user account.

    In the Confirm Password box, reenter the password.

  4. In the Binary Directory box, enter the path to the directory where the MySQL application files are stored.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\bin

    Unix: /usr/bin

    Alternatively, click Browse to select the location.

  5. In the Log Data Directory box, enter the directory name where the log files are stored.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\data

    Unix: /var/lib/mysql

    Click OK.

  6. In the Config File box, enter the path to the configuration file.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\my.ini

    Unix: /etc/my.cnf

  7. Depending on which platform you use, enter the TCP/IP port number or the pathname to the socket file.
    • For Windows, enter the TCP/IP port number into the Port box to open the communication between MySQL server and clients.
    • For Unix, enter the pathname to the socket file in the Socket File box.

      If you have multiple instances installed on the client computer, make sure that the Binary Directory, Log Data Directory, and the Config File locations, and the Port number or Socket file path are different for each instance that you want to back up.

  8. In the Log Backup Storage Policy box, select the storage policy name.
  9. Click OK.

Changing the Home Directory

The HOME directory, also called the Binary directory, is the location where the application files are stored. When instances are discovered, the home path is automatically assigned to the instance. However, you can modify the home path location for an instance any  time.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL.
  2. Right-click the <Instance>, and then click Properties.
  3. In the dialog box that appears, enter a new pathname into the Binary Directory box.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\bin

    Unix: /usr/bin

  4. Click OK.

Changing the Log Directory

The Archive log index file is a text file that is made up of all of the running lists of transaction logs for the MySQL database. This index file (.index) is generated when you enable binary logging, and the index resides in the data directory. Typically, the index file is used to back up the logs. If the binary logging is disabled on the MySQL server, then the path to the data directory is displayed.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL.
  2. Right-click the <Instance>, and then click Properties.
  3. In the dialog box that appears, enter a new pathname into the Log Data Directory box.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\data

    Unix: /var/lib/mysql

    Alternatively, click Browse to select the location.

  4. Click OK.

Changing the Configuration Files

If there is a compatibility issue with the operating system on Unix clients, rename the config file and map it to the common library that is by the operating system and the iDataAgent.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL.
  2. Right-click the <Instance>, and then click Properties.
  3. In the dialog box that appears, enter a new pathname into the Config File box.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\my.ini

    Unix: /etc/my.cnf

  4. Click OK.

Changing the Port or Socket File

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL.
  2. Right-click the <Instance>, and then click Properties.
  3. For Windows, enter the TCP/IP port number to open the communication between MySQL server and clients.

    For Unix, enter the pathname to the socket file. Alternatively, click Browse to select the location.

  4. Click OK.

Configuring User Accounts for Backups

The MySQL iDataAgent requires a MySQL Server user account that has sufficient privileges for the software to:

  • Perform backups and restores
  • Access the MySQL Server application
  • Stop or start the MySQL Server services

The following table illustrates the necessary privileges the user account should have to perform backup and restore operations:

Operations Privileges MySQL User Should Have Example Query to Grant the Permission
Backup
  • SHOW DATABASES
  • SUPER
  • RELOAD
  • SELECT
  • LOCK TABLES
  1. mysql> GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD, SUPER ON *.* to '<backup_agent_user>'@'localhost' IDENTIFIED BY '<backup_agent_password>';
    mysql> GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD, SUPER ON *.* to '<backup_agent_user>'@'127.0.0.1' IDENTIFIED BY '<backup_agent_password>';
    mysql> FLUSH PRIVILEGES;
  2. It is recommended to grant all Database Administrator privileges to perform backup operations for some versions.

    Example: For MySQL 5.7 and later, you should grant all Database Administrator privileges to perform backup operations.

    mysql> GRANT ALL PRIVILEGES ON *.* TO '<backup_agent_user>'@'localhost' IDENTIFIED BY '<backup_agent_password>';
    mysql> GRANT ALL PRIVILEGES ON *.* TO '<backup_agent_user>'@'127.0.0.1' IDENTIFIED BY '<backup_agent_password>';
    mysql> FLUSH PRIVILEGES;

Restore Full Database Administrator privileges mysql> GRANT ALL PRIVILEGES ON *.* TO '<restore_agent_user>'@'localhost' IDENTIFIED BY '<restore_agent_password>';
mysql> GRANT ALL PRIVILEGES ON *.* TO <restore_agent_user>'@'127.0.0.1' IDENTIFIED BY '<restore_agent_password>';
mysql> FLUSH PRIVILEGES;

Initially, the user account credentials is provided during the instance configuration after the installation of the Agent. You can change the user account at the Instance level.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
  2. Right-click the <Instance> and click Properties.
  3. Click the Accounts tab.
  4. Type the user name in the User Name box.
  5. Type the SA user name in the SA User Name box.
  6. Type the password in the SA Account Password box and retype it in the SA Confirm Password box.
  7. Click OK.

Enabling or Disabling Automatic Database Discovery

For the MySQL iDataAgent, you can set up the system to search the instance for databases that have not been configured, yet, and add them to the system.

By default, unconfigured databases on the MySQL Server are discovered automatically and assigned to the default subclient. You can enable or disable this functionality.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.
  2. Right-click the instance, and then select Properties.
  3. Select or clear Enable Auto Discovery.
  4. Click OK.

Manually Discovering Databases

If automatic discovery of databases is disabled, you can manually add databases to a subclient.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.
  2. Right-click the default subclient, and then click Properties.
  3. Click the Content tab.
  4. Click Configure.
  5. Click Discover.
  6. To assign the database that you want to add to a subclient, select the appropriate subclient from the Subclient Name list.
  7. Click OK to save your settings.
  8. Click OK.

Creating User-Defined Subclients

By default, all databases within each MySQL Server instance are automatically assigned to the default subclient. This subclient backs up the entire instance. However, you can distribute the database content by creating user-defined subclients.

If you want to back up groups of specific databases, you can do so by creating a user-defined subclient containing any number of databases that exist within the instance. This is useful if you want to back up specific databases at certain intervals or with a particular frequency.

When you create a user-defined subclient, the contents of the user-defined subclient are excluded from the default subclient.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.
  2. Right-click the instance, select All Tasks, and then click New Subclient.
  3. On the General tab of the dialog box that appears, enter a name in the Subclient box.
  4. Click the Storage Device tab.
  5. On the Data Storage Policy sub tab, select the appropriate storage policy from the Storage Policy list.
  6. Select the Content tab, and then click Configure.
  7. Click Discover on the dialog box that appears.
  8. For each database that you want to include, select the name of the appropriate subclient from the Subclient Name list.

  9. Click OK to save the content.
  10. Click OK.

Manually Deleting Databases from a Subclient

Databases that have been added to the default subclient manually are not removed automatically if they are deleted from the MySQL Server. Such databases must be removed from the subclient manually to make sure that the subsequent backup job completes without any errors.

  1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.
  2. Right-click the default subclient, and then click Properties.
  3. Click the Content tab.
  4. From the Database List, select the database that you want to delete.
  5. Click Delete.
  6. Click OK.

Enabling Binary Logging

You must enable binary logging in order to perform transaction log backup. This is done in the MySQL configuration file.

  1. On the computer where the MySQL server is installed, navigate to the location of the MySQL configuration file.

    For example:

    Windows: C:\Program Files\MySQL\MySQL Server 5.5\my.ini

    Unix: /etc/my.cnf

  2. Open the configuration file to edit it.
  3. In the [mysqld] section, add the following:

    log-bin=mysql-bin.log

  4. Restart the MySQL server for the changes to take effect.
  5. When the MySQL server is restarted, verify that binary logging is enabled by running the following query:

    mysql > show variables like “%log_bin%”

    If binary logging has been enabled correctly, the value of log_bin = ON. If it has not be enabled correctly, the value of log_bin = OFF.

Excluding the Data Phase from Incremental Log Backups for MySQL System Database

During incremental log backups, if MySQL system database is part of a subclient content, then by default the data phase is also run for the MySQL system database after the completion of transaction log backups. You can exclude the data phase from the log backup for MySQL system database.

  1. From the CommCell Browser, navigate to the Client Computers node.
  2. Right-click the <Client>, and then click Properties.
  3. On the Client Computer Properties dialog box, click Advanced.
  4. Click the Additional Settings tab and then click Add.
  1. In the Name box, enter bExcludeSysDBFromLogJob. The Category and Type details fill automatically.

    Alternatively, you can click Lookup and search for the additional setting using the Find box.

  2. In the Value box, enter 1.
  3. Click OK to save the additional setting configuration.
  4. Click OK.

Excluding Stored Procedures from Backup

During mysqldump backups of user databases, the stored procedures (routines) and functions are included automatically in the backup using the routine parameter. You can prevent the dump of routines at the database level when you perform a mysqldump of each user database.

  1. From the CommCell Browser, navigate to the Client Computers node.
  2. Right-click the <Client>, and then click Properties.
  3. On the Client Computer Properties dialog box, click Advanced.
  4. Click the Additional Settings tab and then click Add.
  1. In the Name box, enter bDisableRoutinesDump. The Category and Type details fill automatically.

    Alternatively, you can click Lookup and search for the additional setting using the Find box.

  2. In the Value box, enter 1.
  3. Click OK to save the additional setting configuration.
  4. Click OK.

Enabling Non-Full Recurring Restores

By default, the entire MySQL server is included in recurring restores. You can enable non-full recurring restores that include only selected databases, followed by logical log restores.

  1. From the CommCell Browser, navigate to the Client Computers node.
  2. Right-click the <Client>, and then click Properties.
  3. On the Client Computer Properties dialog box, click Advanced.
  4. Click the Additional Settings tab and then click Add.
  1. In the Name box, enter bFreshRecRestore. The Category and Type details fill automatically.

    Alternatively, you can click Lookup and search for the additional setting using the Find box.

  2. In the Value box, enter 1.
  3. Click OK to save the additional setting configuration.
  4. Click OK.

Configuring Packet Buffer Size for Restore

During restores of MySQL databases, the default packet buffer size defined in the max_allowed_packet variable for the MySQL server is used. You can increase the value of max_allowed_packet for the restore.

  1. From the CommCell Browser, navigate to the Client Computers node.
  2. Right-click the <Client>, and then click Properties.
  3. On the Client Computer Properties dialog box, click Advanced.
  4. Click the Additional Settings tab and then click Add.
  1. In the Name box, enter sAdvancedRestoreOptions. The Category and Type details fill automatically.

    Alternatively, you can click Lookup and search for the additional setting using the Find box.

  2. In the Value box, enter the appropriate value, for example, --max_allowed_packet=1GB.
  3. Click OK to save the additional setting configuration.
  4. Click OK.

Configuring Streams for Backups

By default, backup data is sent to media in two streams. This means that a database is sent to media during a backup in two parallel waves. The result is that the backup takes about half the time to complete than it would if only one stream is used.

You can increase the number of streams that are used for backups for a particular subclient provided that the number of streams does not exceed the maximum number configured in the subclient's storage policy. Increasing the number of streams for a subclient further reduces the amount of time that a backup takes to complete. For example, increasing the number of streams from 2 to 3 enhances backup time from one-half that of a single stream to one-third.

The same number of streams that are used for performing a backup are needed to restore the data.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL | <Instance>.
  2. Right-click the <Subclient>, and then click Properties.
  3. Click the Storage Device tab.
  4. In the Number of Data Backup Streams box, enter or select the number of data backup streams.
  5. Click OK.

Configuring Pre and Post Processing for Backups

Setting Up Pre and Post Processes

You can add, modify, or view Pre/Post processes for the subclient. These are batch files or shell scripts that you can run before or after certain job phases.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL | <Instance>.
  2. Right-click the subclient, and then click Properties.
  3. Click the Pre/Post Process tab in the dialog box that appears.
  4. In one of the following phases, enter the full path of the process that you want to execute during that phase. Alternatively, click Browse to locate the process (applicable only for paths that do not contain any spaces).
    • PreBackup Process
    • PostBackup Process
  5. For subclients on Windows platforms, Run As is defined as Not Selected. If necessary, change the account that has permission to run these commands.
    1. Click Change.
    2. In the User Account dialog box, select Use Local System Account, or select Impersonate User, and then enter the user name and password.
    3. Click OK.
    4. If you selected Use Local System Account, a message appears that tells you that commands that use this account have rights to access all data on the client computer. Click OK.
  6. Click OK.

Setting Up Post Processes to Run during Failures

By default, a specified post process command is executed only on successful completion of the scan or backup operation or if the job is killed.

Use the following steps to run a post process even if the scan or backup operation did not complete successfully. For example, this may be useful to bring a database online or release a snapshot.

  1. From the CommCell Browser, navigate to Client Computers | <Client>| MySQL | <Instance> .
  2. Right-click the subclient, and then click Properties.
  3. Click the Pre/Post Process tab in the dialog box that appears.
  4. Select Run Post Backup Process for all attempts.
  5. Click OK.

See Pre and Post Processes - Commands and Arguments for information on additional arguments that the CommServe sends to the Pre and Post processes.

Modifying an Agent, Instance, or Subclient

There are several configurable properties available for your agent that can be modified from the agent, instance, or subclient level.

We recommend that you do not modify the properties of a subclient when a job is in progress for that specific subclient. If a job is in progress, either wait for the job to complete or kill the job from the Job Controller.

The following table describes the properties that can be configured from the client, agent, instance, and subclient levels.

Option Description Related Topics
Change Storage Policies You can configure storage policies from the instance level or subclient level.

You might need to modify the storage policies in any of the following situations:

  • To include a different media for the backup operation.
  • To use a storage policy with a different retention criteria.

Change the storage policies for log backup at the instance level.

  1. From the CommCell Browser, right-click <Instance>.
  2. Click Properties.
  3. Click the Log Backup tab.
  4. Select the appropriate storage policy from the list.
  5. Click OK.

Change the storage policies for data backup from the subclient level.

  1. From the CommCell Browser, right-click <Subclient>.
  2. Click Properties.
  3. Click the Storage Device tab.
  4. On the Data Storage Policy sub tab select the appropriate policy from the Storage Policy list.
  5. Click OK.
Refer to Storage Policies.
Rename a Subclient

You can rename a subclient:

  1. From the CommCell Browser, right-click the subclient.
  2. Click Properties.
  3. Type the new name in the  Subclient name field.
  4. Click OK.
 
Data Transfer Options You can configure the available resources for transferring data secured by data protection operations from the subclient level. This includes the following:
  • Enable or disable Data Compression either on the client or on the MediaAgent.
  • Configure the transfer of data in the network using the options for Network Bandwidth Throttling and Network Agents.

You can configure the data transfer options.

  1. From the CommCell Browser, right-click the subclient.
  2. Click Properties.
  3. Click Storage Device.
  4. Click Data Transfer Option tab.
  5. Choose the appropriate software compression option for this subclient.
  6. Select Throttle Network Bandwidth and set the required bandwidth.
  7. Click OK.
Refer to Data Compression and Network Bandwidth Throttling.
View Data Paths You can view the data paths associated with the primary storage policy copy of the selected storage policy or incremental storage policy. You can also modify the data paths including their priority from the subclient level.
  1. From the CommCell browser, right-click the subclient.
  2. Click Properties.
  3. Click Storage Device.
  4. Select Storage Policy from the drop-down menu.
  5. Click Data Paths.
 
Configure Activity Control You can enable backup and restore operations from the agent and subclient level. However, you can enable restore operations only from the agent level.
  1. From the CommCell browser, right-click the subclient.
  2. Click Properties.
  3. Click Activity Control, select or clear option(s) as desired.
  4. Click OK.
Refer to Activity Control.
Configure User Security You can configure user security from the agent or instance level.

You can perform the following functions:

  • Identify the user groups to which this CommCell object is associated.
  • Associate this object with a user group.
  • Disassociate this object from a user group.
  1. From the Security tab of the associated Properties dialog box at the Agent or instance, select the appropriate user groups to which you want to associate to the CommCell object from the Available Groups pane, and then move the user group to the Associated Groups pane.
  2. Click OK to save your settings.
Refer to User Administration and Security.
Enable and Disable Data Encryption When you configure encryption at the client level, it is configured automatically for all the subclients associated with all the agents installed on that client. If you want to disable or change the encryption at the subclient level, follow the steps given below:
  1. From the CommCell browser, right-click the subclient.
  2. Click Properties.
  3. Click Encryption.
  4. Select the desired encryption.
  5. Click OK.
Refer to Data Encryption.
View Software Version and Installed Updates At the client level, the Version tab of the Properties dialog box displays the software version of the component.
  1. From the CommCell Browser, expand Client Computers.
  2. Right-click the appropriate client, and then click Properties.
  3. Select the Version tab.
  4. Click OK.
 
CommCell Configuration Report The CommCell Configuration Report provides the properties of the CommServe, MediaAgents, clients, agents, subclients, and storage policies within the CommCell based on the selected filter criteria.
  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Configuration.

    The Report Selection dialog box appears.

  3. Click Run.
Refer to CommCell Configuration.

Deleting an Agent, Instance, or Subclient

The following sections describe the steps involved in deleting an agent, instance, or subclient.

When you delete an instance or backupset, the associated data is logically deleted and you can no longer access the corresponding data from CommCell Console for recovery purposes.

For information about how to recover data if you delete an entity accidentally, see Recovering data associated with deleted clients and storage policies.

Deleting an Agent

You need to uninstall or DeConfigure the agent software from the client computer before deleting from CommCell Browser. After you delete the client software, you can either leave the corresponding data intact for appropriate action or you can remove the data immediately. If you choose to remove the data immediately, you must delete the agent from the CommCell Browser. If you delete the agent, all of the agent's data is irretrievably lost.

  • You cannot delete an agent while operations for that agent are running.
  1. From the CommCell Browser, navigate to Client Computers | <Client>.
  2. Right-click the <Agent>, and then click Delete.
  3. A confirmation message is displayed with the following message:

    This operation will permanently delete the data backed up from this level and it cannot be restored.

  4. Click OK to continue with the deletion operation or click No to abort the deletion.

Deleting an Instance

Consider the following before deleting an instance:

  • When you delete a specific instance all job schedules and job histories that pertain to any of the levels within the deleted instance are deleted.
  • You cannot delete an instance if it is being backed up. Attempts to delete an instance under such conditions cause the deletion to fail. If a backup is in progress, either wait for the backup to complete or kill the backup job using the Job Controller. Once the backup is no longer in progress, you can delete the instance level.
  • You cannot delete an instance if there is only one instance present for an agent. To delete the final instance, you must remove the agent software from the client computer.
  1. From the CommCell Browser, right-click the instance that you want to delete, click All Tasks and then click Delete.
  2. Click Yes to confirm the deletion. (Clicking No cancels the deletion and retains the node.)
  3. Type the requested phrase in the Enter Confirmation Text dialog box and click OK. This should delete the instance.

Deleting a Subclient

Consider the following before deleting a subclient:

  • You cannot delete a default subclient.
  • Schedules associated with the subclient are also automatically deleted.
  1. From the CommCell Browser, navigate to Client Computers | <Client> | <Agent> | <Instance>.
  2. Right-click the <Subclient> that you want to delete, and then click Delete.
  3. A confirmation message is displayed, asking if you want to delete the subclient.

    Click No to cancel the deletion and retain the subclient, or click Yes to continue the deletion.