Join the community today
Register Now

Fastest way to migrate 15gb+ database to remote server?

Discussion in 'System Administration' started by rdan, Sep 22, 2019.

  1. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:03 AM
    Mainline
    10.2
    mysqldump/mysql import is fine but takes 30 minutes more all in all to complete.
    Is there fastest way?

    I'm thinking of...
    • Having same MariaDB version on both server 10.3.x
    • Stop both mysql remote/local
    • Create the same database name on remote server
    • rsync /var/lib/mysql/database_name/
    • Start Mysql again
    Will that work fine?
    Without data loss or corruption?
    What my.cnf config I need to have the same value?

    I've read a lot of articles online, seems a lot is doing this method for huge database.
     
  2. eva2000

    eva2000 Administrator Staff Member

    41,720
    9,390
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,424
    Local Time:
    1:03 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    30 mins seems kind of long but depends on server hardware, data being restored and whether you have optimised /etc/my.cnf mysql settings for backup and restore on both source and destination servers. On decently fast modern hardware servers + optimal config, mysqldump single threaded backups for 15GB sized databases would usually take between 10-20 minutes. But yeah non-optimal, could be around 30+ minutes.
    100% sure way of potentially corrupting your database if you have InnoDB tables as manipulating individual Innodb database/tables directly can corrupt your data and break entire MySQL/MariaDB server - even preventing the server from starting up properly. Only way do to that is if you (with mysql server stopped on both ends) are wiping clean destination server's /var/lib/mysql data directory and copying ALL /var/lib/mysql data on source server to destination server + both servers need exact same MariaDB/MySQL server and client versions. So if source server had that single database at 15GB size but entire server had 500GB of mysql data, you'd need to copy over all 500GB of data from /var/lib/mysql data directory and not just the 15GB single database.

    mysqldump is single threaded, for large databases you can use multi-threaded database backup tools like mydumper which backup each table individually into their own .sql file utilising multiple cpu threads. So the more cpu threads/cores you have, faster the backup and restore speed. With mydumper on multi-threaded/cpu server, with right settings 15GB database should take less than 10 minutes to backup and restore.

    Just be aware, github repo's listed mydumper RPM builds are built against MySQL 5.7 but you can also build mydumper against MariaDB too but can be tricker but either should work.

    mydumper github repo's RPM's
    Code (Text):
    mydumper -V
    mydumper 0.9.5, built against MySQL 5.7.21-21
    

    my own mydumper github build against master and MariaDB 10.3.13 with GCC 8.3.1 compiler instead of CentOS GCC 4.8.5 compiler
    Code (Text):
    mydumper -V
    mydumper 0.10.0, built against MySQL 10.3.13
    

    or against 0.95
    Code (Text):
    mydumper -V                             
    mydumper 0.9.5, built against MySQL 10.3.13
    

    old benchmarks I did a few years ago on 21+GB size InnoDB database on Intel Core i7 3930K 6C/12T local computer/test server on Centmin Mod LEMP stack install
    • Intel Core i7 3930K Sandy Bridge-E (6 cpu cores/12 cpu threads)
    • Gigabyte X79-UD5 F8d bios
    • 8GB DDR3 @1600Mhz 9-9-9-24 1T (4x2GB A-Data 2000X)
    • 4x 60GB Gskill Phoenix Pro SSD (~29% over-provisioning)
    • Software Raid 1 /boot (md0) and Raid 0 for root / (md1)
    [​IMG]

    MySQL backup speed
    • mysqldump backup: 22,435MB backed up in 444.21 seconds = 50.50 MB/s or 177.53 GB/hr
    • mydumper backup 12 threads: 22,435MB backed up in 165.61 seconds = 135.46 MB/s or 476.22 GB/hr
    MySQL restore speed
    • mysql restore: 18,397 MB sql file restored in 1261.77 seconds = 14.58 MB/s or 51.25 GB/hr
    • myloader restore 12 threads: 18,528 MB sql files cumulative size restored in 353.62 seconds = 53.92 MB/s or 189.56 GB/hr
    Conclusion
    • mydumper was 2.68x times faster than mysqldump for MySQL InnoDB backup
    • mydumper’s myloader tool was 3.7x times faster than mysql for MySQL InnoDB restore / import
    For full mysql server all databases multi-threaded backup/restore, Percona XtraBackup or MariaDB Backup (fork of XtraBackup made for MariaDB specifically) are good too but it's more for all databases including system mysql database backup/restores. Individual database backup/restores are trickier to handle for them for backup/restore.

    Of course, learning and using mydumper, Percona XtraBackup and MariaDB Backup tools are left up to you to read the online documentation and practice first on test data before trying it on live data :)
     
    • Informative Informative x 2
    • Like Like x 1
  3. pamamolf

    pamamolf Premium Member Premium Member

    3,477
    335
    83
    May 31, 2014
    Ratings:
    +642
    Local Time:
    6:03 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    MariaDB Backup is working great but yes the problem that i had also was related with backup and restore of individual databases as it seems that some files was for general usage purpose and other databases use them or some files was not in the backup and for sure the instructions for deleting the mysql folder is totally wrong.

    I would love to get that working per database but not at the moment :)
     
    • Agree Agree x 1
    • Informative Informative x 1
  4. eva2000

    eva2000 Administrator Staff Member

    41,720
    9,390
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,424
    Local Time:
    1:03 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Yes MariaDB Backup and Percona XtraBackup is all or nothing approach.

    Quick test with mydumper 0.95 built against MariaDB 10.3.13 with GCC 8.3.1 compiler and 261MB sized dummy InnoDB MySQL database consisting of 20 tables each with 50000 rows.
    Code (Text):
    mydumper -V
    mydumper 0.9.5, built against MySQL 10.3.13
    

    Code (Text):
    +-----------------+----------------+----------------+-----------+------------+---------+------------+
    | Table Name      | Number of Rows | Storage Engine | Data Size | Index Size | Total   | ROW_FORMAT |
    +-----------------+----------------+----------------+-----------+------------+---------+------------+
    | sbtest.sbtest4  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest10 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest13 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest7  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest5  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest20 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest3  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest12 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest14 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest9  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest15 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest2  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest17 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest6  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest1  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest8  | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest19 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest18 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest11 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    | sbtest.sbtest16 | 49392 Rows     | InnoDB         | 11.52MB   | 1.52MB     | 13.03MB | Dynamic    |
    +-----------------+----------------+----------------+-----------+------------+---------+------------+
    

    On 2 cpu thread KVM VPS with Intel Xeon Silver 4114 @ 2.20Ghz
    • mysqldump = backup speed 53.59 MB/s 3215.40 MB/min 188.40 GB/hr
    • mydumper = backup speed 111.06 MB/s 6663.60 MB/min 390.44 GB/hr - yup twice as fast as using 2 cpu threads unlike single threaded mysqldump :D
    mysqldump single threaded
    Code (Text):
    ./mydumperbench.sh mysqldump
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.8 by George Liu (eva2000)
    https://centminmod.com
    Sat Sep 21 22:20:24 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [sbtest total size: 261 MB]
     [mysqldump threads: 1 ] mysqldump sbtest > /backup-mydumper/mysqldump/sbtest_210919-222024.sql
     [backup stats:] real: 4.87s user: 3.41s sys: 0.61s cpu: 82% maxmem: 5120 KB cswaits: 1285
     [backup speed:] 53.59 MB/s 3215.40 MB/min 188.40 GB/hr
    Successfully backed up database using mysqldump
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sat Sep 21 22:21:31 UTC 2019
    results logged: /root/mydumperlogs/mysqldumpbench_210919-222024.txt
    Sep 21 22:21 mysqldump_extstats_210919-222024.txt
    Sep 21 22:21 mysqldump_mysqlreport_210919-222024.txt
    Sep 21 22:21 mysqldumpbench_210919-222024.txt
    Sep 21 22:21 mysqldumpstats_210919-222024.txt
    Sep 21 22:21 mysqldumpcsv_210919-222024.txt
    Sep 21 22:21 mysqldump_mycnf_210919-222024.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    1,4.87,3.41,0.61,82,5120,1285,53.59,3215.40,188.40
    

    mydumper multi-threaded - 2 cpu threads
    Code (Text):
    ./mydumperbench.sh mydumper
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.8 by George Liu (eva2000)
    https://centminmod.com
    Sat Sep 21 22:24:45 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [sbtest total size: 261 MB]
     [mydumper threads: 2 ] mydumper -t 2 -B sbtest
     [backup stats:] real: 2.35s user: 1.25s sys: 0.65s cpu: 81% maxmem: 8128 KB cswaits: 1986
     [backup speed:] 111.06 MB/s 6663.60 MB/min 390.44 GB/hr
    Successfully backed database using mydumper using 2 threads
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sat Sep 21 22:25:49 UTC 2019
    results logged: /root/mydumperlogs/mydumperbench_210919-222445.txt
    Sep 21 22:24 mydumper_extstats_210919-222320.txt
    Sep 21 22:24 mydumper_mysqlreport_210919-222320.txt
    Sep 21 22:24 mydumperbench_210919-222320.txt
    Sep 21 22:24 mydumperstats_210919-222320.txt
    Sep 21 22:24 mydumpercsv_210919-222320.txt
    Sep 21 22:24 mydumper_mycnf_210919-222320.txt
    Sep 21 22:25 mydumper_extstats_210919-222445.txt
    Sep 21 22:25 mydumper_mysqlreport_210919-222445.txt
    Sep 21 22:25 mydumperbench_210919-222445.txt
    Sep 21 22:25 mydumperstats_210919-222445.txt
    Sep 21 22:25 mydumpercsv_210919-222445.txt
    Sep 21 22:25 mydumper_mycnf_210919-222445.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    2,2.35,1.25,0.65,81,8128,1986,111.06,6663.60,390.44
    

    mydumper backups up each table in it's own sql file
    Code (Text):
    ls -lah /backup-mydumper/export-20190921-222547/
    total 190M
    drwx------ 2 root root 4.0K Sep 21 22:25 .
    drwxr-xr-x 5 root root 4.0K Sep 21 22:25 ..
    -rw-r--r-- 1 root root   75 Sep 21 22:25 metadata
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest10-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest10.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest11-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest11.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest12-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest12.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest13-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest13.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest14-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest14.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest15-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest15.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest16-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest16.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest17-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest17.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest18-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest18.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest19-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest19.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest1-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest1.sql
    -rw-r--r-- 1 root root  413 Sep 21 22:25 sbtest.sbtest20-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest20.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest2-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest2.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest3-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest3.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest4-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest4.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest5-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest5.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest6-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest6.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest7-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest7.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest8-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest8.sql
    -rw-r--r-- 1 root root  411 Sep 21 22:25 sbtest.sbtest9-schema.sql
    -rw-r--r-- 1 root root 9.5M Sep 21 22:25 sbtest.sbtest9.sql
    -rw-r--r-- 1 root root   65 Sep 21 22:25 sbtest-schema-create.sql
    

    my mydumperbench.sh script also logs MariaDB MySQL stats on test runs too. This is for mydumper above run
    Code (Text):
    cat /root/mydumperlogs/mydumper_mysqlreport_210919-222445.txt
                  total        used        free      shared  buff/cache   available
    Mem:           3789         934         155         192        2699        2355
    Swap:          1023           3        1020
    MariaDB 10.3.13-MariaDB     uptime 0 0:0:32     Sat Sep 21 22:25:49 2019
    __ Key _________________________________________________________________
    Buffer used         0 of   32.00M   %Used:   0.00
      Current       5.84M              %Usage:  18.26
    Write hit       0.00%
    Read hit        0.00%
    __ Questions ___________________________________________________________
    Total              98       3.1/s
      DMS              47       1.5/s  %Total:  47.96
      Com_             45       1.4/s           45.92
      COM_QUIT          7       0.2/s            7.14
      -Unknown          1       0.0/s            1.02
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
    DMS                47       1.5/s           47.96
      SELECT           47       1.5/s           47.96        100.00
      REPLACE           0         0/s            0.00          0.00
      DELETE            0         0/s            0.00          0.00
      INSERT            0         0/s            0.00          0.00
      UPDATE            0         0/s            0.00          0.00
    Com_               45       1.4/s           45.92
      show_create      20       0.6/s           20.41
      set_option       11       0.3/s           11.22
      begin             3       0.1/s            3.06
    __ Rows ________________________________________________________________
    Rows            1.00M     31.3k/s
      Using idx         3       0.1/s  %Index:   0.00
    Rows/question  10.21k
    __ SELECT and Sort _____________________________________________________
    Scan               50       1.6/s %SELECT: 106.38
    Range               0         0/s            0.00
    Full join           0         0/s            0.00
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan           0         0/s
    Sort range          0         0/s
    Sort mrg pass       0         0/s
    __ Query Cache _________________________________________________________
    Memory usage   17.38k of   64.00M  %Usage:   0.03
    Block Fragmnt 100.00%
    Hits                0         0/s
    Inserts             1       0.0/s
    Insrt:Prune       1:1         0/s
    Hit:Insert     0.00:1
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate          18       0.6/s
    __ Tables ______________________________________________________________
    Open               21 of   4096    %Cache:   0.51
    Opened             38       1.2/s
    __ Connections _________________________________________________________
    Max used            3 of    300      %Max:   1.00
    Total               9       0.3/s
    __ Created Temp ________________________________________________________
    Disk table          0         0/s   %Disk:   0.00
    Table              30       0.9/s    Size: 128.0M
    File                5       0.2/s
    __ Threads _____________________________________________________________
    Running             3 of      1
    Created             3       0.1/s
    Slow                0         0/s
    Cached              2 of    128      %Hit:  66.67
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            0         0/s
    __ Bytes _______________________________________________________________
    Sent          195.82M      6.1M/s
    Received        7.42k     232.0/s
    __ InnoDB Buffer Pool __________________________________________________
    Usage         238.44M of  256.00M  %Usage:  93.14
    Read hit       96.12%
    Pages
      Free          1.12k              %Total:   6.86
      Data         15.26k                       93.14  %Drty:   0.00
      Misc              0                        0.00
      Latched           0                        0.00
    Reads         140.34k      4.4k/s
      From disk     5.45k     170.2/s   %Disk:   3.88
      Ahead Rnd         0         0/s
    Writes            971      30.3/s
    Flushes           131       4.1/s
    Wait Free           0         0/s   %Wait:   0.00
    __ InnoDB Lock _________________________________________________________
    Waits               0         0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads        15.16k     473.9/s
      Writes          133       4.2/s
      fsync             2       0.1/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    Pages
      Created         131       4.1/s
      Read         15.13k     472.8/s
      Written         131       4.1/s
    Rows
      Deleted           0         0/s
      Inserted          0         0/s
      Read          1.00M     31.2k/s
      Updated           0         0/s
    __ Aria Pagecache ______________________________________________________
    Buffer used         0 of  384.00M   %Used:   0.00
      Current      16.12M              %Usage:   4.20
    Write hit       0.00%
    Read hit        0.00%
    
     
    • Winner Winner x 1
  5. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:03 AM
    Mainline
    10.2
    By the way, using your dbbackup.sh script, taking backup of my 17GB single database + compression takes 130+ seconds only.

    But importing it using mysql -uUSER -pPASS DATABASE < xxx.sql, takes very long.
     
    • Informative Informative x 1
  6. eva2000

    eva2000 Administrator Staff Member

    41,720
    9,390
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,424
    Local Time:
    1:03 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Yes imports take much longer though you can tune your mysql settings to improve that but still need optimal hardware and it also depends on what type of data it is. But standard mysql imports still single threaded unlike mydumper's myloader multi-threaded restore tool - but data reimported needs to have been backed up via mydumper first.
     
    • Like Like x 1
  7. eva2000

    eva2000 Administrator Staff Member

    41,720
    9,390
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,424
    Local Time:
    1:03 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    same server as before, but with mydumper + myloader multi-thread backup + restore versus mysqldump/restore native single threaded.

    for just 261MB sized 20x InnoDB table database on 2CPU KVM VPS :)
    • mydumper backup speed = 103.57 MB/s 6214.20 MB/min 364.11 GB/hr = 1.9x faster than mysqldump backup.
    • myloader restore speed = 14.27 MB/s 856.20 MB/min 50.16 GB/hr = 2.03x faster native mysql restores
    • mysqldump backup speed = 54.26 MB/s 3255.60 MB/min 190.75 GB/hr. Edit optimised mysql config backup speed = 61.26 MB/s 3675.60 MB/min 215.36 GB/hr which is 12.9% faster now.
    • mysql native restore speed = 7.03 MB/s 421.80 MB/min 24.71 GB/hr
    if your server hardware and mysql configuration is optimal, it should scale with more cpu threads allocated to mydumper/myloader :)

    mydumper backup
    Code (Text):
    ./mydumperbench.sh mydumper
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.9 by George Liu (eva2000)
    https://centminmod.com
    Sun Sep 22 05:15:02 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [sbtest total size: 261 MB]
     [mydumper threads: 2 ] mydumper -t 2 -B sbtest
     [backup stats:] real: 2.52s user: 1.28s sys: 0.75s cpu: 80% maxmem: 8124 KB cswaits: 2178
     [backup speed:] 103.57 MB/s 6214.20 MB/min 364.11 GB/hr
    Successfully backed database using mydumper using 2 threads
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sun Sep 22 05:16:07 UTC 2019
    results logged: /root/mydumperlogs/mydumperbench_220919-051501.txt
    Sep 21 22:24 mydumper_extstats_210919-222320.txt
    Sep 21 22:24 mydumper_mysqlreport_210919-222320.txt
    Sep 21 22:24 mydumperbench_210919-222320.txt
    Sep 21 22:24 mydumperstats_210919-222320.txt
    Sep 21 22:24 mydumpercsv_210919-222320.txt
    Sep 21 22:24 mydumper_mycnf_210919-222320.txt
    Sep 21 22:25 mydumper_extstats_210919-222445.txt
    Sep 21 22:25 mydumper_mysqlreport_210919-222445.txt
    Sep 21 22:25 mydumperbench_210919-222445.txt
    Sep 21 22:25 mydumperstats_210919-222445.txt
    Sep 21 22:25 mydumpercsv_210919-222445.txt
    Sep 21 22:25 mydumper_mycnf_210919-222445.txt
    Sep 22 05:16 mydumper_extstats_220919-051501.txt
    Sep 22 05:16 mydumper_mysqlreport_220919-051501.txt
    Sep 22 05:16 mydumperbench_220919-051501.txt
    Sep 22 05:16 mydumperstats_220919-051501.txt
    Sep 22 05:16 mydumpercsv_220919-051501.txt
    Sep 22 05:16 mydumper_mycnf_220919-051501.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    2,2.52,1.28,0.75,80,8124,2178,103.57,6214.20,364.11
    

    myloader restore
    Code (Text):
    ./mydumperbench.sh myloader
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.9 by George Liu (eva2000)
    https://centminmod.com
    Sun Sep 22 05:16:16 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [export-20190922-051604 total size: 190 MB]
     [myloader threads: 2 ] myloader -t 2 -d export-20190922-051604 -B sbtest -q 10000
     [restore stats:] real: 13.31s user: 0.36s sys: 0.24s cpu: 4% maxmem: 7912 KB cswaits: 1241
     [restore speed:] 14.27 MB/s 856.20 MB/min 50.16 GB/hr
    Successfully restored mydumper backup using 2 threads
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sun Sep 22 05:17:32 UTC 2019
    results logged: /root/mydumperlogs/myloaderbench_220919-051616.txt
    Sep 22 05:17 myloader_extstats_220919-051616.txt
    Sep 22 05:17 myloader_mysqlreport_220919-051616.txt
    Sep 22 05:17 myloaderbench_220919-051616.txt
    Sep 22 05:17 myloaderstats_220919-051616.txt
    Sep 22 05:17 myloadercsv_220919-051616.txt
    Sep 22 05:17 myloader_mycnf_220919-051616.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    2,13.31,0.36,0.24,4,7912,1241,14.27,856.20,50.16
    

    mysqldump backup
    Code (Text):
    ./mydumperbench.sh mysqldump
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.9 by George Liu (eva2000)
    https://centminmod.com
    Sun Sep 22 05:19:09 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [sbtest total size: 261 MB]
     [mysqldump threads: 1 ] mysqldump sbtest > /backup-mydumper/mysqldump/sbtest_220919-051909.sql
     [backup stats:] real: 4.81s user: 3.49s sys: 0.99s cpu: 93% maxmem: 5120 KB cswaits: 434
     [backup speed:] 54.26 MB/s 3255.60 MB/min 190.75 GB/hr
    Successfully backed up database using mysqldump
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sun Sep 22 05:20:16 UTC 2019
    results logged: /root/mydumperlogs/mysqldumpbench_220919-051909.txt
    Sep 21 22:21 mysqldump_extstats_210919-222024.txt
    Sep 21 22:21 mysqldump_mysqlreport_210919-222024.txt
    Sep 21 22:21 mysqldumpbench_210919-222024.txt
    Sep 21 22:21 mysqldumpstats_210919-222024.txt
    Sep 21 22:21 mysqldumpcsv_210919-222024.txt
    Sep 21 22:21 mysqldump_mycnf_210919-222024.txt
    Sep 22 05:20 mysqldump_extstats_220919-051909.txt
    Sep 22 05:20 mysqldump_mysqlreport_220919-051909.txt
    Sep 22 05:20 mysqldumpbench_220919-051909.txt
    Sep 22 05:20 mysqldumpstats_220919-051909.txt
    Sep 22 05:20 mysqldumpcsv_220919-051909.txt
    Sep 22 05:20 mysqldump_mycnf_220919-051909.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    1,4.81,3.49,0.99,93,5120,434,54.26,3255.60,190.75
    

    mysql restore
    Code (Text):
    ./mydumperbench.sh mysqlrestore               
    ----------------------------------------------------------------------------------------
    mydumperbench.sh 0.0.9 by George Liu (eva2000)
    https://centminmod.com
    Sun Sep 22 05:30:12 UTC 2019
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    [/backup-mydumper/mysqldump/sbtest_220919-051909.sql size: 188 MB]
     [mysql threads: 1 ] mysql sbtest < /backup-mydumper/sbtest.sql
     [restore stats:] real: 26.74s user: 3.48s sys: 0.21s cpu: 13% maxmem: 6480 KB cswaits: 1159
     [restore speed:] 7.03 MB/s 421.80 MB/min 24.71 GB/hr
    Successfully restored mysqldump backup
    ----------------------------------------------------------------------------------------
    *************************************************************************************
    Sun Sep 22 05:31:43 UTC 2019
    results logged: /root/mydumperlogs/mysqlrestorebench_220919-053012.txt
    Sep 22 05:22 mysqlrestorebench_220919-052052.txt
    Sep 22 05:22 mysqlrestore_extstats_220919-052052.txt
    Sep 22 05:22 mysqlrestore_mysqlreport_220919-052052.txt
    Sep 22 05:22 mysqlrestorestats_220919-052052.txt
    Sep 22 05:22 mysqlrestorecsv_220919-052052.txt
    Sep 22 05:22 mysqlrestore_mycnf_220919-052052.txt
    Sep 22 05:31 mysqlrestore_extstats_220919-053012.txt
    Sep 22 05:31 mysqlrestore_mysqlreport_220919-053012.txt
    Sep 22 05:31 mysqlrestorebench_220919-053012.txt
    Sep 22 05:31 mysqlrestorestats_220919-053012.txt
    Sep 22 05:31 mysqlrestorecsv_220919-053012.txt
    Sep 22 05:31 mysqlrestore_mycnf_220919-053012.txt
    *************************************************************************************
    CSV format:
    threads,real time,user time,system time,cpu %,max mem,context switch waits,speed (MB/s),speed (MB/min),speed (GB/hr)
    1,26.74,3.48,0.21,13,6480,1159,7.03,421.80,24.71
    
     
    • Like Like x 3