Introduction to MySQL Table Dump
MySQL Table Dump is a technique responsible to dump different databases organised by the MySQL server. A database dump is a kind of text file that comprises a record of database table structure or/and also the data which is normally in the arrangement of SQL statements list. Generally, the MySQL Table Dump is implemented to take backup of a database or multiple databases available in the server so that their data contents can be renovated in the event of any data loss. Using the dump analysis, the corrupted databases are often recovered. This database dumps are published usually by any free software or content projects that allows forking or reuse of 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:
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 is created by CREATE_TABLE.
- CONTINUATION: States if the table contents is a persistence of a preceding DUMP_TABLE query command or no. If it exists, then the valid values can be YES or NO where the default one 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 which the Enhanced HTML or Generic Driver may use to generate coherent result like BQD, CSV and XML.
The mysqldump tool is positioned in the bin/root directory or folder of the MySQL installation folder. For accessing 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: Creates a dump of multiple databases.
- Disable-keys: Initiates MySQL to inactivate index updates while the data is being loaded for MyISAM database tables. Hence, loading is completed by mysqldump, MySQL will create indexes that improves the renewal speed.
- Extended-insert: Associates single-row INSERT queries into one query that insert more than one table rows.
- Flush-logs: Before dumping the database data, the server logs will be flushed.
- Lock-tables: During the dump, to ensure consistent snapshot mysqldump locks all the database tables.
- No-data: Makes a dump file containing the SQL statements to re-create the database structure only not data.
- Opt: mysqldump opt enables all other options described before or after. And if we want to disable then 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 fulfils a condition in the clause WHERE.
How Table Dump works in MySQL?
In MySQL, a mysqldump query command is implemented to dump the database that is accomplished by MySQL.
Let us see three main significant cases of database dump in MySQL as follows:
1. The naivest 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 with the succeeding way.
Mysqldump – u user_name – p password databasename tablename> particular_table_dump.sql
Here, a user can also define various other tables divided by whitespaces to dump those tables in the database only.
3. If a user need to dump only table rows which matches certain conditions, then you need to add the option WHERE keyword to the mysqldump query command.
Suppose, we have an illustration as follows to show dump 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 decent 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:
Making backup of a database by mysqldump tool.
Following is the command that backups data of only one database from the MySQL Server:
mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename>
- The username defines the user account to login the MySQL server.
- The password denotes the password used for the username. If no password leave 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 specific database which a user wants to take backup of.
Suppose, the query below makes a backup of the database ‘test’ to a file c:\backup\test.sql in the backup folder:
Mysqldump –user=root –password= --resultfile=c:\backup\test.sql –databases test.sql –databases test
Taking backup of multiple databases by mysqldump tool.
Following is the command that backups data of multiple databases from the MySQL Server by stating the list of MySQL database names which is placed after the –database option:
mysqldump -- user=<username> -- password=<password> -- resultfile=<backup_file_path> -- databases <databasename1>[, < databasename2>,< databasename3>,…]
To backup two databases:
Mysqldump –user=root –password= --resultfile=c:\backup\testall.sql –databases test.sql –databases test achu
Backup of distinct database tables.
We will use the following query:
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 backup of the Books table from the test database as follows:
Mysqldump –user=root –password= --resultfile=c:\backup\books.sql –databases test.sql –databases test books
MySQL Table Dump is a query tool that permits a user for taking a backup of single or multiple databases by producing a text file which includes SQL commands that has the capability to restore the previous databases in the server from scratch. This is like a client utility to perform logical backups that when executed reproduces the original object definitions of databases and also table data. Even then it can dump the databases for backup or transferring to other MySQL server.
This is a guide to MySQL Table Dump. Here we discuss the introduction, how table dump works in MySQL? along with examples respectively. You may also have a look at the following articles to learn more –