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.