Results 1 to 24 of 24
  1. #1

    Need server provider with very good MySql disaster recovery

    I run a MySql intensive social network and I need to find a server provider with good backup and disaster recovery options.

    My MySql database had gotten corrupted somehow and my provider had a problem with restoring all of the MySql data accurately using R1Soft. I need a provider that knows what they're doing especially with MySql InnoDb tables. My budget is $600/month. Any suggestions?

  2. #2
    Join Date
    Apr 2010
    Posts
    386
    May I ask why you get table corruptions?

  3. #3
    I have no idea why the tables got corrupted and neither does my current hosting provider. The most common reason is a non-graceful server or mysql reboot but I don't know for sure.

  4. #4
    Join Date
    Apr 2004
    Location
    Singapore
    Posts
    1,522
    I assume your budget includes the servers?
    tanfwc

  5. #5
    Join Date
    Aug 2010
    Location
    Prague, Czech Republic
    Posts
    404
    Why do you think your InnoDB tables are corrupted? Usually MyISAM tables are become corrupted, InnoDB has more sophisticated internals and should repair automatically after crash with use of log. Probably there are some other issues?
    Supportex.Net server management, full range of services. EU-based outsourced company. Since 1998.
    Outstanding quality for high performance projects; clustering and high-availability solutions, DDoS protection.
    Cisco/Juniper network management & deployment assistance. Network design and monitoring.

  6. #6
    I'm taking my current hosting provider's word for it and that's what they're tell me.

    Does anyone have any suggestions for reliable providers with good support?

  7. #7
    Join Date
    Feb 2005
    Location
    localhost
    Posts
    5,473
    Are you looking for a fully managed solution? what OS/CP are you currently using?
    Respectfully,
    Mr. Terrence

  8. #8
    Yes, my budget includes the servers.

  9. #9
    Yes, I'm looking for a fully managed solution. Currently using CentOS 5.

  10. #10
    Join Date
    Feb 2005
    Location
    localhost
    Posts
    5,473
    Quote Originally Posted by Jason A View Post
    Yes, I'm looking for a fully managed solution. Currently using CentOS 5.
    What is your current server configuration?
    Respectfully,
    Mr. Terrence

  11. #11
    I currently have a quad core, 8 GB RAM, 1 primary SATA HDD, and an 80 GB SSD in RAID 1 for the database. Current backup service is R1Soft with the MySql plugin but that's proven itself as unreliable for InnoDb. Is there a better way to do MySql backups and restores?

  12. #12
    Join Date
    Apr 2010
    Posts
    386
    Xtrabackup?

  13. #13
    Join Date
    Dec 2004
    Posts
    569
    Quote Originally Posted by Jason A View Post
    Is there a better way to do MySql backups and restores?
    No fancy tools, MySQL's built-in functionality should be enough.

    - Make a daily mysqldump.

    - Use MySQL replication to make a live backup to a secondary server.


    If you also keep the binlog files on the second server, you should be able to recover to any point in time.
    E.g. if you just did something destructive like dropping the wrong database table, you should be able to restore it from yesterday's mysqldump, and get today's modifications back by replaying the binlog up to your destructive command.

  14. #14
    Quote Originally Posted by Maxnet View Post
    No fancy tools, MySQL's built-in functionality should be enough.

    - Make a daily mysqldump.

    - Use MySQL replication to make a live backup to a secondary server.
    The problem I've had with mysqldump is that it locks the tables during the dump preventing users from accessing the database. I need 24/7 uptime and mysqldump tended to take way too long on a 25 GB database.

  15. #15
    Join Date
    Dec 2004
    Posts
    569
    Quote Originally Posted by Jason A View Post
    The problem I've had with mysqldump is that it locks the tables during the dump preventing users from accessing the database. I need 24/7 uptime and mysqldump tended to take way too long on a 25 GB database.
    1) It shouldn't be necessary to use locks when using Innodb, double check your dump arguments.

    2) With a secondary server, you can run Mysqldump on that. Doesn't matter if it is slow. Replication is asynchronous, the slave lagging behind doesn't affect the master.

  16. #16
    Quote Originally Posted by Maxnet View Post
    1) It shouldn't be necessary to use locks when using Innodb, double check your dump arguments.

    2) With a secondary server, you can run Mysqldump on that. Doesn't matter if it is slow. Replication is asynchronous, the slave lagging behind doesn't affect the master.
    For my budget of $600/month, can you recommend a host that can do that?

  17. #17
    Join Date
    Apr 2000
    Location
    Brisbane, Australia
    Posts
    2,602
    Quote Originally Posted by Jason A View Post
    The problem I've had with mysqldump is that it locks the tables during the dump preventing users from accessing the database. I need 24/7 uptime and mysqldump tended to take way too long on a 25 GB database.
    Do you have mix of myisam and innodb tables or 100% innodb ? If 100% innodb, use mysqldump with –single-transaction option for non-blocking backups.

    If you have 100% innodb based tables, then besides percona xtrabackup also check out mydumper for multi-threaded based backup and restore if you want between 3x to 10x times faster mysql data backup compared to mysqldump.

    Google search for 'mydumper innodb backup ' and 'mydumper innodb benchmarks'

    My own tests found mydumper around 3x times faster for MySQL backup and around 4-5x times faster for MySQL restore with MariaDB 5.2.10 compared to mysqldump.

    Whole thread on this at http://www.webhostingtalk.com/showthread.php?t=1113263 and another at http://www.webhostingtalk.com/showthread.php?t=1112716

    But like maxnet said, if you want high availability, you'd need to set mysql replication with a 2nd server as mysql slave and run backups off the slave for uninterrupted MySQL usage.


    HTH
    : CentminMod.com Nginx Installer Nginx 1.25, PHP-FPM, MariaDB 10 CentOS (AlmaLinux/Rocky testing)
    : Centmin Mod Latest Beta Nginx HTTP/2 HTTPS & HTTP/3 QUIC HTTPS supports TLS 1.3 via OpenSSL 1.1.1/3.0/3.1 or BoringSSL or QuicTLS OpenSSL
    : Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack

  18. #18
    Join Date
    Mar 2009
    Location
    NL
    Posts
    594
    For 600USD i would recommend to setup a load balanced solution with something like mysql proxy. We have recently setup a cluster for a client and this works really well. For replication you could try tungsten or the buildin replication features. Backups are very straightforward as described above. Any provider with a decent tech. team should be able to provide this. I would recommend to find a smaller company with a lot of knowledge. The big guys don't seem to care enough to set this up properly.
    Last edited by Rens; 03-13-2012 at 02:00 PM.
    YISP - High Bandwidth dedicated servers and colocation in YISP-AS(Amsterdam)!

    Website: http://www.yisp.nl
    Contact: info "(AT)" yisp.nl

  19. #19
    Join Date
    Dec 2004
    Posts
    569
    Quote Originally Posted by Jason A View Post
    For my budget of $600/month, can you recommend a host that can do that?
    Cannot recommend any provider in particular.

    But generally speaking you want to look for smaller managed hosting providers that do not require a control panel.
    Even when you do want one, it is a good indication that their sysadmins have more experience with doing stuff manually than some of the others, and therefore might be better to assist you in situations when fancy tools do not work properly.

  20. #20
    Join Date
    Apr 2010
    Posts
    386

  21. #21
    Quote Originally Posted by cedivad View Post
    Thanks, I'll definitely look into Percona's XtraBackup

  22. #22
    Quote Originally Posted by eva2000 View Post
    Do you have mix of myisam and innodb tables or 100% innodb ? If 100% innodb, use mysqldump with –single-transaction option for non-blocking backups.

    If you have 100% innodb based tables, then besides percona xtrabackup also check out mydumper for multi-threaded based backup and restore if you want between 3x to 10x times faster mysql data backup compared to mysqldump.

    Google search for 'mydumper innodb backup ' and 'mydumper innodb benchmarks'

    My own tests found mydumper around 3x times faster for MySQL backup and around 4-5x times faster for MySQL restore with MariaDB 5.2.10 compared to mysqldump.

    Whole thread on this at http://www.webhostingtalk.com/showthread.php?t=1113263 and another at http://www.webhostingtalk.com/showthread.php?t=1112716

    But like maxnet said, if you want high availability, you'd need to set mysql replication with a 2nd server as mysql slave and run backups off the slave for uninterrupted MySQL usage.


    HTH
    Thanks a lot for this. All of my tables but one are running on InnoDB with one being a memory table. Do you know if there are consulting services out there or at least a forum where I can go to find knowledgeable people who I can hire to do all of the setup and suggest the best options? I'm more of a developer and all of this is taking a lot of time away from me developing new features.

  23. #23
    Join Date
    Apr 2000
    Location
    Brisbane, Australia
    Posts
    2,602
    My short list from my research



    For MySQL replication, I prefer Tungsten Replicator based mysql replication http://code.google.com/p/tungsten-replicator/ over native MySQL replication as Rens above touched on but if you can't do it yourself and can afford to pay then pro version with support = Tungsten Enterprise http://www.continuent.com/solutions/overview

    Note #1: I never used any of the above listed services as I can do most of the stuff myself and for my clients (I'm not a DBA though). But if I needed more expert help myself, the above list is basically a short list of where I'd start.

    Note #2: With Percona XtraDB, if your data isn't 100% innodb, then backups will still have table locking on myisam tables, it's only innodb tables that are non-blocking when using Percona XtraDB. As explained here http://www.webhostingtalk.com/showpo...4&postcount=50

    I'd re-read http://www.webhostingtalk.com/showthread.php?t=1112716 as well as that poster had similar concerns as you wanting high availability as well with least interruption.
    : CentminMod.com Nginx Installer Nginx 1.25, PHP-FPM, MariaDB 10 CentOS (AlmaLinux/Rocky testing)
    : Centmin Mod Latest Beta Nginx HTTP/2 HTTPS & HTTP/3 QUIC HTTPS supports TLS 1.3 via OpenSSL 1.1.1/3.0/3.1 or BoringSSL or QuicTLS OpenSSL
    : Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack

  24. #24
    Join Date
    Apr 2000
    Location
    Brisbane, Australia
    Posts
    2,602
    One more piece of advice, backup speed is also dependent on disk i/o performance. If you're source and destination disks only are capable of 10-20MB/s sequential read/writes, then adding the overhead of the backup method/system, there's no way to get faster backups on a 25GB sized database if disk speed is the bottleneck.

    You mentioned 80GB SSDs in raid 1, Intel 80GB X25-M is around 70MB/s writes and 80GB 320 SSD around 90MB/s writes so not the fastest out there.

    So check your disk speed
    : CentminMod.com Nginx Installer Nginx 1.25, PHP-FPM, MariaDB 10 CentOS (AlmaLinux/Rocky testing)
    : Centmin Mod Latest Beta Nginx HTTP/2 HTTPS & HTTP/3 QUIC HTTPS supports TLS 1.3 via OpenSSL 1.1.1/3.0/3.1 or BoringSSL or QuicTLS OpenSSL
    : Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack

Similar Threads

  1. Anyone knows of any good data recovery provider?
    By cheaptraffic in forum Dedicated Server
    Replies: 6
    Last Post: 06-08-2010, 12:35 AM
  2. Server Disaster Recovery
    By KennyP in forum Dedicated Server
    Replies: 18
    Last Post: 04-26-2009, 11:43 PM
  3. Disaster Recovery & A Good Night's Sleep
    By Tekerz in forum Colocation, Data Centers, IP Space and Networks
    Replies: 4
    Last Post: 06-01-2006, 12:39 AM
  4. A Business Without A Disaster Recovery Plan Is A Disaster Waiting To Happen.
    By ByteFortressAaron in forum Other Web Hosting Related Offers
    Replies: 0
    Last Post: 05-29-2006, 01:38 AM
  5. Harddrive Failure - Disaster Recovery - Windows Server 2003
    By EricP in forum Hosting Security and Technology
    Replies: 1
    Last Post: 01-28-2004, 06:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •