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.

How To Use Enum Values In MySQL

The ENUM value is a valid value from a list of values. In other words, one of the values from a defined list of values forms an ENUM(or enumerated) value. MySQL allows columns to take values from a list of values. In this article, we will look into defining and usage of ENUM values in MySQL.

MySQL ENUM type

Columns in MySQL can take many different data types like integers, floating-point decimals, and string data types. Apart from the type of data that can reside in a column, MySQL can also enforce the value that can reside in the column. For example, we can restrict unique values across the columns of a table through unique indexes.

 We can also use CHECK constraints which allows for only certain values to be inserted into columns. However, the CHECK constraint works only in version 8.0.16 and onwards. For previous versions, the syntax is allowed by effectively ignored by the engine while executing queries.

 CHECK constraints can be emulated to some degree by using the SQL ENUM type in MySQL.

 An ENUM is a list of values which are explicitly enumerated. These values are attached to a column at the time of the creation of the table.

MySQL ENUM example

Let’s say we have a table that stores the weather conditions at 12 noon of the major cities. The table has a column, namely sky_condition, which can take the following values only.

• Fully Overcast
• Mostly Overcast
• Partly Overcast
• Mostly Sunny
• Bright Sunny

We can define and enforce the sky_condition column to only have these values.  If any user tries to insert a value other than one of these, an error will appear.

 In this context, the above list is an ENUM list, and every value within the list is an ENUM or ENUM value. 

We define an ENUM list at the time of table creation, and that ensures that the column can take only one of the values from the ENUM list. Let us see this with a simple example. 

Creating a table that has a column with ENUM data type

Create a table named city_weather_noon_snapshot using the following command:

CREATE TABLE city_weather_noon_snapshot (    city_id int, city_name varchar(255), day_date date, sky_condition ENUM('Fully Overcast','Mostly Overcast','Partly Overcast','Mostly Sunny','Bright Sunny'), observatory_id int );

Inserting a valid ENUM value 

Now, let’s insert a record with a valid sky_condition:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (21, 'Vienna', current_date(),'Partly Overcast',432);

The above statement succeeds.

Inserting an invalid ENUM value  

Now, try inserting a value for sky_condition column which doesn’t exist in the ENUM list.

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date(),'Partly Sunny',1885);

Since ‘Partly Sunny’ is not a valid ENUM value here we get an error message with the following feedback:

Error Code: 1265. Data truncated for column 'sky_condition' at row 1

Even though the message is cryptic, the error indicates that we could not insert the data in the column.

Inserting a NULL value to a column with ENUM data type

However, it should be noted that we can insert NULL into the column with ENUM definition. Therefore the following statement executes without error:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date(),null,1885);

 If we want to go ahead and does not want to have an error when inserting an invalid ENUM value for the column, we can use the INSERT IGNORE clause. The statement inserts an empty value for the ENUM column.

insert IGNORE into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date()-1,'Partly Sunny',1885);

The above statement succeeds.

Now, let’s select our city_weather_noon_snapshot table.

select * from city_weather_noon_snapshot

Result:

Figure 1. city_weather_noon_snapshot table

ENUM in SQL – things to lookout for

SQL ENUM values are an efficient way to store repeated values. At the same time, they check for the existence and enforcement of those values. However, there are some things that we need to be aware of before we start using them:

• The ENUM values are stored internally as integer values. The value stored is the ordinal or index position of the ENUM list. Therefore in an ENUM list of (‘trees’, ‘shrubs’,’herbs’), if a column takes on value ‘shrubs’, the value actually stored is 2. This is because ‘trees’ is assigned 1, ‘shrubs’ is assigned 2 and ‘herbs’ is assigned 3
• The actual conversion of these indexed values to string values happens in real-time at the time of the query. The storage of data, therefore, is more efficient.
• NULL values are stored as NULL internally as well.
• Invalid values which appear as empty values are internally assigned 0
• Since ENUM values are stored as integers the sorting of the columns in order by clause also follows the same ordinal scheme. Therefore the order is dependent on how the ENUM was defined instead of the lexicographical values. In the above ENUM, ‘trees’ sorts above ‘herbs’.
• If we insert a numeric integer and unquoted value into the ENUM column during insert statement, MySQL engine tries to insert the corresponding value of the ENUM at that index position, e.g.:

insert into city_weather_noon_snapshot(city_id,city_name,day_date,sky_condition,observatory_id) values (45, 'Canberra', current_date()-2,3 ,1885);

In the above query, we are inserting 3 (means 3rd value of the ENUM) and hence its ‘Partly Overcast’ that gets retrieved in the select query.

Figure 2. The output of the select statement

• It’s not a good practice to use ENUM values for numeric data. E.g., ENUM list (‘0′,’1′,’2’) as it doesn’t offer any storage benefit and can also result in confusion during query development.

CONVERT vs CAST in MySQL

In SQL, we can convert the data type of values to another data type, This process of changing the data type is also known as explicit type casting. MySQL has two functions to change the data type:

1. CAST
2. CONVERT

We will learn what is explicit data type casting, uses of MySQL CAST and MySQL CONVERT functions and finally what is the difference between MySQL CAST and MySQL CONVERT  functions.

Why Do We Need Data Type Conversion?

To perform any operation on any data type basic constraint is that data type of input data should be the same.

For example, let’s say we have two values: 10 (Data Type: Number), and ‘20’ (Data Type: String). we want to add them together using the + operator, we will get an error saying that we have “incompatible data types”. To resolve this, we need to convert String to a Number Data Type.

1. MySQL CAST()

The CAST() function explicitly changes the data type of the input value to the specified target data type.

Syntax

1. CAST(input_value AS Output_data_type)

This function has two parameters, separated by the keyword AS. Both parameters are mandatory.

Parameter Details:

• Input_value: Any type of value or data that we want to convert
• Output_data_type: Any data type in which we want to change our Input value or data.

Below are the data types that we can use to change the data type:

1. MySQL CONVERT()

The CONVERT() function is the same as MySQL CAST(), explicitly changing the data type of the input to a specified target data type. Additionally, CONVERT() function can also be used to change the character set of the input value.

Syntax

CONVERT() has two syntaxes:

1. CONVERT(Input_value, Output_data_type) : used to change the data type
2. CONVERT(Input_value USING Output_character_set) : used to change the character set

Parameter Details:

• Input_value: Any type of value or data that we want to convert
• Output_data_type: Any data type in which we want to change our Input value or data.
• Output_character_set: Any supported character set in which we want to change our Input value.

Below are the few character sets in which we can convert our value:

Note:

We can get the character set list using below command in MySQL.

MySQL command:

                 show character set

Step-by-Step Explanation of Using CAST on String to Float

1. SELECT CAST('4' as DECIMAL(10,5));

In the above example DECIMAL(M, D):

        M total number of digits and

        D denotes the number of digits after the decimal point

Note:

The first argument should be greater than the second argument

Since we have specified the second parameter as 5, our resultant float value has five zeros after the decimal point.

Below are a few examples:

#1: MySQL cast to integer  / MySQL convert to integer

1. SELECT CAST("10" as SIGNED) ;
3. SELECT CONVERT("10", SIGNED) ;

#2: MySQL cast to char /  MySQL convert to char

1. SELECT CAST(45 as CHAR(10));
3. SELECT CONVERT(45 , CHAR(10));

#3: MySQL cast float to int  /  MySQL convert float to int

1. SELECT CAST(45.10 as UNSIGNED);
3. SELECT CONVERT(45.10 , UNSIGNED);

#4: MySQL string to date conversion

1. SELECT CAST('2018-10-05 00:00:00' as DATE);
3. SELECT CAST('2018-10-05 00:00:00' as DATETIME);

#5: MySQL cast string to date

1. SELECT CONVERT('2018-10-05 00:00:00' , DATE);
3. SELECT CONVERT('2018-10-05 00:00:00' , DATETIME);

#6: MySQL converts the character set to utf8

1. SELECT CONVERT('GotIt' using utf8);

Comparison Between MySQL CAST and CONVERT Functions: