The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another server. The mysqldump command can also generate output in CSV, other delimited text, or XML format. Although it appears to be a little executable file, its scope is greater to accommodate a variety of use-cases.
mysqldump accepts various connection options, DDL Options, Format Options, Filtering Options, Performance Options, Transactional Options, etc.
This post will demonstrate how to use mysqldump to include or exclude the “CREATE DATABASE” statement inside the backup file.
1)
To include CREATE DATABASE statement, add –databases option to it as shown below.
Syntax:
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
–databases dbname > schema.sql
A “CREATE DATABASE IF NOT EXISTS” Statement will be added to the backup or dump file when you specify the –databases option. Included in this is “USE DATABASE.” This indicates that the database being backed up will have the same database name for the statements to build the database and use the database. If you wish to restore the dump file in a separate instance, it is a good thing. In other words, creating a copy of the database on a different system is a solid strategy. Since the dump file will replace the database objects, it shouldn’t be run in the same instance.
Examples:
mysqldump -h 127.0.0.1 -u root -p –databases dbname > schema.sql
mysqldump -h 127.0.0.1 -u root -pMyPassword –databases dbname > schema.sql
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p
–databases dbname > schema.sql
2)
To exclude ‘CREATE DATABASE’ Statement, omit “–databases” option as shown below.
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
dbname > schema.sql
The “CREATE DATABASE IF NOT EXISTS” Statement will not be added to the backup or dump file if the —databases option is not specified. There won’t be a “USE DATABASE” button. This indicates that all the objects, including tables, table data, triggers, etc., will be present in the database that is being backed up. If you wish to restore the dump file in a separate instance where the database already exists, this method works well. In other words, it is a good idea to create a copy of the database on the same instance or on a different machine but with a different name. Since the dump file will replace the database objects, it shouldn’t be run in the same instance.
Examples:
mysqldump -h 127.0.0.1 -u root -p dbname > schema.sql
mysqldump -h 127.0.0.1 -u root -pMyPassword dbname > schema.sql
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p dbname > schema.sql
3)
The ‘CREATE DATABASE IF EXISTS’ Option will not be present in your backup file if –no-create-db and –databases options are used, but the USE Statement will be. The target database cannot have a different name than the source database because the USE DATABASE statement uses the source database’s name as the target database’s name.
Example:
mysqldump -h 127.0.0.1 -u root -p –no-create-db –databases dbname > schema.sql
4)
If you specify –no-create-db without –databases option, it is equal to Point-1 in which the option –databases doesn’t have. Similar to point(1), it will not have ‘CREATE DATABASE IF EXISTS’ Statement and ‘USE DATABASE’ Statement. Hence the target database name can be anything.
Example:
mysqldump -h 127.0.0.1 -u root -p –no-create-db dbname > schema.sql
Happy learning!!
Big Data and SQL 