Administering the database

This topic provides the procedures to administer the MySQL database installed with BMC Application Diagnostics Server components, including for changing the MySQL root user password and backing up the the database data.

The following topics are presented:

• • Changing the MySQL root user password
  • Backing up the existing database
  • Recovering database data
  • Configuring data retention
  • Optimizing database tables

Changing the MySQL root user password

On each computer with a BMC Application Diagnostics Portal or Collector component, you can modify the MySQL root user password. The password is required when you need to backup, restore, and perform other maintenance tasks on the database.

If the BMC Application Diagnostics Portal and the BMC Application Diagnostics Collector are installed on the same host, both the portal and collector databases are affected by changing the password, regardless of which script you use to change the password.

To change the MySQL root user password

1. At a command line, navigate to the following directory:
   • Windows
     <installationDirectory>\portal\bin\db
     or
     <installationDirectory>\collector\bin\db
     The default <installationDirectory> is C:\Program Files\BMC Software\BMC Application Diagnostics
   • Linux
     <installationDirectory>\portal\bin\db
     or
     <installationDirectory>\collector\bin\db
     The default <installationDirectory> is /opt/bmc/BMC_Application_Diagnostics
2. Enter the following command:
   • Windows
     change-db-root-password.bat
   • Linux
     change-db-root-password.sh
3. At the Enter password prompt, enter the current password. The default password is admysql.
4. At the New password prompt, enter a new password, using a strong password that comprises the following:
   
   • A minimum of 10 characters
   • Two of the following nonalphabetic characters that are noncontiguous:
     0 1 2 3 4 5 6 7 8 9 ~ ! @ # $ % ^ & * ( ) _ + - =
5. At Confirm password, enter the new password again.

Backing up the existing database

BMC recommends that you make a physical backup of the database data files. The output of a physical backup contains data files that the MySQL server can use directly, resulting in a faster recovery operation.

Before you begin

Ensure that you have upgraded the MySQL databases or installed Service Pack 1.

To create a database backup file

1. On the computer with the BMC Application Diagnostics Server component (Portal or Collector), navigate to serverInstallationDirectory\collector\bin\db
2. Run the following script:
   
   • (Windows) create-mysql-dump.bat
   • (Linux) ./create-mysql-dump.sh

The script creates a backup of the data in the ad-mysql-dump.sql file.

For more information about making MySQL backup files, see Database Backup Methods.

Recovering database data

In case of a database issue, you can recover the backed up data file.

To recover the backed up data file

1. On the computer with the BMC Application Diagnostics Server component (Portal or Collector), navigate to the following directory, according to your system:
   
   • (Windows)  serverInstallationDirectory\ADOP_DB\mysql5.6.14\bin
   • (Linux)  serverInstallationDirectory/ADOP_DB/mysql5.6.14/bin

2. Run the following command:

   mysql -u root -p -h localhost < ad-mysql-dump.sql
3. Enter the MySQL root user password.

The database data is recovered.

For more information about how to recover the database data, see recovering a database.

Configuring data retention

Data retention is set during installation of the BMC Application Diagnostics Collector. You can change the data retention configuration anytime by changing the values of the following properties in the collector.properties file:

• retention.time — The period of time, in days, to retain data in the database
• db.max.size — The maximum size of the database in megabytes (MB). Default: 100000MB = 100GB

Optimizing database tables

To re-index database tables, use the optimize tables script. Due to the way in which BMC Application Diagnostics works with the database, you rarely need to run this script. You should run it only if performance degrades and analysis shows that the reason is fragmented indexes.