MySQL: add user to database

[Kurieuo]

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!

[Kurieuo]

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?

[sirdopes]

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.

[cPanelDon]

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!

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?

I recommend using our documented APIs while attempting to automate the desired task; here is relevant documentation to help you get started:

• Developer Resources
• Introduction to the cPanel API
• Authenticating API Function Calls
• cPanel XML and JSON APIs — cpanel (Using the XML and JSON APIs to call API1 and API2 functions)
• For MySQL:
  • API1 Mysql Module Documentation
  • API2 MysqlFE Module Documentation
• For PostgreSQL:
  • API1 Postgres Module Documentation
  • API2 Postgres Module Documentation

[cPanelDavidN]

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

[disstress]

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:

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"

[cPanelDavidN]

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

[Kurieuo]

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.