How to Export to MySQL

How to Export to MySQL

Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Explorer, you're ready to export your data to your MySQL server. With our MySQL export feature, you can run complex queries over your data and integrate your data with a variety of applications. MySQL exports are available in the Enterprise plan.

How We Store Data in MySQL Exports

Creating a table

Our MySQL export feature will create a table with the same name as the Data Explorer report you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Explorer report being exported.

Appending and updating rows

Unlike BigQuery exports, exports to MySQL are not partitioned by date. Instead, new data from the export is simply appended to the table, while any changed data will be updated in the affected rows.

Adding columns

When you edit the report in Data Explorer to add another field, this field will be added to the table in MySQL as a new column the next time the export runs. This column will be populated with the field's values going forward. Historical rows from before the new column was added will have the new field's value set to null.

Dropping columns

When you edit the report in Data Explorer to remove an existing field, this field's corresponding column will be dropped from the table in MySQL the next time the export runs.
Be careful: when dropping a column, historical data for the dropped fields will be deleted.

Requirements

  1. An Enterprise subscription
  2. A MySQL server with at least one database
  3. Your MySQL credentials:
    1. Hostname or public IP address of your server
    2. Port (default: 3306)
    3. Username
    4. Password
    5. Name of database to use
If you don't currently have a MySQL server, you can create one using Google's Cloud SQL Instance feature.

Creating a Cloud MySQL Instance

Follow Google's instructions to create a Cloud MySQL instance. Remember the password you choose for the "root" username during this step, as this will be needed when setting up your MySQL export.

When customizing your instance, we recommend the following minimum specifications:
  1. 3.75 GB memory
  2. SSD storage
  3. 100 GB storage capacity is usually enough (customers with many accounts may require more)
  4. If in the European Union: Select a European region for your Cloud MySQL instance in order to meet GDPR requirements

Under Connections, check Public IP and add PMA's IP ranges as authorized networks:
  1. 35.188.118.242/32
  2. 35.209.185.69/32
When accessing your MySQL server from any additional location, that IP range must also be added as an authorized network.



Follow Google's instructions to create a database with the name of your choice. Remember the name of your database, as this will be needed when setting up your MySQL export.



Set Up Your Initial Backfill and Rolling Updates

Initial backfill

The first step is to backfill your historical data to your MySQL server. You can backfill data for up to 2 years for most PMA connectors. In your report in Data Explorer, set the date range to the entire range of data you want to export. To backfill your data from a specified start date up to yesterday, choose Custom start to date and enter a start date.



Click Save to save the report.



Go to MySQL under Exports and click the Create Export button.



Under Destination, select MySQL.



Click the blue + to add a MySQL destination. 



For Type, select MySQL.



Enter your MySQL server's hostname, port, and your username and password.



Next, enter a nickname to use for your MySQL server.



Then click the Connect button.



Under Destination, select your MySQL server.



For database, enter the name of the database you want to use on your MySQL server. A data table will be created with the same name as your Data Explorer report, with any spaces replaced by underscores (_).



For Report or Report Group, choose whether to report a single Data Explorer report or a group of reports.



Then choose the report(s) you want to export, and click Next.



Next, choose how frequently you want your data export to run. Since this export is being used for the initial backfill, and the export will be run manually in the next step, this can be set to monthly. You can select any day and hour.



Then click Add. Your new export will appear under Exports. Click the play icon to run export now.



Your export will begin running, as indicated by the three dots icon.



When the three dots change back to a play button icon, the export has finished running. You can confirm the export was successful by clicking on the paper icon to view logs.




Rolling Updates

Once your initial backfill has run, it's time to set up rolling updates for your report. This will keep your MySQL export updated with the latest data from your report.

Go to Data Explorer and find your report. Click the pencil icon to Edit Report. 




Go to Date Range and choose a rolling window for updates to your report, such as yesterday, last 7 days, or last 30 days. A range of 30 days can be an ideal choice for e-commerce to settle orders that are returned.



Then click Save.



Go back to MySQL under Exports. Find your export and click the pencil icon to Edit Export.



Go to Schedule and choose how frequently you want your data export to run: monthly, weekly, daily, or hourly. You will be prompted to choose a day of the month (for monthly), day of the week (for weekly), and hour of the day (for monthly, weekly, and daily). Then click Update.



Make sure your report's date range in Data Explorer is at least as long as the refresh period in BigQuery. Failing to do so may result in data missing from your backfill. For example:
  1. If your report's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
  2. If your report's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.
Your export will now run automatically at the scheduled time.

    Still having issues?
    Send us a ticket and we will get back to you.
    Submit a ticket

      • Related Articles

      • How to Use the SQL Connector

        Power My Analytics now offers an SQL connector in addition to our Hosted SQL solution. This article will help you get started with using our SQL connector and set up a MSSQL or MySQL query. Connect to your server in the Hub Select Sources in the left ...

      • Power My Analytics' Data Warehouse Solution

        Power My Analytics offers a Hosted MySQL solution with your Enterprise plan. This article will help you learn about its uses and how to get started. With our Hosted MySQL solution, you can achieve GDPR compliance by setting up your data warehouse ...

      • How to Create an FTP Export

        FTP can be a valuable way for Enterprise users to export their Data Explorer reports. In this article, we will walk you through creating an FTP export, including selecting an FTP destination, scheduling data refreshes, and managing your exports to ...

      • BigQuery Export Limitations

        Your data exports to BigQuery may encounter errors in certain situations such as large export sizes. This article will show you how to identify and correct errors with your BigQuery exports. Export Limitations If you are using connectors other than ...

      • How to Create a Report in Data Explorer

        Data Explorer is a valuable tool that can be used to create reports for export to BigQuery or FTP , to test out a connection, or even to create a CSV file. This tool is available to all Power My Analytics customers and is included in the cost of your ...