Command to take a backup of MySQL database table Structure and Data 

Here we will talk about how can we back up the whole database or a single table structure and data in the database. But why are we doing all these things so I want to tell you if you accidentally drop the database then this backup will help you.

In this quick tutorial, we will learn three things first the entires database backup command, 2nd- particular data table backup, and third all available database backup on your MySQL server.

For this, we will use a simple MySQL command. So, let’s start:

BACKUP || DATABASE || TABLES || SINGLE || MULTIPLE

  • Go to MySQL console and login with your Database user.
  • Type SHOW DATABASES; command and press Enter then all databases are seeing you.
  • Now type USE Your_Database_Name; command and press Enter key.
  • Type SHOW TABLES; and now all tables of the database are showing.
  • Then run the important database backup command MySQL dump.

For example:

Syntax:

mysqldump -u root -p database_name > database_name.sql

The above common will create a complete backup of your database. And note that if you are logged as the same user then -u and -p not require.

If you want to backup only a single table then you have to change the command a little bit.

Syntax:

mysqldump database_name table_name > table_name.sql

In the above command instead of selecting whole database, we have just use the name of the particular Database table, we want to backup in SQL format.

If you want to backup of multiple databases then:

Syntax:

mysqldump -u root -p –all-databases > all_databases.sql

So, as we saw above how we can make database backup and use it later when needed.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.