MySQL: Taking Backup by Dumping Table into a File

I often used to take backup of a single MySQL table while testing or developing an application. Taking backup of one or more tables by making a dump of them in a file is a convenient way to quickly backup and restore data without going through the proper backup of the entire database. Such a situation may arise when, for example, we want to transfer data from one server to another. Backup can be easily achieved by using the mysqldump MySQL command on shell prompt. Here is the syntax that you can use:

mysqldump -ustar -purpwd db_name table_name > file_name.sql

-u and -p options specify the user name and password for the MySQL server. There should not be a any space between -u & user name and -p & password.

Please note that it is not mandatory to keep the extension of file as .sql but because this file would contain the SQL statements -so keeping .sql makes more sense.

This dump file will also have SQL command for dropping the MySQL table if it already exist in the target database.

In order to import the dumped table into a database, use the following MySQL command:

mysql -ustar -purpwd db_name < y1.sql

I hope this information was useful for you. Please feel free to ask if you have any questions. I will be happy to try and help you. Thank you for using TechWelkin.