Welcome to Centmin Mod Community
Become a Member

mysqladmin_shell.sh Shell based Addon

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

  1. ModeltogTossen

    ModeltogTossen I wish I could??

    313
    97
    28
    Dec 20, 2015
    Denmark
    Ratings:
    +143
    Local Time:
    4:42 PM
    1.9.12
    10.0.23
    Oh - did not read up - so the question was in use of @eva2000 mysqladmin_shell.sh script. That I have not used.. But the other idea works..
     
  2. ModeltogTossen

    ModeltogTossen I wish I could??

    313
    97
    28
    Dec 20, 2015
    Denmark
    Ratings:
    +143
    Local Time:
    4:42 PM
    1.9.12
    10.0.23
  3. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    I prefer the more simple way like:

    Code:
    /root/tools/mysqladmin_shell.sh database1 12345 userdata1 showgrants
    But i am not sure if syntax is correct and if it is ok for the user to get privileges on the db.....
     
  4. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
  5. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Ok i read it again :)

    If i got it running only:

    Code:
    /root/tools/mysqladmin_shell.sh setuserdb
    I will get prompts to create a database and a user and a password for this user and it will automatically add privileges also for this user in the database?
     
  6. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    yup
     
  7. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Is it exactly the same as this (same permissions for user?):

    Code:
    create database1;
    grant all privileges on database1.* to datauser1@'%' identified by '123456';
    flush privileges;
    quit;
    
    The '%' does that the datauser1 is allowed from everywhere.
     
  8. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    no one user to one database pairing see again from 1st post example
     
  9. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    How can i adjust the above commands to get the same result one user to one database pairing? :)
     
  10. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    that's what it does when you run
    Code:
    /root/tools/mysqladmin_shell.sh setuserdb
     
  11. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Yea but i just asking to know how can i do it manually also by adjusting the:

    Code:
    grant all privileges on database1.* to datauser1@'%' identified by '123456';
    :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    best to read up on Getting Started Guide step 11 linked into if you want command client grants or https://community.centminmod.com/posts/24985/
     
  13. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Does it ask when it creates a new database if i want to set it for example to utf8_general_ci ?
     
  14. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    no it doesn't as that usually is done at server global and table levels

    see characterset and collation setting/change guide on official site MariaDB MySQL - CentminMod.com LEMP Nginx web stack for CentOS
     
  15. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Phpmyadmin is giving that option when you try to create a database and is super easy.....not need to run so many commands :(

    Isn't good if it is possible to ask what collation the database will be at creation time using mysqladmin shell?
     
  16. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    you don't need phpmyadmin, if you set default character set in mysql /etc/my.cnf any databases created automatically are UTF8 and matching collation
     
  17. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Ok but setting this:

    Code:
    character-set-server=utf8
    It can be any utf8 general or unicode or ..... :(

    Can i set it as:

    Code:
    character-set-server=utf8_general_ci
    ?
     
    Last edited: Feb 7, 2016
  18. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    read MySQL :: MySQL 5.7 Reference Manual :: 10.1 Character Set Support

    utf8 is character set and utf8_general_ci is the collation they are a pair. When you set UTF-8 default at server level, all databases have uft8 character set and uft8_general_ci collation.

    it's shown at MariaDB MySQL - CentminMod.com LEMP Nginx web stack for CentOS too
    Code:
    mysqladmin var | grep -E 'character|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 |
    and

    Code:
    mysql -e "show character set;"
    +----------+-----------------------------+---------------------+--------+
    | Charset  | Description                 | Default collation   | Maxlen |
    +----------+-----------------------------+---------------------+--------+
    | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
    | dec8     | DEC West European           | dec8_swedish_ci     |      1 |
    | cp850    | DOS West European           | cp850_general_ci    |      1 |
    | hp8      | HP West European            | hp8_english_ci      |      1 |
    | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
    | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
    | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
    | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
    | ascii    | US ASCII                    | ascii_general_ci    |      1 |
    | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
    | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
    | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
    | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
    | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
    | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
    | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
    | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
    | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
    | gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
    | latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
    | armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
    | utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
    | ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
    | cp866    | DOS Russian                 | cp866_general_ci    |      1 |
    | keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
    | macce    | Mac Central European        | macce_general_ci    |      1 |
    | macroman | Mac West European           | macroman_general_ci |      1 |
    | cp852    | DOS Central European        | cp852_general_ci    |      1 |
    | latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
    | utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
    | cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
    | utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
    | utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
    | cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
    | cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
    | utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
    | binary   | Binary pseudo charset       | binary              |      1 |
    | geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
    | cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
    | eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
    +----------+-----------------------------+---------------------+--------+
     
  19. pamamolf

    pamamolf Premium Member Premium Member

    3,858
    379
    83
    May 31, 2014
    Ratings:
    +731
    Local Time:
    5:42 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    When i am using phpmyadmin and try to create a database is asking me about the collation and the default one is utf8 general ci.....and i can select what i want from the drop down list.

    Now how can i edit my.cnf file as i want to use mysqladmin shell script to create a new database utf8 unicode ci and then to import my database ?
     
  20. eva2000

    eva2000 Administrator Staff Member

    45,441
    10,313
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,995
    Local Time:
    1:42 AM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    why do you need utf8 unicode ci ? be very sure what you're doing by setting the my.cnf default to utf8 unicode ci for all created mysql databases - it applies to all databases created on the server