The mysqldump command in MySQL is used to create a backup of MySQL databases. When using the option -p or --password=name, you can provide the password for the MySQL connection directly within the command itself. This is particularly useful in automated scripts or when you need to specify the password without being prompted interactively.
Example 1: Backing up a database named ‘exampledb’ with password prompt:
mysqldump -u username -p exampledb > exampledb_backup.sql
Explanation: Here, the mysqldump command dumps the database ‘exampledb’ to a file named ‘exampledb_backup.sql’. It prompts for the password after executing the command.
Example 2: Providing the password directly in the command:
mysqldump -u username -psecretpassword exampledb > exampledb_backup.sql
Explanation: In this example, ‘secretpassword’ is directly provided as the password for the MySQL connection. This allows for non-interactive execution of the backup process.
Example 3: Using the long form of password option:
mysqldump -u username --password=secretpassword exampledb > exampledb_backup.sql
Explanation: This is equivalent to the previous example but uses the long form of the password option --password=secretpassword.
Example 4: Backing up multiple databases with password prompt:
mysqldump -u username -p --databases db1 db2 db3 > multi_db_backup.sql
Explanation: This command backs up multiple databases ‘db1’, ‘db2’, and ‘db3’ into a single file ‘multi_db_backup.sql’ after prompting for the MySQL password.
Example 5: Exporting all databases with password provided:
mysqldump -u username -psecretpassword --all-databases > alldatabases_backup.sql
Explanation: Here, all databases in the MySQL instance are dumped into ‘alldatabases_backup.sql’ with the password ‘secretpassword’ provided in the command.
Example 6: Dumping a specific table with password in the command:
mysqldump -u username -psecretpassword exampledb table1 > table1_backup.sql
Explanation: This command dumps only ‘table1’ from ‘exampledb’ into ‘table1_backup.sql’ with the specified password.
Example 7: Dumping with compression and password:
mysqldump -u username -psecretpassword --compress exampledb > exampledb_backup.sql.gz
Explanation: This command compresses the backup of ‘exampledb’ using gzip and saves it as ‘exampledb_backup.sql.gz’ with the password provided.
Example 8: Dumping and excluding a specific table with password:
mysqldump -u username -psecretpassword exampledb --ignore-table=exampledb.table2 > exampledb_backup.sql
Explanation: This command dumps ‘exampledb’ excluding ‘table2’ into ‘exampledb_backup.sql’ while using the provided password.
Example 9: Dumping with extended insert and password:
mysqldump -u username -psecretpassword --extended-insert=FALSE exampledb > exampledb_backup.sql
Explanation: Here, the dump includes single-row INSERT statements for each row of the table with the provided password.
Example 10: Dumping and including create database statement with password:
mysqldump -u username -psecretpassword --databases exampledb --add-drop-database > exampledb_backup.sql
Explanation: This command includes the DROP DATABASE statement before each CREATE DATABASE statement in the dump file along with the specified password.
To verify if the mysqldump command executed successfully, check the generated backup file in each example. If the file exists and contains SQL statements corresponding to the specified database or tables, the command executed correctly. You can also confirm by checking the output messages in the terminal or command prompt for any errors or warnings.
Also check similar articles.
Sorting Rows by Primary Key in mysqldump Output
Enabling Optimization Options in mysqldump
Skipping SET NAMES Statement in mysqldump Output
Not Including Data Rows in mysqldump Output
Omitting Table Creation Info in mysqldump Output
Discussion about this post