Introduction to mysqldump tool
The mysqldump program allows you to back up one or more databases by creating a text file with SQL statements that We may use to recreate the databases from scratch.
The mysqldump program is found in the MySQL installation directory's root/bin directory.
To use the mysqldump tool, go to the root/bin folder and run the mysqldump command with the arguments listed below.
The following are the most popular mysqldump options:
-
Add-drop-table
Each table in the database has a DROP TABLE statement.
-
Add-locks
Before and after each INSERT statement, use the LOCK TABLES and UNLOCK TABLES statements. It increases the speed of recovering data from a dump file.
-
All-databases
Creates a backup of the MySQL server's databases.
-
Create-options
Includes ENGINE and CHARSET options in the CREATE TABLE statement for each table.
-
Databases
The command creates a dump of one or more databases.
-
Extended-insert
This option helps speed up data restoration by combining single-row INSERT statements into a single account that inserts numerous table rows.
-
Flush-logs
Before dumping the data, it flushes the server logs. This is very beneficial when using incremental backups.
-
Lock-tables
Locking all the tables in a database during the dump ensures that the dump is a consistent snapshot.
-
No-data create
A dump file containing only the commands needed to recreate the database structure (only CREATE DATABASE, CREATE TABLE...), not the data (no INSERT statements).
-
OptBy default,
The opt is used by the mysqldump utility. The opt option enables add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, and set-charset. Skip-opt is used to disable this option. You can use skip-option name> to skip each specific option. To bypass the disable-keys option, for example, use the skip-disable-keys option.
-
Quick
This command tells mysqldump not to buffer tables in memory before writing them to the file. This option reduces the time it takes to dump data from large tables.
-
Result-file
The path to the output dump file is specified.
-
Set-charset
The command specifies the database's character set, such as latin1 or utf8.
-
Tables
The table command creates a dump of one or more tables.
-
Where
Rows that satisfy the condition mentioned in the WHERE clause are dumped. In the next section, let us now see how we can back up databases using the mysqldump tool.
In the next section, let us now see how we can back up databases using the mysqldump tool.
Backing Up databases using mysqldump tool
Let's look at some instances of how to back up databases with the mysqldump tool:
1. Making BackUp of a single database
Syntax:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <database_name> |
Where:
- The user account that will log into the MySQL server is named <username>.
- The password for the username is password>.
- The path_to_the_backup_file is specified by a path to the backup file.
- The —databases option tells the mysqldump utility to dump the databases provided.
- The database name> specifies the name of the database to be backed up.
The following command, for example, creates a backup of the database dbs1 in the file c:\backup\dbs1.sql:
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1.sql --databases dbs1 |
2. Making Back up of multiple databases:
After the —database option, you must enter a list of database names to make a backup of multiple databases:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <dbname1>[,<dbname2>, ...] |
The following command, for example, creates a backup of the dbs1 and dbs2 databases:
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1_dbs2.sql --databases dbs1dbs2 |
3. Backing up all the databases using mysqldump
The –all-database option is used to backup all databases in a MySQL Server:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --all-databases |
All databases on the current MySQL server will be backed up with the following command:
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\all_databases.sql --all-databases |
4. Making a backup of specified tables in a database
The following command is used to make a backup of given tables from a database:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> <database_name> <table_name> |
Multiple tables can also be specified after the database:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> <database_name> <table1> <table2> <table3> |
To make a backup of the table1 from the dbs1 database, the following command is used:
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\table1.sql dbs1table1 |
5. Back up the database structure using the mysqldump tool:
–n-data option is used to make the backup of the database structure:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --no-data --databases <database_name> |
The statement will create a dump file with the SQL statements required to recreate the database structure. Also, there are no INSERT statements in the dump file.
mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\classicmodels-ddl.sql --no-data --databases dbs1 |
6. Backing up of data only using mysqldump
The —no-create-info option is used to make a backup of the database data only:
mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> –-no-create-info --databases <database_name> |
The statement creates a dump file with the SQL queries needed to lock tables and insert data into them. There are no CREATE TABLE statements in this database.
The command below creates a backup of the data in the dbs1 database:
mysqldump –-user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1-data.sql –-no-create-info --databases dbs1 |
In this article, you learned how to back up databases in MySQL Server using the mysqldump tool.