The mysqldump command in MySQL is used to create backups of databases. One useful option is -K or --disable-keys, which allows you to generate dump files without including statements to disable and enable keys for the tables. This can be beneficial when you want to create a dump file quickly without worrying about the integrity of the data due to foreign key constraints.
Here are several examples of how to use mysqldump with the --disable-keys option:
Example 1: Dumping a single database named mydatabase with disabled keys.
mysqldump -K mydatabase > mydatabase_dump.sql
Explanation: This command dumps the mydatabase database into the file mydatabase_dump.sql with keys disabled. To verify, open the generated SQL file and search for occurrences of /*!40000 ALTER TABLE `table_name` DISABLE KEYS */; which indicates keys are disabled.
Example 2: Dumping all databases on the MySQL server with disabled keys.
mysqldump -A -K > all_databases_dump.sql
Explanation: This command dumps all databases into all_databases_dump.sql with keys disabled. Check the SQL file for similar disable keys statements as in Example 1.
Example 3: Dumping a specific table mytable from the mydatabase database with keys disabled.
mysqldump -K mydatabase mytable > mytable_dump.sql
Explanation: This command dumps only the mytable table from mydatabase into mytable_dump.sql with keys disabled. Verify the SQL file for disable keys statements specific to mytable.
Example 4: Dumping a database and excluding certain tables while disabling keys.
mysqldump -K mydatabase --ignore-table=mydatabase.table1 --ignore-table=mydatabase.table2 > mydatabase_dump.sql
Explanation: Here, table1 and table2 are excluded from the dump of mydatabase, with keys disabled for the other tables. Verify the SQL file to ensure that keys are only disabled for included tables.
Example 5: Dumping multiple databases and excluding tables with keys disabled.
mysqldump -K --databases mydatabase1 mydatabase2 > databases_dump.sql
Explanation: This command dumps mydatabase1 and mydatabase2 with keys disabled. Verify the SQL file to confirm the absence of enable keys statements.
Example 6: Dumping a database with tables sorted by primary key with disabled keys.
mysqldump -K --order-by-primary mydatabase > mydatabase_sorted_dump.sql
Explanation: The --order-by-primary option sorts tables by primary key when dumping, with keys disabled. Verify the dumped SQL file to ensure tables are ordered by primary key and keys are disabled.
Example 7: Dumping a database with extended inserts and disabled keys.
mysqldump -K --extended-insert mydatabase > mydatabase_extended_dump.sql
Explanation: This command uses extended inserts for more efficient dumping, with keys disabled. Verify the SQL file to confirm the use of extended inserts and disabled keys.
Example 8: Dumping a database while including CREATE DATABASE statement and disabling keys.
mysqldump -K --databases --add-drop-database mydatabase > mydatabase_with_create_dump.sql
Explanation: Here, the dump includes the CREATE DATABASE statement and disables keys. Verify the SQL file to see the CREATE DATABASE statement and confirm keys are disabled.
Example 9: Dumping a database and compressing the output with disabled keys.
mysqldump -K mydatabase | gzip > mydatabase_dump.sql.gz
Explanation: This command dumps mydatabase and compresses the output using gzip, with keys disabled. Verify by decompressing the file and checking for disabled keys statements.
Example 10: Dumping a database with stored procedures and disabled keys.
mysqldump -K --routines mydatabase > mydatabase_with_procs_dump.sql
Explanation: This command dumps mydatabase with stored procedures included, and keys disabled. Verify the SQL file to ensure stored procedures are included and keys are disabled.
Also check similar articles.
Deleting Master Logs in mysqldump (Deprecated)
Rotating Logs Before Backup in mysqldump
Setting Default Character Set in mysqldump
Debug Information in mysqldump
Debug Check in mysqldump
Discussion about this post