Transfers among cPanel&WHM servers sharing a MySQL database server [Case 45083]

[mephisto]

Hello.
My configuration consists of 3 servers:
cpanel1
cpanel2
mysql

I keep all DBs on mysql server - it is being accessed from both cpanels. I am moving some accounts from cpanel1 to cpanel2. After moving accounts and testing everything I would like to remove accounts on the old server.

Unfortunately I couldn't find a parameter to killacct, which would allow me to remove an account with MySQL DBs untouched. There is only an option to leave DNS zones, which I use.

Any suggestions how to remove an account and leave the dbs in place? Maybe there is a way to detach databases from specific cPanel user - that way killacct wouldn't remove them.

All suggestions are welcome.

Thanks,
mephisto

[mephisto]

Pushing this up. Anyone has a solution? I read about MySQL mappings, maybe I could use this new feature to unmap specific DBs from a user?

[cPanelTristan]

You could likely use that feature to unmap a database from a user temporarily. I'd have to actually test this out to see the results, but it is likely possible with the database mapping to perform this task to terminate an account without the database being removed by removing the mapping to that database.

I'll be testing this later today on one of my machine and post back with details on whether I figured out how to accomplish it.

Edit: So far, I haven't been able to determine a way to disassociate the database with the user. Changing the cPanel files in any fashion, revoking grants on the databases, none are working to prevent the database from being removed on account deletion. The best idea I can see right now is to simply use mysqldump to get a good copy of the database, rename the database, then terminate the account:

mysqldump dbname --add-locks --create-options > /home/dbname.sql
cd /var/lib/mysql
mv dbname dbname.bak
/scripts/killacct username

If you do those steps, it won't find the database for the termination. You can then move the database back or restore from the backup SQL file.

[mephisto]

Thanks Tristian. I was looking for something more sophisticated, but Your method works as well.

Anyone trying this method in future please remember to dump entries from db and user tables in mysql DB. After deleting the user and copying the database back in place You need to restore these entries and flush privileges to keep the same user credentials working.

Cheers.

[WebXSolution]

Dear Team,

I would like to request that cPanel starts to support more than one cPanel/WHM server using one shared remote MySQL database server.

Kindest Regards,


Mark Smeed

[WebXSolution]

Dear Team,

We have multiple cPanel Servers using one shared Remote Database. Since 11.28 we are no longer able to transfer users between servers as the account creation script throws the error 'mysql user already exists'.

Can you please add an option in the transfer screen to be able to ignore that error.

Kindest Regards,


Mark Smeed

[cPanelDavidG]

Dear Team,

We have multiple cPanel Servers using one shared Remote Database. Since 11.28 we are no longer able to transfer users between servers as the account creation script throws the error 'mysql user already exists'.

Can you please add an option in the transfer screen to be able to ignore that error.

Kindest Regards,


Mark Smeed

To clarify: while multiple cPanel & WHM servers using 1 shared remote database server is a common deployment tactic, it is not a configuration we officially support at this time.

However, in the spirit of this being a feature request rather than a bug report, I will leave this thread open so this can be discussed further and others can voice their support.

One question I have is, now that we're discussing 11.28, how should our transfer script determine if you intend to "overwrite" a database or if you are unintentionally clashing databases. Keep in mind, not all 11.28 servers will have prefixing enabled, so the assumption of "well, it starts with the cPanel user's username" is no longer an accurate assumption.

Or should this feature come with a warning and just assume that if you use this feature that you are aware of the consequences of using this feature?

[cPanelDavidG]

I know this is a common deployment tactic. Does anyone that currently deploys servers like this have insight into what issues we need to resolve?

For example, I know there's a feature request addressing that copying accounts from one server to another using this same remote database server results in "database already exists" errors.

[markb14391]

Our users are now getting the "mysql user already exists" error when trying to create a mysql username that matches the name of the database...something some of them have done for some time now.

Is this a new "feature"? If so, how will it affect existing databases with usernames that are the same as the db name?

Thanks.

[WebXSolution]

Hi cPanelDavidG,

From what I understand and have seen of 11.28, the script checks to see if the accounts username already exists in the database, and if it does then it fails.

Therefore am I right in assuming if the prefix is different, then the script will not fall over on this error? If I am right, then it would not affect them?

A tick box in the transfer screens saying 'Ignore Database Overwrite Warning' could suffice, or atleast some wording to that effect.

One thought crossed my mind is that other users might be confused by the option as they are not aware of the conflict. So if you did agree to the additional feature, I would have thought you might need to add a description to the feature.

BTW: I have noticed that the script already allows for the option '--force', which ignores the DB error. Which has helped a lot.

Kindest Regards,


Mark

[WebXSolution]

Hi cPanelDavidG,

1. When upgrading server 1 to the latest version of cPanel, the other server's roundcube db user losses rights to the roundcubemail db.

2. When server 1 has a user, say 'marks', that is transfered to server 2 and the webmaster removes the account from server 1, all of 'marks' databases are deleted.

3. When server 1 has a user, say 'john', that is transfered to server 2 and the webmaster does not removes the account from server 1, at midnight server 2's 'john' database user losses all rights to access the shared 'john' databases.

I hope that makes sense and helps .

Kindest Regards,


Mark

[mephisto]

Hello Mark.
I am receiving the same problem on WHM 11.28. Can You write where did You set the --force flag? I tried creating a wrapper to run /scripts/pkgacct with this flag, but it didn't work.

Looking forward to Your reply.

Mephisto

[cPanelDavidG]

Hi cPanelDavidG,

1. When upgrading server 1 to the latest version of cPanel, the other server's roundcube db user losses rights to the roundcubemail db.

2. When server 1 has a user, say 'marks', that is transfered to server 2 and the webmaster removes the account from server 1, all of 'marks' databases are deleted.

3. When server 1 has a user, say 'john', that is transfered to server 2 and the webmaster does not removes the account from server 1, at midnight server 2's 'john' database user losses all rights to access the shared 'john' databases.

I hope that makes sense and helps .

Kindest Regards,


Mark

Yes, that makes sense and it will help. Thanks for the insight.

[cPanelDon]

I have relocated this thread into the feature requests forum so that the idea, of preserving SQL databases upon account termination, may gain more attention and possible consideration as an enhancement for a future cPanel version: Feature Requests for cPanel and WHM - cPanel Forums

[mephisto]

Anyone? I still can't transfer my accounts due to the above problem.