Use Mysqldump to back up data in MySQL
This article will explain how to use mysqldump to back up only the data. Here we will explore the --no-create-info, --compact, , --skip-triggersand --no-create-dboptions.
If you are planning to back up single or multiple databases and tables, or looking for a way to move a database from a remote server to your local machine, you can read this article.
mysqldump in MySQL
mysqldump resides in MySQL's relational database package and is used to back up only one or more databases, tables, database structures, and sometimes only data. It depends on what we want to achieve with the mysqldump command.
We can also transfer the backup to another SQL server. The server on the other end where the data is imported does not have to be MySQL.
A flat file is created by the mysqldump utility that contains all the SQL statements used to rebuild the database/schema to its original form.
We can use this tool to move one or more databases to another web host or export to a backup file. Other file formats can be generated using the mysqldump command, including XML and CSV.
Using the mysqldump utility requires two things:
- We must have all access rights to views, functions, triggers, and the database.
- The backup file (dump file) must have CREATE, ALTER, and INSERT permissions.
Use mysqldump to back up only the data in MySQL
We can use various options with mysqldump, but we will focus only on those that are useful when dumping only data.
grammar:
mysqldump -u [username] -p [password] [options] dbname > path_of_dump_file
Use mysqldump with the --no-create-info option to back up data in MySQL only
We can --no-create-infouse the -dump option with the mysqldump utility to back up the data of the MySQL database only.
mysqldump does not write CREATE TABLE statements with this option. It generates only a dump file that contains the SQL statements needed to lock the tables and to insert data into them.
mysqldump -u root -p ***** --no-create-info test > D:\data.sql
If you are storing the dump file in the current directory, you may receive an access denied error. If so, save the dump file in another drive, for example, drive D or E as we are doing.
Use mysqldump with the --compact option to back up data in MySQL only
To get rid of the extra comments and focus keenly on the necessary details, we can use the --compact option below. It produces better and compact result by enabling --skip-add-locks, --skip-add-drop-table, --skip-comments, --skip-set-charset and --skip-disable-keys options.
mysqldump -u root -p ***** --no-create-info --compact test > D:\data.sql
Use mysqldump with the --skip-triggers option to back up data in MySQL only
--triggersThe --skip-triggers option is enabled by default to include triggers for each dumped table in the output. If we were using triggers, we could have disabled it using the --skip-triggers option.
mysqldump -u root -p ***** --no-create-info --compact --skip-triggers test > D:\data.sql
Use mysqldump with the --no-create-db option to back up data in MySQL only
If we use the --database ... option, we can also use the --no-create-db option to suppress the CREATE DATABASE statement. Otherwise, if one uses the --databases or --all-databases option, the CREATE DATABASE statement will be included in the dump file.
mysqldump -u root -p ***** --no-create-info --compact --skip-triggers --no-create-db --databases test > D:\data.sql
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
Changing max_allowed_packet Size in MySQL Server
Publish Date：2025/04/22 Views：192 Category：MySQL
-
This article explains how to change the max_allowed_packet size in MySQL server. To understand this, we will use two operating systems, Windows 10 and Linux (Ubuntu). Changing max_allowed_packet Size in MySQL Server If we try to upload a fi
Zerofill usage, advantages and alternatives in MySQL
Publish Date：2025/04/22 Views：195 Category：MySQL
-
In this article we will understand the uses, advantages and alternatives of ZEROFILL attribute in MySQL. Use and benefits of the ZEROFILL attribute in MySQL The benefit of using the ZEROFILL attribute is that it has nothing to do with input
Compare only MySQL timestamp dates to date parameters
Publish Date：2025/04/22 Views：64 Category：MySQL
-
In this article we will use the DATE() , CAST() , and CONVERT() functions to compare MySQL timestamp dates with only the date parameter. DATE() vs. CAST() vs. CONVERT() in MySQL Below is a brief description of each function. You can also fi
Calculating Percentages in MySQL
Publish Date：2025/04/22 Views：66 Category：MySQL
-
We will use one or more columns to calculate percentages in MySQL. There are different ways to do this, and for each method we will use an example table. Calculate percentage using a column in MySQL We have a table called sales where ID, Re
Selecting multiple values using WHERE in MySQL
Publish Date：2025/04/22 Views：185 Category：MySQL
-
This article is about using MySQL query to get data from a specific table or relation that satisfies a specific condition. To do this, the WHERE clause is used in the SQL query. WHERE clause in SQL query WHERE The clause specifies the condi
Changing the connection timeout in MySQL
Publish Date：2025/04/22 Views：59 Category：MySQL
-
We are learning how to change the connection timeout in MySQL using Linux (Ubuntu 20.04) and Windows operating systems. Changing the connection timeout in MySQL Sometimes you keep losing connection to the MySQL server because the connect_ti
MySQL fix Data Is Truncated for a Column error
Publish Date：2025/04/22 Views：101 Category：MySQL
-
This article describes possible causes and solutions for the MySQL error Data is truncated for a column . Fix data truncated due to column error in MySQL Here, we will discuss the possible causes and solutions to eliminate MySQL data trunca
MySQL Error Server PID File Could Not Be Found Solution
Publish Date：2025/04/22 Views：192 Category：MySQL
-
In this article, we will study about the Error! Error Server PID File Could Not Be Found! in MySQL and its solution with full explanation. MySQL PID file The file that contains the process identification number or process ID of a running My
Get the last inserted ID using PHP MySQLi function
Publish Date：2025/04/22 Views：99 Category：MySQL
-
This article briefly introduces the PHP mysqli() function and demonstrates how to use it to get the last inserted ID from a MySQL database. PHP mysqli() Function It is an extended version of the MySQL driver called mysqli and is typically u
