mysqldump is a tool used to backup MySQL Database.

  • It creates .sql file having DROP TABLE , CREATE TABLE , INSERT INTO TABLE sql Statements.
  • To restore the database we have to execute the .sql file on destination database.
  • Using mysqldump command you can backup a local database and can restore it on remote database.

We will now see how to backup and restore MySQL database using mysqldump.

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:# mysql -u root -p[root_password] [database_name] > dumpfilename.sql

How to Backup MySQL database

  • Backup a single Database
  • # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

  • Backup multiple Databases
  • # mysqldump -u root -p[root_password] [database_name1] [database_name2]…[database_nameN] > dumpfilename.sql

  • Backup all Databases
  • # mysqldump -u root -p[root_password] —all-databases > all-database.sql

  • Backup a single Table
  • # mysqldump -u root -p[root_password] [database_name] [table_name] > tablename.sql

  • Backup multiple Tables
  • # mysqldump -u root -p[root_password] [database_name] [table_name1] [table_name2]…[table_nameN] > tablename.sql

    How to Restore MySQL database

  • Restore a Database

  • 1.Create Database Using Command:
    # mysql -u root -p root_password
    mysql> create database database_name;

    2. Restore a Database
    # mysql -u root -p[root_password] [database_name] < dumpfilename.sql

  • Restore a Table

  • 1.Create Table Using Command:
    # mysql -u root -p root_password
    mysql> create table table_name;

    2. Restore a table
    # mysql -u root -p[root_password] [database_name] < dumpfilename.sql

    This way , you can Backup and restore a MySQL Database.

    Thanks ,
    Sayali Mahale

    Leave a Reply