MySQL Table Dump

Updated May 30, 2023

Introduction to MySQL Table Dump

MySQL Table Dump is a technique for dumping different databases organized by the MySQL server. A database dump is a text file comprising a record of the database table structure or/and the data, usually in the arrangement of SQL statement lists. Generally, the MySQL Table Dump is implemented to take a backup of a database or multiple databases available in the server so that their data contents can be renovated in case of any data loss. Using the dump analysis, the corrupted databases are often recovered. These database dumps are usually published by any free software or content projects that allows forking or reusing that database.

Syntax of MySQL Table Dump

The function of MySQL Table Dump is to dump the entire table contents into an SPF file.

For this MySQL Table Dump, we will use the syntax as follows:

DUMP-TABLE
NAME = TableName_var|_lit|_col
[CONTINUATION = continuation_var|_lit|_col]

Let us see the parameters and keywords used above:

• NAME: Denotes the table name that CREATE_TABLE creates.
• CONTINUATION: States if the table contents are the persistence of a preceding DUMP_TABLE query command. If it exists, the valid values can be YES or NO, where the default is NO.

Description of syntax:

MySQL Dump Table can be applied in any section except BEGIN-DOCUMENT, BEGIN-SETUP, and BEGIN-SQL. Also, the data info dumped into the SPF file exits in a format that the Enhanced HTML or Generic Driver may use to generate coherent results like BQD, CSV, and XML.

For Example:

DUMP_TABLE
Name='Employees'
Continuation='Yes'

The mysqldump tool is positioned in the MySQL installation folder’s bin/root directory or folder. To access the mysqldump tool, a user needs to navigate to the bin/root directory and should use the command mysqldump having the common subsequent options:

• Add-drop-table: Contains DROP TABLE query for every database table.
• Add-locks: Consists of LOCK TABLES and UNLOCK TABLES queries before and after every INSERT query. Hence, it progresses the data restoration speed from the dump file.
• All databases: Builds up a dump of all MySQL databases in the server.
• Create-options: Comprises CHARSET and ENGINE options in the CREATE TABLE query for every Table.
• Databases: Create a dump of multiple databases.
• Disable keys: Initiates MySQL to inactivate index updates while the data is loaded for MyISAM database tables. Hence, loading is completed by mysqldump, and MySQL will create indexes that improve the renewal speed.
• Extended-insert: Associates single-row INSERT queries into one query that inserts more than one table row.
• Flush-logs: The server logs will be flushed before dumping the database data.
• Lock-tables: To ensure consistent snapshots, mysqldump locks all the database tables during the dump.
• No-data: Makes a dump file containing the SQL statements to re-create the database structure, not data.
• Opt: mysqldump opt enables all other options described before or after. And if we want to disable it, apply skip-<option_name>.
• Quick: Commands mysqldump not to buffer tables in the memory until written to a file.
• Result-file: Provides the way to the output dump file.
• Set-charset: Identifies the character set like latin1 or utf8 of the database.
• Tables: Makes a dump of multiple tables.
• Where: Dumps the table rows that fulfill a condition in the clause WHERE.

How does Table Dump work in MySQL?

In MySQL, you can use the mysqldump command to perform a database dump, which allows you to create a backup of the MySQL database.

Let us see three main significant cases of database dump in MySQL as follows:

1. The most naive case available in the entire MySQL database dumping.

Mysqldump – u user_name – p password databasename > the_entire_database_dump.sql

2. Occasionally, there might be a requirement to dump a particular database table only so a user can work it the succeeding way.

Mysqldump – u user_name – p password databasename tablename> particular_table_dump.sql

Here, a user can also define other tables divided by whitespaces to dump those tables in the database only.

3. If a user must dump only table rows that match certain conditions, then you need to add the option WHERE keyword to the mysqldump query command.

Suppose we have an illustration to show a dump of the specific rows where join_date is tomorrow.

Mysqldump – u user_name – p password databasename tablename – WHERE = “join_date”=’2020-09-20’

Mysqldump does not generally consume ample CPU assets on modern hardware because, by default, mysqldump applies only one thread. Therefore, this process is suitable for a deeply overburdened server.

Examples of MySQL Table Dump

We implement the mysqldump query tool to build up a backup of MySQL databases in the server.

Let us illustrate the process and command execution using mysqldump, shown as follows:

Example #1

I am making a backup of a database with the mysqldump tool.

Following is the command that backups data of only one database from the MySQL Server:

Code:

mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename>

Here,

• The username defines the user account to log in to the MySQL server.
• The password denotes the password used for the username. If there is no password, leave a blank space.
• The backup_file_path defines the path of the backup file.
• –databases denotes an option that guides mysqldump query to create a dump of particular databases.
• The databasename defines the name of a specific database a user wants to take a backup of.

Suppose the query below makes a backup of the database test’ to a file c:\backup\test.sql in the backup folder:

Code:

Mysqldump –user=root –password=   --resultfile=c:\backup\test.sql –databases test.sql –databases test

Example #2

I am taking backups of multiple databases using by mysqldump tool.

To back up data from multiple databases on the MySQL Server, you can use the following command, specifying the list of MySQL database names after the –database option:

Code:

mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename1>[, < databasename2>,< databasename3>,…]

For example:

To backup two databases:

Code:

Mysqldump –user=root –password=   --resultfile=c:\backup\testall.sql –databases test.sql –databases test achu

Example #3

Backup of different database tables.

We will use the following query:

Code:

mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename> <tablename>

Or, we can add multiple table names in the above syntax as:

mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename> <tablename1> <tablename2> <tablename3>

Suppose we are making a backup of the Books table from the test database as follows:

Code:

Mysqldump –user=root –password=   --resultfile=c:\backup\books.sql –databases test.sql –databases test books

Conclusion

MySQL Table Dump is a query tool that permits a user to take a backup of single or multiple databases by producing a text file that includes SQL commands that can restore the previous databases in the server from scratch. This is like a client utility to perform logical backups that, when executed, reproduce the original object definitions of databases and also table data. It can dump the databases for backup or transfer to other MySQL servers.

Recommended Articles

We hope that this EDUCBA information on “MySQL Table Dump” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL DELETE JOIN
2. mysql_real_escape_string
3. MySQL Math Functions
4. MySQL List User