MySQL is a popular open source relational DBMS.
RudderStack supports MySQL as a source from which you can ingest data and route it to your desired downstream destinations.
RudderStack requires you to grant certain user permissions on your MySQL database to successfully access data from it.
Run the SQL queries listed in the following sections in the exact order to grant these permissions:
- Create a new user
RUDDERwith a password
<strong_unique_password>in MySQL, as shown:
CREATE USER RUDDER IDENTIFIED BY '<strong_unique_password>';
- Create a dedicated schema
CREATE SCHEMA _rudderstack;
_rudderstackschema is used by RudderStack for storing the state of each data sync. This name should not be changed.
- Grant full access to the schema
_rudderstackfor the user
GRANT ALL PRIVILEGES ON _rudderstack.* TO RUDDER;
To set up MySQL as a source in RudderStack, follow these steps:
- Log into your RudderStack dashboard.
- From the left navigation bar, go to Source > New Source > Reverse ETL. Then, select MySQL.
- Assign a name to your source and click Continue.
- Enter the relevant connection details in the Connection Credentials, as shown:
- Host: Enter the host name of your MySQL service.
- Port: Enter the port number of your MySQL service.
- Database: Enter the database name in your MySQL instance where the data is loaded.
- User: Specify the username which has the required read/write access to the above database.
- Password: Enter the password for the above user.
- Click Continue. RudderStack will then verify and validate your credentials.
- Once verified, click Continue to proceed.
- Specify the Schedule Settings to schedule the data syncs from your MySQL instance.
RudderStack lets you schedule data syncs for your Reverse ETL sources and specify how and when the syncs will run. For more information on the Basic, CRON, and Manual schedule types, refer to the Sync Schedule Settings guide.
- After specifying the schedule type and run settings, click Continue to finish the setup.
MySQL is now successfully configured as a source in your RudderStack dashboard. You can further connect this source to your preferred destination by clicking on Add Destination button, as shown:
- If you have already configured a destination in RudderStack, select the Use Existing Destinations option which will take you to the Schema tab in the source settings.
- To add a new destination from scratch, select the Create New Destination option which will take you to the destination configuration page.
While connecting your MySQL source to a destination, you can configure the data to be imported using any one of the following methods:
When setting up a Reverse ETL source, once you proceed after entering the connection credentials, you will see the following three validations under the Verifying Credentials option:
These options are explained below:
- Verifying Connection: This option indicates that RudderStack is trying to connect to the warehouse with the information specified in the connection credentials.
If this option gives an error, it means that one or more fields specified in the connection credentials are incorrect. Verify your credentials in this case.
- Able to List Schema: This option checks if RudderStack is able to fetch all the schema details using the provided credentials.
- Able to Access RudderStack Schema: This option implies that RudderStack is able to access the
_rudderstackschema you have created by successfully running all the commands in the User Permissions section.