How to update stored procedures in multiple databases in mysql

Question

I am very new to mysql and I have I a situation where I need to update all my stored procedure in all my database. for example I have 10 database just say:

client_1,client_2,client_3,.....client_10.

Every database have same stored procedure just say:

proc_1,proc_2,proc_3,proc_4.

So if I made any changes to one of my stored procedure then it should get updated in all other database So that I don't have to do it manually.

I know the similar question have been asked but I am looking for some different approach. So what I want is some kind of mysql query or something like that in which we will pass the name of the database like:

client_1, client_3, client_8

and changes will only made to this databases.

I am using heidiSql- 10.2 with MySQL 5.6.

Thanks.

you couuld always write a stored procedure, that goes over all databases and replaces the stroed procudre in xomeon by executing a statement — nbk, Jun 15 '20 at 18:49
@nbk can you please tell me how can I do that or can provide me any link for the help. — Akash Verma, Jun 15 '20 at 18:59

score 1 · Answer 1 · answered Jun 15 '20 at 20:09

I am not entirely sure what you are wanting to do but I think you want something like this. First save the definition of your stored procedure to a file. Make sure it doesn't contain an schema references like client1.tableA. You want it to be able to run in any copy of your schema correctly. Be sure to follow the syntax rules defined by MySQL Defining Stored Programs

Then once the stored procedure is saved you can use the mysql command line to run it for each client you want to update.

You would first connect to the database server using the mysql command line. Then issue a USE command to activate the first client database. Then run the script using the SOURCE command. See MySQL Batch Commands Then repeat for each client.

USE client1;
source c:\temp\storedProcedure.sql
USE client2;
source c:\temp\storedProcedure.sql

If this is not exactly what you needed hopefully it gives you some ideas to get you what you need.

Note that you could do the connection to the database and execute these commands via batch file instead of manually if you wanted to.

score 0 · Answer 2 · answered Jun 15 '20 at 18:48

There are no statements in MySQL that create/drop/alter multiple procedures at once. You can only change one procedure at a time.

You can write an SQL script that includes a series of statements. But it's up to you to write that script.

You may write some script in Python (or other favorite language) that outputs the SQL script.

I don't know HeidiSQL, but I doubt it has any facility to apply the same change to many procedures. Nor does any other MySQL client that I'm aware of.

How to update stored procedures in multiple databases in mysql

2 Answers2