Adding a MySQL Database Instance

When you need to manage a MySQL database, add an instance for the database.

When you add an instance, a default database group is automatically created. The default database group includes all the data in the MySQL database.

Note

  • You cannot delete the default database group.

  • The AD authentication for on-premise MySQL servers backup is not supported. The backup user needs to be created using mysql_native_password authentication method.

About This Task

When you discover instances, the newly discovered instances are assigned a plan as follows:

  • If the server is installed from the Command Center, then the newly discovered instances inherit the plan of the server.

  • If the server is not installed from the Command Center, then you must set a plan for the newly discovered instances.

Procedure

  1. From the navigation pane, go to Protect > Databases.

    The Instances page appears.

  2. Click Add instance > MySQL.

    The Add MySQL instance dialog box appears.

  3. From the Server name list, select the server where you want to create the instance.

  4. In the Instance name box, type a name for the instance.

  5. From the Plan list, select a server plan.

  6. In the Database user box, type the user name to access the MySQL application.

  7. In the Password box, type the password to access the MySQL application.

  8. If you are using a Linux platform, then in the UNIX username box, type the Linux operating system user name.

  9. In the Socket file box, enter the following details:

    • For Windows, select the TCP/IP port number to open the communication between MySQL server and clients.

    • For UNIX, type the path to the socket file or provide the endpoint in hostname:port format, where 'hostname' is the hostname of the client and 'port' is where mysql server is running on the client. Alternatively, click Browse to select the location for socket file.

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

  11. In the Log Data Directory box, enter the path to the directory where the log files are stored.

  12. In the Configuration file box, enter the path to the configuration file as shown in the following examples:

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

    • UNIX: /etc/my.cnf

  13. To enable SSL, move the Use SSL options toggle key to the right. In the SSL CA file box, enter the SSL CA file path in the client computer.

  14. To enable XtraBackup or Enterprise Backup on a Linux client, move the Enable hot backup toggle key to the right.

  15. Complete one of the following steps to enable XtraBackup or Enterprise Backup:

    • To use the XtraBackup utility for backup operations, select XtraBackup, and then enter the path to the folder that contains XtraBackup binaries.

    • To run a MySQL Enterprise Backup using SBT, select Enterprise backup, and then enter the path to the MySQL utility files that are required to enable enterprise backup. To avoid locking the non-InnoDB tables, select the No lock check box.

  16. Click Add.

Note

To avoid inconsistencies in the non-InnoDB data, lock the non-InnoDB tables while running a backup operation.

Configuring User Accounts for Backups

The MySQL agent 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

  • PROCESS

  • EXECUTE

  • SHOW VIEW

mysql> GRANT SHOW DATABASES, SUPER, RELOAD, SELECT, LOCK TABLES, PROCESS, EXECUTE, SHOW VIEW ON *.* to '<backup_agent_user>'@'localhost' IDENTIFIED BY '<backup_agent_password>';
mysql> GRANT SHOW DATABASES, SUPER, RELOAD, SELECT, LOCK TABLES, PROCESS, EXECUTE, SHOW VIEW ON *.* to '<backup_agent_user>'@'127.0.0.1' IDENTIFIED BY '<backup_agent_password>';
mysql> FLUSH PRIVILEGES;

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 navigation pane, go to Protect > Databases.

    The Instances page appears.

  2. Click the database instance for which you want to change the user account at the instance level.

  3. In the Overview tab, click Edit.

    The Edit MySQL instance dialog box appears.

  4. In the Connection details section, make the required changes.

  5. Click Save.

Loading...