Dumping Data from a specific table of MySQL database

Posted on

Database         : MySQL

Dumping only the inserted data of a certain table.

There are certain times when we actually need to dump just the inserted data of a table. The requirement to achieve that thing by having mysqldump tool or utility installed as part of MySQL database installation.   Below is the command syntax of it :

mysqldump -t -uroot -p database_name table_name  > target_file_name

Below is the further explanation of the command :

mysqldump : It is a command which is used to dump databases, tables among the others. But to put it simple, manual page of mysqldump defines it as a database backup program.
-t : It is a parameter specified in this term of command to just dump the inserted command to populate data later on. To put it simple, it is a parameter which is used when you want to backup only the data of a specific table
-u : It is a parameter which is used to specify user associated or utilized for the command.
root : It is the value given for the -u parameter, which is user ‘root’ in this context.
-p : It is  a parameter which is used to specify that the command will give an additional entry or attribute value of password.

The execution of that the above command in real action can be shown below :

[username@hostname ~]$ mysqldump -t -uroot -p experiment user > /home/username/tbl_data_dump.sql
Enter password:
[username@hostname ~]$ 

To prove that the above command combination will achieve specific purpose which is dumping only the data of a specific table, we will try to demonstrate it in the following scenario  :

  1. Login to MySQL console
username@hostname:~$ mysql -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
  1. Create a simple databases
mysql> create database experiment;
Query OK, 1 row affected (0,00 sec)
  1. Use the already created database or utilize it by executing the following command :
mysql> use experiment;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
  1. Create a simple table
mysql> create table user(id int not null auto_increment, username varchar(255), password varchar(255), primary key(id));
Query OK, 0 rows affected (0,08 sec)

mysql>
  1. Populate the table with data
mysql> INSERT INTO user(username, password) VALUES('james',PASSWORD('My_Password2016'));
Query OK, 1 row affected, 1 warning (0,01 sec)
mysql> INSERT INTO user(username, password) VALUES('mark',PASSWORD('My_Password2016'));
Query OK, 1 row affected, 1 warning (0,01 sec)
mysql> INSERT INTO user(username, password) VALUES('steve',PASSWORD('My_Password2016'));
Query OK, 1 row affected, 1 warning (0,02 sec)
mysql> INSERT INTO user(username, password) VALUES('jack',PASSWORD('My_Password2016'));
Query OK, 1 row affected, 1 warning (0,02 sec)
  1. Select the table to make sure that all the data have been successfully inserted by executing the following command :
mysql> select * from user;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  2 | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  3 | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  4 | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
+----+----------+-------------------------------------------+

4 rows in set (0,00 sec)

mysql>
  1. Logout from MySQL Console by executing the following command :
mysql> quit
Bye
  1. Dumping the data from the table using mysqldump command from the shell prompt :
username@hostname:~$ mysqldump -t -uroot -p experiment user > /home/username/tbl_user_data.sql
Enter password:
username@hostname:~$
  1. Erase the data by following several steps below :
username@hostname:~$ mysql -uroot -p experiment
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

The above command is the command executed to log in to MySQL Console. Continuing on the erasing data process, we have to select the table first :

The command pattern is shown as follows :

select * from table_name
select : It is a reserved keyword in SQL Query Language to select data from tables. It returns a result of set of records from one or more tables. It is one of most common used Data Manipulation Language (DML) command.
* : It is a sign which is used to identify the SELECT command to select all of the fields from one or more tables.
from : It is a reserved keyword in SQL Query Language which is followed with the name of tables which is being targeted as query language operation.
table_name : The name of the table which is being operated with SQL query language. It follows the reserved keyword ‘FROM’.

Below is the command in action :

mysql> select * from user;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  2 | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  3 | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  4 | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
+----+----------+-------------------------------------------+

4 rows in set (0,00 sec)

Based on the output given by the previous command, the user table is still containing several records. The next step is to erase all the data by executing the following command :

truncate tbl_name
truncate : It is a reserved keyword which is used to completely erase all the data in a table.
tbl_name : It is a table name which is in this context is ‘user’.

Below is the command execution :

mysql> truncate user;
Query OK, 0 rows affected (0,07 sec)

After erasing all the data in the table, try to select the table again :

mysql> select * from user;
Empty set (0,00 sec)

It displays no record at all since the ‘truncate’ command has erased all of data. Quit the MySQL console and try to restore the data back.

mysql> quit
Bye
  1. Restore the data by following steps below :

Restore the data by executing the following command :

mysql -uroot -p database_name  < file_dump_data_location.sql
mysql : mysql command used for various needs concerning mysql database tools.
-u : It is a parameter which is used to define user attribute.
root : It is a user name which is given as value attribute of -u parameter. In this context, it is ‘root’.
-p : It is a parameter which is used to define password attribute.
database_name : It is a table which is given as the operation target.
< : Redirect input sign which is used to redirect the following parameter as the source of input.
file_dump_data_location.sql : It is the path and also name of file which is used as the source of input.
username@hostname:~$ mysql -uroot -p experiment < /home/username/tbl_user_data.sql
Enter password:

After successfully restoring the data in the able by executing the above command, continue on login to MySQL console :

 

username@hostname:~$ mysql -uroot -p experiment;
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 

Check the table by using the same ‘select’ command just to check whether the restore process is succeed or not.

 

mysql> select * from user;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  2 | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  3 | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  4 | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
+----+----------+-------------------------------------------+

4 rows in set (0,00 sec)

 

mysql>

 

Based on the output displayed above, the restore process has been successful since several records has shown an part of the output.

 

Leave a Reply