Stored procedure backup

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

I am taking the backup of mysql database with mysqldump command.

mysqldump --routines database_name > database_name.sql

But when backup is taken all the stored procedure are commented and I have to do lot of editing in the file. So there is any other way so that I got the backup of database with stored procedure , so that I need not to edit the file and restore it .

2

Hi,

Did you tried to load the dump and the routines doesn’t loaded in to your db well?

I would recommend --opt switch also. Try with that it must work.

Regards,
Istvan

3

Thanks,

I will try that.

4

I try it but it didn’t work. I have 1000 of stored procedure in my database. When I take backup of mysql with mysqldump it shows the comments. I have to remove it. It takes my lot of time.

As I have shown below:-

mysqldump --routines --no-create-info --no-data --no-create-db --opt vaneet > /tmp/outputfile.sql

DELIMITER ;;
/!50003 CREATE/ /!50020 DEFINER=root@localhost/ /*!50003 PROCEDURE sp_test()
Begin select * from friends;
end */;;
DELIMITER ;