creation process of DB in the Webmin-GUI-tool: Steps and methods

[dhubs]

hello everyone good day.


i am on webmin - and create & configure databases for a server.

note: i run webmin version 2xy.


generally i use the GUI-steps to create a new database/ user.

1. Create the database first Servers MySQL, Database Server, Create new database.
2. Create a new user with username + password.
3. Assign that user to the database under User Permissions give at least ALL PRIVILEGES if the app needs full access.
That?s it DB User Permissions.

well i think we can run SQL commands directly from Webmin?s SQL tab

well that said: since i have messes up some things during the manual creation process several times. .. now i am going to start again..
so far so good: one qestion: what About the host - do i Need to take care for the host?

as mentioned allready: i am generally creating dbs for WordPress - on Vhosts. well the WordPress site needs remote DB access, change 'localhost' to '%' (but again, only if necessary).

regarding the SQL-STATEMENT-Method - (instead of doin it manulally)

unfortunatly i do not have such "Execute SQL Globally in Webmin": This would be the place where i could run the full SQL sequence (database + user + grants).
I only see the per-database SQL editor - then the question is: can i still run CREATE DATABASE and CREATE USER there

Assumption; MySQL doesn?t care which DB we?re ?in? when running those commands, because they?re server-wide statements.

if ist stands so - then i could use the following SQL Template:

a reusable SQL snippet to run in the Webmin SQL editor (global or per-database, both will work)

i donot have such "Execute SQL Globally in Webmin": This would be the place where i could run the full SQL sequence (database + user + grants).

....a reusable SQL snippet to run in the Webmin SQL editor (global or per-database, will both work !?): i am not sure!

Can you help out. is this possible!?

-- STEP 1: Create the database
CREATE DATABASE IF NOT EXISTS `__DBNAME__`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- STEP 2: Create the user (replace password!)
CREATE USER IF NOT EXISTS `__DBUSER__`@'localhost'
  IDENTIFIED BY '__PASSWORD__';

-- STEP 3: Grant permissions for WordPress
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
ON `__DBNAME__`.*
TO `__DBUSER__`@'localhost';

-- STEP 4: Apply changes
FLUSH PRIVILEGES;

Some musings and questions that areis here: due to the fact that i unfortunatly do not have such "Execute SQL Globally in Webmin":
Tis would be the place where i could run the full SQL sequence (database + user + grants).


the question of the day is: Can i run this SQL-Statement in the per-Database-Execution-widget!

look forward to hear from you

regards





update: i tested to run SQL-Statements throught the "per-DB-" execution widget - (that means - not the Global - DB-Execution-Widget (which we mentioned above).

and yes: we can run here all the sql-statements that are necesary:


here a Tip for WordPress admins running Webmin without CLI access:


You don?t need to rely on Webmin?s sometimes buggy GUI to create databases and users. The per-database SQL execution widget can run any SQL statement, even global ones.


That means you can:

a. create databases (CREATE DATABASE ...),

b. create users (CREATE USER ...),

c. assign privileges (GRANT ... ON db.* TO user@host,

d. and verify everything with SHOW DATABASES; and SHOW GRANTS;.


This way, every WordPress site gets its own DB and user, which is much more secure than re-using accounts.

Once you learn the basic sequence, you?re independent of the GUI and fully in control.



greetings

[michaelk]

I would verify it all works from the command line.
https://developer.wordpress.org/adva...ting-database/

[dhubs]

good morning dear MichaelK

many thanks for the hint. Thats a great idea -

thank you for pointing to that page: https://developer.wordpress.org/adva...ting-database/

btw. I do not have full acess to CLI - i do only have the webmin-tool (on the frontend)


and i fund out last week:

- but i have here the access to a DB-Execution-widget - where i can run SQL-Commands - perhaps i am able to to lots of things there


I will do further tests and try out what is possible here.


meanwhile -many many thnks fo the sharing of this valuable tipp. Thank you very much dear MichaelK for your continued support here.

I am very glad about that!!

have a great day

greetings