cPanel Remote Database Server - MySQL Server Setup - Cpanel Integration - Existing DB Migration

[rasa]

Hello
i Plan to install a remote database server as one of our main server MYSQL Service have 200 % of CPU usage ( as cpanel show in top )

i will order a server in the same data-center with lower than 10ms latency

here i need professional setupguide and advise in

1 - How to setup a MySQL server and what to do for performance and security in general ( i know performance and security can be a very long topic and discussed before T i just need general facts )

2- what is the rules of conceting remote server to local servers ( exclude the description in cpanel documentation Setup Remote MySQL Server )


3- i want to migrate a production server databases to remote DB server
what shall i do ? what are the steps and commands :D ?

4-is there any thing esle that i have consider in remote DB Server ?

thanks

 

[cPanelMichael]

Hello

1. It's difficult to provide any specific optimization advice without knowing the version of MySQL or the actively level of your databases. Most optimizations will occur after you have imported the databases and had time to see the usage.

2. The following document includes pretty much all of the steps that are required to configure the cPanel server to utilize a remote MySQL server:

Setup Remote MySQL Server

I am not aware of any additional steps for the configuration itself that are not already listed in this document.

3. There are a few methods you can use to backup your databases:

MySQL :: MySQL 5.1 Reference Manual :: 7.2 Database Backup Methods
MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods

Most customers utilize the "mysqldump" utility to backup the databases, and then restore those dumps via the command line on the remote MySQL server.

Thank you.

 

[rasa]

Hello and thanks ( is there any Thanks Button ?)
about installation that documents not mentioned any information on step by step MySQL server installation,setup and recommendation about thing like Partitioning and ......

and
what about the time that we setup adn connected remote database server the time taht we migrating databases to new server ( is this cause any problem in current configuration and applications ?)
and also that documentation dont say anything about cpanel andapplications DB like mails and ......

one more thing is
days ago i did a bad mistake ( i think )
i have a server with MySQL 5.1 installed when i need to migrate accounts to new server i installed a new Server and cpanel installation installed MySQL 5.5 as default !
i dont know that and i migrated all accounts to new server !
after some sort of problem i realize that the server have MySQL 5.5 instead of 5.5 !
do you think im going any major or minor problem in databases and applications like Wordpress ? as the MySQL versions and database migrations are some tims end up with bad data curreption

 

[cPanelMichael]

I will try to answer each question individually.

1. There are several methods to install MySQL on the remote server. The most common method on a non-cPanel server is to install it via your system package manager (YUM).

2. You can backup your databases, shut down the local MySQL server, and then import the databases on the remote MySQL server before configuring the Remote MySQL option in Web Host Manager. This may result in a small time frame where you have to shut down MySQL on the local server to keep updates to the databases from occurring.

3. There should not be many compatibility issues with MySQL 5.5 coming from MySQL 5.1. There is documentation on it at:

MySQL :: MySQL 5.5 Reference Manual :: 2.12.1.1 Upgrading from MySQL 5.1 to 5.5

Thank you.

 

[rasa]

hello
thanks for the
i setup a mysql 5.5 database server from remi repository

after that i suspend all accounts on server
create a backup fromm all databases using

mysqldump -u root --all-databases | gzip -9 > /dbbackup/alldatabases.sql.gzip

]

after that i used

scp /dbbackup/alldatabases.sql.gzip [email protected]:/dbbackup/

on remote database server

/dbbackup/alldatabases.sql.gzip
mysql -u root < /dbbackup/alldatabases.sql

after that i setup remote database from cpanel and everything goes fine

but there are 2 question/problem

when i look at mysql privilages i saw same user names from diffrent hosts like
olserverip
localhost
current remote database server ip
current remote database server hostname
other ips that maybe belong to our customer previous server

what shal i do about that

and now in cpanel when i want to user whm
it diplay the invalid security token error and i need to login again
also i cant login to useres account using login button form WHM

thanks

 

[rasa]

hello again
another problem that users are reported is that they recive error 500 on their websites randomly ! and in short or long periods

The OS is Cloud Linux

 

[cPanelMichael]

It's normal to see multiple hosts for your MySQL privileges. Is this causing a specific error? What is the full error message in the Apache error log (/usr/local/apache/logs/error_log) when the "500" error is displayed in the browser?

Please open a separate thread for any security token issues as that is not directly related to the use of a remote MySQL server.

Thank you.

 

[rasa]

Hello and thanks
ok ill do that
but i have another question taht related to my configuration

the 500 error was caused by Eaccelerator ! but before remote database all of the websites are working like charm! ( eaccelator give PHP crash error - i think this could related to following problem)

also i found out that error 500 caused when DB Server uplink to server 100% utilize !
i found out that 2 account that they have Vbulletin froum use 50 ~ 60Mbps of server network capacity ( the server and remote DB are in the same Datacenter but the remote DB server have 100Mbps network capacity witout capabiltu to have cross conncetion or dedicated uplink to Cpanel Server !


so my question is is it possible for me to start mysql server on local server and ask this to customers to set thier Db server address to local host ? this way that wxternal datatransfer could reduced to acceptable value
and absolutely lower cost ! because i dont need to order another server with higher uplink capacity

thanks

 

[cPanelMichael]

1. It's likely the eAccelerator issue is related to the following thread if you are using PHP 5.4:

[Case 74677] EACCELERATOR can't restore parent class

2. Starting MySQL on the local server would negate the use of the remote MySQL server through cPanel. Do you mean to ask your customers to configure their scripts to use a remote IP address instead of localhost?

Thank you.

 

[rasa]

Hello
no its PHP 5.3.27 and its just apeared after remote database !
i have php crash message in log and also our customers receive email with this text and
eAccelerator: Unable to change cache directory /tmp/eaccelerator permissions

 

[cPanelMichael]

Hello
no its PHP 5.3.27 and its just apeared after remote database !
i have php crash message in log and also our customers receive email with this text and
eAccelerator: Unable to change cache directory /tmp/eaccelerator permissions

It sounds like this is the same bug that will be addressed with case 74677 in the thread referenced in my last response. There have been reports of this affecting PHP 5.3 as well.

Thank you.

 

[rasa]

Hello
i ve setup my remote database server it start im really surprised ! server 100Mbsp unlink is fully utilized!
after i order a new server with 500Mbsp internet and 1Gbps internal traffic ! the traffic was surprising too ! more than 350Mbps !
OK ! i give it up and accept that is what it shuld be ! i hope !

now i want to use MariaDB instead od MYSQL Server Communality Edition on this remote database !

because it seems that have much more efficiency ! and less bug and it have tread pool and tread pool cache ! that is available in Enterprise edition !
and this DBserver is fully compatible with my SQL ! ( i HOPE )

i found a step by step guide here
How to replace MySQL with MariaDB in 11.36 | cPanel Blog
what do you think about this upgarde ?
is this good and worth to do ?
is setup procedure is the same for remote database server as mentioned on that link ?
is phpmyadmin working after that i use MariaDB ?

thank everybody for helping me

 

[cPanelMichael]

MariaDB is considered to be unsupported by the cPanel/WHM team. While you are welcome to attempt a manual installation, it's not something we can troubleshoot for you in the event of an error.

Thank you.