Dumping Events Using mysqldump

The mysqldump command in MySQL is used to create a backup of databases. One of its options, -E or --events, specifically enables the dumping of events along with the database structure and data. Events in MySQL are scheduled tasks that run at specified times.

Here are several examples of using mysqldump with the -E option:

Example 1: Dumping a database named mydatabase including events:

            mysqldump -u username -p --events mydatabase > mydatabase_dump.sql
        

This command exports the database mydatabase to mydatabase_dump.sql file, including any scheduled events. To verify, you can check the dump file mydatabase_dump.sql for event definitions using a text editor or MySQL client.

Example 2: Dumping all databases including events:

            mysqldump -u username -p --all-databases --events > alldatabases_dump.sql
        

This command exports all databases to alldatabases_dump.sql file, ensuring all events are included. To verify, examine the dump file to confirm the presence of event definitions.

Example 3: Dumping a database with events and compressing the output:

            mysqldump -u username -p --events mydatabase | gzip > mydatabase_dump.sql.gz
        

This command pipes the dump output to gzip for compression, creating mydatabase_dump.sql.gz file with events included. To verify, decompress the file and inspect for event declarations.

Example 4: Dumping events only (no database structure or data):

            mysqldump -u username -p --no-create-info --events mydatabase > events_only_dump.sql
        

This command exports only the events for mydatabase into events_only_dump.sql file. To verify, ensure the dump file contains event definitions without database schema or data.

Example 5: Dumping a database and events with extended insert statements:

            mysqldump -u username -p --extended-insert --events mydatabase > mydatabase_extended_dump.sql
        

This command exports mydatabase with events using extended insert statements for more compact output. To verify, examine the dump file for the presence of events and extended insert syntax.

Example 6: Dumping a database with events in a specific character set:

            mysqldump -u username -p --default-character-set=utf8 --events mydatabase > mydatabase_utf8_dump.sql
        

This command exports mydatabase with events using UTF-8 character set for compatibility. To verify, check the dump file for the correct character set declaration and event definitions.

Example 7: Dumping events and ignoring tables matching a pattern:

            mysqldump -u username -p --ignore-table=mydatabase.table_to_ignore --events mydatabase > mydatabase_filtered_dump.sql
        

This command exports mydatabase with events while ignoring specified tables, storing it in mydatabase_filtered_dump.sql. To verify, review the dump file to confirm events are included and specified tables are excluded.

Example 8: Dumping a database with events and using a specific MySQL socket:

            mysqldump -u username -p --socket=/path/to/mysql.sock --events mydatabase > mydatabase_socket_dump.sql
        

This command exports mydatabase with events, specifying a custom MySQL socket path. To verify, check the dump file for the presence of events and the correct socket path.

Example 9: Dumping a database with events and including table locks in the dump:

            mysqldump -u username -p --lock-tables --events mydatabase > mydatabase_locks_dump.sql
        

This command exports mydatabase with events, ensuring table locks are applied during the dump process. To verify, inspect the dump file for event definitions and any indications of table locking.

Example 10: Dumping a database with events and suppressing comments in the dump file:

            mysqldump -u username -p --skip-comments --events mydatabase > mydatabase_nocomments_dump.sql
        

This command exports mydatabase with events while omitting comments from the dump output. To verify, examine the dump file for the absence of comments and the presence of event definitions.

Also check similar articles.

Dumping Slave Position in mysqldump Output (Deprecated)
Dumping Replica Position in mysqldump Output
Disabling Keys in mysqldump Output
Deleting Master Logs in mysqldump (Deprecated)
Rotating Logs Before Backup in mysqldump