How To Import and Export Databases in MySQL

How To Import and Export Databases in MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer’s storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.

What is mysql?

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and
non-interactive use. When used interactively, query results are presented in an ASCII-table format.
When used non-interactively (for example, as a filter), the result is presented in tab-separated
format. The output format can be changed using command options.

What is mysqldump?

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump
a database or a collection of databases for backup or transfer to another SQL server (not necessarily
a MariaDB server). The dump typically contains SQL statements to create the table, populate it, or
both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML
format.

Export a MySQL Database

Use mysqldump to export your database:

mysqldump -u username -p database_name > database_name-dump.sql

You can compress the data on the run using pipe and gzip.

mysqldump -u username -p database_name | gzip > database_name-dump.sql.gz

*Using GZIP will save a lot of space on disk for huge databases.

Import a MySQL Database

Use mysql to import your database:

Create the database first.

mysql > CREATE DATABASE database_name;

Import the database now.

mysql -u username -p database_name < database_name-dump.sql

If the file is compressed with gzip. use zcat to extract on the run.

zcat database_name-dump.sql.gz | mysql -u username -p database_name

Handy scripts for admins who do backups daily

bkpmysqlgz() {
    set -x
    DEST=${1:-/opt/backups}
    shift
    sudo mkdir -p $DEST
    DATE=$(DATE)
    if command -v pv > /dev/null 2>&1; then
        sudo mysqldump $@ | pv | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
    else
        sudo mysqldump $@ | gzip > $DEST/"${@: -1}"-$DATE.sql.gz
    fi
    ls -lh $DEST/"${@: -1}"-$DATE.sql.gz
    set +x
}

using script.

$ bkpmysqlgz /opt/backups -u root -p secret dbname