“Backups are an essential feature for database developers. Using databases, you can keep up-to-date copies of the data in your applications. You can then use the backed-up data to restore the database in case of failure or use it to replicate the database schema in another project.
This tutorial will teach you how to back up a MySQL database without including the database structure. This is because the database will only store the actual data rather than the design.”
That being said, let’s dive in and discuss.
What is MySQLDump?
MySQL Dump is a powerful command-line utility that allows you to create and manage MySQL backups. In addition, this tool will enable you to export various aspects of a MySQL database, such as the schema, data, and more.
MySQLDump provides a relatively simple syntax that is easy to learn and use. Feel free to explore its documentation or manual pages to learn more.
Creating Sample Database and Data
Before discussing how we can export the data from a database, let us set up a sample database and data.
If you already have a database you wish to use, feel free to skip this section and proceed with the backup process.
Otherwise, run the SQL statements provided below to create sample information.
drop table if exists linuxhint.blog;
create table blog(
post_id int auto_increment not null primary key,
author_id int not null,
title varchar(255) not null,
author_name varchar(100) not null,
post_date date not null,
category varchar(50) not null,
published bool
);
insert into blog(author_id, title, author_name, post_date, category, published)
values (1, 'post 1', 'lh', '2022-07-08', 'cat1', true),
(1, 'post 2', 'li', '2022-07-09', 'cat2', true),
(1, 'post 3', 'lj', '2022-07-10', 'cat3', false),
(1, 'post 4', 'lk', '2022-07-11', 'cat4', false),
(1, 'post 5', 'll', '2022-07-12', 'cat1', true);
Once completed, we can query the records from the table:
MySQL Backup Database (Data Only)
To back up only the data of a given database without the structure, we can use the mysqldump utility with the –no-create-info option.
The command syntax is as shown:
For example, to backup the linuxhint database we created earlier, we can run the command:
The command above will log in as the root user and export the data of the linuxhint database to the lx_data.sql file.
You will notice that the command returns the file with lots of comments, increasing file size. To reduce the file size, we can use the –compact parameter as shown:
The command will reduce the file size by removing all the comments which are included by default.
Conclusion
In this tutorial, you learned how to back up only the data of a given database without including its structure. You also learned how to reduce the size of the backup file by removing comments from the resulting file.
Thanks for reading!!
