MySQL Backup and Restore Commands for Database Administration

This article shows you several practical examples on how to perform various backup operations of MySQL/MariaDB databases using the mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.

mysqldump is a command-line client program, it is used to dump local or remote MySQL databases or collections of databases for backup into a single flat file.

We assume that you already have MySQL installed on the Linux system with administrative privileges and we believe that you already have a small amount of knowledge of MySQL.

If you don’t have MySQL installed or don’t have any exposure to MySQL then read our articles below.

How to Backup MySQL Database in Linux?

To take a backup of MySQL databases or databases, the database must exist in the database server and you must have access to it. The format of the command would be.

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

The parameters of the said command are as follows.

  • [username] : A valid MySQL username.
  • [password] : A valid MySQL password for the user.
  • [database_name] : A valid Database name you want to take backup.
  • [dump_file.sql]: The name of the backup dump file you want to generate.

How to Backup a Single MySQL Database?

To take a backup of a single database, use the command as follows. The command will dump the database [rsyslog] structure with data onto a single dump file called rsyslog.sql.

# mysqldump -u root -ptecmint rsyslog > rsyslog.sql

How to Backup Multiple MySQL Databases?

If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data into a single file called rsyslog_syslog.sql.

# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql

How to Backup All MySQL Databases?

If you want to take a backup of all databases, then use the following command with the option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

How to Backup MySQL Database Structure Only?

If you only want the backup of the database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a file rsyslog_structure.sql.

# mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql

How to Backup MySQL Database Data Only?

To backup database data without structure, use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql.

# mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql

How to Backup a Single Table of Database?

With the below command you can take a backup of a single table or specific tables of your database. For example, the following command only takes a backup of the wp_posts table from the database wordpress.

# mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql

How to Backup Multiple Tables of Database?

If you want to take a backup of multiple or certain tables from the database, then separate each table with space.

# mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql

How to Backup Remote MySQL Database

The below command takes the backup of the remote server [172.16.25.126] database [gallery] into a local server.

# mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql

How to Restore MySQL Database?

In the above tutorial, we have seen how to take the backup of databases, tables, structures, and data only, now we will see how to restore them using the following format.

# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

How to Restore Single MySQL Database

To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example, the following command will restore the rsyslog.sql file to the rsyslog database.

# mysql -u root -ptecmint rsyslog < rsyslog.sql

If you want to restore a database that already exists on the targeted machine, then you will need to use the mysqlimport command.

# mysqlimport -u root -ptecmint rsyslog < rsyslog.sql

In the same way, you can also restore database tables, structures, and data. If you liked this article, then do share it with your friends.

Hey TecMint readers,

Exciting news! Every month, our top blog commenters will have the chance to win fantastic rewards, like free Linux eBooks such as RHCE, RHCSA, LFCS, Learn Linux, and Awk, each worth $20!

Learn more about the contest and stand a chance to win by sharing your thoughts below!

Ravi Saive
I am an experienced GNU/Linux expert and a full-stack software developer with over a decade in the field of Linux and Open Source technologies

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

49 Comments

Leave a Reply
  1. I would like to share somethings after go through this web site. I crack Linux system administrator interview on first attempt. And I got the job. And I have crack the second interview on second attempt. And I have offer letter from second one. I m very happy.

    Reply
  2. Hi Ravi, backup and restore commands are really useful.

    I was looking for the command to know the backup status. can you please help on that.

    Reply
    • @Siva,

      There isn’t any command that shows backup status, you need to check manually after taking backup manually or through script…or you can set a ‘mail to’ in script to get email notification once backup is complete..

      Reply
  3. Your last item regarding mysqlimport is not accurate. Two problems:
    1) < character is not used
    2) mysqlimport is for importing text files (csv) and the file name needs to be the table name. The program will automatically use the string in front of the period as the table name…so it errors out because it can't find a table named rsyslog in the database rsyslog.

    Reply
    • @Tad,

      Thanks for pointing out, yes it should be mysql or mysqladmin for restoring databases. I will make the changes as suggested..

      Reply
  4. Hi,

    I am using follwoing command, but it generates #1049 unknown database error.

    mysql -u [username] –p[password] [database_name] < [dump_file.sql]

    Do you have any idea why?

    Thanks in adavnce!!
    Mamta

    Reply
    • @Mamta,
      This means you’re trying to connect to a database that does not present in the MySQL. Please check your database name and try again.

      Reply
  5. HI Ravi ,

    I backup all the DBes using this command mysqldump -u root -ptecmint –all-databases > all-databases.sql

    , How to sliptup this file now .

    Please advice .

    Shufil

    Reply
  6. Hello I am new to mysql i Have to create the mysql user with proper privileges to restore the dump what privileges should i give for that user. Can you please help me.

    Reply
  7. There´s yet a backup that include: tables, procedures, functions, etc… all of estructures:

    mysqldump -u user name_of_database –routines > backupFullStructures.sql

    I´ve used this

    Reply
  8. hi,
    How to restore only data to existing database….
    i tried following…bt does’t worked…

    mysqlimport -u root -proot ml < c:\ml.sql

    Reply
    • From the mysql shell type the following command to give access to other or any remote machine.

      mysql> GRANT ALL PRIVILEGES ON databasename.* TO username@'remote-hoste' IDENTIFIED BY 'password';
      
      Reply
  9. Do I must change folder in linux before mysqldump (maybe /var/..)

    # mysqldump -u username -p –all-databases > dump.sql
    And then restore them all using:

    # mysql -u username -p < dump.sql

    Reply
  10. how to restore whole database(including all the databases) in one go. because i took backup of all database in single .sql file ,now how to restore them via command line (linux)…

    Reply
    • First dump all your databases into one file using:

      # mysqldump -u username -p –all-databases > dump.sql 
      

      And then restore them all using:

      # mysql -u username -p < dump.sql
      
      Reply
  11. I just had upgraded/installed my mysql version from 5.1 to 5.5 redhat in a way that i had to remove the existing version using the command ‘rpm -e’ then installed the new version. I successfully installed the new version which is the mysql 5.5 but the problem is when i was going to restore my database using innobackupex it fails. It could not copy the file or database to its targeted folder when i use the command ‘innobackupex –copy-back /home/backup’. I did not use the mysqldump during the installation or upgrade. I need your help on this sir..thank you.

    Reply
  12. hi
    thank you for these articles
    I want to back up all tables form a database , except only one table (for example or two tables)
    what do i do ?

    Reply
  13. Thanks for this. I have not yet tried it. But sure this will help me to backup and restore my databases faster than the usual way. Specially those databases larger than 1GB.

    Reply
  14. Thanks for such a nice and consolidated information. please tell how to restore a single table, will it not overwrite the existing one? if so can it be prevented?

    Reply
      • I’ve attempted to restore…or update my database on my development machine with a MySQL dump file I made on my production machine.

        when logged into my dev machine, used your restore command, and it just didn’t do anything….lots of info explaining how MySQl import works, but it did NOT import the database and overwrite the residing db.

        The ONLY difference in my code was that I wait for the machine to ask me for my password, and I have a full path to the db file I want to import. I changed the name of the file to just the name of the db.sql.

        Have any suggestions as to why it should not work?

        mysqlimport -u root -p dbname < /home/web/pathtofile/dbname.sql is the command I used.

        can you help me?

        Reply
        • No you can’t overwrite database on existing database, the only way to restore database is, first remove the existing database and create new database with same name and then try the restore command it will work.

          Reply

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.