Want more timely Centmin Mod News Updates?
Become a Member

MariaDB Backup Large InnoDB Databases

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by BamaStangGuy, May 28, 2015.

  1. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    What are you guys with 20+GB InnoDB databases doing to mitigate downtime when backing up your database?
     
  2. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    Cheapest and most simple way is implementing a site wide maintenance system that closes site for XX minutes during backup. 20GB database should only take between 4-8 minutes to backup when done optimally, so maintenance time can be 10 minutes at a time. Most my private clients opt for this approach otherwise you may be throwing $1000s of dollars at the solution to keep that extra 10 minutes of online client facing time intact. 10 minutes is a small price to pay :)

    banks and financial institutions do this too :D
     
    Last edited: May 28, 2015
    • Agree Agree x 1
  3. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    Well ours is near 50GB. I looked at your MySQL Dumper thread but it seems that the site that is linked to no longer works.
     
  4. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah mydumper.org domain was lost when it wasn't renewed and now we have a domain squatter hoarding it :(

    copy of docs posted on official centmin mod site at http://centminmod.com/mydumper.html
     
    Last edited: May 28, 2015
  5. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    BTW, I can't like any posts.
     
  6. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    permissions or errors ?
     
  7. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    Seems permissions. I don't have a like link at all.
     
  8. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    ad blockers ? try with them off
     
  9. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    Never was enabled for this site :)
     
  10. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    strange everyone else is liking posts fine heh - why style you using for forums ? i have post ratings installed Post Ratings Added | Centmin Mod Community
     
    • Funny Funny x 1
  11. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    1:35 AM
    Hmm, there seems to be an issue where the hover does not work in certain areas of a post to make the post ratings show. Might want to disable the opacity?
     
  12. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    try now i disabled postratings.js from pagespeed too if that doesn't do it maybe you mean opacity at

    Code:
    .dark_postrating_inputlist li {
      display: inline;
      /* opacity: .5; */
      -ms-filter: 'alpha(opacity=50)';
      filter: alpha(opacity=50);
      zoom: 1;
    }
    what browser you using ?
     
  13. RoldanLT

    RoldanLT Well-Known Member

    3,829
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    2:35 PM
    1.11
    10.2
    I have this problem most of the time here.
    It's definitely because of defer_js and prioritize_critical_css.
     
    • Informative Informative x 1
  14. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah could be.. strange i have not once experienced this myself for postratings.. i have disallowed postratings.js from pagespeed for now anyway
     
    Last edited: May 31, 2015
  15. RoldanLT

    RoldanLT Well-Known Member

    3,829
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    2:35 PM
    1.11
    10.2
    Looks like works normal now :)
     
    • Agree Agree x 1
  16. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    other sites indeed use site wide maintenance :)

    blitzio_maintenance.png
     
  17. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    11:35 PM
    @BamaStangGuy Why not just dump as a single transaction while the site is live? Is mysqldump crashing on you with a DB that size?

    Unlike MyISAM, InnoDB doesn't require taking the site offline to grab a snapshot backup within a single transaction.
     
  18. Revenge

    Revenge Active Member

    287
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:35 AM
    1.9.x
    10.1.x
    Why not use Percona XtraBackup? Its a Mysql Hot Backup that have incremental backups and full backups without downtimes. I have used it and its perfect for large databases.
    The restore is also much faster.
     
    Last edited: Feb 24, 2016
    • Informative Informative x 2
  19. eva2000

    eva2000 Administrator Staff Member

    29,016
    6,585
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,775
    Local Time:
    4:35 PM
    Nginx 1.13.x
    MariaDB 5.5
    Percona Xtrabackup is nice but it's more all or nothing out of box for ALL databases on server and is a bit more involved if you only want to restore a single database from the backup usually. For single database restores and backups, I prefer mydumper.

    Last tests I did with mydumper 0.9 branch vs mysqldump on MariaDB 10.0.23 and percona xtrabackup 2.3.3 for 24 table test innodb only database of 3.1GB size on 1GB OpenVZ server with 4 cpu threads on CentOS 6.7 64bit with Centmin Mod LEMP.

    These results are with OpenVZ server with 4 cpu threads and a database with 24 tables and as mydumper is multi-threaded, usually mydumper starts to match xtrabackup backup times closer as the number of cpu threads increases.

    mydumper 0.9 branch
    • mydumper backup = real: 43.74s user: 11.22s sys: 2.92s cpu: 32% maxmem: 8052 KB cswaits: 32510
    • myloader restore = real: 92.31s user: 2.33s sys: 1.09s cpu: 3% maxmem: 8084 KB cswaits: 23075
    mysqldump with single-transaction flag via MariaDB 10.0.23
    • mysqldump backup = real: 150.37s user: 23.15s sys: 3.89s cpu: 17% maxmem: 3160 KB cswaits: 10274
    • mysql restore of mysqldump backup = real: 189.70s user: 18.77s sys: 1.19s cpu: 10% maxmem: 5504 KB cswaits: 6540
    percona xtrabackup normal innobackupex
    • innobackupex backup = real: 28.79s user: 4.90s sys: 6.65s cpu: 40% maxmem: 59372 KB cswaits: 93419
    • innobackupex apply stage = real: 4.99s user: 0.11s sys: 0.30s cpu: 8% maxmem: 68488 KB cswaits: 19710
    • total time 28.79 + 4.99 = 33.78s
    • innobackupex copy-back restore = real: 32.89s user: 0.00s sys: 5.64s cpu: 17% maxmem: 14852 KB cswaits: 18213
    percona xtrabackup stream based backup
    • to .tar file = real: 44.63s user: 4.54s sys: 5.15s cpu: 21% maxmem: 28648 KB cswaits: 12212
    • to .tar.gz via gzip = real: 213.80s user: 4.77s sys: 3.60s cpu: 3% maxmem: 28652 KB cswaits: 115973
    • to .tar.gz via pigz multi threaded gzip = real: 73.62s user: 4.56s sys: 2.76s cpu: 9% maxmem: 28636 KB cswaits: 82625
    percona xtrabackup xbstream backup
    • backup = real: 47.58s user: 7.79s sys: 5.33s cpu: 27% maxmem: 36772 KB cswaits: 12208
    • backup with compress and compress threads = real: 26.64s user: 16.18s sys: 5.04s cpu: 79% maxmem: 100808 KB cswaits: 103586
    • restore via unpack of xbstream = real: 29.07s user: 1.88s sys: 3.46s cpu: 18% maxmem: 10472 KB cswaits: 18131
     
    Last edited: Feb 25, 2016
    • Informative Informative x 1
  20. Revenge

    Revenge Active Member

    287
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:35 AM
    1.9.x
    10.1.x
    @eva2000 you can backup a single database with xtrabackup using the --include command. For example, if you wanted to backup only the centminmod database, you would do: innobackupex --include="^centminmod\." /tmp/ (innodb_file_per_table needs to be enabled). This is a partial backup and restoring it is also a bit more complicated.
    You can even restore single tables from a database: Restoring Individual Tables

    The whole process is a lot more complicated than using mysqldump or mydumper, but for very larges databases, like 100Gb or more, it totally worth it.
     
    • Informative Informative x 1