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.