creation process of DB in the Webmin-GUI-tool: Steps and methods
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.
Quote:
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!?
Code:
-- 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