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:-
| Option | Effect |
| — | # 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-table | Add a DROP TABLE statement before each CREATE TABLE statement. Useful if you want to replace tables in the server. |
| –no-create-db | Suppress 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:
- Store the dump file in the source server
- Copy the dump file to your destination server
- 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.