# How to import and export a MySQL database

We always recommend to dump and import through the command line, if possible. This documentation will
explain how to do this securely.

## MySQL export

For most databases (only a couple of gigabytes big), you can dump the database with this command:

```bash
mysqldump --single-transaction --triggers --routines --events DBNAME > DBNAME.sql
```

For larger databases (tens of gigabytes or bigger), compress the database to conserve disk space and
speed up transfer:

```bash
mysqldump --single-transaction --triggers --routines --events DBNAME | gzip -3 -v > DBNAME.gz
```

## Transferring the database

Use `scp` to transfer the file:

```bash
scp FileName user@HostnameOrIP:Path/To/Folder
```

> [!NOTE]
> To place the file in the user's home directory, remove everything after the colon.

## Importing the database

```bash
mysql DBNAME < DBNAME.sql
```

Or, if compressed:

```bash
gunzip -c DBNAME.gz | mysql DBNAME
```

> [!NOTE]
> Large databases can take time to import, especially on high-load servers.

## Extra tips

### Nohup

Use `nohup` to ensure the dump or import continues if the connection is lost.

### Screen

Use `screen` to allow session sharing or recovery:

- Create a session:

  ```bash
  screen -S <session_name>
  ```

- Disconnect (keep running):

  ```
  Ctrl + A, then D
  ```

- Reattach or take over session:

  ```bash
  screen -dr <session_name>
  ```

- List sessions:

  ```bash
  screen -ls
  ```

### SSH key

Avoid password prompts by setting up SSH key authentication.

## Fixes for potential errors

### MySQL ERROR 1227 (42000)

> *Access denied; you need (at least one of) the SUPER privilege(s) for this operation*

This is caused by `routines`, `views`, or `triggers` defined with a definer the user cannot access.

**Solution:** Strip the definer using `sed`:

```bash
mysqldump --single-transaction --triggers --routines --events DBNAME | sed -e 's/DEFINER=[^*]*\*/\*/g' > DBNAME.sql
```

**Or with compression:**

```bash
mysqldump --single-transaction --triggers --routines --events DBNAME | sed -e 's/DEFINER=[^*]*\*/\*/g' | gzip -3 -v > DBNAME.gz
```

**If you already have the file:**

```bash
cat DBNAME.sql | sed -e 's/DEFINER=[^*]*\*/\*/g' | mysql DBNAME
```

**Compressed file:**

```bash
gunzip -c DBNAME.gz | sed -e 's/DEFINER=[^*]*\*/\*/g' | mysql DBNAME
```

### MySQL ERROR 1118 (42000)

> *Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.*

Caused by limits on row size when using `ROW_FORMAT=COMPACT`.

**Fix during import:**

```bash
cat DBNAME.sql | sed -e 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' | mysql DBNAME
```

**Compressed file:**

```bash
gunzip -c DBNAME.gz | sed -e 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' | mysql DBNAME
```

## Related

- [Configure MySQL](configure.md)
- [Create and manage database users](manage-database-users.md)
- [Fix character set and collation](fix-charset-and-collation.md)
- [Backups and restore](../../platform/hosts/backups.md)
- [SSH access](../../platform/hosts/ssh.md)
- [Database problems](../../troubleshooting/database-issues.md)