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.
dip sPosted Jul 5, 2021, 11:36 AM
Rajanikant HawaldarPosted Jul 4, 2021, 2:13 PM
Check if the code of procedures is in the text if the dump. If no, Re-check mysqldump parameters. If yes, check output of restore process for errors related to the procedures. Also, use mysqldump of same version as your dump source db