If you run an application that includes a database, you want to be taking backups regularly. Your backup needs to include tables, views and triggers. It must also include any stored procedures / routines you are using. Otherwise, when you restore from backup, your procedures will be missing.
This article is not about Microsoft SQL Server. This concerns MySQL and its forks MariaDB and Percona.
I've found lots of people asking this question on the web, but not many of the posts I've found have a working answer.
Let's say you run the mysqldump command to export your database to a file. If you're like me, you run that command piped into gzip, to end up with a compressed backup file ending in .sql.gz. I tend to create a mysql user specifically to back up the database, so it only has the permissions it needs. I'll have to save the password for this user in a backup script, or in the .my.cnf file, so I don't want someone to be able to use that login to change the state of the database.
Then you notice the following error as your backup runs:
mysqldump: {user name} has insufficient privileges to SHOW CREATE PROCEDURE `{procedure name}`!
What has gone wrong, and what permissions do you need to give it?
When I create a user to backup a specific database, I tend to give it 6 permissions for that database
- SELECT
- LOCK TABLES
- EVENT
- TRIGGER
- SHOW VIEW
- EXECUTE
But those permissions will not enable that user to export a stored procedure as part of the backup.
I found various online forums where the suggestion was made to add CREATE ROUTINE or ALTER ROUTINE to that list. I've tried, and it doesn't work.
Here's the answer: Stored procedures are stored in the proc table within the database named mysql. This is a system database created when you install MySQL (or a fork) to store various system information.
Your backup user needs SELECT permission on that specific table.
So you need to give your backup user the 6 permissions above on the whole database it is to back up, and then SELECT permission on `mysql`.`proc`.
Command line
If you're administering MySQL through the command line, this is one easy command:
GRANT SELECT ON `mysql`.`proc` TO '{user name}'@'localhost';
phpMyAdmin
There are a great many SQL admin tools out there, but phpMyAdmin is very widely used. It's open-source and ships with popular hosting control panels such as cPanel.
If you're using phpMyAdmin, go to the database server (the left-most item in the breadcrumb, probably "localhost"). Click on User accounts. Click on "Edit privileges" against the user you're using for backups. Initially, you're shown a screen to set global permissions for that user. You want the user to have permissions for just one table (proc) in one database (mysql). So click "Database" to set permissions for a specific database.
Your then have to choose which database to grant permissions for. Click "mysql" in the option list, and click "Go"
You don't want to grant permissions to the whole 'mysql' database, just to the 'proc' table. So choose "table" at the top of the screen:
Now you need to type the name of the table into the box. The table you're assigning is 'proc'. Then click "Go".
You want to give SELECT permission to every column in the table. You'll see 4 option boxes for 4 different types of permission, each listing the columns for which the permission will be given. MySQL permissions are very granular. You want the left-hand box for SELECT. Just click "select all" at the bottom of that list. Then click "Go"!
And that's it - your backup script will now include procedures.
Add new comment