---
title: "MySQL"
slug: "mysql-destination"
description: "Send your data from any source to MySQL easily with Dataddo! Create secure and reliable data flows in 3 steps. Get started with your data insights now."
updated: 2025-08-06T20:17:52Z
published: 2025-08-06T20:17:52Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dataddo.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL

**MySQL** is an open-source relational database management system. It is widely used for storing, managing, and retrieving structured data, making it a popular choice for web applications, content management systems, and various other software solutions.

:::(Info) (**DATADDO TIP**)
Dataddo supports **MySQL** as both a cloud-managed and an on-premises source or destination.
:::

## Prerequisites
:::(Error) ()
* You have a running instance of MySQL with public IP or hostname.
* You have configured a [MySQL User](/docs/mysql-destination#in-mysql) with `CREATE`, `SELECT`, `UPDATE`, `INSERT`, `DELETE` permissions.
* You have configured your network firewall to allow incoming connection from [Dataddo IPs](/docs/network-acl){target="_blank"}.
:::

## Authorize Connection to MySQL
### In MySQL
{{snippet.Destinations-Disclaimer}}

#### Create a MySQL User and Set Permissions

1. Log into **MySQL Shell**.
2. Run a command to create a new user. The command below presumes using root user for user creation, however, you can replace it with any user that has role for **creating the users and granting permissions**.
    ```Shell
    mysql -u root -p
    ```

    ```Sql
    CREATE USER 'dataddo'@'%' IDENTIFIED BY '***';
    ```
3. Grant the user the following permissions on your database: `CREATE`,`SELECT`, `UPDATE`, `INSERT, `DELETE`:

    ```Sql
    GRANT CREATE ON database_name.* TO 'dataddo'@'%';
    GRANT INSERT, UPDATE, DELETE ON database_name.* TO 'dataddo'@'%';
    FLUSH PRIVILEGES;
    ```
Make sure to replace the `database_name` value with your actual database.

#### Allow Network Connection
For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access **at the firewall level**. Here are the general steps:

1. **Identify the firewall** that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
2. Open the **firewall's configuration settings**. This process will vary depending on the type of your firewall.
3. Look for the **setting that allows you to define inbound rules or policies**.
4. Create **a new rule or policy** that allows inbound traffic on the port that the service is using (the default is **3306**) from the [Dataddo IP addresses](/docs/network-acl){target="_blank"}.
5. **Save** your changes and **test the connection from Dataddo** to make sure everything is working as intended.

### In Dataddo

1. In the **Authorizers** tab, click on [**Authorize New Service**](https://app.dataddo.com/service/new){target="_blank"} and select **Universal MySQL**.
2. In the **Universal MySQL** configuration page, click on Add new account.
3. You will be asked to fill the following fields
    1. **Server IP or Hostname**: Public IP or hostname of your MySQL Server.
    3. **Database**: Name of the database used for writing or reading data.
    4. **Username**: Username of your MySQL user.
    5. **Password**: Password to your MySQL user.
    6. **Port**: Port to connect to MySQL. The default value is **3306**.
    7. **TLS/SSL Settings**: Keep the value on **PREFERRED**, this will ensure using the SSL connection when available. If you are using a self-signed certificate, please use **SKIP VERIFY**.
4. [Optional] Configure your destination using an existing SSH tunnel or [create a new one](/docs/ssh-tunnelling){target="_blank"}.
5. Click on **Save**.

## Create a New MySQL Destination
 {{snippet.DestinationGeneral}}
 
## Create a Flow to MySQL
{{snippet.FlowGeneral}} 

### Table Naming Convention
When naming your table, please make sure the table name contains only:
* Letters
* Numbers
* Underscores
* Dollar signs

***

## Troubleshooting
### Incorrect String Value Error
```
Error 1366 (22007): Incorrect string value: '***' for column *** at row ***
```
This issue may be caused by **incorrect encoding in the target table or column**. To solve the issue, please follow these steps:
1. Ensure your table and column use the **utf8mb4** encoding to handle a wider range of characters.
2. Run the following query in your MySQL instance to fix the issue. Remember to replace `your_table_name` with the actual name of your table.
    ```sql
    ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ```
### Failed to verify certificate
```
pinging database server: tls: failed to verify certificate: x509: cannot validate certificate for 0.0.0.0 because it doesn't contain any IP SANs
```
For your own security we are by default enforcing the TLS connection to your MySQL Instance. The error indicates that the Transport Layer Security (TLS) protocol failed to verify the server's SSL/TLS certificate, specifically due to an issue validating the certificate with the x509 standard. This typically arises from a problem with the certificate's trust chain, expiration, or format.

In Dataddo MySQL Authorizer Set TLS/SSL Settings to `SKIP_VERIFY`.

### Data Too Long Error
```
Error 1406 (22001): Data too long for column '***' at row ***
```

This issue may is caused by attempting to **insert data that's too lengthy for the specified column's defined data type or length**. To solve the issue, please follow these steps:
1. Ensure the data you're inserting matches the column's allowed length or data type.
2. Run the following query in your MySQL instance to fix the issue. Replace `your_table_name` with your table's name, `your_column_name` with the column in question, `DATA_TYPE` with the desired type (e.g., VARCHAR), and `SIZE` with the new size limit.

```sql
ALTER TABLE your_table_name MODIFY your_column_name DATA_TYPE(SIZE);
```
    
### Cannot Change Write Mode to Upsert in Existing Flow
**ERROR MESSAGE**
```
"Action failed: stream transfer: write data from stream: do batch write: writeBufferedData: Failed to prepare table: checking unique constraints: missing UNIQUE INDEX for column: id"
```
This issue specifically occurs when you attempt to change the write mode to [{{glossary.upsert}}](/docs/data-storages#upsert){target="_blank"} for an existing flow to MySQL. Dataddo creates indexes to ensure smooth system operations, and to maintain the integrity of these operations, changes to the write mode in existing flows to MySQL are restricted. However, the following workarounds are available:

1. If you can **either lose the data in the existing table without consequences or recover it through backfilling**, we recommend deleting the table and the existing flow. Then, re-create the flow using {{glossary.upsert}} to ensure that the table and indexes are correctly established.
2. If **preserving the existing data is crucial**, consider these steps:
    1. Delete the existing flow.
    2. Edit the destination table to add a unique index on the columns you wish to include in the [composite key](/docs/data-storages#upsert){target="_blank"}. For example, you could use the SQL query like in the example below.
    3. Re-create the flow, selecting {{glossary.upsert}} write mode and a composite key that incorporates the column on which you created the unique index.

```sql
ALTER TABLE table_name
ADD UNIQUE INDEX unique_index_name (column1, column2);
```

{{snippet.Troubleshooting RelatedArticles Destinations}}