Check and Back Up the Databases › Check the MySQL Databases

Check the MySQL Databases

When you upgrade to CA Network Flow Analysis 9.1.3, the data from several MySQL databases is migrated after the software upgrade is complete. Data migration consists of saving the data to newly formatted MySQL database tables. Data migration may be a lengthy process, which is likely to fail if any of the existing database tables are corrupt.

We recommend that you check the database tables before migration to correct problems in database tables, avoid a migration failure, and avoid stepping through recovery assistance with CA Support. This topic describes how to run the mysqlcheck command before the upgrade to verify that the database tables are set up properly for migration.

You can run the mysqlcheck command to check the following pre-upgrade databases:

• reporter:* Located on each standalone or NFA console server (upgrade from 9.0.1, 9.1.00, or 9.1.1)
• harvester: Located on each standalone or Harvester server (upgrade from 9.0.1, 9.1.00, or 9.1.1)
• poller: Located on each standalone or Harvester server (upgrade from 9.1.00 or 9.1.1)
• data_retention: Located on each CA Network Flow Analysis component server (upgrade from 9.1.00 or 9.1.1)
• nqrptr:* Located on each DSA server (upgrade from 9.0.1)

  * Database that typically contains some large tables

Checking large database tables can be time-consuming. If you run the check on an entire database, each table in the database is locked in read-only state and is unavailable for write operations. If you run the check on single tables, the other tables in the database remain write-accessible.

You can run mysqlcheck without stopping MySQL: The MySQL deamon process (mysqld) can continue to run on Linux servers and the MySQL service can continue to run on Windows servers.

Follow these steps:

1. Log in to one of the CA Network Flow Analysis servers as a user with administrator privileges. On a Linux Harvester server, log in as root.

   Upgrade from 9.0.1:

   • Standalone server: Check the harvester, reporter, and nqrptr databases.
   • Harvester server (distributed deployment): Check the harvester database.
   • NFA console server (distributed deployment): Check the reporter database.
   • DSA server (distributed deployment): Check the nqrptr database.

   Upgrade from 9.1.00 or 9.1.1:

   • Standalone server: Check the harvester, reporter, poller, and data_retention databases
   • Harvester server (distributed deployment): Check the harvester, poller, and data_retention databases
   • NFA console server (distributed deployment): Check the reporter database
2. Enter one of the following mysqlcheck commands at a command or shell prompt:
   • To check the tables in all of the applicable databases on the server:

     mysqlcheck --all-databases
     

   • To check all of the tables in a single database:

     mysqlcheck --databases db_name
     

     Example:

     mysqlcheck --databases reporter
     

   • To check a single database table:

     mysqlcheck db_name [tbl_name]
     

     Example:

     mysqlcheck reporter [reporter.harvesters]
     

   where:

   db_name = Name of the database that you want to check

   tbl_name = Name of any table that you want to check individually in a database

   You do not need to specify the path to the database. The mysqlcheck command will find any or all databases that use the default port (port 3308). The custom storage engine does not support the use of the mysqlcheck command for its archive and archive15 databases. The command fails to run even if you specify the correct port (port 3307) for the connection to these databases.

   The command checks each table, attempts to repair any problems, then analyzes and optimizes the table. The command returns a list of the database tables that were checked and reports the status for each table.

   If the table passed the check, "OK" follows the table name. If a warning is returned and is followed by "OK," the problem was resolved. If unresolved errors occur, contact CA Support.

Next: Stop the services, then back up the databases, as described in the following topics.