Backing Up MySQL Tables: A Step-by-Step Guide

Summary: in this tutorial, you will learn how to back up one or some tables in MySQL using the mysqldump program.

To make a backup of one table, you use mysqldump command with the following option:

mysqldump -h hostname -u username -p dbname tblname > table.sqlCode language: SQL (Structured Query Language) (sql)

To back up some tables, you specify a list of table names after the database name in the mysqldump command:

mysqldump -h hostname -u username -p dbname tblname1 tblname2 > table.sqlCode language: SQL (Structured Query Language) (sql)

We’ll illustrate how to dump a table or some tables from a database.

Creating a sample database

First, open a Command Prompt on Windows or Terminal program on Unix-like systems and connect to a MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, create a new database called sales:

CREATE DATABASE sales;Code language: SQL (Structured Query Language) (sql)

Third, switch the current database to sales:

USE sales;Code language: SQL (Structured Query Language) (sql)

Fourth, create four tables in the sales database called products, customers, orders, and order_details:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price DECIMAL(10, 2)
);

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);Code language: SQL (Structured Query Language) (sql)

Fifth, insert data into the tables:

-- Inserting into Products Table
INSERT INTO products (product_name, unit_price)
VALUES
    ('Desktop Computer', 800.00),
    ('Tablet', 300.00),
    ('Printer', 150.00);

-- Inserting into Customers Table
INSERT INTO customers (customer_name, email)
VALUES
    ('Alice Johnson', '[email protected]'),
    ('Charlie Brown', '[email protected]'),
    ('Eva Davis', '[email protected]');

-- Inserting into Orders Table
INSERT INTO orders (customer_id, order_date)
VALUES
    (1, '2023-02-01'),
    (2, '2023-02-02'),
    (3, '2023-02-03');

-- Inserting into Order_Details Table
INSERT INTO order_details (order_id, product_id, quantity, total_price)
VALUES
    -- Order 1 details
    (1, 1, 2, 1600.00),
    (1, 2, 3, 900.00),

    -- Order 2 details
    (2, 2, 2, 600.00),
    (2, 3, 1, 150.00),

    -- Order 3 details
    (3, 1, 3, 2400.00),
    (3, 3, 2, 300.00);Code language: SQL (Structured Query Language) (sql)

Sixth, show all the tables in the sales database:

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| Tables_in_sales |
+-----------------+
| customers       |
| order_details   |
| orders          |
| products        |
+-----------------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Backing up one table

First, open a Command Prompt on Windows or Terminal program on Unix-like systems.

Second, execute the mysqldump command to back up the order_details table in the sales database:

mysqldump -u root -p sales order_details > D:\backup\order_details.sqlCode language: SQL (Structured Query Language) (sql)

Let’s examine the command:

• mysqldump: The command-line utility for creating MySQL database backups.
• -u root: Specifies the MySQL user, in this case, “root,” which is often a superuser with administrative privileges.
• -p: Prompts for the MySQL user’s password. After entering the command, you’ll be prompted to input the password interactively.
• sales: The name of the database in which you want to back up a table.
• order_details: The name of the table within the “sales” database. Only the data from the “order_details” table will be included in the backup.
• > D:\backup\order_details.sql: Redirects the output of the mysqldump command to a file named “order_details.sql” located at the specified path “D:\backup\”. This creates an SQL file containing the dumped data from the “order_details” table.

Making a backup of some tables

First, open a Command Prompt on Windows or Terminal program on Unix-like systems.

Second, execute the following mysqldump command to back up the orders and order_details tables in the sales database:

mysqldump -u root -p sales orders order_details > D:\backup\some_tables.sqlCode language: SQL (Structured Query Language) (sql)

Summary

• Use the mysqldump program to make a backup of one or more tables from a database.