Check and Back Up the Databases › Check the MySQL Databases

Check the MySQL Databases

We recommend that you check the database tables before upgrading. Checking the database tables corrects some problems and helps to avoid failures and 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.

You can run the mysqlcheck command to check the following databases:

• reporter: Located on each stand-alone or NFA console server (which typically contains some large tables)
• harvester: Located on each stand-alone or Harvester server
• poller: Located on each stand-alone or Harvester server
• data_retention: Located on the following servers: Stand-alone, Harvesters, and DSAs
• nsas: Located on the CA Anomaly Detector server, which can be the stand-alone server or NFA console server

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 sequentially. The table that is being checked is unavailable for write operations.

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.
2. Check the following databases:
   • Stand-alone server: harvester, reporter, poller, and data_retention databases
   • Harvester server (distributed deployment): harvester, poller, and data_retention databases
   • NFA console server (distributed deployment): reporter database
   • DSA server (distributed deployment): nqrptr database
   • CA Anomaly Detector server: nsas database (if CA Anomaly Detector is installed on the stand-alone server or the NFA console server
3. 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
     

   where:

   db_name = Name of the database that you want to check

   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 return text lists 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.