Often database administrators need to take backup of their data. But while doing so, they generally take backup of the entire database. Sometimes you may need to take backup of single table in MySQL. In this article, we will learn how to take backup of single table in MySQL.
How to Take Backup of Single Table in MySQL
Here is the command to take backup of a single database table into a .sql file.
mysqldump db_name table_name > table_name.sql
Generally, we specify only the database name after mysqldump command and before > operator. In this case, you need to specify the table name after database name and before > operator.
For example, if you want to take backup of table sales from database ecommerce here is the command for it, after you have logged into MySQL.
mysqldump ecommerce sales > sales.sql
The above command works for local databases only. If you want to backup remote database, you need to specify the remote host name after -h option.
mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql
If you want to dump as well as compress the backup into a .gz file, then pass the output of MySQLdump to gzip command.
mysqldump db_name table_name | gzip > table_name.sql.gz
Using the above command for our example, here is the command to backup and compress the table.
mysqldump ecommerce sales | gzip > sales.sql.gz
If you want to restore the table, you can do so using mysql command. First you log into the desired database. Then use source command followed by the full path to .sql file.
mysql -u <user_name> -p db_name mysql> source <full_path>/table_name.sql
Let us say backup file of table sales is located at /home/ubuntu/sales.sql and you want to load it to database ecommerce then first log into this database.
mysql -u <user_name> -p ecommerce
You will be prompted for password. Enter right password to login. Then run the source command to load the backup file sales.sql.
mysql> source /home/ubuntu/sales.sql
Alternatively, you can also restore it using < operator.
$ mysql -u username -p db_name < /path/to/table_name.sql
Applying the above command to our example,
$ mysql -u username -p ecommerce < /home/ubuntu/sales.sql
If you want to restore a table from compressed gz file, you need to pass it to gunzip command first and then call the above command on its output, as shown below.
gunzip < table_name.sql.gz | mysql -u username -p db_name
In this article, we have learnt several different ways to take backup of single table in MySQL.
Also read:
How to Show Indexes in Table or Database in MySQL
How to Set Global SQL Mode in MySQL
How to Check if Column is Empty or Null in MySQL
How to Create Please Wait Loading Animation in jQuery
How to Get Current URL With jQuery