How to Backup a Single Table in MySQL Easily

To backup a single table in MySQL, use the mysqldump command with the database and table name specified. For example, run mysqldump -u username -p database_name table_name > backup.sql to export the table to a file.

Syntax

The basic syntax to backup a single table in MySQL uses the mysqldump tool:

• mysqldump: The command-line utility to export database contents.
• -u username: Your MySQL username.
• -p: Prompts for your MySQL password.
• database_name: The name of your database.
• table_name: The specific table you want to backup.
• > backup.sql: Redirects the output to a file named backup.sql.

bash

mysqldump -u username -p database_name table_name > backup.sql

Example

This example shows how to backup the table employees from the database company. It will create a file named employees_backup.sql containing the table data and structure.

bash

mysqldump -u root -p company employees > employees_backup.sql

Output

Enter password: 
-- MySQL dump 10.13  Distrib 8.0.33, for Linux (x86_64)
-- Host: localhost    Database: company
-- ------------------------------------------------------
-- Table structure for table `employees`

CREATE TABLE `employees` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `employees`

LOCK TABLES `employees` WRITE;
INSERT INTO `employees` VALUES (1,'Alice','Manager'),(2,'Bob','Developer');
UNLOCK TABLES;

-- Dump completed on 2024-06-01 12:00:00

Common Pitfalls

Common mistakes when backing up a single table include:

• Forgetting to specify the table name, which causes the entire database to be dumped.
• Not having proper permissions to access the database or table.
• Using the wrong database or table name, resulting in errors or empty backups.
• Not redirecting the output to a file, which just prints the dump to the screen.

Always double-check your command before running it.

bash

Wrong: mysqldump -u root -p company > backup.sql  # Dumps whole database
Right: mysqldump -u root -p company employees > employees_backup.sql  # Dumps single table

Quick Reference

Option	Description
-u username	MySQL username to connect with
-p	Prompt for password
database_name	Name of the database containing the table
table_name	Name of the single table to backup
> backup.sql	Redirect output to a file named backup.sql

Key Takeaways

Use mysqldump with database and table name to backup a single table.

Always redirect output to a file to save the backup.

Double-check table and database names to avoid mistakes.

Ensure you have the right permissions to access the table.

Use the -p option to securely enter your password.