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

rdan · Sep 22, 2019

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.

eva2000 · Sep 22, 2019

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.

rdan said: ↑

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

Click to expand...

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
What is mydumper? Why?

Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)

Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)

Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc

Manageability - supports PCRE for specifying database and tables inclusions and exclusions

Click to expand...

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)

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

pamamolf · Sep 22, 2019

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.
Click to expand...

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

eva2000 · Sep 22, 2019

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

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%

rdan · Sep 22, 2019

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.

eva2000 · Sep 22, 2019

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.

eva2000 · Sep 22, 2019

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

eva2000 · Apr 7, 2021

rdan said: ↑

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.
Click to expand...

Recently had to restore from mysqldump SQL file backup a client's Centmin Mod MariaDB 10.3 Xenforo database it's 40GB in size and restore/import part took ~51 minutes - which isn't bad, but going to optimise server settings more for better speed in future. This was on a AMD EPYC 7452 32C/64 128GB memory, 2x 960GB NVMe SSD based CentOS 7 server

eva2000 · Apr 8, 2021

eva2000 said: ↑

Recently had to restore from mysqldump SQL file backup a client's Centmin Mod MariaDB 10.3 Xenforo database it's 40GB in size and restore/import part took ~51 minutes - which isn't bad, but going to optimise server settings more for better speed in future. This was on a AMD EPYC 7452 32C/64 128GB memory, 2x 960GB NVMe SSD based CentOS 7 server
Click to expand...

Been optimising my backup and restore processes more in light of OVH datacenter fires and WebNX datacenter water damage event. Managed to get single threaded mysqldump backups to restore at least 20-40% faster and multi threaded mysqldump backed up logical files restored at least 20-80% faster

eva2000 · Apr 8, 2021

rdan said: ↑

mysqldump/mysql import is fine but takes 30 minutes more all in all to complete.
Click to expand...

Besides mysqldump, you could also try MariaDB Backup fork of Percona XtraBackup which can backup all databases including system database and restore all much faster than mysqldump/mysql restore.

For example for my client on 50MB/s tested network connection, managed to backup and transfer 65GB of MySQL databases for Wordpress and Xenforo to new server in 8.8 minutes and restore in ~11-13 minutes - all up it took ~20-22 minutes to backup + transfer + restore data with the speed boost of faster and better zstd compression support for netcat tunnel between source and remote servers https://blog.centminmod.com/2021/01...eed-for-linux-backups-using-zstd-compression/

pamamolf · Apr 8, 2021

When we will be able to have that?

eva2000 · Apr 9, 2021

pamamolf said: ↑

When we will be able to have that?
Click to expand...

My paying private clients have access right now. Then later, Centmin Mod Premium Members eventually

pamamolf · Apr 9, 2021

Great thank you

As a Premium Member i will wait for it

ct_roy · Jul 10, 2023

@eva2000
I've been experimenting with mariadb-backup (or mariabackup as it is still known on older mariadb instances!) and when used with Restic it's really powerful (especially when you can dramatically simplify incremental backups using --stream=xbstream see video here and slides here https://archive.fosdem.org/2022/sch...kup_restic/slides/5135/mariabackup_restic.pdf )

But I'd still love to see your new scripts for how your optimizing mysqldump backup/restore times. Any progress on when this be added to Premium members?

eva2000 · Jul 10, 2023

ct_roy said: ↑

But I'd still love to see your new scripts for how your optimizing mysqldump backup/restore times. Any progress on when this be added to Premium members?
Click to expand...

Yeah MariaBackup is awesome. This + faster mysqldump will be part of the new centmin.sh menu option 21 https://community.centminmod.com/th...ion-21-submenu-routine-in-130-00beta01.23773/ and https://community.centminmod.com/tags/centminsh-menu-option-21/ that is being developed and will be released to AlmaLinux / Rocky Linux 8 private beta testers and Centmin Mod Premium members first. Preview documentation for centmin.sh menu option 21 centminmod/centmin.sh-menu-21.readme.md at 130.00beta01 · centminmod/centminmod

ct_roy · Jul 10, 2023

eva2000 said: ↑

Yeah MariaBackup is awesome. This + faster mysqldump will be part of the new centmin.sh menu option 21 https://community.centminmod.com/th...ion-21-submenu-routine-in-130-00beta01.23773/ and https://community.centminmod.com/tags/centminsh-menu-option-21/ that is being developed and will be released to AlmaLinux / Rocky Linux 8 private beta testers and Centmin Mod Premium members first. Preview documentation for centmin.sh menu option 21 centminmod/centmin.sh-menu-21.readme.md at 130.00beta01 · centminmod/centminmod
Click to expand...

I did spot those commits but I don't think the actual code is in the wild yet correct?
As a premium member/eager beta tester when do you think I'll be able to take this for a spin?

buik · Jul 10, 2023

ct_roy said: ↑

I did spot those commits but I don't think the actual code is in the wild yet correct?
As a premium member/eager beta tester when do you think I'll be able to take this for a spin?
Click to expand...

You don't have to wait for this beta. It is easy to install on any system + backup (old server) and restore (new server) the database.

Mariabackup Overview

eva2000 · Jul 11, 2023

ct_roy said: ↑

I did spot those commits but I don't think the actual code is in the wild yet correct?
As a premium member/eager beta tester when do you think I'll be able to take this for a spin?
Click to expand...

Hard to say, juggling a lot of work on my end for paid clients + Centmin Mod development. Have to be a bit more careful with this as it's touching and dealing with user's data which a lot of folks wouldn't have any other backups But thanks for the reminder

buik said: ↑

You don't have to wait for this beta. It is easy to install on any system + backup (old server) and restore (new server) the database.

Mariabackup Overview
Click to expand...

I think @ct_roy is referring to me spin on it's implementation

ct_roy · Jul 23, 2023

eva2000 said: ↑

Hard to say, juggling a lot of work on my end for paid clients + Centmin Mod development. Have to be a bit more careful with this as it's touching and dealing with user's data which a lot of folks wouldn't have any other backups But thanks for the reminder

I think @ct_roy is referring to me spin on it's implementation
Click to expand...

Correct

Happy to it a good solid test whenever you're comfortable releasing it

eva2000 · Apr 28, 2024

ct_roy said: ↑

I did spot those commits but I don't think the actual code is in the wild yet correct?
As a premium member/eager beta tester when do you think I'll be able to take this for a spin?
Click to expand...

FYI, private Premium member forum actually had posted a thread back in February 2024 for this https://community.centminmod.com/th...tmin-sh-menu-option-21-data-management.24429/. You can private feedback in private Premium member forum https://community.centminmod.com/forums/premium-user-memberships.47/

Log in / Register

Forums

Discover Centmin Mod today

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

rdan Well-Known Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

rdan Well-Known Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

ct_roy Premium Member Premium Member

eva2000 Administrator Staff Member

ct_roy Premium Member Premium Member

buik “The best traveler is one without a camera.”

eva2000 Administrator Staff Member

ct_roy Premium Member Premium Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Discover Centmin Mod today

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

rdan Well-Known Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

rdan Well-Known Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

eva2000 Administrator Staff Member

pamamolf Premium Member Premium Member

ct_roy Premium Member Premium Member

eva2000 Administrator Staff Member

ct_roy Premium Member Premium Member

buik “The best traveler is one without a camera.”

eva2000 Administrator Staff Member

ct_roy Premium Member Premium Member

eva2000 Administrator Staff Member

.