Move Database to a different server

Discussion in 'ISPConfig 3 Priority Support' started by stst, Jun 21, 2021.

  1. stst

    stst New Member HowtoForge Supporter

    Hi,

    I would like to move the complete database from my ISPconfig server to a different location. It is running on an aws-EC2 instance and I would like to move the database to aws-RDS. Now this is a productive server and I want to make maintenance time as short as possible and at very low risk.

    What can you recomment? Is there a possibility to move all database at once and adjust all database-connections simultainously or even redirect all database traffic to the new service? Or would you recommend to move it step by step?

    thanks, Stefan
     
  2. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Which database?
    What are you going to do with the database on the target host?
    It is not clear if you plan to
    • move one database to another host
    • move all databases on ispconfig host to another host
    • move the complete ISPConfig installation to another host
     
  3. stst

    stst New Member HowtoForge Supporter

    sorry for being unprecise...
    I want to move all databases from ISPConfig host to another host, namely to aws-RDS database service.
     
  4. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    Have you checked ISPConfig Migration Tool whether it has what you need?
     
  5. stst

    stst New Member HowtoForge Supporter

    As I understand, this tool is intended for moving databases from other platforms to ISPconfig. Thats completely different, from what I need.
     
  6. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    I don't think so as its page said:
    You do mean to transfer data from one ISPconfig host to another, right?
     
  7. stst

    stst New Member HowtoForge Supporter

    Yes, but that is not my use case. I want to leave all ISPconfig server, as is, just move the databases to an external database service, means a different server.

    Meanwhile I found this: https://aws.amazon.com/de/getting-started/hands-on/move-to-managed/migrate-sql-server-to-amazon-rds/
    I guess that can help me in moving the databases, but I'm still not sure what to change in ISPconfig to reference the new database-server.
    Or is there a possibility to redirect all database traffic to the new server?
     
  8. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Have you added this new database server host to your ISPConfig multiserver setup?
     
  9. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

    is this a standalone ispconfig instance currently? are you also looking at moving the dbispconfig database as well?

    moving the databases is going to be tricky. if you were already running a multi-server system with a dedicated ispconfig database server, then moving everything on that to aws rds would be easiest, but most likely all your hosted websites are using 'localhost' as their db server, and if you're hosting client websites, it's going to be even trickier, they could all be using different cms's, so where you need to change their db server details are different on each site. (and some particularly badly developed sites can have multiple places, i had one customer site where the db details were set in around 20 different files :eek: )
    i would suggest moving the db for each site individually, gives yourself time to fix any issues on one site if it has any, rather than have multiple sites with issues simultaneously and having to rush trying to fix them all at the same time.
    how are you going to manage databases going forward? i guess it's not going to be done through the ispconfig interface.
     
  10. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    I personally won't recommend transferring database server from an ISPConfig server framework to another non-ISPConfig server framework. That is suicidal at best for most admins except for those who are very advanced. :D

    Anyway it's your server, so it is up to you to do it BUT don't expect free help from here, I think. ;)
     
  11. stst

    stst New Member HowtoForge Supporter

    yes, its a standalone ispconfig instance and yes I would also like to move dbispconfig database.

    I know, that this is not an easy task, thats why I'm asking here for any experiences.

    Just to give you a little bit of background, as I said it is running on EC2 and the mysql server is writing its data to the EBL (Block Storage). Now EBL has a mechanism to reduce permittedThroughput when the number of read/write operations is above a certain level. The mysql server does lots of reads/writes, so over the time performance goes down. Now aws offers a dedicated database service, that would help me to overcome this problem and additionally would bring some useful features, like automatic backups...

    What do you think? When I make a complete replication of the databases as described in the link above, would it be sufficient to forward mysql port to the new server?
     
  12. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

    looking at aws rds connectivity for mysql, it looks do-able, you'll just need to change the all db connections from localhost to whatever the rds db endpoint is.
    but as already stated, db creation and user management becomes a lot more difficult, since it won't be done through the ispconfig interface, so client users won't be able to manage their own db's, at least not unless you do a lot of customisation to ispconfig itself, or provide them with some other management interface.
    also do they still add charges according the number of db read/writes (in millions) each month? that makes it difficult to know how much that rds instance is going to cost you each month... at least with mysql running on an ec2 instance, you know what the mysql costs are going to be.

    couldn't you just add another EBS volume to the ec2 instance and have that volume dedicated to the mysql db's?
    you should have a fixed iops level for ebs, increasing according to ebs size, along with a burstable iops rate to cover spikes in activity, so you shouldn't be seeing your throughput being reduced unless you are consistently remaining above that level for extended periods, and again, splitting website files, mail folders, db's and logs across different ebs volumes should help avoid that.

    the other issue i can see with this is that you are going to have what is effectively, a remote database for your websites, even if it's on the same network. i tried that originally when i first setup ispconfig, using a large ec2 instance as a dedicated ispconfig db server. it was way over-specced to what was needed 16 cpu, 64Gb ram etc, but even the extra latency introduced connecting to that db rather than localhost was extremely noticeable, especially considering how badly wordpress is optimized for db usage. a 2 cpu 8gb ec2 instance running as both the webserver, and local db server was way faster serving wordpress sites, than a 4cpu 16Gb instance just running a webserver, connecting to this dedicated 16cpu/64gb db server.
     
    stst, ahrasis and till like this.
  13. till

    till Super Moderator Staff Member ISPConfig Developer

    That's the reason why ISPConfig setups normally use a local database, socket connections as used by MySQL when using 'localhost' are way faster than network connections due to lower latency and also bandwidth is probably much higher on most systems. While this is most important for web servers and CMS like WordPress, also mail systems benefit from the local database as the mail system does quite a few lookups as well on a heavy loaded mail system.

    Personally, I would go away from AWS to a hoster that supports running your database locally reliably. In my opinion, AWS is way overpriced because they charge so many things in addition to the base price and they are not even fast compared to other systems when comparing VM's in the same price range.
     
    stst and ahrasis like this.
  14. stst

    stst New Member HowtoForge Supporter

    thank you for the good advices.
    I will take a deeper look into the EBS solution. Didn't know, that I can combine fixed with burstable iops rates.
    @nhybgtvfr still don't get the advantage of using different EBS volumes, is it just to keep track, which application uses up iops rate?
    For now, I'm using EFS for the website data and one EBS device for everything else.
     
  15. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

    just treat ebs volumes like any other disk... you can only write to them at a maximum speed.
    so just like on an old physical server with magnetic drives, raid5 may have been used for critical data for safety, but mirrored drives for logs for speed. and different drives may have been used for /home and /var and /usr etc.
    same with aws ec2 and ebs, eg, use one ebs volume for /var/lib/mysql, one ebs volume for /var/log etc. they each have their own iops limit, so you can write to each one separately without a massive logfile write impacting the iops available for a db write.
    it also means if running low on disk space, you can extend eg, the /var/vmail volume, without worrying about anything happening with any data in /var/lib/mysql or /var/www...
    not an issue with EFS, i believe that just expands as required. did look at using that when i first setup our servers, but it was a lot slower than ebs back then, and was still experimental/unstable.

    also, if you're using gp2 (gp3 now?) for ebs volumes, you already have a baseline iops set by the size of the volume, and a burstable rate which is constantly replenished at 3 iops/sec/GB.
    https://aws.amazon.com/blogs/databa...baseline-performance-with-amazon-rds-and-gp2/
    says it's about rds, but they're built on ebs volumes, so it's also directly applicable to any gp2 ebs volume.
     
    ahrasis likes this.

Share This Page