centmin.sh Create MySQL User and Database

Please fill in any relevant information that applies to you:

• CentOS Version: i.e. CentOS 7 64bit / AlmaLinux / Rocky Linux 8/9 ?
• Centmin Mod Version Installed: i.e. 132.00stable or 140.00beta01 or 131.00stable or 140.00beta01. Run command:
  
  Code (Text):

  cminfo versions

• Nginx Version Installed: i.e. 1.27.5 or 1.29.1 ? Run command and post output in QUOTE BBCODE tags:
  
  Code (Text):

  nginx -V

• PHP Version Installed: i.e. 7.3.33, 7.4.33, 8.0.30, 8.1.33, 8.2.29, 8.3.25, 8.4.11. Run command and post output in CODE/CODEB BBCODE tags:
  
  Code (Text):

  php -v

• MariaDB MySQL Version Installed: i.e. 10.3.xx or 10.4.xx or 10.6.xx or 10.11.xx. Run command and post output in CODE/CODEB BBCODE tags:
  
  Code (Text):

  mysqladmin ver

• When was last time updated Centmin Mod code base ? : i.e. run centmin.sh menu option 23 submenu option 2 or cmupdate command
• Persistent Config: Do you have any persistent config file options set in /etc/centminmod/custom_config.inc ? You can check via this command:
  
  Code (Text):

  cat /etc/centminmod/custom_config.inc
  

  
  Post output in CODE or CODEB bbcode tags.

Sensitive Info

For /etc/centminmod/custom_config.inc persistent file contents, you may have setup private sensitive information i.e. API keys, etc so filter those out if applicable before posting publicly on forums etc.

 

Just tried centmin.sh menu option 6 submenu option 1 and worked for me with MariaDB 10.6.23

Code (Text):

----------------------------------------------------------------
 MySQL User Database Management Sub Menu
----------------------------------------------------------------
 Command Line Version community.centminmod.com/threads/543/
----------------------------------------------------------------
1). Create MySQL User and Database
2). Add Database to Existing MySQL User
3). Add Existing Database to Existing MySQL User
4). Add Existing Database to New MySQL User
5). Change MySQL User Password
6). Delete MySQL User
7). Show Grants for MySQL User
8). Create Global MySQL User (access to all databases)
9). Create Multiple MySQL Databases and Users from File
10). Delete MySQL Database
11). List All MySQL Databases and Users
12). Return to Main Menu
----------------------------------------------------------------
Enter option [ 1 - 12 ]: 1
Do you want to proceed with this option? [y/n]: y
--------------------------------------------------------------
MySQL Admin
--------------------------------------------------------------
--------------------------------------------------------------

Code (Text):

---------------------------------
Create MySQL username:
---------------------------------
 Enter new MySQL username you want to create: user01
 Enter new MySQL username's password: pass01
---------------------------------
Create MySQL database:
---------------------------------
 Enter new MySQL database name: db01

Grants for user01@localhost
GRANT USAGE ON *.* TO `user01`@`localhost` IDENTIFIED BY PASSWORD '*C622305CD165FFAA9E89A3A6B60BDDCC5A5FD729'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db01`.* TO `user01`@`localhost`

Ok: MySQL user: user01 MySQL database: db01 created successfully

test login

Code (Text):

mysql -u user01 -ppass01 db01
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2643
Server version: 10.6.23-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [db01]> 

The log file for the menu run is at /root/centminlogs/centminmod_141.00beta01.b019_170925-000411_mysqladminshell.log and shows the same.

Code (Text):

ls -lahrt /root/centminlogs/ | grep 'mysqladminshell.log'
-rw-r--r--   1 root root 3.7K Sep 17 00:05 centminmod_141.00beta01.b019_170925-000411_mysqladminshell.log

db01 created database on file date/time

Code (Text):

ls -lahrt /var/lib/mysql/ | grep db01
drwx------   2 mysql mysql   20 Sep 17 00:05 db01

How are you checking correct creation date for user or database? Server by default uses UTC timezone unless you changed it

 

centmin.sh menu option 6 submenu option 2 also works

Code (Text):

----------------------------------------------------------------
 MySQL User Database Management Sub Menu
----------------------------------------------------------------
 Command Line Version community.centminmod.com/threads/543/
----------------------------------------------------------------
1). Create MySQL User and Database
2). Add Database to Existing MySQL User
3). Add Existing Database to Existing MySQL User
4). Add Existing Database to New MySQL User
5). Change MySQL User Password
6). Delete MySQL User
7). Show Grants for MySQL User
8). Create Global MySQL User (access to all databases)
9). Create Multiple MySQL Databases and Users from File
10). Delete MySQL Database
11). List All MySQL Databases and Users
12). Return to Main Menu
----------------------------------------------------------------
Enter option [ 1 - 12 ]: 2
Do you want to proceed with this option? [y/n]: y
--------------------------------------------------------------
MySQL Admin
--------------------------------------------------------------
--------------------------------------------------------------

Code (Text):

-------------------------------------------------------------------------
Add new database name to existing MySQL username:
-------------------------------------------------------------------------
 Enter existing MySQL username you want to add new database name to: user01
---------------------------------
Create MySQL database:
---------------------------------
 Enter new MySQL database name: db02

Grants for user01@localhost
GRANT USAGE ON *.* TO `user01`@`localhost` IDENTIFIED BY PASSWORD '*C622305CD165FFAA9E89A3A6B60BDDCC5A5FD729'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db01`.* TO `user01`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db02`.* TO `user01`@`localhost`

Ok: New MySQL database: db02 assigned to existing MySQL user: user01

Code (Text):

mysql -u user01 -ppass01 db02
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2655
Server version: 10.6.23-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [db02]> 

 

addons/mysqladmin_shell.sh is the underlying tool used for centmin.sh menu option 6, so should end up with same results

see commands for addons/mysqladmin_shell.sh at https://community.centminmod.com/threads/mysqladmin_shell-sh-shell-based-addon.543/ where

• createuserdb - creates new user and database
• setuserdb - Create an individual MySQL user and database or assign a new database to an existing MySQL user.

Code (Text):

./mysqladmin_shell.sh setuserdb
--------------------------------------------------------------
Basic MySQL Admin - create mysql user & databases
--------------------------------------------------------------
--------------------------------------------------------------
 1. Create a new MySQL username & new MySQL database
 2. Add a new database name to existing MySQL username
 3. Add an existing database name to existing MySQL username
 4. Add an existing database name to new MySQL username
 5. Exit
 Enter option number 1-5:

Code (Text):

addons/mysqladmin_shell.sh

Usage: addons/mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|createuserdb|deluser|showgrants}

Options:

  multidb
    Multiple MySQL database/user creation mode. Pass a file name containing
    db, user, and pass as 3-column entries.

  setglobaluser
    Create a MySQL username with access to all databases on the server
    without SUPER ADMIN privileges (non-root).

  setuserdb
    Create individual MySQL usernames and databases or assign a new
    database to an existing MySQL username.

  setpass
    Change MySQL username password.

  createuserdb
    Unattended create individual MySQL username & databases. Fields
    required are dbname, dbuser, and dbpass.

  deluser
    Delete MySQL usernames.

  showgrants
    Show existing MySQL username granted privileges.

there should be logged logs in /root/centminlogs if you need to inspect past centmin.sh menu or addons/tools runs output etc for troubleshooting

Code (Text):

ls -lahrt /root/centminlogs