The mysqldump command is a powerful utility in MySQL used to create backups of MySQL databases. When using the -B or --databases option, you can dump multiple databases at once. This option allows you to specify a list of databases to be dumped, separated by spaces.
Here are several examples demonstrating how to use mysqldump with the -B option:
Example 1: Dump two databases named database1 and database2:
mysqldump -u username -p --databases database1 database2 > databases.sql
This command dumps both databases database1 and database2 into a file named databases.sql.
To verify, you can check the contents of databases.sql using a text editor or the cat command in a terminal.
Example 2: Dump all databases except mysql and information_schema:
mysqldump -u username -p --databases $(mysql -u username -p -e "show databases" -s --skip-column-names | grep -Ev "mysql|information_schema") > alldatabases.sql
This command uses a subshell to list all databases and excludes mysql and information_schema using grep -Ev.
To verify, check the contents of alldatabases.sql.
Example 3: Dump databases listed in a file databases.txt:
mysqldump -u username -p --databases $(cat databases.txt) > databases_from_file.sql
This command reads database names from databases.txt and dumps each of them into databases_from_file.sql.
To verify, ensure all databases listed in databases.txt are included in databases_from_file.sql.
Example 4: Dump databases matching a pattern:
mysqldump -u username -p --databases $(mysql -u username -p -e "show databases like 'prefix%'" -s --skip-column-names) > prefixed_databases.sql
This command dumps databases whose names start with prefix into prefixed_databases.sql.
To verify, check the contents of prefixed_databases.sql.
Example 5: Dump databases using a wildcard:
mysqldump -u username -p --databases database*
This command dumps databases matching the wildcard database*.
To verify, ensure the dumped file contains databases matching the wildcard pattern.
Example 6: Dump databases using options for compression:
mysqldump -u username -p --databases database1 database2 | gzip > databases.sql.gz
This command dumps databases database1 and database2 and compresses them into databases.sql.gz.
To verify, decompress databases.sql.gz and check its contents.
Example 7: Dump databases with extended inserts:
mysqldump -u username -p --databases database1 database2 --extended-insert > extended_databases.sql
This command dumps databases database1 and database2 with extended insert statements.
To verify, review extended_databases.sql and ensure extended inserts are used.
Example 8: Dump databases with table structure only (no data):
mysqldump -u username -p --databases --no-data database1 database2 > structure_only.sql
This command dumps only the structure of tables from database1 and database2 into structure_only.sql.
To verify, examine structure_only.sql and confirm it contains table structures without data.
Example 9: Dump databases with specific tables:
mysqldump -u username -p --databases database1 --tables table1 table2 > specific_tables.sql
This command dumps table1 and table2 from database1 into specific_tables.sql.
To verify, check the contents of specific_tables.sql and confirm it includes data from table1 and table2.
Example 10: Dump databases with custom SQL queries:
mysqldump -u username -p --databases database1 --where="column='value'" > filtered_data.sql
This command dumps data from database1 where the condition column='value' is met into filtered_data.sql.
To verify, review filtered_data.sql and ensure it contains data based on the specified SQL condition.
Read Also
Including MySQL Specific Create Options in mysqldump
Using Compression in mysqldump Output
Using Complete INSERT Statements in mysqldump
Generating Compact Output in mysqldump
Setting MySQL Dump Compatibility Mode
Discussion about this post