July 7, 2020
mysqldump

Backup using mysqldump

In this article, we will be covering completely the entire mysqldump example in order to back up one or more databases in a MySql Server. Before you begin you should have the root access of the system with sudo privileges.

So let us first understand the MySQL server basic Syntax:-

OptionEffect
# Server login options
-h (–host)Host (IP address or hostname) to connect to. Default is localhost (127.0.0.1) Example: -h localhost
-u (–user)MySQL user
-p (–password)MySQL password. Important: When using -p, there must not be a space between the option and the password. Example: -pMyPassword
Dump options
–add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement. Useful if you want to replace databases in the server.
–add-drop-tableAdd a DROP TABLE statement before each CREATE TABLE statement. Useful if you want to replace tables in the server.
–no-create-dbSuppress the CREATE DATABASE statements in the dump. This is useful when you’re sure the database(s) you’re dumping already exist(s) in the server where you’ll load the dump.
-t (–no-create-info) Suppress all CREATE TABLE statements in the dump. This is useful when you want to dump only the data from the tables and will use the dump file to populate identical tables in another database/server.
-d (–no-data)Do not write table information. This will only dump the CREATE TABLE statements. Useful for creating “template” databases
-R (–routines)Include stored procedures/functions in the dump.
-K (–disable-keys)Disable keys for each table before inserting the data, and enable keys after the data is inserted. This speeds up inserts only in MyISAM tables with non-unique indexes.

Specifying username and password

> mysqldump -u username -p [other options]
Enter password:

If you need to specify the password on the command line (e.g. in a script), you can add it after the -p option without
space:

> mysqldump -u username -ppassword [other options]

If your password contains spaces or special characters, remember to use escaping depending on your shell/system.

Optionally the extended form is:

> mysqldump --user=username --password=password [other options]

(Explicitly specifying the password on the command line is Not Recommended due to security concerns.)

Creating a backup of a database or table

Create a snapshot of a whole database:

mysqldump [options] db_name > filename.sql

Create a snapshot of multiple databases:

mysqldump [options] --databases db_name1 db_name2 ... > filename.sql
mysqldump [options] --all-databases > filename.sql

Create a snapshot of one or more tables:

mysqldump [options] db_name table_name... > filename.sql

Create a snapshot excluding one or more tables:

mysqldump [options] db_name --ignore-table=tbl1 --ignore-table=tbl2 ... > filename.sql

The file extension .sql is fully a matter of style. Any extension would work.

Restoring a backup of a database or table

mysql [options] db_name < filename.sql

Note that:

-> db_name needs to be an existing database;
-> your authenticated user has sufficient privileges to execute all the commands inside your filename.sql;
-> The file extension .sql is fully a matter of style. Any extension would work.
-> You cannot specify a table name to load into even though you could specify one to dump from. This must be done within filename.sql.

Alternatively, when in the MySQL Command line tool, you can restore (or run any other script) by using the source
command:

source filename.sql

or

\. filename.sql

Transferring data from one MySQL server to another

If you need to copy a database from one server to another, you have two options:

Option 1:

  1. Store the dump file in the source server
  2. Copy the dump file to your destination server
  3. Load the dump file into your destination server

On the source server:

mysqldump [options] > dump.sql

On the destination server, copy the dump file and execute:

mysql [options] < dump.sql

Option 2:

If the destination server can connect to the host server, you can use a pipeline to copy the database from one
server to the other:

On the destination server

mysqldump [options to connect to the source server] | mysql [options]

Similarly, the script could be run on the source server, pushing to the destination. In either case, it is likely to be
significantly faster than Option 1.

mysqldump from a remote server with compression

In order to use compression over the wire for a faster transfer, pass the –compress option to mysqldump. Example:

mysqldump -h db.example.com -u username -p --compress dbname > dbname.sql

Important: If you don’t want to lock up the source db, you should also include –lock-tables=false. But you may
not get an internally consistent db image that way.

To also save the file compressed, you can pipe to gzip

mysqldump -h db.example.com -u username -p --compress dbname | gzip --stdout > dbname.sql.gz

restore a gzipped mysqldump file without uncompressing

gunzip -c dbname.sql.gz | mysql dbname -u username -p

Note: -c means write output to stdout.

Backup database with stored procedures and functions

By default stored procedures and functions or not generated by mysqldump, you will need to add the parameter —
routines (or -R):

mysqldump -u username -p -R db_name > dump.sql

When using –routines the creation and change timestamps are not maintained, instead, you should dump and
reload the contents of MySQL.proc.

Backup direct to Amazon S3 with compression

If you wish to make a complete backup of a large MySql installation and do not have sufficient local storage, you
can dump and compress it directly to an Amazon S3 bucket. It’s also a good practice to do this without having the
DB password as part of the command:

mysqldump -u root -p --host=localhost --opt --skip-lock-tables --single-transaction \
 --verbose --hex-blob --routines --triggers --all-databases |
 gzip -9 | s3cmd put - s3://s3-bucket/db-server-name.sql.gz

You are prompted for the password, after which the backup starts.

Vedant Singh

Linux System Administrator | Entrepreneur | Founder Techmion. I have started my career as a Linux system Administrator after that quit my job to start something new that excites me and started a Tech Company in order to provide the best technology to my clients. My Company website link: https://www.techmion.com/ For Bulk, SMS-Marketing follows the link : https://www.pingsms.in/

View all posts by Vedant Singh →

Leave a Reply

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