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 ?

This is what mysqladmin_shell.sh does and is early beta so you might want to test it on test VPS server before using it on live production servers

Note: Added to Centmin Mod .08 beta 03

mysqladmin_shell.sh has now been added to /addons/mysqladmin_shell.sh folder for Centmin Mod .08 beta03 and higher releases. So do not need to download via github.com

Download via github.com

• https://github.com/centminmod/centminmod/raw/123.09beta01/addons/mysqladmin_shell.sh

Code (Text):

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

Basic usage

Code (Text):

/root/tools/mysqladmin_shell.sh {setuserdb|setpass|deluser|showgrants}

Create MySQL user/pass and MySQL database and grant privileges to database

Code (Text):

/root/tools/mysqladmin_shell.sh setuserdb

Change existing MySQL user's password

Code (Text):

/root/tools/mysqladmin_shell.sh setpass

Delete MySQL user

Code (Text):

/root/tools/mysqladmin_shell.sh deluser

Show existing MySQL user's privileges via Show Grants

Code (Text):

/root/tools/mysqladmin_shell.sh showgrants

Screenshots below

---

Multiple DB Creation

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 (Text):

dbname dbusername dbpassword
db2name db2username db2password

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

Code (Text):

./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

New Global MySQL User Creation Option

September 20th, 2016: Updated Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh with new menu option = setglobaluser to create a MySQL username with has access to all databases on server but without SUPER ADMIN (root) privileges.

Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh available options

Code (Text):

./mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|deluser|showgrants}

multidb - multiple mysql databse/user creation mode passing a file name containing db, user, pass 3 column entries
setglobaluser - create a mysql username with access to all databases on server without SUPER ADMIN privileges (non-root)
setuserdb - create individual mysql username and databases or assign a new database to an existing mysql username
setpass - change mysql username password
deluser - delete a mysql usernames
showgrants - show existing mysql username granted privileges

create MySQL username = dbu1 global user with password dbp1

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: dbu1
 Enter new MySQL username's password: dbp1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dbu1@localhost                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'dbu1'@'localhost' IDENTIFIED BY PASSWORD '*5D00BE915D6AB01172C807300ED23E31F251C24F' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Ok: MySQL global user: dbu1 created successfully

mysqladmin_shell.sh createuserdb Added

New mysqladmin_shell.sh option has been added for createuserdb option. This allows unattended creating of a mysql database + mysql username + mysql password without any question prompts. Suited for scripted setups where you want to create mysql database, user and password on the command line.

Full addons/mysqladmin_shell.sh options

Code (Text):

./mysqladmin_shell.sh

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

multidb - multiple mysql databse/user creation mode passing a file name containing db, user, pass 3 column entries
setglobaluser - create a mysql username with access to all databases on server without SUPER ADMIN privileges (non-root)
setuserdb - create individual mysql username 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 dbpass
deluser - delete a mysql usernames
showgrants - show existing mysql username granted privileges

Example for createuserdb run where you pass on command line the database name, mysql username and mysql password - in this example db name = xdb1, db user = xdbuser1 and db pass = xdbpass1

Code (Text):

./mysqladmin_shell.sh createuserdb xdb1 xdbuser1 xdbpass1
--------------------------------------------------------------
Basic MySQL Admin - create mysql user & databases
--------------------------------------------------------------
--------------------------------------------------------------

Grants for xdb1@localhost
GRANT USAGE ON *.* TO 'xdb1'@'localhost' IDENTIFIED BY PASSWORD '*6D7525873C093916CA45CF6A9F12142EFC4C5F38'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `createuserdb`.* TO 'xdb1'@'localhost'

Ok: MySQL user: xdb1 MySQL database: createuserdb created successfully

This is opposed to using the old setuserdb command which creates the mysql database name, db user and db pass with question prompts interactively below example created mysql dbname = ydb1, db user = ydbuser1 and db pass = ydbpass1

Code (Text):

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

 Do you want to create a new MySQL username (type = y) or
 Add a new database name to existing MySQL username (type = n) ?
 Enter y or n: y
---------------------------------
Create MySQL username:
---------------------------------
 Enter new MySQL username you want to create: ydbuser1
 Enter new MySQL username's password: ydbpass1
---------------------------------
Create MySQL database:
---------------------------------
 Enter new MySQL database name: ydb1

Grants for ydbuser1@localhost
GRANT USAGE ON *.* TO 'ydbuser1'@'localhost' IDENTIFIED BY PASSWORD '*34CE4FFBB62702263F1970A79FB0BA6CF651489B'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `ydb1`.* TO 'ydbuser1'@'localhost'

Ok: MySQL user: ydbuser1 MySQL database: ydb1 created successfully

Updates

• 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