mysqladmin_shell.sh Shell based Addon

We have phpmyadmin.sh Addon for Centmin Mod already.

How about shell based script to do some simple tasks just for creating new MySQL users/passwords, creating new MySQL databases and assigning MySQL permissions, changing MySQL user passwords and deleting MySQL users ?

mysqladmin_shell.sh is a shell script for managing MySQL users, databases, and permissions. This script provides an easy-to-use interface to perform common tasks such as creating, deleting, or modifying MySQL users and databases.

Always run command cmupdate to ensure you're on latest Centmin Mod code to get the latest addons/mysqladmin_shell.sh code.

Usage Options

Code (Text):

./mysqladmin_shell.sh
Usage: ./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.

Available Options

1. multidb: Create multiple MySQL databases and users by providing a file with the necessary details.
2. setglobaluser: Create a MySQL user with global access to all databases on the server without SUPER ADMIN privileges.
3. setuserdb: Create an individual MySQL user and database or assign a new database to an existing MySQL user.
4. setpass: Change the password of an existing MySQL user.
5. createuserdb: Create an individual MySQL user and database in an unattended mode.
6. deluser: Delete an existing MySQL user.
7. showgrants: Show the granted privileges of an existing MySQL user.

Usage Examples

1. multidb
   To create multiple MySQL databases and users using a file i.e. /home/nginx/domains/domain.com/dbfile.txt containing database, user, and password details in three columns, run:
   
   Code (Text):

   ./mysqladmin_shell.sh multidb /home/nginx/domains/domain.com/dbfile.txt
   

   
   The input file /home/nginx/domains/domain.com/dbfile.txt should be in the following format:
   
   Code (Text):

   dbname dbusername dbpassword
   db2name db2username db2password
   

   
   Example output
   
   Code (Text):

   ./mysqladmin_shell.sh multidb /home/nginx/domains/domain.com/dbfile.txt
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   ----------------------------------------------------------------------------
   Create Multiple MySQL Databases, User & Pass From specified filepath/name
   i.e. /home/nginx/domains/domain.com/dbfile.txt
   One entry per line in dbfile.txt in format of:
   databasename databaseuser databasepass
   ----------------------------------------------------------------------------
   ---------------------------------
   Ok: MySQL user: db2username MySQL database: db2name created successfully
   ---------------------------------
   Ok: MySQL user: dbusername MySQL database: dbname created successfully
   

2. setglobaluser
   To create a MySQL user with global access to all databases without SUPER ADMIN privileges, run:
   
   Code (Text):

   ./mysqladmin_shell.sh setglobaluser
   

   
   Follow the prompts to enter the desired username and password.
   Example output
   
   Code (Text):

   ./mysqladmin_shell.sh setglobaluser
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   Create a MySQL Username that has access to all Databases
   But without SUPER ADMIN privileges
    Enter new MySQL username you want to create: globalusername
    Enter new MySQL username's password: globalpassword
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Grants for globalusername@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 *.* TO `globalusername`@`localhost` IDENTIFIED BY PASSWORD '*83C5F6453A29A03C47435CEE47906380E12CE3EF' |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   Ok: MySQL global user: globalusername created successfully
   

3. setuserdb
   To create an individual MySQL user and database or assign a new database to an existing MySQL user, run:
   
   Code (Text):

   ./mysqladmin_shell.sh setuserdb
   

   
   Follow the prompts to enter the necessary information.
   As to mysqladmin_shell.sh 0.1.2+, there are now 4 options to choose from with setuserdb option (3rd and 4th option are new in 0.1.2+).
   
   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: 
   

   
   Option 1
   
   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: 1
   ---------------------------------
   Create MySQL username:
   ---------------------------------
    Enter new MySQL username you want to create: newusername1
    Enter new MySQL username's password: newusernamepass1
   ---------------------------------
   Create MySQL database:
   ---------------------------------
    Enter new MySQL database name: newusernamedb1
   Grants for newusername1@localhost
   GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
   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 `newusernamedb1`.* TO `newusername1`@`localhost`
   Ok: MySQL user: newusername1 MySQL database: newusernamedb1 created successfully
   

   
   Option 2
   
   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: 2
   -------------------------------------------------------------------------
   Add new database name to existing MySQL username:
   -------------------------------------------------------------------------
    Enter existing MySQL username you want to add new database name to: newusername1
   ---------------------------------
   Create MySQL database:
   ---------------------------------
    Enter new MySQL database name: newusernamedb2
   Grants for newusername1@localhost
   GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
   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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`localhost`
   Ok: New MySQL database: newusernamedb2 assigned to existing MySQL user: newusername1
   

   
   Option 3
   
   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: 3
   -------------------------------------------------------------------------
   Add existing database name to existing MySQL username:
   -------------------------------------------------------------------------
    Enter existing MySQL username you want to add existing database name to: newusername1
    Enter existing MySQL database name to attach to MySQL user newusername1: existingdb1
   Grants for newusername1@localhost
   GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
   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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`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 `existingdb1`.* TO `newusername1`@`localhost`
   Ok: existing MySQL database: existingdb1 assigned to existing MySQL user: newusername1
   

   
   Option 4
   
   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: 4
   -------------------------------------------------------------------------
   Add existing database name to new MySQL username:
   -------------------------------------------------------------------------
    Enter new MySQL username you want to create: newusername2
    Enter new MySQL username's password: newusername2pass
    Enter existing MySQL database name to attach to new MySQL user newusername2: existingdb1
   Grants for newusername2@localhost
   GRANT USAGE ON *.* TO `newusername2`@`localhost` IDENTIFIED BY PASSWORD '*DB5B4F64CF824616AB992AEB513699CBD6502899'
   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 `existingdb1`.* TO `newusername2`@`localhost`
   Ok: MySQL user: newusername2 MySQL database: existingdb1 created successfully
   

4. setpass
   To change the password of an existing MySQL user, run:
   
   Code (Text):

   ./mysqladmin_shell.sh setpass
   

   
   Follow the prompts to enter the username and the new password.
   Example
   
   Code (Text):

   ./mysqladmin_shell.sh setpass
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   --------------------------------------------------------------
   Change Existing MySQL username's password:
   --------------------------------------------------------------
    Enter MySQL username you want to change password for: newusername1
    Enter MySQL username's new password to change to: newusername1newpass
   Ok: Changing MySQL password for newusername1 was successful
   

5. createuserdb
   To create an individual MySQL user and database in an unattended mode, run:
   
   Code (Text):

   ./mysqladmin_shell.sh createuserdb mydbname mydbuser mydbpass
   

   
   Replace mydbname, mydbuser, and mydbpass with the desired database name, user, and password, respectively.
   Example output
   
   Code (Text):

   ./mysqladmin_shell.sh createuserdb mydbname mydbuser mydbpass
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   Grants for mydbuser@localhost
   GRANT USAGE ON *.* TO `mydbuser`@`localhost` IDENTIFIED BY PASSWORD '*656A1F1C6814E2C4D43C302508A1E82184AD5320'
   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 `mydbname`.* TO `mydbuser`@`localhost`
   Ok: MySQL user: mydbuser MySQL database: mydbname created successfully
   

6. deluser
   To delete an existing MySQL user, run:
   
   Code (Text):

   ./mysqladmin_shell.sh deluser
   

   
   Follow the prompts to enter the username to be deleted.
   Example output
   
   Code (Text):

   ./mysqladmin_shell.sh deluser
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   ---------------------------------
   Delete MySQL username:
   ---------------------------------
    Enter MySQL username you want to delete: mydbuser
   Ok: Deleting 'mydbuser'@'localhost' was successful
   

7. showgrants
   To display the granted privileges of an existing MySQL user, run:
   
   Code (Text):

   ./mysqladmin_shell.sh showgrants
   

   
   Follow the prompts to enter the username to display their granted privileges.
   Example output
   
   Code (Text):

   ./mysqladmin_shell.sh showgrants
   --------------------------------------------------------------
   Basic MySQL Admin - create mysql user & databases 
   --------------------------------------------------------------
   --------------------------------------------------------------
   ---------------------------------
   Show Grants for MySQL username:
   ---------------------------------
    Enter MySQL username to Show Grant permissions: newusername1
   Grants for newusername1@localhost
   GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*B60F2CF5DA878C3D9335B46D7B6E52B9D38B3F67'
   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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`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 `existingdb1`.* TO `newusername1`@`localhost`
   Ok: SHOW GRANTS for 'newusername1'@'localhost' was successful
   

Additional Information
The mysqladmin_shell.sh script simplifies the process of managing MySQL users, databases, and permissions by providing a user-friendly interface. Here are some additional points to consider when using the script:

1. Error Handling: The script includes error handling to inform the user if a task was unsuccessful. For instance, if the creation of a MySQL user fails, the script will display an error message to notify the user.
2. Compatibility: The script is compatible with different MySQL server versions, including MariaDB. Ensure that the MySQL server is properly installed and configured on your system before using the script.
3. Security: When using this script, be mindful of security best practices. For example, avoid using weak passwords or granting excessive privileges to users.
4. Unattended Mode: The createuserdb option allows you to create individual MySQL users and databases in an unattended mode, which can be useful for automation purposes or when deploying multiple instances.
5. File Cleanup: The script uses temporary files to store user input and automatically cleans them up upon completion or termination. This helps prevent sensitive information from being stored on the system.

Updates

• April 19th, 2023: Add additional options to setuserdb menu for adding an existing database name to existing MySQL username or adding an existing database to a new MySQL username
  
  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: 
  

• September 19th, 2016: Updated Centmin Mod 123.09beta01 branch's addons/mysqladmin_shell.sh multidb option to support detecting if the db list file has multiple mysql databases assigned to same mysql user/pass and add those mysql databases to the same mysql user/pass details here.
• September 20th, 2016: example manual SSH commands to DROP mysql usernames and database names etc.
• September 20th, 2016: Example of manually create mysql username with access to all databases.
• September 20th, 2016: Updated Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh's multidb mode to support creating databases without any specified accompanying mysql username/passwords.
• May 8th, 2017: Updated to add new createuserdb option for unattended script usage with no interactive prompts

Notes

---

Reminder when creating database names make sure you do not use hyphens on invalid identifier characters in the names. For database names and tables, hyphens aren't really a valid mysql identifiers allowed MySQL :: MySQL 5.6 Reference Manual :: 9.2 Schema Object Names. Underscores yes, hyphens no.

 

it's a standalone file so no need for phpmyadmin.sh

just download it - i like to put all my custom .sh files in /root/tools

Code:

mkdir -p /root/tools
cd /root/tools/
wget -O mysqladmin_shell.sh https://github.com/centminmod/centminmod/raw/master/addons/mysqladmin_shell.sh
chmod +x mysqladmin_shell.sh

 

Granting privileges to user accounts and databases requires grant privileges and only mysql root user has those privileges by default. So the commands need mysql root user and if password is set for mysql root user it needs to pass the password when command is run.

Set to n in screenshots, because .07+ and higher have mysql root password set with /root/.my.cnf file which has the mysql root password so no need to pass mysql root password on command line. But if you remove /root/.my.cnf and change your mysql root password, then you'd have to answer yes when prompted and enter the mysql root password

 

The /root/.my.cnf is just a stored static reference to currently set mysql root password you set at Centmin Mod install time. Changing the password in /root/.my.cnf DOES NOT change mysql root password set. But when you do change your mysql root password the proper way or this way to change msyql root password at mysql client command line, you will need to edit /root/.my.cnf with the new mysql root password if you want to run mysql client commands

 

No this addon is for creating the initial mysql username and password and assigning it (connecting it) to a newly created mysql database only which is info you add to Xenforo config.php. For backup and restore use included inbuilt tools for MySQL mysqldump/mysql command line MySQL :: MySQL 5.5 Reference Manual :: 7 Backup and Recovery and various tutorials MySQL :: MySQL 5.5 Reference Manual :: 3 Tutorial

mysqldump - MySQL :: MySQL 5.5 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

You can use phpmyadmin to backup and restore but once your database size grows, reliability of phpmyadmin for complete backups diminishes and the chance of php time outs and incomplete backup sql files grows. You could end up with corrupted or incomplete backup sql files for MySQL datbaases with phpmyadmin and no avenue to restore a fully working backup. Safest is mysqldump for backup and mysql command for restores.

 

mysqldump and mysql command restore as outlined at important notes section of official phpmyadmin addon page

Google search for 'how to use mysqldump" see lmgtfy.com/?q=how+to+use+mysqldump

 

yeah i should add this to /addons folder for Centmin Mod .08 beta03 at least for now

done committed to .08 beta03 CentOS 7.x - How to help test .08 CentOS 7 Betas with Github code ? | Page 29 | Centmin Mod Community

 

Updated addons/mysqladmin_shell.sh with multiple database creation support from a specified text file. Also fixed root prompt as it is not needed with .08 beta03+ and higher as /root/.my.cnf has a record of the mysql root password which is auto generated on initial Centmin Mod install

For example, contents of /home/dblist.txt - one entry per line in format order of

Code:

dbname dbusername dbpassword
db2name db2username db2password

run new multidb command option and enter path of dblist.txt file i.e. /home/dblist.txt

Code:

./addons/mysqladmin_shell.sh multidb
--------------------------------------------------------------
Basic MySQL Admin - create mysql user & databases
--------------------------------------------------------------
--------------------------------------------------------------

----------------------------------------------------------------------------
Create Multiple MySQL Databases, User & Pass From specified filepath/name
i.e. /home/nginx/domains/domain.com/dbfile.txt
One entry per line in dbfile.txt in format of:
databasename databaseuser databasepass
----------------------------------------------------------------------------

Enter full path to db list file i.e. /home/nginx/domains/domain.com/dbfile.txt (to exit type = x): /home/dblist.txt

Grants for dbusername@localhost
GRANT USAGE ON *.* TO 'dbusername'@'localhost' IDENTIFIED BY PASSWORD '*6207EE3F049D8F87CC0B4BBB8814917B31FB8E4F'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname`.* TO 'dbusername'@'localhost'

Ok: MySQL user:  MySQL database: dbname created successfully

Grants for db2username@localhost
GRANT USAGE ON *.* TO 'db2username'@'localhost' IDENTIFIED BY PASSWORD '*59F34589CBECBF3AB16AA51EA7E7C1D8DF068A76'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `db2name`.* TO 'db2username'@'localhost'

Ok: MySQL user:  MySQL database: db2name created successfully