How to Backup a Single Table Using Mysqldump

1. Create a Table in MySQL
2. Backup a Single Table in MySQL Using mysqldump
3. Conclusion

Although MySQL is neither a database nor a programming language, it is a widely used open-source tool for managing and organizing data in databases. This article discusses mysqldump, its uses, and how to back up a single table using mysqldump, along with an example.

Create a Table in MySQL

To backup a single table, we first need to create one. Let us create a simple table using CREATE TABLE in MySQL.

Syntax:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    .....
);

Let us see an example. In a new schema, let us create the employee table.

CREATE TABLE employee (
    EmployeeID int,
    FirstName varchar(255),
    LastName varchar(255),
    Address varchar(255),
    PhoneNumber int
);

Let us insert some details into the table.

INSERT INTO employee (employeeID, FirstName, LastName, Address, PhoneNumber)
VALUES ('001', 'John', 'Andrew','London','4420112');

You can see the output table of the above code using this query.

SELECT * FROM employee;

Output:

There are various backup types in MySQL. Which backup technique you employ relies on variables, data size, gear, performance goals, database storage capacity, etc.

The time it will take to restore the backup should also be considered.

MySQL supports these backup formats:

1. Logical backup returns a database structure in a .sql file by creating the CREATE or INSERT statements. We can use the mysqldump tool later to restore this file.

   Because it only backs up data without indexes, this type is compact. The fact that it must execute each sentence separately makes its recovery option slower than the others.

2. Physical backup reprints the database files in the same format they are saved on the disk. Although it is quicker than the logical backup, it can only be restored from the same database engine to the MySQL server.

3. Consistent backup reprints the files at the exact moment, only after the server pauses running or is locked.

Backup a Single Table in MySQL Using mysqldump

Backups can store the original data before data is updated or deleted. There are different types of backups in MySQL based on data size, the storage capacity of your database, etc.

mysqldump is the command used to back up the MySQL database and is located in the installation directory of the MySQL server.

Let us see how to back up a single table using the mysqldump command. Versions of MySQL before 5.0 made use of the backup table concept.

Syntax:

mysqldump -u [username] –p [password] -h [host name] [database_name] [tablename] > [dumpfilename.sql]

Explanation of the above syntax:

• -u (--user = "username" ): This is the username connected to MySQL.
• -p (--p): This is the password of the username.
• -h(--host = ‘host name’): This is the name of the server which it wants to connect to dump data from.
• database_name: This is the database name you want to backup.
• tablename: This is the table name you want to backup.
• <: This symbol refers to the restoration of the database.
• >: This symbol refers to the backup database.
• dumpfilename.sql: This is a path to a dump file that contains a database backup.

Give the mysqldump command the name of the particular table if you want to take a backup of just that one table in the database.

Take a specific table from the other database as an example. Here, we are using mysqldump to perform a backup.

First, use this shortcut key to launch the cmd: windows key+R.

Next, enter cmd in the search option as shown below:

Now, the cmd will open.

The MySQL bin folder is located in the following location:

C:\Program Files\MySQL\MySQL Server 8.0\bin

To access the bin folder, type the path shown above.

Check the tables available:

Create a folder to store the backups:

We are now in the bin folder. The following is the backup query:

mysqldump --host=localhost --user=root --port=3306 -p customers employee > backup_single_table.sql

The backup script outputs the structure and data only for the employee table:

The data of the script:

Conclusion

In the article, we discussed how to use the mysqldump command, a MySQL backup option, and gave an example of how to backup a single table.

You may also look into other backup creation techniques, such as MySQL Workbench and dbForge Studio for MySQL. Also, there are different approaches where you can restore data from databases.