mysqldump

Create a backup (user will be prompted for a password)

$ mysqldump --user [user] --password [database_name] --result-file=[path/to/file.sql]

$ mysqldump --user [user] --password [database_name] [table_name] > [path/to/file.sql]

$ mysqldump --user [user] --password --all-databases > [path/to/file.sql]

$ mysqldump --host=[ip_or_hostname] --user [user] --password --all-databases > [path/to/file.sql]

mysqldump [options] [db_name [tbl_name ...]]

Examples:
mysqldump -u root -p --all-databases > all_databases_backup.sql
mysqldump -u user -p mydatabase > mydatabase_backup.sql
mysqldump -u user -p mydatabase table1 table2 > selected_tables_backup.sql
mysqldump -u user -p --single-transaction mydatabase | gzip > mydatabase_backup.sql.gz

-u user, --user=user
    The MySQL user to connect as.

-p[password], --password[=password]
    The password for the specified user. If omitted, the user will be prompted for a password. Providing it directly in the command line is generally insecure.

-h host, --host=host
    The host name or IP address of the MySQL server to connect to.

-P port_num, --port=port_num
    The TCP/IP port number to use for the connection.

-A, --all-databases
    Dump all databases. This option will cause all databases on the server to be included in the output.

-B db_name ..., --databases=db_name ...
    Dump several databases. The names are listed following the option, separated by spaces.

-d, --no-data
    Dump only the database schema (table creation statements), without any row data.

-t, --no-create-info
    Dump only the row data, without any table creation statements (schema).

--single-transaction
    Creates a consistent snapshot by issuing a START TRANSACTION statement before dumping data. This option is useful for InnoDB tables as it avoids locking the tables during the dump, allowing other connections to continue operations. It does not work for MyISAM or MEMORY tables.

-R, --routines
    Include stored routines (procedures and functions) in the dump output.

--triggers
    Include triggers for tables in the dump output. (This is enabled by default unless --no-create-info is used).

-E, --events
    Include scheduled events in the dump output.

--master-data[=value]
    Records the binary log position and file name in the output, which is useful for setting up a replication slave. value=2 (default) adds the position as a commented line; value=1 adds it as an executable SQL statement.

--add-drop-database
    Add a DROP DATABASE IF EXISTS statement before each CREATE DATABASE statement in the output.

--add-drop-table
    Add a DROP TABLE IF EXISTS statement before each CREATE TABLE statement in the output. (This is enabled by default).

--ignore-table=db_name.tbl_name
    Do not dump the specified table. This option can be used multiple times.

--where="condition"
    Dump only rows selected by the given WHERE condition. The condition must be quoted.

-C, --compress
    Compress all information sent between the client and the server.

mysqldump is a command-line utility used for backing up MySQL and MariaDB databases. It exports a database's schema and data as SQL statements, which can then be used to recreate the database or restore it to a previous state. This makes it an invaluable tool for disaster recovery, database migration, and cloning.

The output generated by mysqldump typically consists of CREATE DATABASE, CREATE TABLE, and INSERT statements, along with other schema definitions like views, stored procedures, functions, events, and triggers. By default, it adds DROP TABLE IF EXISTS before each CREATE TABLE statement, ensuring a clean slate upon restoration.

It can dump entire database servers (all databases), specific databases, or even selected tables within a database. For transactional storage engines like InnoDB, mysqldump can leverage options like --single-transaction to perform a consistent backup without locking tables, which is crucial for busy production environments. For MyISAM tables, however, full table locks are typically required during the dump process.

While mysqldump is versatile, it has limitations, especially for very large databases. Performing a dump can be I/O intensive and time-consuming, potentially blocking writes (for MyISAM tables) or causing high load during the process. For massive databases, physical backup methods like Percona XtraBackup or MariaDB's MariaBackup are often preferred for their speed and ability to perform hot backups without blocking writes on any storage engine. Additionally, be cautious when using the -p option by providing the password directly on the command line, as it can expose credentials in process lists.

mysqldump creates logical backups (SQL statements), which are flexible for cross-version compatibility and schema manipulation. To restore a backup, you typically pipe the SQL file back into the mysql client:
mysql -u user -p database_name < backup.sql
For restoring all databases from an --all-databases dump:
mysql -u root -p < all_databases_backup.sql
For large dumps, it's often more efficient to use source command within mysql client or ensure sufficient memory.

To save disk space, the output of mysqldump can be directly piped to a compression utility like gzip or bzip2:
mysqldump -u user -p mydatabase | gzip > mydatabase_backup.sql.gz
To restore from a compressed file:
gunzip < mydatabase_backup.sql.gz | mysql -u user -p mydatabase

To exclude specific tables from a database dump, use the --ignore-table option. This is particularly useful for excluding large log tables or caches:
mysqldump -u user -p mydatabase --ignore-table=mydatabase.log_table --ignore-table=mydatabase.cache_table > mydatabase_filtered_backup.sql

mysqldump has been a core utility of the MySQL database system since its early days, evolving alongside the database server itself. Its initial purpose was to provide a simple, human-readable, and portable way to back up databases, which it continues to excel at. Over time, new options like --single-transaction were added to support transactional engines like InnoDB more efficiently, and features for replication (--master-data) and advanced schema objects (routines, events, triggers) were integrated to maintain its relevance as a comprehensive logical backup solution. Its widespread adoption across various MySQL and MariaDB versions highlights its enduring utility.

mysql(1), mysqladmin(1), mariadb-dump(1), mysqld(8), percona-xtrabackup(1), mariabackup(1)