Backup Databases in MySQL using mysqldump

The Backup process is required to be carried out regularly and continuously to maintain the integrity of the databases and anticipate various problems that can cause data or structures in our databases to be lost or damaged. Various ways can be done to perform the Backup process, in MySQL we can use mysqldump.exe application.

This article will show us step-by-step on how to backup Databases in MySQL using the mysqldump command from the command line. The following shows the mysqldump.exe application file in the default MySQL installation mysql\bin folder.

mysqldump.exe placed in C:\mysql\bin

MySQL Database Dump

The mysqldump command performs backup instructions on one or several Databases and the backup file will be stored to the MySQL default folder or other specified location.

The following is a basic MySQL command through command line in Windows for a single database dump:

1. mysql\bin> mysqldump [options] db_name > [options] a_db_backup_name.sql

The backup command to several databases is as follows:

1. mysql\bin> mysqldump [options] --databases 1st_db_name 2nd_db_name 3rd_db_name > [options] several_db_backup_name.sql

Appending the “–all-databases” command will backup entire existing databases, the syntax is as follows:

1. mysql\bin> mysqldump [options] --all-databases > [options] all_db_backup_name.sql

Note:

A Database dump is an SQL file that contains table structures which are drop, create, and insert command for all tables in the database. The database dump can contain data only from the database tables by using options “-t” or “–no-create-info” of mysqldump.  

Our Playground Illustrations for Database Dump

Our existing databases are sports_teams and sports_players. The sports_teams has a table named team_detail which contains the names of teams from various countries based on the types of sports. The sports_players database has player_detail that contains the names and roles of members on a team.

#1 Example Backup Databases

We want to back up the database and save the backup file into C:\DBbackup folder. To perform a single database backup with dump file name 1db_backup, the command is as follows:

1. mysql\bin> mysqldump -u root -p sports_teams > C:\DBbackup\1db_backup.sql
3. Enter password: [type password]

To backup 2 databases consist of sports_teams and sports_players into a backup file named 2db_backup as follows:

1. mysql\bin> mysqldump -u root -p --databases sports_teams sports_players > C:\DBbackup\2db_backup.sql
3. Enter password: [type password]

If there are ONLY two databases and no other database, we can add the “–all” to backup all existing databases without writing the names of particular databases. The command is as follows

1. mysql\bin> mysqldump -u root -p --all-databases > C:\DBbackup\alldb_backup.sql
3. Enter password: [type password]

The following is a list of the backup files that we made and saved in C:\DBbackup folder.

Database dump files placed in C:\DBbackup

MySQL Dump Tables

The dump tables have commands similarity as the database dump command. It creates a dump MySQL tables file contains one table or selected tables by using an addition specified command. The following is a basic command using the mysqldump single table.

1. mysql\bin> mysqldump [options] db_name table_name > [options] a_table_backup_name.sql

Performing the dump tables with the mysqldump selected tables, the command is as follows:

1. mysql\bin> mysqldump [options] db_name 1st_table_name 2nd_table_name other_table_name > [options] several_table_backup_name.sql

The mysqldump exclude table uses “–ignore” command to specify one or more tables to be ignored in the backup. Each table must be specified with both database and table name. The command is as follows:

1. mysql\bin> mysqldump [options] db_name --ignore-table=db_name.table_name > [options] exclude_table_backup_name.sql

The following is the mysqldump table structure only uses “-d” or “–no-data” command.

1. mysql\bin> mysqldump [options] db_name table_name -d > [options] table_structure_backup_name.sql

#2 Example of MySQL Dump Tables

Let’s say, we want to back up a table on our existing database named player_detail in sports_players database. The dump table file names as a_table_backup_name and will be saved in C:\DBbackup folder. The command is as follows:

1. mysql\bin> mysqldump -u root -p sports_players player_detail > C:\DBbackup\a_table_backup_name.sql
3. Enter password: [type password]

The following is the command to create a dump file names as a_table_structure_backup_name that contains player_detail table structure only.

1. mysql\bin> mysqldump -u root -p sports_players player_detail -d > C:\DBbackup\a_table_structure_backup_name.sql
3. Enter password: [type password]

The following is a list of the dump table files in C:\DBbackup folder that we have made.

Database table dump files placed in C:\DBbackup

The mysqldump WHERE Clause

Appending the “-w” or “–where” commands as a conditional to limit data selected in the backup. The mysqldump WHERE clause has similarities with SQL commands in the SELECT statement using the WHERE clause.

The following is a basic command using the mysqldump WHERE clause.

1. mysql\bin> mysqldump [options] db_name table_name --where=”conditions”

#3 Example of  mysqldump WHERE Clause

We want to back up the data that has existed in the team_detail table but only rows where the type contains a “Football” sport. The dump file name 1_list_teamFootball_backup will be saved in C:\DBbackup folder, the command is as follows:

1. mysql\bin> mysqldump -u root -p sports_teams team_detail --where=”type LIKE ‘%Football%’” > C:\DBbackup\1_list_teamFootball_backup.sql

The following is a list row of the team names that have football type of sports.