MariaDB Database Export and Import
Commands
# Export (does not lock tables with InnoDB; includes stored procedures, triggers, and scheduled events)
mariadb-dump --default-character-set=utf8mb4 --single-transaction \
--routines --triggers --events -u username -p mydb > mydb_20261006.sql
# Export and compress
mariadb-dump --default-character-set=utf8mb4 --single-transaction -u username -p mydb | gzip > mydb_20261006.sql.gz
# Import; the database must already exist
mariadb --default-character-set=utf8mb4 -u username -p mydb < mydb_20261006.sql
# Export only the specified tables
mariadb-dump --default-character-set=utf8mb4 --single-transaction -u username -p mydb posts users > mydb_tables.sql
Use Cases
Use this when backing up a database, migrating a host, or copying production data to a test environment. Specifying utf8mb4 for both export and import is safer and helps prevent Chinese text or emoji from becoming garbled.
Parameter Reference
| Parameter | Description |
|---|---|
--default-character-set=utf8mb4 |
Specifies the character set used for the connection |
--single-transaction |
Exports an InnoDB consistent snapshot without locking tables |
--routines, --events |
Also exports stored procedures and scheduled events, which are excluded by default |
--triggers |
Exports triggers; enabled by default, but specifying it makes the behavior explicit |
-p |
When no password is provided, prompts for it after execution |
Common Pitfalls
- Importing may overwrite tables with the same name: Export files include
DROP TABLE IF EXISTSby default. Back up the target database before importing. - Do not put the password in the command:
-p'password'remains in the shell history and may also appear in the process list. For scheduled tasks, put the credentials in a configuration file with permission mode 600 and specify it with--defaults-extra-file. - Garbled text in Windows PowerShell: Do not use
>for exports. See Garbled Text When Exporting MySQL in PowerShell. - Import fails with an older client: Newer MariaDB export files have a sandbox mode marker on the first line, which older clients may be unable to parse.