Post
Ask Question
dip s

dip s

  • NA
  • 256
  • 82.7k

Mysqldump command doesn't take backup of store procedure and functions

Jul 3 2021 12:40 PM

Hello,

I want to take whole back up (tables with structure and data, store procedures ,functions, triggers) of my mysql database everyday every two hrs. For that I have created a .bat file and using this file I have a created a task in task shceduler. So after every 2 hours it take backup of databse. My .bat file has code as follows=>

@echo off

set current=%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%
set filename="C:\Backup\DBName-%current%.sql"

echo %filename%

C:\"Program Files (x86)"\MySQL\bin\mysqldump.exe [database_name] 
--user=root --password=rational --routines --host="127.0.0.1" 
--port=3306 --result-file=%filename% --default-character-set=utf8 
--single-transaction=TRUE
echo backup-finished

This created a .sql file having backup of my databse. When I restored this sql file then it restores only tables with data but store procedures does not get restored. I have used --routines in command. I even tried with -R but could not get success.

I am having version of mysql 8.0.23 . After searching on internet I got to know that Use of this option(--routines or -R) requires the SELECT privilege for the mysql.proc table.  I tried to grant permission but it shows message that mysql.proc table does not exist. 

So, how to take backup of store procedures, functions and triggers? Thank you in advance.


Answers (2)