MySQL: add user to database

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.

 

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

 

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"

 

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

 

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.

 

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.

 

There's an old blog post here: Frequently Asked Scripts: "How do I automate XYZ after creating an account?" | cPanel, Inc.

It would appear that when the cPanel blogsite was migrated, the formatting within the code blocks was lost. Still, the info in that blog article should be fairly accurate and should give you some ideas about how you might want to proceed.

Regards,
-DavidN

 

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

 

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