Tuesday, August 20, 2019

mysqldump - sql dump with triggers, procedures, functions

--routines, -R

           Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the SELECT privilege
           for the mysql.proc table.

           The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. However,
           these statements do not include attributes such as the routine creation and modification timestamps, so when the routines are reloaded,
           they are created with timestamps equal to the reload time.

           If you require routines to be created with their original timestamp attributes, do not use --routines. Instead, dump and reload the
           contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

Example :
mysqldump --routines -u username -p database_name > database_name.sql

mysqldump error

1044: Access denied for user 'username'@'localhost' to database 'database_name' when using LOCK TABLES

Solution :
Use --single-transaction with mysqldump.
Example :
mysqldump --single-transaction --routines -u username -p database_name > database_name.sql