How to Export to MySQL

How to Export to MySQL

Info
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 data table in Data Builder, 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 as a paid add-on on the Business, Grow, and Pro plans, and are included with the Custom plan when your negotiated agreement includes data exports.

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 Builder data table you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Builder data table 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 data table in Data Builder 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 data table in Data Builder to remove an existing field, this field's corresponding column will be dropped from the table in MySQL the next time the export runs.
Alert
Be careful: when dropping a column, historical data for the dropped fields will be deleted.

Requirements

  1. A plan that includes data exports (available as a paid add-on on the Business, Grow, and Pro plans; included with the Custom plan when your agreement includes data exports)
  2. Create the Data Builder data table(s) you want to export
  3. A MySQL server with at least one database
  4. 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 data table in Data Builder, click the three-dots action menu and select Edit.



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 Changes to save the report.
Go to Exports > SQL and click + Create export. The Create export wizard opens and walks you through four steps: Destination, Data, Schedule, and Review.

Under Destination, select your MySQL server if it is already connected. If you haven't yet connected your server, click the plus button to add a destination account:
  1. For Type, select MySQL.
  2. Enter your MySQL server's Host (IP address or host name) and Port (default: 3306 for MySQL).
  3. Enter your MySQL Username and Password.
  4. Under Name, enter a nickname to use for your MySQL server, then click Connect. Once the server is added, return to the wizard and restart the export setup.
Info
The Help panel in the Connect SQL dialog lists the IP addresses PMA uses to interact with your database: 35.188.118.242 and 35.209.185.69. Make sure both are whitelisted on your server.
For Database name, enter the name of the database you want to use on your MySQL server. A table will be created with the same name as your Data Builder data table, with any spaces replaced by underscores (_). Then click Next.

Under Data table or dataset, select either the single data table or the entire dataset you want to export (exporting a dataset sends every data table in it). Then click Next.

Next, choose a Refresh period. Since this export is being used for the initial backfill, select On demand and leave Run as soon as you create the export enabled. An On demand export has no schedule and runs only when you run it manually from the exports list. (For the scheduled refresh periods, the Timezone is required and determines the local wall-clock time at which your export will run.) Click Next.

Under Review, check the Destination, Data, and Schedule summaries; click Edit on any card to make a change. Then click Create export. Because Run as soon as you create the export was enabled, your initial backfill begins immediately.

To confirm the export was successful, find your new export on the Exports to SQL page, open the three-dot action menu at the end of its row, and select View logs. You can run the export manually at any time by selecting Run export now from the same menu.

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 Builder and find your data table. Click the action menu and select Edit.



Go to Available Data Range and choose a rolling window for updates to your data table, 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.


Click Save Changes. 



Go back to Exports > SQL. Open your export's three-dot action menu and select Edit export.

Under Schedule, 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, 1 through 28), day of the week (for Weekly), and hour of the day (for Monthly, Weekly, and Daily). Confirm or update your Timezone, which determines the local wall-clock time at which your export will run. Then save your changes.
Make sure your data table's date range in Data Builder is at least as long as the refresh period in your export. Failing to do so may result in data missing from your backfill. For example:
  1. If your data table'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 data table'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.