mysqldump usage - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is mysqldump used for?

mysqldump is a tool to create a backup of MySQL databases by exporting their structure and data into a file.

beginner

How do you dump a single database named shop using mysqldump?

Use the command: mysqldump -u username -p shop > shop_backup.sql. This exports the shop database to a file.

beginner

What does the -u and -p option mean in mysqldump?

-u specifies the MySQL username. -p tells the tool to prompt for the password.

intermediate

How can you dump all databases at once using mysqldump?

Use mysqldump -u username -p --all-databases > all_backup.sql to export every database on the server.

beginner

What is the purpose of redirecting output with > in mysqldump commands?

The > symbol sends the dump output to a file instead of showing it on the screen, saving the backup for later use.

Which command backs up a single database named library?

A mysqldump -u user -p --tables library > backup.sql

B mysqldump -u user -p library > library_backup.sql

C mysqldump -u user -p --databases library books > backup.sql

D mysqldump -u user -p --all-databases > library_backup.sql

Option B correctly dumps the single database library to a file.

What does the --all-databases option do?

A Backs up all databases on the server

B Backs up only the current database

C Backs up only tables with data

D Backs up only the database structure

--all-databases exports every database on the MySQL server.

Why do you use -p with mysqldump?

A To prompt for the password

B To specify the port number

C To print the dump on screen

D To pause the dump process

-p makes mysqldump ask for the password securely.

What does the symbol > do in mysqldump commands?

A Runs the command in the background

B Appends output to an existing file

C Redirects output to a file

D Deletes the output file

> sends the output to a file instead of the screen.

Which of these commands is correct to dump multiple specific databases?

A mysqldump -u user -p --tables db1 db2 > multi_backup.sql

B mysqldump -u user -p --all-databases db1 db2 > multi_backup.sql

C mysqldump -u user -p db1 db2 > multi_backup.sql

D mysqldump -u user -p --databases db1 db2 > multi_backup.sql

Option D uses --databases to dump multiple named databases.

Explain how to use mysqldump to back up a single database and save it to a file.

Think about the command structure and how to save output.

Describe the difference between dumping a single database and dumping all databases with mysqldump.

Consider the options and what they export.

Practice

1. What is the primary purpose of the mysqldump command in MySQL?

easy

A. To create a backup file of a database

B. To delete a database permanently

C. To optimize database performance

D. To create new tables in a database

Solution

1. Step 1: Understand the function of mysqldump

   mysqldump is a utility used to export database contents into a file for backup or transfer.

2. Step 2: Identify the correct purpose

   It does not delete or optimize databases; it creates a backup file.

3. Final Answer:

   To create a backup file of a database -> Option A

4. Quick Check:

   mysqldump = backup creation [OK]

Hint: Remember mysqldump = dump database to file [OK]

Common Mistakes:

• Confusing mysqldump with delete commands
• Thinking it optimizes database
• Assuming it creates tables

2. Which of the following is the correct syntax to back up a database named shopdb using mysqldump?

easy

A. mysqldump backup.sql > shopdb -u root -p

B. mysqldump shopdb -u root -p backup.sql

C. mysqldump -p -u root backup.sql shopdb

D. mysqldump -u root -p shopdb > backup.sql

Solution

1. Step 1: Recall mysqldump syntax

   The correct syntax is mysqldump -u [user] -p [database_name] > [file].

2. Step 2: Match the syntax with options

   mysqldump -u root -p shopdb > backup.sql matches the correct order and redirection to a file.

3. Final Answer:

   mysqldump -u root -p shopdb > backup.sql -> Option D

4. Quick Check:

   Correct mysqldump syntax = mysqldump -u root -p shopdb > backup.sql [OK]

Hint: Syntax: mysqldump -u user -p dbname > file [OK]

Common Mistakes:

• Placing output file before database name
• Wrong order of options
• Missing redirection operator >

3. What will be the result of running the command mysqldump -u root -p shopdb products > products_backup.sql?

medium

A. Backs up the entire shopdb database

B. Backs up only the products table from shopdb

C. Creates an empty file named products_backup.sql

D. Returns a syntax error

Solution

1. Step 1: Analyze the command components

   The command specifies shopdb as the database and products as a table name after it.

2. Step 2: Understand mysqldump behavior with table names

   When a table name is given after the database, only that table is backed up.

3. Final Answer:

   Backs up only the products table from shopdb -> Option B

4. Quick Check:

   Database + table name = single table backup [OK]

Hint: Table name after DB backs up only that table [OK]

Common Mistakes:

• Assuming whole database is backed up
• Thinking it creates empty file
• Believing it causes syntax error

4. You run mysqldump -u root shopdb > backup.sql but get an error. What is the most likely cause?

medium

A. Output file name is invalid

B. Wrong database name syntax

C. Missing password prompt option -p

D. User root does not exist

Solution

1. Step 1: Check command for required options

   The command lacks the -p option which prompts for the password.

2. Step 2: Understand error cause

   Without -p, mysqldump may fail if password is required for user root.

3. Final Answer:

   Missing password prompt option -p -> Option C

4. Quick Check:

   Missing -p causes authentication error [OK]

Hint: Always include -p if password is needed [OK]

Common Mistakes:

• Forgetting -p for password prompt
• Assuming output file name causes error
• Thinking database name syntax is wrong

5. You want to back up only the structure (no data) of the employees database. Which mysqldump command should you use?

hard

A. mysqldump -u root -p --no-data employees > structure.sql

B. mysqldump -u root -p --no-create-info employees > structure.sql

C. mysqldump -u root -p --skip-structure employees > structure.sql

D. mysqldump -u root -p --only-data employees > structure.sql

Solution

1. Step 1: Identify option to dump only structure

   The --no-data option tells mysqldump to export only table structure without data.

2. Step 2: Verify other options

   --no-create-info exports only data, not structure; others are invalid or opposite.

3. Final Answer:

   mysqldump -u root -p --no-data employees > structure.sql -> Option A

4. Quick Check:

   --no-data = structure only backup [OK]

Hint: Use --no-data to dump structure only [OK]

Common Mistakes:

• Using --no-create-info instead of --no-data
• Confusing options to skip structure
• Assuming --only-data dumps structure