Welcome to Centmin Mod Community
Become a Member

MariaDB Backup Large InnoDB Databases

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

  1. eva2000

    eva2000 Administrator Staff Member

    45,656
    10,358
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,076
    Local Time:
    4:56 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    @Revenge yeah that's what I mean about it being a bit more complicated for average folks compared to mysqldump and mydumper :)
     
  2. Revenge

    Revenge Active Member

    459
    93
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +350
    Local Time:
    6:56 AM
    1.9.x
    10.1.x
    @eva2000 thats why you created centminmod, to make a task that is complicated into something very easy even for the average user. And its a damn good work you have accomplished.
    Maybe in the future you can implement xtrabackup with automated commands from centminmod menu. ;)
     
  3. eva2000

    eva2000 Administrator Staff Member

    45,656
    10,358
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,076
    Local Time:
    4:56 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    Thanks for kind words - I try to make things less complicated or less time consuming with Centmin Mod - time is more precious than money, so more time saved the better :)

    Yeah for some of my paying clients they're using a mixture of mydumper and xtrabackup and mysqldump via custom scripts. Centmin Mod has dbbackup.sh for premium members MariaDB - dbbackup.sh - quick mysql database backups for Centmin Mod stack | Centmin Mod Community It uses mysqldump right now but probably extend it further in future for mydumper and percona xtrabackup :D
     
  4. Revenge

    Revenge Active Member

    459
    93
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +350
    Local Time:
    6:56 AM
    1.9.x
    10.1.x
    I haven't used mydumper for a very long time, so i compiled 0.9.1 version and compared the times against mysqldump.
    This is for a 3Gb database in a vps from vpsdime, so its 4 vcpu's, 6Gb of Ram and SSD Drive.

    Mysqldump Backup: 46s
    Mysqldump Restore: 10m40s

    Mydumper: 16s
    Myloader: 6m10s

    I was specially impressed with the 16s of Mydumper. Its almost instant for a 3Gb Database. I might start using Mydumper when i backup my Database prior to the web software update.
     
  5. eva2000

    eva2000 Administrator Staff Member

    45,656
    10,358
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,076
    Local Time:
    4:56 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    Yeah i use mydumper and mysqldump together. More frequent backups can use mydumper :)
     
  6. Revenge

    Revenge Active Member

    459
    93
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +350
    Local Time:
    6:56 AM
    1.9.x
    10.1.x
    One strange thing. The restore of Mydumper have decreased the Database to 2.8GB. Its normal because of the fragmentation.
    But the Mysqldump restore have raised the Database to 3.1Gb. Very strange lol
     
  7. eva2000

    eva2000 Administrator Staff Member

    45,656
    10,358
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,076
    Local Time:
    4:56 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    try mysqldump single table of database and compare it's sql file to the individual mydumper sql file for that table and see what's different :)