MariaDB Backup Large InnoDB Databases

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

 

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

 

permissions or errors ?

 

ad blockers ? try with them off

 

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

 

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 ?

 

yeah could be.. strange i have not once experienced this myself for postratings.. i have disallowed postratings.js from pagespeed for now anyway

 

other sites indeed use site wide maintenance

 

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