The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL: add user to database

Discussion in 'cPanel Developers' started by Kurieuo, Aug 12, 2010.

  1. Kurieuo

    Kurieuo Well-Known Member

    Joined:
    Dec 13, 2002
    Messages:
    98
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    Hi,

    This is perhaps more of a general question, however I am trying to automate creating a database, user and adding the user to the database. I create a shell script as follows:

    db="create database dbname;GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost' IDENTIFIED BY 'password';FLUSH PRIVILEGES;"
    mysql -e "$db"

    The database gets created along with the user, however when I check MySQL databases within cPanel, the user hasn't been added to the database.

    Am I doing something wrong? This is just bugging me, so any help appreciated.

    Thanks!
     
  2. Kurieuo

    Kurieuo Well-Known Member

    Joined:
    Dec 13, 2002
    Messages:
    98
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    The user is actually getting added to the database, but cPanel doesn't seem to pick it up.

    Is there somewhere that cPanel regulates which users belong to what database outside of MySQL?
     
  3. sirdopes

    sirdopes Well-Known Member
    PartnerNOC

    Joined:
    Sep 25, 2007
    Messages:
    141
    Likes Received:
    0
    Trophy Points:
    16
    Cpanel uses the format cpuser_db name for the database name. The same format is used for the user, cpuser_username. Cpanel did just start supporting names with the cpanel username at the front but it is going to need to be added through cpanel so that the db is mapped to the correct account.
     
  4. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    I recommend using our documented APIs while attempting to automate the desired task; here is relevant documentation to help you get started:
     
  5. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Hi Kurieuo,

    cPanel has at least one layer of abstraction between MySQL and the cPanel account resources. Performing direct MySQL statements may seem like a valid solution, however doing so will not guarrantee that the cPanel accounts will see or have proper access to those MySQL resources.

    sirdopes is absolutely right: you can prefix your databases, however, you're going to have serious misconfiguration once you server is running a cPanel version greater than 11.25.0.

    I STRONGLY recommend you use our APIs. The APIs are the best way to ensure the longevity of your code; consistency from it's resulting action. cPanelDon has pointed out all the relevant documentation. You should look at the Integration Blog too, specifically this article, "How do I automate XYZ after creating an account?", were I illustrate how one might automate database creation for new cPanel accounts (use a postwwwacct script hook written in PHP). I'm sure you could do something similar for you purposes.

    Regards,
    -David
     
  6. disstress

    disstress Member

    Joined:
    Jun 9, 2009
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have tested on 11.25.1 and I have no issues with database creation from a perl or bash script or automating at account creation. I am not sure if this version is one that David was referring to, but I haven't run into any major misconfiguration issues.

    As far as creating a user from the command line you will need to escape the underscore so that cPanel will properly display the user in the interface. Even without the underscore (from the username prefix) mysql still sees the user properly and you can check the mysql database to confirm.

    The following code will create a database and user and assign the appropriate privileges just as if created through the cPanel GUI, provided you alter or populate the variables:

    Code:
    mysql -e "create database if not exists ${UN}_${SQLBASE}"; mysql -Be "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE ON \`"$UN"\\_"$SQLBASE"\`.* TO '"$UN"_"$SQLUSER"'@'localhost' IDENTIFIED BY '$SQLPASS';FLUSH PRIVILEGES"
    
     
  7. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Hi guys,

    cPanel 11.25.1 has DB Mapping which is a complete abstraction of account-database relationships between cPanel and MySQL/Postgress. I don't like discouraging developers from performing operations that fit their workflow or are efficient for their processes, however I must say: if you do direct statements like this I can guarantee that heartache will befall you sooner or later. It may not obviously brake in 11.25.1, but I'd be very surprised if didn't break or produce mis-information in 11.28 without some additional follow-through in your code.

    If you decide to perform direct operations like that, by all means do so. But you'll need to script other CRUD, mainly concerning modification of files in /var/cpanel/databases/ and execution of some utility scripts in /usr/local/cpanel/bin/. There's a white paper outlining everything you need to know about DB Mapping here.

    Direct operations are completely do-able, it's just that you need to do some other stuff besides just the SQL statements. All that other stuff is done for you if you use the API methods. That's why they exist :)

    Best Coding!
    -DavidN
     
  8. Kurieuo

    Kurieuo Well-Known Member

    Joined:
    Dec 13, 2002
    Messages:
    98
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    Thanks everyone for all the responses.

    I will definitely try to leverage of the API as I already experienced heartache going against cPanel advice and upgrading MySQL before cPanel supported it. ;) Wouldn't want websites to go down that depend on such databases once when upgrading to a future version of cPanel.

    I haven't looked into the API yet and have never coded using it, so I don't know how easy/complicated it would be for a simple bash script. However, it would be great to have a script in the /scripts folder that allowed one to create a database and username with password to ensure compatibility with cPanel.

    Probably only a handful of people like myself would benefit from this though.
     
    #8 Kurieuo, Aug 14, 2010
    Last edited: Aug 14, 2010
  9. ohhai

    ohhai Registered

    Joined:
    Dec 27, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hey guys. Im setting up a server and want to auto install an application. I know there are tons of threads on these but Ive had a hard time finding a functional or useful post on it so far.

    The software needs the files and of course a database. I was thinking I could just use postwwwacct to create a db and db user for the client, copy the files over, and auto build the config file.

    All of that is fine and dandy, though I cant find out how to create the db/user under the cpanel account. I dont see a local api for this, or remote. Closest thing Ive found so far is a tool to map a DB to an account, which may work though is less than ideal.

    So I am asking here, what is the most ideal way to achieve this?

    Thankyou.
     
  10. KostonConsulting

    KostonConsulting Well-Known Member

    Joined:
    Jun 17, 2010
    Messages:
    255
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    San Francisco, CA
    cPanel Access Level:
    Root Administrator
    Looks like you'll need to use the API2 calls for MysqlFE and createdb, createdbuser, setdbuserprivileveges. These are unfortunately not documented but I was able to find them with:

    Code:
    # grep 'sub api2_' /usr/local/cpanel//Cpanel/MysqlFE.pm 
    sub api2_listhosts {
    sub api2_gethosts {
    sub api2_getalldbsinfo {
    sub api2_listdbs {
    sub api2_getmysqlprivileges {
    sub api2_getmysqlserverprivileges {
    sub api2_userdbprivs {
    sub api2_getdbuserprivileges {
    sub api2_listusersindb {
    sub api2_getdbusers {
    sub api2_listusers {
    sub api2_getalldbusersanddbs {
    sub api2_listdbsbackup {
    sub api2_dbuserexists {
    sub api2_revokedbuserprivileges {
    sub api2_deauthorizehost {
    sub api2_deletedbuser {
    sub api2_deletedb {
    sub api2_setdbuserprivileges {
    sub api2_createdbuser {
    sub api2_authorizehost {
    sub api2_createdb {
    sub api2_changedbuserpassword {
    
    You'll need to read into MysqlFE.pm to figure out which variables to pass to the API2 calls.
     
    kbuser likes this.
  11. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
  12. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    FYI: "How do I automate XYZ after creating an account?" has moved to, http://blog.cpanel.com/frequently_asked_scripts_how_do_i_automate_xyz_after_creating_an_account/

    David,

    On the off chance you see this, can you re-link to the script download page?

    "You can download a copy of the complete example script at our Developer Downloads page."

    That doesn't seem to link to it any more, and the script code is trashed in the blog post.

    Thanks,
    Michael
     
    #12 Michael-Inet, Sep 16, 2015
    Last edited: Sep 16, 2015
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    This thread, and the blog post you have referenced, is over five years old. Thus, the information and links have since changed or been removed. You can find more information about hooking the account creation process at:

    Standardized Hooks - Accounts::Create

    Thank you.
     
  14. Michael-Inet

    Michael-Inet Active Member

    Joined:
    Feb 20, 2014
    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Austin, TX, USA
    cPanel Access Level:
    Root Administrator
    Hi Everyone,

    Per the docs cPanelMichael linked to, Script Hooks were Deprecated awhile ago. These three posts (at least) should probably not be used.

    https://forums.cpanel.net/threads/mysql-add-user-to-database.162518/
    https://forums.cpanel.net/threads/db-creation-postwwwacct.313141/
    http://blog.cpanel.com/frequently_asked_scripts_how_do_i_automate_xyz_after_creating_an_account/

    # # #

    Part of the current answer to the title of this thread, "MySQL: add user to database" may be found at:

    https://forums.cpanel.net/threads/db-user-missing-from-privileges-column.497271

    # # #

    cPanelMichael,

    As this is Google's current favorite answer to searches related to using command line MySQL in cPanel, is it possible to 301 redirect all three of the above links to the above db-user-missing-from-privileges-column post? Or remove everything from them and add notes the info is now there?

    It would save the odd person a significant number of hours digging down old rabbit holes.

    Best All,
    Michael
     
  15. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I've merged these posts into the same thread.

    Thank you.
     
Loading...

Share This Page