build Nyrdex

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 EXISTS by 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.

Related Links

← All runbooks