The mysqldump command is used to create backups of MySQL databases. When dealing with SSL connections, you can specify the X509 certificate to be used for SSL encryption using the --ssl-cert=name option.
Here are several examples of how to use this option:
Example 1: Dumping a database with SSL certificate specified:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p database_name > backup.sql
This command dumps the database_name database to backup.sql using SSL certificate /path/to/certificate.pem. You can verify the command by checking if backup.sql exists and contains database data.
Example 2: Dumping all databases with SSL certificate:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --all-databases > alldatabases_backup.sql
This command dumps all databases to alldatabases_backup.sql using SSL certificate /path/to/certificate.pem. Verify by checking the size and content of alldatabases_backup.sql.
Example 3: Dumping a specific table with SSL certificate:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p database_name table_name > table_backup.sql
This command dumps table_name from database_name using SSL certificate /path/to/certificate.pem to table_backup.sql. Verify by examining table_backup.sql.
Example 4: Dumping with compressed output and SSL certificate:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --all-databases | gzip > alldatabases_backup.sql.gz
This command dumps all databases, compresses them with gzip, and uses SSL certificate /path/to/certificate.pem. Verify by checking the existence and size of alldatabases_backup.sql.gz.
Example 5: Dumping with SSL certificate and excluding specific tables:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p database_name --ignore-table=database_name.table1 --ignore-table=database_name.table2 > database_without_tables.sql
This command dumps database_name excluding table1 and table2, using SSL certificate /path/to/certificate.pem. Verify by reviewing database_without_tables.sql.
Example 6: Dumping with SSL certificate and specific extended-insert length:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p database_name --extended-insert=FALSE > extended_insert_false.sql
This command dumps database_name using SSL certificate /path/to/certificate.pem with extended-inserts disabled to extended_insert_false.sql. Verify the absence of extended-insert statements in extended_insert_false.sql.
Example 7: Dumping with SSL certificate and specifying data-only dump:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --no-create-info database_name > data_only_dump.sql
This command dumps only the data from database_name without schema information, using SSL certificate /path/to/certificate.pem. Verify by examining data_only_dump.sql.
Example 8: Dumping with SSL certificate and excluding triggers:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --skip-triggers database_name > no_triggers_dump.sql
This command dumps database_name excluding triggers, using SSL certificate /path/to/certificate.pem to no_triggers_dump.sql. Verify by checking the absence of trigger definitions in no_triggers_dump.sql.
Example 9: Dumping with SSL certificate and adding drop table statements:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --add-drop-table database_name > with_drop_tables.sql
This command dumps database_name with drop table statements included, using SSL certificate /path/to/certificate.pem. Verify by ensuring drop table statements are present in with_drop_tables.sql.
Example 10: Dumping with SSL certificate and using hex-blob option:
mysqldump --ssl-cert=/path/to/certificate.pem -u username -p --hex-blob database_name > hex_blob_dump.sql
This command dumps database_name with binary data in hexadecimal format, using SSL certificate /path/to/certificate.pem. Verify by examining hex_blob_dump.sql for hexadecimal encoded binary data.
Also check similar articles.
Setting CA Directory for SSL in mysqldump
Setting CA File for SSL in mysqldump
Configuring SSL Connection Mode in mysqldump
Retrieving Server Public Key in mysqldump
Setting Server Public Key Path in mysqldump
Discussion about this post