mysqldump to Restore and Backup MySQL database

By whgandalf | June 20, 2018

When you need to handle MySQL databases, it is useful to have a tool which lets you create backup files and also restore them properly; this can be achieved by using mysql and mysqldump commands in the command line.

To be able to use them, you have to make sure the user you will use has the privileges to manage the database you are working with, we will be using root MySQL user to go through the examples.

Are you in a rush? Read this summary (don’t forget to type password after):

    backup ~: mysqldump -u root -p [database_name] > backupfilename.sql
    Enter password:

    restore ~: mysql -u root -p [database_name] < backupfilename.sql
    Enter password:


How to backup a single MySQL database

One of the possible solutions can be mysqldump which is an effective tool to backup MySQL databases. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

The next example takes a backup of wordpressdb database and dumps the output to wordpressdb.sql

    ~: mysqldump -u root -p wordpressdb > wordpressdb.sql
    Enter password:

The wordpressdb.sql file will contain commands to drop tables, create tables and insert for all the tables in the wordpressdb database, that is your backup file.

How to backup multiple MySQL databases

Let’s start identifying what databases are we going to back up. Login to MySQL using the user and password you have (we will use root user) after this, you can list current databases managed by the user typing SHOW DATABASES;:

    ~: mysql -u databaseman -p
    Enter password:

    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | wordpressdb        |
    | sugarcrmdb         |
    | testdb             |
    +--------------------+
    4 rows in set (0.00 sec)

Now, assuming you want to take backup of both wordpressdb and sugarcrmdb database, use the mysqldump as we do it below:

    ~: mysqldump -u root -p --databases wordpressdb sugarcrmdb > wpandsugardbbk.sql
    Enter password:

After this, you can also verify if the new file contains the info you want executing:

    ~: grep -i "Current database:" /tmp/wpandsugardbbk.sql
    -- Current Database: `wordpressdb`
    -- Current Database: `sugarcrmdb`


How to backup all MySQL databases

Same principles, you just have to add the --all-databases to mysqldump command as shown below:

    ~: mysqldump -u root -p --all-databases > /your-path/all-database.sql
    Enter password:


How to restore MySQL database

Did you realize about the > operator used in the command line when backing up data? Well, restoring will use the sign with the opposite direction <. We will use mysql command to restore the wordpressdb database, shown below. When you are restoring the wordpressdb.sql on a remote database, make sure to create the wordpressdb database before you can perform the restore (we do this with our root user ).

    ~: mysql -u root -p
    Enter password:

    mysql> CREATE DATABASE wordpressdb;
    Query OK, 1 row affected (0.02 sec)

    mysql> exit;

    ~: mysql -u root -p wordpressdb < /your-path/wordpressdb.sql
    Enter password:

If you want to make sure your database has the imported data, you have to login MySQL again, select the wordpressdb database with the command USE and then list the tables of it using SHOW TABLES; like this:

    ~: mysql -u root -p
    Enter password:

    mysql> USE wordpressdb;
    Database changed

    mysql> SHOW TABLES;
    +-------------------------------------------------+
    | Tables_in_wordpressdb                           |
    +-------------------------------------------------+
    | wp_commentmeta                                  |
    | wp_comments                                     |
    | wp_links                                        |
    | wp_options                                      |
    | wp_postmeta                                     |
    | wp_posts                                        |
    | wp_term_relationships                           |
    | wp_term_taxonomy                                |
    | wp_termmeta                                     |
    | wp_terms                                        |
    | wp_usermeta                                     |
    | wp_users                                        |
    | wp_wc_download_log                              |
    | wp_wc_webhooks                                  |
    +-------------------------------------------------+
    14 rows in set (0.00 sec)

    mysql> exit;
comments powered by Disqus