MySQL Backup and Restore Commands for Database Administration

This article shows you several practical examples on how to perform various backup operations of MySQL databases using 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 database or collection of databases for backup into a single flat file.

How to backup mysql database

How to Backup and Restore MySQL Database

We assume that you already have MySQL installed on Linux system with administrative privileges and we assume that you already have a small amount of knowledge on MySQL. If you don’t have MySQL installed or don’t have any exposure to MySQL then read our articles below.

  1. Install MySQL Server on RHEL/CentOS 6-5, Fedora 17-12
  2. 20 MySQL Commands for Database Administration

How to Backup MySQL Database?

To take a backup of MySQL database 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 as follows.

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

How to Backup a Single MySQL Database?

To take a backup of single database, use the command as follows. The command will dump database [rsyslog] structure with data on to 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 in to 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 backup of all databases, then use the following command with 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 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 only without structure, then 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 Single Table of Database?

With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of 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 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 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 the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using 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 exist on 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.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Ravi Saive

I am Ravi Saive, creator of TecMint. A Computer Geek and Linux Guru who loves to share tricks and tips on Internet. Most Of My Servers runs on Open Source Platform called Linux. Follow Me: Twitter, Facebook and Google+

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

47 Responses

  1. JunCab says:

    can you please show how to restore all mysql databases?tnx

    • Ravi Saive says:

      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
      
  2. JunCab says:

    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.

    • Ravi Saive says:

      Did you getting any errors while running that command? Please post here, so it could be helpful to trace the problem.

  3. Gurpreet Singh says:

    Try Mydumper. It is way faster to backup and restore the database than the traditional mysqladmin way. Check this out:.

    http://nlightme.com/?p=224

  4. mohammad reza says:

    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 ?

  5. Gopu says:

    Hi,

    I want to take backup of specific databases and need to restore it into a remote server. How can I do that ?

    Thanks,

  6. Pat says:

    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.

  7. mansi says:

    Thanks for replying.

  8. mansi says:

    Can you please explain some other way of backing up MySQL?

  9. sspadmin says:

    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?

  10. Ninad says:

    Really nice tutorial with examples.. Only the syntax in restore command needs to be corrected.. Please keep up the good work..

    • Ravi Saive says:

      @Ninadas,
      Thanks for pointing out, corrected now.

      • 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?

        • Ravi Saive says:

          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.

Got something to say? Join the discussion.

Your email address will not be published. Required fields are marked *

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