Tag Archives: backup

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

How to use rsync with ssh

Rsync is a fast and extraordinarily versatile file copying tool. It can copy locally, to/from another host over any remote shell, or to/from a remote rsync daemon. It offers a large number of options that control every aspect of its behavior and permit very flexible specification of the set of files to be copied. It is famous for its delta-transfer algorithm, which reduces the amount of data sent over the network by sending only the differences between the source files and the existing files in the destination. Rsync is widely used for backups and mirroring and as an improved copy command for everyday use.

Rsync finds files that need to be transferred using a lqquick checkrq algorithm (by default) that looks for files that have changed in size or in last-modified time. Any changes in the other preserved attributes (as requested by options) are made on the destination file directly when the quick check indicates that the file’s data does not need to be updated.

While tar over ssh is ideal for making remote copies of parts of a filesystem, rsync is even better suited for keeping the filesystem in sync between two machines. Typically, tar is used for the initial copy, and rsync is used to pick up whatever has changed since the last copy. This is because tar tends to be faster than rsync when none of the destination files exist, but rsync is much faster than tar when there are only a few differences between the two filesystems.
To run an rsync over ssh, pass it the -e switch, like this:
[root@host]# rsync -ave ssh remote_server:/home/backups/ /home/user/server_backups/
Notice the trailing / on the file spec from the source side  On the source specification, a trailing / tells rsync to copy the contents of the directory, but not the directory itself. To include the directory as the top level of whatever is being copied, leave off the /:
[root@host]# rsync -ave ssh remote_server:/home/backups .
By default, rsync will only copy files and directories, but not remove them from the destination copy when they are removed from the source. To keep the copies exact, include the — delete flag:
[root@host]# rsync -ave ssh -- delete remote_server:~one/reports . 
If you run a command like this in cron, leave off the v switch. This will keep the output quiet (unless rsync has a problem running, in which case you’ll receive an email with the error output).
Using ssh as your transport for rsync traffic has the advantage of encrypting the data over the network and also takes advantage of any trust relationships you already have established using ssh client keys. For keeping large, complex directory structures in sync between two machines (especially when there are only a few differences between them), rsync is a very handy (and fast) tool to have at your disposal.