MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+

MySQL Backup using mysqldump

From WikiOD

Syntax[edit | edit source]

  • mysqldump -u [username] -p[password] [other options] db_name > dumpFileName.sql /// To Backup single database
  • mysqldump -u [username] -p[password] [other options] db_name [tbl_name1 tbl_name2 tbl_name2 ...] > dumpFileName.sql /// To Backup one or more tables
  • mysqldump -u [username] -p[password] [other options] --databases db_name1 db_name2 db_name3 ... > dumpFileName.sql /// To Backup one or more complete databases
  • mysqldump -u [username] -p[password] [other options] --all-databases > dumpFileName.sql /// To Backup entire MySQL server

Parameters[edit | edit source]

Option Effect
-- # Server login options
-h (--host) Host (IP address or hostname) to connect to. Default is localhost ( 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.

Remarks[edit | edit source]

The output of a mysqldump operation is a lightly commented file containing sequential SQL statements that are compatible with the version of MySQL utilities that was used to generate it (with attention paid to compatibility with previous versions, but no guarantee for future ones). Thus, the restoration of a mysqldumped database comprises execution of those statements. Generally, this file

  • DROPs the first specified table or view
  • CREATEs that table or view
  • For tables dumped with data (i.e. without the --no-data option)
    • LOCKs the table
    • INSERTs all of the rows from the original table in one statement
  • Repeats the above for all other tables and views
  • DROPs the first included routine
  • CREATEs that routine
  • Repeats the same for all other routines

The presence of the DROP before CREATE for each table means that if the schema is present, whether or not it is empty, using a mysqldump file for its restoration will populate or overwrite the data therein.

Specifying username and password[edit | edit source]

> 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 a space:

> mysqldump -u username -ppassword [other options]

If you 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]

(Explicity specifying the password on the commandline is Not Recommended due to security concerns.)

Creating a backup of a database or table[edit | edit source]

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[edit | edit source]

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


\. filename.sql

mysqldump from a remote server with compression[edit | edit source]

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

mysqldump -h -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 -u username -p --compress dbname | gzip --stdout > dbname.sql.gz

restore a gzipped mysqldump file without uncompressing[edit | edit source]

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

Note: -c means write output to stdout.

Backup direct to Amazon S3 with compression[edit | edit source]

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.

Tranferring data from one MySQL server to another[edit | edit source]

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.

Backup database with stored procedures and functions[edit | edit source]

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 time stamps are not maintained, instead you should dump and reload the contents of mysql.proc.