The mysqldump command in MySQL is used to create backups of MySQL databases. By default, it reads database connection settings from the standard MySQL configuration files like my.cnf. However, there are situations where you may want to use a custom configuration file with specific settings for the mysqldump command. This is where the --defaults-file= option comes into play, allowing you to specify a custom configuration file to be used instead of the default one.
Here are several examples demonstrating how to use the --defaults-file= option with mysqldump:
Example 1: Using a custom configuration file named custom.cnf located in the current directory:
mysqldump --defaults-file=./custom.cnf dbname > backup.sql
Explanation: This command dumps the database dbname using the settings specified in custom.cnf and saves the output to backup.sql.
Example 2: Specifying an absolute path to the custom configuration file:
mysqldump --defaults-file=/path/to/custom.cnf dbname > /path/to/backup.sql
Explanation: Here, the command uses /path/to/custom.cnf as the configuration file to connect to the database dbname and dumps it to /path/to/backup.sql.
Example 3: Using a configuration file with a different name in a non-standard location:
mysqldump --defaults-file=/opt/mysql/custom-config.cnf dbname > dump.sql
Explanation: This command specifies /opt/mysql/custom-config.cnf as the configuration file and dumps the database dbname to dump.sql.
Example 4: Utilizing a configuration file from the home directory:
mysqldump --defaults-file=~/mycustom.cnf dbname > ~/backup.sql
Explanation: Here, ~/mycustom.cnf in the home directory is used to configure the mysqldump command for dumping dbname to backup.sql.
Example 5: Using a configuration file with specific connection details:
mysqldump --defaults-file=/etc/mysql/custom-config.cnf -h localhost -u root -p dbname > backup.sql
Explanation: This command uses /etc/mysql/custom-config.cnf along with explicit connection parameters to dump the database dbname to backup.sql.
Example 6: Combining multiple options with --defaults-file for complex configurations:
mysqldump --defaults-file=/etc/mysql/custom.cnf --single-transaction --triggers --routines dbname > backup.sql
Explanation: In this command, --single-transaction, --triggers, and --routines options are used along with --defaults-file=/etc/mysql/custom.cnf to create a consistent backup of dbname with triggers and routines included.
Example 7: Using mysqldump with SSL connection settings in a custom configuration file:
mysqldump --defaults-file=/etc/mysql/ssl-config.cnf --ssl-ca=/etc/mysql/ca-cert.pem --ssl-cert=/etc/mysql/client-cert.pem --ssl-key=/etc/mysql/client-key.pem dbname > backup.sql
Explanation: This command specifies a custom SSL configuration file (/etc/mysql/ssl-config.cnf) and additional SSL certificate files to dump dbname securely to backup.sql.
Example 8: Using mysqldump with a custom configuration file and compressing the output:
mysqldump --defaults-file=/etc/mysql/custom.cnf dbname | gzip > backup.sql.gz
Explanation: Here, the output of mysqldump using /etc/mysql/custom.cnf is piped to gzip for compression, and the compressed backup is saved as backup.sql.gz.
Example 9: Using mysqldump with a custom configuration file to dump specific tables:
mysqldump --defaults-file=/etc/mysql/custom.cnf dbname table1 table2 > tables_backup.sql
Explanation: This command dumps only table1 and table2 from the database dbname using the settings in /etc/mysql/custom.cnf and saves them to tables_backup.sql.
Example 10: Using mysqldump with a custom configuration file and ignoring specific tables:
mysqldump --defaults-file=/etc/mysql/custom.cnf --ignore-table=dbname.table_to_ignore dbname > backup_without_ignored_table.sql
Explanation: This command excludes dbname.table_to_ignore from the dump of dbname using the settings from /etc/mysql/custom.cnf and saves the result to backup_without_ignored_table.sql.
To verify that each example command has executed successfully, you can check the existence and size of the output file specified (backup.sql, dump.sql, etc.). Additionally, you can examine the content of these files using MySQL client tools or text editors to ensure that they contain the expected database dump.
Read Also
Dump All Tablespaces with mysqldump
Dump All Databases Using mysqldump
How to Utilize Named Login Paths in mysqldump
Exclude Default Option Files in mysqldump Output
How to Print MySQL Configuration Defaults using mysqldump
Discussion about this post