B.4 Backing up MySQL Enterprise Service Manager

All of the MySQL Enterprise Monitor repository information is stored within a set of databases, one is simply named "mem", and the others are prefixed by "mem__", with a suffix denoting the type of data stored, such as "mem__config", "mem__instruments", "mem__events", and so on.

To back up this information using the mysqldump command, first you can use the following command to generate the list of schema names into the file /tmp/mem_schemas.


shell> mysql [connection args] [user/pass args] --batch -e "SELECT group_concat(SCHEMA_NAME separator ' ') 
       FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mem' 
       OR SCHEMA_NAME LIKE 'mem\_\_%'" > /tmp/mem_schemas

Once done, a command like the following will use that list of schema names to perform a backup:


shell> mysqldump --single-transaction -uservice_manager -pPassword -P13306 -h127.0.0.1 $(< /tmp/mem_schemas ) > mem.dump

The above command creates a file, mem.dump, containing all of the MySQL Enterprise Monitor data.

To ensure consistency in a recovery situation, you may also want to back up the agent configuration and metadata stored on each monitored MySQL server. To do this:

In case the backup process makes the Service Manager unresponsive for a time, you might also adjust the agent-backlog-timeout setting as appropriate, to avoid losing data from the agents during a backup of the repository database.