MicroStrategy ONE

MySQL Maintenance

Since Platform Analytics stores telemetry in the Platform Analytics MySQL Repository, it's important to maintain your MySQL database. There are four recommended ways to maintain your database:

Backup Your MySQL Database

You can quickly backup and restore your MySQL databases on your server by downloading the backup tool mysqldump. This tool is located in the root/bin folder of the MySQL installation folder.

mysqldump allows you to dump databases for backup or transfer database to another database server. The dump file contains a set of SQL statements to create database objects.

The basic syntax for backing up the database is:

Copy 
mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

Where:

[username] is a valid MySQL username.

[password] is a valid password for the user. There is no space between –p and the password in the command.

[database_name] is the database name you want to backup. For Platform Analytics, the database name is platform_analytics_wh.

[dump_file.sql] is the dump file you want to generate.

You can modify the syntax depending on the information you want to backup.

To only backup the structure, add -no-data to the syntax:

Copy 
mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql]

To only backup data, add -no-create-info to the syntax:

Copy 
mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql]

For more information on the database backup program, see Backup and Recovery.

Replicate Your MySQL Database

Replication allows data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). There are several benefits to replication, such as the ability to isolate read/write load to improve performance, perform backups on one database without risk of corruption, or create a local copy of data for remote use.

Typical replication requires synchronization between the master and slave. There are two types of synchronization:

  • Asynchronous Replication

    Replication is asynchronous by default. This type of synchronization is one-way, where one server acts as the master and the other server or servers act as the slaves.

  • Semi-Synchronous Replication

    With semi-synchronous replication, a commit performed on the master blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction.

In either case, you can configure your system so that Platform Analytics Consumer writes to the master and the Intelligence server reads data from one of the replicas. This is useful for systems with heavy read/write load and if you have several custom cubes created using the Self Service Schema in the Platform Analytics project.

For more information on replication, see Replication.

Secure Your MySQL Database

There are general factors that should be considered to secure your MySQL database. Review the general security issues outlined in the MySQL documentation. Additionally, after installing MySQL, it's recommended to perform post-installation security testing. For more information, see Postinstallation Setup and Testing.

Finally, general access control and security should be prioritized. For information about account management, see Access Control and Account Management. If you've lost your MySQL root password, see Reset you Root Password.

Upgrade Your MySQL

It is a best practice to upgrade your MySQL with the latest bug fixes. Additionally, upgrades provide the latest features offered between releases of new MySQL. To have a seamless upgrade, see Upgrading.