The mysqldump command in MySQL is used to create backups of MySQL databases. One of its useful options is -A or --all-databases, which allows you to dump all databases managed by the MySQL server into a single dump file.
This option is particularly handy in scenarios where you need to back up all databases at once, whether for disaster recovery, migration purposes, or simply for archival. Below are some examples demonstrating the usage of mysqldump with the -A option:
Example 1: Dump all databases to a SQL file named alldatabases.sql.
mysqldump -A > alldatabases.sql
This command directs the output of mysqldump with -A to a file named alldatabases.sql. To verify its execution, check if the file alldatabases.sql exists and contains SQL statements from all databases.
Example 2: Compress the dump output using gzip.
mysqldump -A | gzip > alldatabases.sql.gz
This command pipes the output of mysqldump into gzip for compression, saving the result to alldatabases.sql.gz. To verify, check if the file alldatabases.sql.gz exists and can be uncompressed to verify its contents.
Example 3: Dump all databases, including triggers and routines.
mysqldump -A --routines --triggers > alldatabases_with_triggers.sql
This command includes triggers and routines in addition to the database structure and data. Verify by reviewing alldatabases_with_triggers.sql for the presence of triggers and routines.
Example 4: Dump all databases with extended insert statements.
mysqldump -A --extended-insert > alldatabases_extended.sql
Using --extended-insert improves the dump file’s efficiency by grouping multiple rows into a single INSERT statement. Verification involves checking the file alldatabases_extended.sql for the extended INSERT syntax.
Example 5: Dump all databases excluding a specific database.
mysqldump -A --ignore-database=mysql > alldatabases_excluding_mysql.sql
This command excludes the mysql database from the dump. Verification involves ensuring alldatabases_excluding_mysql.sql contains data from all databases except mysql.
Example 6: Dump all databases with specific login credentials.
mysqldump -A -u username -p password > alldatabases_custom_credentials.sql
Replace username and password with actual MySQL credentials. Verify by accessing alldatabases_custom_credentials.sql using the provided credentials.
Example 7: Dump all databases with specific character set and collation.
mysqldump -A --default-character-set=utf8mb4 --collate-server=utf8mb4_unicode_ci > alldatabases_utf8.sql
This command ensures the dumped SQL file uses UTF-8 encoding and the specified collation. Verify by reviewing alldatabases_utf8.sql for the correct character set and collation settings.
Example 8: Dump all databases using a socket connection.
mysqldump -A --socket=/path/to/mysql.sock > alldatabases_socket.sql
Specify the path to your MySQL socket file. Verify by confirming the contents of alldatabases_socket.sql reflect all databases accessible through the socket connection.
Example 9: Dump all databases excluding specific tables.
mysqldump -A --ignore-table=mydb.table1 --ignore-table=mydb.table2 > alldatabases_excluding_tables.sql
Replace mydb.table1 and mydb.table2 with the tables you want to exclude. Verify by ensuring alldatabases_excluding_tables.sql does not contain data from the excluded tables.
Example 10: Dump all databases with verbose output for debugging.
mysqldump -A --verbose > alldatabases_verbose.log
This command provides detailed information during the dump process, helpful for troubleshooting and debugging. Verify by reviewing alldatabases_verbose.log for verbose output messages.
Read Also
How to Utilize Named Login Paths in mysqldump
Exclude Default Option Files in mysqldump Output
How to Print MySQL Configuration Defaults using mysqldump
Git Push: Updating Remote Repositories
Git Pull: Integrating Changes from Another Repository
Discussion about this post