The mysqldump command in MySQL is used for creating backups of MySQL databases. When used with the -R or --routines option, it includes stored routines such as functions and procedures in the dump file. This option is particularly useful when you need to backup not only the data but also the logic stored in routines within your MySQL database.
Here are some examples illustrating how to use mysqldump with the -R option:
Example 1: Dumping routines from a specific database named mydatabase:
mysqldump -u root -p --routines mydatabase > mydatabase_dump.sql
This command dumps all routines (functions and procedures) from the database mydatabase into a file named mydatabase_dump.sql.
Example 2: Dumping routines from all databases on the MySQL server:
mysqldump -u root -p --routines --all-databases > all_databases_dump.sql
Here, the --all-databases option is used to dump routines from all databases on the server into a file named all_databases_dump.sql.
Example 3: Dumping routines and data from a database in a single file:
mysqldump -u root -p --routines --databases mydatabase > mydatabase_dump_with_routines.sql
This command dumps both the routines and data from the database mydatabase into a file named mydatabase_dump_with_routines.sql.
Example 4: Dumping routines without the CREATE DATABASE statement:
mysqldump -u root -p --no-create-db --routines mydatabase > mydatabase_dump_no_create.sql
In this example, the --no-create-db option ensures that only the routines are dumped without including the CREATE DATABASE statement in the dump file.
Example 5: Dumping routines with extended insert statements:
mysqldump -u root -p --routines --extended-insert mydatabase > mydatabase_dump_extended.sql
The --extended-insert option enhances the dump file by using multiple-row INSERT statements, making it more efficient for large data sets.
Example 6: Dumping routines with a specific character set for the dump file:
mysqldump -u root -p --routines --default-character-set=utf8 mydatabase > mydatabase_dump_utf8.sql
Here, the --default-character-set=utf8 option specifies that the dump file should use UTF-8 character encoding.
Example 7: Dumping routines and compressing the output using gzip:
mysqldump -u root -p --routines mydatabase | gzip > mydatabase_dump.sql.gz
This command pipes the output of mysqldump into gzip to compress the dump file named mydatabase_dump.sql.gz.
Example 8: Dumping routines with specific tables excluded:
mysqldump -u root -p --routines --ignore-table=mydatabase.table1 --ignore-table=mydatabase.table2 mydatabase > mydatabase_dump_ignore_tables.sql
Using --ignore-table, this command excludes table1 and table2 from the dump while including all routines from mydatabase.
Example 9: Dumping routines using a custom login path from MySQL config:
mysqldump --login-path=myloginpath --routines mydatabase > mydatabase_dump_custom_loginpath.sql
This command uses the login path myloginpath defined in the MySQL configuration file to authenticate and dump routines from mydatabase.
Example 10: Dumping routines and specifying a custom delimiter for routines:
mysqldump -u root -p --routines --routines --routines --routines --routines --routines --routines --routines --routines --routines --delimiter=mydelimiter mydatabase > mydatabase_dump_custom_delimiter.sql
This command uses the --delimiter=mydelimiter option to specify a custom delimiter for routines in the dump file named mydatabase_dump_custom_delimiter.sql.
To verify whether the mysqldump command executed successfully, you can check the content of the generated dump file. For example, after executing mysqldump -u root -p --routines mydatabase > mydatabase_dump.sql, you can open mydatabase_dump.sql and ensure that it contains the definitions of all routines from mydatabase.
Also check similar articles.
Directing Output to File in mysqldump
Using REPLACE INTO Instead of INSERT INTO in mysqldump
Quoting Table and Column Names in mysqldump Output
Dumping Quickly without Buffering in mysqldump
Setting Connection Protocol in mysqldump
Discussion about this post