Learn about Centmin Mod LEMP Stack today
Become a Member

mysqladmin_shell.sh Shell based Addon

Discussion in 'Add Ons' started by eva2000, Jun 23, 2014.

  1. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    As per MySQL :: MySQL 5.6 Reference Manual :: 10.1 Character Set Support

    defaults for Centmin Mod MariaDB MySQL via default /etc/my.cnf
    Code:
    mysqladmin var | egrep 'char|collation' | tr -s ' '
    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | latin1_swedish_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci |
    
    to switch from latin1 to utf8, edit /etc/my.cnf as per MariaDB MySQL - CentminMod.com LEMP Nginx web stack for CentOS edit /etc/my.cnf and add under [mysqld] group and then restart mysql server
    Code:
    character-set-server=utf8
    
    end result
    Code:
    mysqladmin var | egrep 'char|collation' | tr -s ' '
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    
    or to switch from latin1 to utf but use different collation by default, edit /etc/my.cnf and set to desired collation under [mysqld] group i.e. utf8_unicode_ci and then restart mysql server
    Code:
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    
    list of available collations for utf8*
    Code:
    mysql -t -e "SHOW COLLATION LIKE 'utf8_%';" | grep -v 'utf8mb4'
    +------------------------------+---------+-----+---------+----------+---------+
    | Collation | Charset | Id | Default | Compiled | Sortlen |
    +------------------------------+---------+-----+---------+----------+---------+
    | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
    | utf8_bin | utf8 | 83 | | Yes | 1 |
    | utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
    | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
    | utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
    | utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
    | utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
    | utf8_polish_ci | utf8 | 197 | | Yes | 8 |
    | utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
    | utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
    | utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
    | utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
    | utf8_czech_ci | utf8 | 202 | | Yes | 8 |
    | utf8_danish_ci | utf8 | 203 | | Yes | 8 |
    | utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
    | utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
    | utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
    | utf8_roman_ci | utf8 | 207 | | Yes | 8 |
    | utf8_persian_ci | utf8 | 208 | | Yes | 8 |
    | utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
    | utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
    | utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
    | utf8_german2_ci | utf8 | 212 | | Yes | 8 |
    | utf8_croatian_mysql561_ci | utf8 | 213 | | Yes | 8 |
    | utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
    | utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
    | utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
    | utf8_croatian_ci | utf8 | 576 | | Yes | 8 |
    | utf8_myanmar_ci | utf8 | 577 | | Yes | 8 |
    +------------------------------+---------+-----+---------+----------+---------+
    
    end result
    Code:
    mysqladmin var | egrep 'char|collation' | tr -s ' '
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | utf8_unicode_ci |
    | collation_database | utf8_unicode_ci |
    | collation_server | utf8_unicode_ci |
    
    Just be aware, if you change these defaults, web apps can still define their own created character set and collations if they specify them in their web apps (i.e. if they create the database via php etc). In that can there's a possibility of mix collation errors if web apps forgot to specify them for all database tables or if a plugin or extension to web app creates additional database tables and doesn't specify a characterset and collation and use your mysql server defaults and result in mix collation errors. You're on your own in fixing those issues if you come across them or have to ask that web app's developers.
     
    • Informative Informative x 1
  2. pamamolf

    pamamolf Premium Member Premium Member

    3,696
    357
    83
    May 31, 2014
    Ratings:
    +687
    Local Time:
    1:08 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    It is only one database on another server and it is in utf8 unicode ci and i was thinking to create the same on the new server so i can avoid compatibility issues and wrong characters display issues...

    I am always follow the old settings for utf8 when i move to a new server and never had any issues but if something goes wrong i drop the database and create it using phpmyadmin and then re import :)

    But i just want to avoid phpmyadmin ....
     
    Last edited: Feb 24, 2016
  3. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    How to use multiple db creation but with same user and password? I'd try this but come up with error can't create user. User just create on first DB.
     
  4. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    unfortunately can't do that with multidb feature, you'd have to use manual input prompted setuserdb which will ask if you're create a new user or adding a database to existing user
    Code (Text):
    /root/tools/mysqladmin_shell.sh setuserdb

    i guess i could code it so if same mysql user/pass is detected, add the subsequent dbs to same mysql user/pass
     
  5. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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.

    So if you created a /home/dblist.txt file with multiple mysql databases listed one per line with format of dbname, dbuser and dbpass.
    Code (Text):
    dbm1 dbmuser1 dbmpass1
    dbm2 dbmuser2 dbmpass2
    dbm3 dbmuser3 dbmpass3
    dbm4 dbmuser4 dbmpass4
    dbm5 dbmuser5 dbmpass5
    dbm6 dbmuser6 dbmpass6
    dbm6b dbmuser6 dbmpass6
    dbm7 dbmuser7 dbmpass7
    dbm7b dbmuser7 dbmpass7
    dbm8 dbmuser8 dbmpass8
    dbm8b dbmuser8 dbmpass8
    dbm9 dbmuser9 dbmpass9
    dbm10 dbmuser10 dbmpass10
    dbm10b dbmuser10 dbmpass10
    dbm11 dbmuser11 dbmpass11
    dbm10c dbmuser10 dbmpass10

    Then ran multidb option to create all the mysql database, users and passwords
    Code (Text):
    ./mysqladmin_shell.sh multidb /home/dblist.txt

    output would be like
    Code (Text):
    ./mysqladmin_shell.sh multidb /home/dblist.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: dbmuser10 MySQL database: dbm10b created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser10 MySQL database: dbm10c created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser10 MySQL database: dbm10 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser11 MySQL database: dbm11 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser1 MySQL database: dbm1 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser2 MySQL database: dbm2 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser3 MySQL database: dbm3 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser4 MySQL database: dbm4 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser5 MySQL database: dbm5 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser6 MySQL database: dbm6b created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser6 MySQL database: dbm6 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser7 MySQL database: dbm7b created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser7 MySQL database: dbm7 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser8 MySQL database: dbm8b created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser8 MySQL database: dbm8 created successfully
    
    ---------------------------------
    Ok: MySQL user: dbmuser9 MySQL database: dbm9 created successfully
    

    Basically i sort the dblist.txt file on the 2nd column so that same mysql usernames are more likely to be listed right after the next instance and save the user and pass to temp file and then compare the next attempted mysql user and db creation to the user and password entry in the temp file. If it matches, skip creating the mysql user and add mysql dbname to the previously listed mysql user/pass.

    Only works for that particular mysqladmin_shell.sh multidb run. So all the databases belonging to same mysql username/password combo, need to be listed in the dblist.txt file for the same run.
     
    Last edited: Sep 19, 2016
    • Like Like x 1
    • Winner Winner x 1
  6. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    Good job @eva2000. Sadly I already install CentminMod 1.2.3-.08 version in my server :(:(:(
     
  7. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    upgrading is easy as outlined in 1st post for switching to 123.09beta01 at Beta Branch - Centmin Mod .09 beta branch Testing | Centmin Mod Community
     
  8. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
  9. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    if the mysql username was created in previous run of mysqladmin_shell.sh, it won't work
    it only works if all databases belonging to the same mysql username are ran in the single sole instance of mysqladmin_shell.sh run. If was previously ran, then the mysql username would already exist.

    so you can either remove the previous mysql databases and mysql user, or just choose a new mysql username and new mysql database names for subsequent runs.

    also read MariaDB MySQL - CentminMod.com LEMP Nginx web stack for CentOS to understand how to do it manually for managing databases and user privileges
     
  10. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    @hendry of easiest way is to have separate mysql user/pass for each database which is recommended so that compromise of one mysql user, doesn't compromise other databases

    it's easy to drop or remove existing mysql usernames see MySQL :: MySQL 5.5 Reference Manual :: 6.3 MySQL User Account Management

    Code (Text):
    mysql -e "DROP USER 'MYSQLUSERNAME'@'localhost';"


    removing an existing database and all data in database MySQL :: MySQL 5.5 Reference Manual :: 13.1.21 DROP DATABASE Syntax

    Code (Text):
    mysql -e "DROP DATABASE DBNAME;"


    listing all databases on server
    Code (Text):
    mysql -e "show databases;"


    default Mysql databases installed which should not be deleted
    Code (Text):
    mysql -e "show databases;"
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+


    using /home/dblist.txt example created mysql users at mysqladmin_shell.sh Shell based Addon | Page 3 | Centmin Mod Community

    i search mysql and can list those db usernames with dbmuser% wildcard
    Code (Text):
     mysql -N -e "SELECT User FROM mysql.user where user like 'dbmuser%';"
    +-----------+
    | dbmuser1  |
    | dbmuser10 |
    | dbmuser11 |
    | dbmuser2  |
    | dbmuser3  |
    | dbmuser4  |
    | dbmuser5  |
    | dbmuser6  |
    | dbmuser7  |
    | dbmuser8  |
    | dbmuser9  |
    +-----------+

    for loop to display drop user commands for all dbmuser% usernames
    Code (Text):
    for u in $(mysql -N -e "SELECT User FROM mysql.user where user like 'dbmuser%';"); do echo "mysql -e \"DROP USER '$u'@'localhost';\""; done
    

    example output
    Code (Text):
    for u in $(mysql -N -e "SELECT User FROM mysql.user where user like 'dbmuser%';"); do echo "mysql -e \"DROP USER '$u'@'localhost';\""; done
    mysql -e "DROP USER 'dbmuser1'@'localhost';"
    mysql -e "DROP USER 'dbmuser10'@'localhost';"
    mysql -e "DROP USER 'dbmuser11'@'localhost';"
    mysql -e "DROP USER 'dbmuser2'@'localhost';"
    mysql -e "DROP USER 'dbmuser3'@'localhost';"
    mysql -e "DROP USER 'dbmuser4'@'localhost';"
    mysql -e "DROP USER 'dbmuser5'@'localhost';"
    mysql -e "DROP USER 'dbmuser6'@'localhost';"
    mysql -e "DROP USER 'dbmuser7'@'localhost';"
    mysql -e "DROP USER 'dbmuser8'@'localhost';"
    mysql -e "DROP USER 'dbmuser9'@'localhost';"
     
    Last edited: Sep 20, 2016
    • Informative Informative x 1
  11. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    Thanks @eva2000 now I understand how to use it.

    @eva2000 it's for my a hundred dummy site, so I don't want to remember each user and pass 1 by 1 or full fill my note with this :);)
     
  12. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    manually just create mysql username that has access to all databases (*.*) as per MySQL :: MySQL 5.5 Reference Manual :: 6.3.2 Adding User Accounts and grant privileges

    where mysqlusername = mysql username and mysqluserpassword is mysql password
    Code (Text):
    mysql -e "CREATE USER 'mysqlusername'@'localhost' IDENTIFIED BY 'mysqluserpassword';"
    mysql -e "GRANT select, insert, delete, update, create, drop, alter, create temporary tables, execute, lock tables ON *.* TO 'mysqlusername'@'localhost'; flush privileges; show grants for 'mysqlusername'@'localhost';"
    
     
  13. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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 [email protected]                                                                                                                                                                                |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 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
    
    
     
    • Informative Informative x 1
  14. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    yeah @eva2000 I use it right now. I just interesting with centminmod because we can add multiDB and domain with one command which is i don't get from panel based on web browser.

    Wow.. this is nice. Is it possible for you to make multiDB addons ending with this new command? So if we want to create multiDB, it will prompt first how we want to create multiDB "with different user pass or with same user pass?". If we want to create it with the same user and pass we just fill on dblist.txt with just 'DBname without DBuser and DBpass' and ended with this command. But if not, we do it like your first made.
     
  15. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Yeah unique to Centmin Mod :D
    Haven't really thought about how to make this work with multidb option mode for global mysql user yet so for now multidb mode won't support that.
     
  16. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Updated Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh's multidb mode to support creating databases without any specified accompanying mysql username/passwords

    for example if listed in /home/dblist.txt - newdba3 database name doesn't have an accompanying mysql username and password.
    Code (Text):
    newdba1 newdbauser1 newdbapass1
    newdba2 newdbauser2 newdbapass2
    newdba3

    So when running multidb mode, newdba3 mysql user creation is skipped.
    Code (Text):
    ./mysqladmin_shell.sh multidb /home/dblist.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: skipped MySQL database: newdba3 created successfully
    
    ---------------------------------
    Ok: MySQL user: newdbauser1 MySQL database: newdba1 created successfully
    
    ---------------------------------
    Ok: MySQL user: newdbauser2 MySQL database: newdba2 created successfully

    When combined with new setglobaluser mode outlined at mysqladmin_shell.sh Shell based Addon | Page 3 | Centmin Mod Community you can just use the created mysql global user to manage databases which are not assigned a mysql username and password like newdba3 database name.
     
  17. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    Woah.. you're the man. just like what I want. I will test it later, now I'm worried about my memory. I have 1gb memory all of them is used and left around 10kb. are this normal if we use centmin mod? I search on this community but I can't found a thread about it.
     
  18. eva2000

    eva2000 Administrator Staff Member

    43,082
    9,781
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,101
    Local Time:
    9:08 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    it's how linux reports memory see Help! Linux ate my RAM!
     
  19. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    so my server is fine.

    ok, @eva2000 I already tried this new feature but come up with an error I can't use setglobaluser. so I try to update centmin mod but I'm on the latest version of 123-9beta1.
    setglobaluser.png

    centminmod 123-9beta1.png
     
  20. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    6:08 PM
    nginx/1.11.4
    MariaDB 10
    an error also on mutidb creation. it won't skipped when I fill the dblist.txt without user and pass.

    ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for ''@'localhost'
    Error: command was unsuccessful

    above error comes up when created multidb.