How to create a MySQL installation setup script?

Question

I have an application that stores its data in an MySQL database. The application is using a specific DB account with full access, the indivdual user rights are maintained on application level. Apart from root there is no other user with access to that database.

In order to install the application on a computer I need an sql script that creates the database, the application user, all tables without data, views, triggers, stored procedures, etc.

mysqldump --no-data --routines --add-drop-database --databases dbname > sqlfile will do almost all these things but I could not find any option to include the creation of the user having access to that database. Any hints?

e4c5 · Accepted Answer · 2016-09-08 06:43:11Z

1

The reason that mysqldump doesn't dump user information is because that is stored in a different database name mysql rather than in the database for your applicaation.

You cannot add this information manually to the generated sql dump either. You have two options. Create a shell script or create a separate sql file that contains user creation information. In either case your file will include statements like

GRANT ALL ON appdb.* TO 'pb_skat'@'localhost';

http://dev.mysql.com/doc/refman/5.7/en/grant.html

answered Sep 8, 2016 at 6:43

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pb_SKAT Over a year ago

Thank you e4c5. That was my concern so I will go with two scripts to be called by the installation script: database structure and user. (And retrospectively I am glad I decided to keep authorization on app level and not in the database...)

pb_SKAT Over a year ago

Sorry, I was - and still am - in a hurry as I am late with the project, so I forgot. Anyway, apart from all buttons on this form (I hope I pushed all required) I still think that a personal thank you to someone who took the time to help is the most important thing. So again e4c5: thank you very much!

e4c5 Over a year ago

All the best wtih your project

Neha Chopra · Accepted Answer · 2017-07-12 17:41:33Z

0

Before introducing the application dump to your database.

First list down the users which all are available with you:

SELECT User, Host, Password FROM mysql.user;

Check if that user has permissions using "show grants" command to perform the operation, else provide the permissions to do so.

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION

Create the database name where you want to introduce the dump.

answered Jul 12, 2017 at 17:41

Neha Chopra

1,80113 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to create a MySQL installation setup script?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related