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] [database] [table ...]

--all-databases
    Dumps all databases. Requires appropriate privileges.

--databases
    Dumps the specified databases. Multiple databases can be listed separated by spaces.


    Dumps the specified tables from the specified database.

--user=
    The MySQL user name to use for connecting to the server.

--password[=]
    The MySQL password to use for connecting to the server. If no password is provided, it will prompt for one. Security Note: avoid providing the password directly on the command line.

--host=
    The host name of the MySQL server.

--port=
    The port number to use for connecting to the MySQL server.

--single-transaction
    Creates a consistent snapshot by using a single transaction. Recommended for InnoDB tables.

--lock-tables
    Locks all tables for reading during the dump. Use --single-transaction for InnoDB to avoid locking.

--no-data
    Only dump the table structure, not the data.

--routines
    Dump stored procedures and functions.

--triggers
    Dump triggers.

--events
    Dump events.

--skip-triggers
    Do not dump triggers.

--default-character-set=
    Specify the default character set.

--result-file=
    Direct output to a given file.

--compress
    Compress all information sent between the client and the server if both support compression.

--set-gtid-purged=
    Controls whether GTIDs are added to the output.
Mode can be: OFF, AUTO, ON

The mysqldump command is a utility for backing up MySQL or MariaDB databases into a SQL format file.
It can be used to create a logical backup of a database, allowing you to restore it later, either on the same server or a different one. It is also often used to transfer databases between different MySQL/MariaDB servers.
The tool supports various options to control the backup process, including specifying which databases or tables to backup, adding `DROP TABLE` statements to the dump file, locking tables during the backup, and generating different output formats.
It's important to note that mysqldump creates a logical backup which is essentially a set of SQL statements to recreate the database, unlike physical backups which involve copying the raw data files. Large databases benefit from using --single-transaction option to allow concurrent reads during dump.
It requires appropriate database user privileges to access the data being backed up.

Large databases can take a significant amount of time to dump and restore. Storing passwords directly on the command line is insecure. Backups created with older versions of MySQL may not be compatible with newer versions and vice versa.

It's crucial to secure the dump files generated by mysqldump, as they contain sensitive data. Protect the files with appropriate permissions and consider encrypting them, especially if storing them offsite.

Backups generated by mysqldump can be restored using the mysql command-line client:
mysql -u -p < backup.sql

mysqldump has been a part of MySQL since its early versions. It has evolved over time with new options and features to support various MySQL/MariaDB versions and storage engines. It remains a core tool for database administration.

mysql(1), mysqladmin(1)