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.

 

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"

 

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.

 

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