Mysqldump

Overview

This guide explains how to use the mysqldump command to back up a MySQL or MariaDB database and how to restore it from an SQL dump file. You’ll learn:

• How to export a full database or a single table
• How to restore from a SQL dump
• Advanced usage, including restoring stored procedures and working around common errors

What is mysqldump?

mysqldump is a utility that creates a logical backup of one or more databases by generating a file of SQL statements. These statements recreate the database structure and data exactly as it was at the time of the backup.

You can view or edit the dump file with any text editor. These SQL files are portable, even across different MySQL-compatible systems.

Basic usage

Backup a full database
To take a full backup of the database, you can use the following mysqldump command:

mysqldump database_name > database_name.date.sql

Backup a single table
To take a backup of the single table from the database, you can use the following mysqldump command:

mysqldump database_name table_name > database_name.table_name.date.sql

Restore a database
To restore/import an SQL dump file, you can use the following mysql command:

mysql database_name < file_name.sql

This command recreates the database from the dump file.

Using a specific user (when you don’t have root access)

mysqldump -u db_user -p database_name > database_name.date.sql

This prompts for a password. Avoid passing passwords directly in the command to protect sensitive information.

Dumping a crashed database

mysqldump --force crashed_database > crashed.database_name.date.sql

Additional customizations via flags

The default mysqldump command uses –opt by default. This is the same as:

mysqldump --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

If you would like to override any of these default flags you will need to use –skip-opt. If you do you will need to add back the flags you want.

mysqldump --skip-opt --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

This command will not drop a table, but instead if it encounters a table that already exists in the database it will not import.

Dump over SSH

From local to remote:

mysqldump db_name | ssh user@host "mysql db_name"

From remote to local:

ssh user@host "mysqldump db_name" | mysql db_name

Dump to a file on remote:

mysqldump db_name | ssh user@host "cat > /root/db_name.sql"

With progress bar (requires pv):

mysqldump db_name | pv | ssh user@host "mysql db_name"

Fixing “Trigger in Wrong Schema” errors during database restore

When restoring a MySQL or MariaDB database from a .sql file, you might encounter the following error:

ERROR 1435 (HY000): Trigger in wrong schema

This happens when the SQL dump includes hardcoded database names, and you’re restoring it into a different database.

The Problem

This works fine if you are restoring the SQL file into a database with the original database name. However, you may see a “trigger in wrong schema” error when the two following conditions are met:

1. You are restoring the SQL file into a new database that has a different database name.
2. The INSERT statements include both the database_name and table_name, rather than just the table_name.

An Example

For example, there would be no problem with this INSERT statement regardless if it is the original database or a new database:

INSERT INTO account VALUES (137,14),(141,1937),(97,-100);

In the above, only the table named account is mentioned.  However, if the data originally came from a database named ‘db’ and was being imported into a new database named ‘newdb’ -> the restore will fail with the “trigger in wrong schema” message. 

The following SQL file would fail having these INSERT statements:

INSERT INTO `db`.`account` VALUES (137,14),(141,1937),(97,-100);

In the above, the trigger is trying to be created in ‘newdb‘ but the data was restored via the INSERT statement to the old database.

The Solution
To get around this, the SQL file needs to be modified and all of the references to the old database need to be updated to reflect the name of the new databases. This is typically done via sed as a search-and-replace function.   With the older references fixed in all of the INSERT statements the trigger(s) will be created successfully.

Use sed to Replace the Database Name

sed -i 's/`old_db`/`new_db`/g' dumpfile.sql

Or, without backticks:

sed -i 's/old_db/new_db/g' dumpfile.sql

This ensures all hardcoded references in INSERT, TRIGGER, and other statements are aligned with the new database name.

Need to check for triggers?
Use the following to check if triggers exist in a database:

mysql -e 'SHOW TRIGGERS FROM database_name;'

Summary

This guide explains how to use the mysqldump command to back up and restore MySQL or MariaDB databases, covering both full database exports and single table dumps. It includes instructions for restoring from SQL files, using custom flags, working without root access, and transferring data over SSH. The article also addresses advanced scenarios like dumping crashed databases and fixing common restore errors, specifically the “trigger in wrong schema” error, which occurs when hardcoded database names conflict with a new target database. A simple sed command is provided to update old database references, ensuring smooth trigger creation and a successful restore.