MySQL’s mysqldump command is a powerful utility for creating backups of MySQL databases. One convenient feature it offers is the ability to use named login paths, specified with --login-path=, to streamline authentication settings.
How to Utilize Named Login Paths in mysqldump:
Named login paths simplify the command-line authentication process by storing credentials in a configuration file rather than specifying them directly in the command. This enhances security and reduces the risk of exposing sensitive information.
Example 1:
Backing up a database using a named login path:
mysqldump --login-path=backup_user mydatabase > backup.sql
In this example, --login-path=backup_user references a login path configured in MySQL’s client configuration file (typically .mylogin.cnf). The command dumps the contents of mydatabase into backup.sql using the credentials stored in backup_user.
Output Verification:
To verify if the backup was successful, inspect the backup.sql file. It should contain SQL statements representing the dumped database schema and data.
Example 2:
Dumping a specific table with a named login path:
mysqldump --login-path=backup_user mydatabase mytable > table_backup.sql
This command dumps only the mytable table from mydatabase into table_backup.sql using the backup_user credentials.
Output Verification:
Check table_backup.sql to ensure it contains data from mytable.
Example 3:
Using compression with a named login path:
mysqldump --login-path=backup_user --compress mydatabase > backup.sql.gz
Here, the --compress option compresses the dump output on-the-fly. The resulting file, backup.sql.gz, contains the compressed SQL dump.
Output Verification:
Verify the presence of backup.sql.gz and ensure it’s a valid gzip-compressed file using a tool like gunzip.
Example 4:
Specifying a custom socket file with a named login path:
mysqldump --login-path=backup_user --socket=/path/to/mysql.sock mydatabase > backup.sql
This command connects to MySQL using the specified socket file /path/to/mysql.sock and dumps mydatabase using the backup_user credentials.
Output Verification:
Inspect backup.sql to ensure it contains the dumped data from mydatabase.
Example 5:
Limiting the number of rows dumped with a named login path:
mysqldump --login-path=backup_user --where="id < 1000" mydatabase > limited_backup.sql
This command dumps rows from mydatabase where the id column is less than 1000 into limited_backup.sql.
Output Verification:
Verify limited_backup.sql to ensure it contains the expected subset of data based on the --where condition.
Example 6:
Excluding specific tables from the dump with a named login path:
mysqldump --login-path=backup_user --ignore-table=mydatabase.table_to_ignore mydatabase > backup.sql
This command excludes table_to_ignore from the dump of mydatabase using the backup_user credentials.
Output Verification:
Check backup.sql to confirm that table_to_ignore is not included in the dump.
Example 7:
Dumping stored procedures and functions with a named login path:
mysqldump --login-path=backup_user --routines mydatabase > backup_with_routines.sql
This command includes stored procedures and functions from mydatabase in backup_with_routines.sql, using the backup_user credentials.
Output Verification:
Verify backup_with_routines.sql to ensure it contains the SQL definitions of the stored procedures and functions.
Example 8:
Dumping triggers along with data using a named login path:
mysqldump --login-path=backup_user --triggers mydatabase > backup_with_triggers.sql
This command includes triggers from mydatabase in backup_with_triggers.sql, alongside the data, using the backup_user credentials.
Output Verification:
Examine backup_with_triggers.sql to ensure it contains the triggers definitions and the data from mydatabase.
Example 9:
Dumping with extended insert statements using a named login path:
mysqldump --login-path=backup_user --extended-insert mydatabase > backup_extended.sql
This command uses extended insert statements to optimize the dump size in backup_extended.sql using the backup_user credentials.
Output Verification:
Review backup_extended.sql to confirm it contains SQL statements with extended inserts.
Example 10:
Dumping with specific character sets using a named login path:
mysqldump --login-path=backup_user --default-character-set=utf8 mydatabase > backup_utf8.sql
This command specifies the UTF-8 character set for the dump in backup_utf8.sql using the backup_user credentials.
Output Verification:
Check backup_utf8.sql to ensure it uses the specified character set for encoding.
Read Also
Exclude Default Option Files in mysqldump Output
How to Print MySQL Configuration Defaults using mysqldump
Git Push: Updating Remote Repositories
Git Pull: Integrating Changes from Another Repository
Git Fetch: Downloading Objects and References
Discussion about this post