Page MenuHomePhabricator
Create Task
Maniphest T206204

Research options for producing binary backups (lvm snapshots, cold backups, mariabackup)
Closed, ResolvedPublic
Actions

Description

Which is the fastest, most reliable, and working method to produce physical database backups?

Options from the top of my mind:

  • lvm snapshots (or other snapshotting technology)
  • xtrabackup/mariabackup
  • cold backups

The 3 of them have been (or still are) in use at WMF in the past.

Either could be combined (or not) with a delayed slave system.

The method should be automatizable and have a small disk footprint. Features should be evaluated and compared for what we want to do: fast recovery and in the future, automatic provisioning of crashed and new hosts.

Related Objects
Search...

Event Timeline

jcrespo claimed this task.Oct 4 2018, 10:09 AM
jcrespo triaged this task as Medium priority.
jcrespo created this task.
jcrespo moved this task from Triage to In progress on the DBA board.
jcrespo added a comment.EditedOct 9 2018, 3:22 PM

Testing mariabackup, got it compiler so far for buster:

# /opt/wmf-mariadb103/bin/mariabackup --help
/opt/wmf-mariadb103/bin/mariabackup based on MariaDB server 10.3.10-MariaDB Linux (x86_64) 
Open source backup tool for InnoDB and XtraDB

Copyright (C) 2009-2015 Percona LLC and/or its affiliates.
Portions Copyright (C) 2000, 2011, MySQL AB & Innobase Oy. All Rights Reserved.

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation version 2
of the License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You can download full text of the license on http://www.gnu.org/licenses/gpl-2.0.txt

Usage: /opt/wmf-mariadb103/bin/mariabackup [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]
[...]

# /opt/wmf-mariadb103/bin/mariabackup --version
/opt/wmf-mariadb103/bin/mariabackup based on MariaDB server 10.3.10-MariaDB Linux (x86_64)
jcrespo added a comment.Oct 9 2018, 3:40 PM

Basic test (no data) work as xtrabackup, but being linked with mariadb server, it doesn't crash:

jynus@sangai:/srv/tmp$ sudo /opt/wmf-mariadb103/bin/mariabackup --backup -u root
181009 17:35:47 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /run/mysqld/mysqld.sock
Using server version 10.3.10-MariaDB
/opt/wmf-mariadb103/bin/mariabackup based on MariaDB server 10.3.10-MariaDB Linux (x86_64) 
mariabackup: uses posix_fadvise().
mariabackup: cd to /srv/sqldata/
mariabackup: open files limit requested 0, set to 1024
mariabackup: using the following InnoDB configuration:
mariabackup:   innodb_data_home_dir = 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
mariabackup:   innodb_log_group_home_dir = ./
2018-10-09 17:35:47 0x7ff9fc3f4740 InnoDB: Using Linux native AIO
2018-10-09 17:35:47 0 [Note] InnoDB: Number of pools: 1
mariabackup: Generating a list of tablespaces
2018-10-09 17:35:47 0 [Warning] InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
181009 17:35:47 >> log scanned up to (1630095)
181009 17:35:47 [01] Copying ibdata1 to /srv/tmp/xtrabackup_backupfiles/ibdata1
181009 17:35:47 [01]        ...done
181009 17:35:47 [01] Copying ./mysql/innodb_table_stats.ibd to /srv/tmp/xtrabackup_backupfiles/mysql/innodb_table_stats.ibd
181009 17:35:47 [01]        ...done
181009 17:35:47 [01] Copying ./mysql/innodb_index_stats.ibd to /srv/tmp/xtrabackup_backupfiles/mysql/innodb_index_stats.ibd
181009 17:35:47 [01]        ...done
181009 17:35:47 [01] Copying ./mysql/transaction_registry.ibd to /srv/tmp/xtrabackup_backupfiles/mysql/transaction_registry.ibd
181009 17:35:47 [01]        ...done
181009 17:35:47 [01] Copying ./mysql/gtid_slave_pos.ibd to /srv/tmp/xtrabackup_backupfiles/mysql/gtid_slave_pos.ibd
181009 17:35:47 [01]        ...done
181009 17:35:48 >> log scanned up to (1630095)
181009 17:35:48 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
181009 17:35:48 Executing FLUSH TABLES WITH READ LOCK...
181009 17:35:48 Starting to backup non-InnoDB tables and files
181009 17:35:48 [01] Copying ./mysql/db.frm to /srv/tmp/xtrabackup_backupfiles/mysql/db.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/db.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/db.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/db.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/db.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/host.frm to /srv/tmp/xtrabackup_backupfiles/mysql/host.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/host.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/host.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/host.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/host.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/user.frm to /srv/tmp/xtrabackup_backupfiles/mysql/user.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/user.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/user.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/user.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/user.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/roles_mapping.frm to /srv/tmp/xtrabackup_backupfiles/mysql/roles_mapping.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/roles_mapping.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/roles_mapping.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/roles_mapping.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/roles_mapping.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/func.frm to /srv/tmp/xtrabackup_backupfiles/mysql/func.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/func.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/func.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/func.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/func.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/plugin.frm to /srv/tmp/xtrabackup_backupfiles/mysql/plugin.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/plugin.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/plugin.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/plugin.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/plugin.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/servers.frm to /srv/tmp/xtrabackup_backupfiles/mysql/servers.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/servers.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/servers.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/servers.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/servers.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/tables_priv.frm to /srv/tmp/xtrabackup_backupfiles/mysql/tables_priv.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/tables_priv.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/tables_priv.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/tables_priv.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/tables_priv.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/columns_priv.frm to /srv/tmp/xtrabackup_backupfiles/mysql/columns_priv.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/columns_priv.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/columns_priv.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/columns_priv.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/columns_priv.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_topic.frm to /srv/tmp/xtrabackup_backupfiles/mysql/help_topic.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_topic.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/help_topic.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_topic.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/help_topic.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_category.frm to /srv/tmp/xtrabackup_backupfiles/mysql/help_category.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_category.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/help_category.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_category.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/help_category.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_relation.frm to /srv/tmp/xtrabackup_backupfiles/mysql/help_relation.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_relation.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/help_relation.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_relation.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/help_relation.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_keyword.frm to /srv/tmp/xtrabackup_backupfiles/mysql/help_keyword.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_keyword.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/help_keyword.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/help_keyword.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/help_keyword.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_name.frm to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_name.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_name.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_name.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_name.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_name.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone.frm to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition.frm to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition_type.frm to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition_type.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition_type.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition_type.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_transition_type.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_transition_type.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_leap_second.frm to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_leap_second.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_leap_second.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_leap_second.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/time_zone_leap_second.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/time_zone_leap_second.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proc.frm to /srv/tmp/xtrabackup_backupfiles/mysql/proc.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proc.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/proc.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proc.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/proc.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/procs_priv.frm to /srv/tmp/xtrabackup_backupfiles/mysql/procs_priv.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/procs_priv.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/procs_priv.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/procs_priv.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/procs_priv.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/general_log.frm to /srv/tmp/xtrabackup_backupfiles/mysql/general_log.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/general_log.CSM to /srv/tmp/xtrabackup_backupfiles/mysql/general_log.CSM
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/general_log.CSV to /srv/tmp/xtrabackup_backupfiles/mysql/general_log.CSV
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/slow_log.frm to /srv/tmp/xtrabackup_backupfiles/mysql/slow_log.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/slow_log.CSM to /srv/tmp/xtrabackup_backupfiles/mysql/slow_log.CSM
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/slow_log.CSV to /srv/tmp/xtrabackup_backupfiles/mysql/slow_log.CSV
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/event.frm to /srv/tmp/xtrabackup_backupfiles/mysql/event.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/event.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/event.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/event.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/event.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/innodb_table_stats.frm to /srv/tmp/xtrabackup_backupfiles/mysql/innodb_table_stats.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/innodb_index_stats.frm to /srv/tmp/xtrabackup_backupfiles/mysql/innodb_index_stats.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/transaction_registry.frm to /srv/tmp/xtrabackup_backupfiles/mysql/transaction_registry.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proxies_priv.frm to /srv/tmp/xtrabackup_backupfiles/mysql/proxies_priv.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proxies_priv.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/proxies_priv.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/proxies_priv.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/proxies_priv.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/table_stats.frm to /srv/tmp/xtrabackup_backupfiles/mysql/table_stats.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/table_stats.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/table_stats.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/table_stats.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/table_stats.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/column_stats.frm to /srv/tmp/xtrabackup_backupfiles/mysql/column_stats.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/column_stats.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/column_stats.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/column_stats.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/column_stats.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/index_stats.frm to /srv/tmp/xtrabackup_backupfiles/mysql/index_stats.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/index_stats.MYI to /srv/tmp/xtrabackup_backupfiles/mysql/index_stats.MYI
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/index_stats.MYD to /srv/tmp/xtrabackup_backupfiles/mysql/index_stats.MYD
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./mysql/gtid_slave_pos.frm to /srv/tmp/xtrabackup_backupfiles/mysql/gtid_slave_pos.frm
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./performance_schema/db.opt to /srv/tmp/xtrabackup_backupfiles/performance_schema/db.opt
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying ./test/db.opt to /srv/tmp/xtrabackup_backupfiles/test/db.opt
181009 17:35:48 [01]        ...done
181009 17:35:48 Finished backing up non-InnoDB tables and files
181009 17:35:48 [01] Copying aria_log_control to /srv/tmp/xtrabackup_backupfiles/aria_log_control
181009 17:35:48 [01]        ...done
181009 17:35:48 [01] Copying aria_log.00000001 to /srv/tmp/xtrabackup_backupfiles/aria_log.00000001
181009 17:35:48 [01]        ...done
181009 17:35:48 Waiting for log copy thread to read lsn 1630095
181009 17:35:49 >> log scanned up to (1630095)
181009 17:35:49 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
mariabackup: The latest check point (for incremental): '1630086'
mariabackup: Stopping log copying thread.181009 17:35:49 >> log scanned up to (1630095)

181009 17:35:49 >> log scanned up to (1630095)
181009 17:35:49 Executing UNLOCK TABLES
181009 17:35:49 All tables unlocked
181009 17:35:49 [00] Copying ib_buffer_pool to /srv/tmp/xtrabackup_backupfiles/ib_buffer_pool
181009 17:35:49 [00]        ...done
181009 17:35:49 Backup created in directory '/srv/tmp/xtrabackup_backupfiles/'
181009 17:35:49 [00] Writing backup-my.cnf
181009 17:35:49 [00]        ...done
181009 17:35:49 [00] Writing xtrabackup_info
181009 17:35:49 [00]        ...done
mariabackup: Redo log (from LSN 1630086 to 1630095) was copied.
181009 17:35:49 completed OK!
jynus@sangai:/srv/tmp$ ls -la
total 0
drwxr-xr-x 3 mysql mysql  36 Oct  9 17:35 .
drwxr-xr-x 4 root  root   32 Oct  9 17:28 ..
drwx------ 5 root  root  236 Oct  9 17:35 xtrabackup_backupfiles
jynus@sangai:/srv/tmp$ sudo -s
root@sangai:/srv/tmp# cd xtrabackup_backupfiles/
aria_log_control   ib_buffer_pool  ib_logfile0  performance_schema  xtrabackup_checkpoints
root@sangai:/srv/tmp/xtrabackup_backupfiles# ls -la
total 12332
drwx------ 5 root  root       236 Oct  9 17:35 .
drwxr-xr-x 3 mysql mysql       36 Oct  9 17:35 ..
-rw-r----- 1 root  root     16384 Oct  9 17:35 aria_log.00000001
-rw-r----- 1 root  root        52 Oct  9 17:35 aria_log_control
-rw-r----- 1 root  root       297 Oct  9 17:35 backup-my.cnf
-rw-r----- 1 root  root       970 Oct  9 17:35 ib_buffer_pool
-rw-r----- 1 root  root  12582912 Oct  9 17:35 ibdata1
-rw-r----- 1 root  root      2560 Oct  9 17:35 ib_logfile0
drwx------ 2 root  root      4096 Oct  9 17:35 mysql
drwx------ 2 root  root        20 Oct  9 17:35 performance_schema
drwx------ 2 root  root        20 Oct  9 17:35 test
-rw-r----- 1 root  root       101 Oct  9 17:35 xtrabackup_checkpoints
-rw-r----- 1 root  root       400 Oct  9 17:35 xtrabackup_info
root@sangai:/srv/tmp/xtrabackup_backupfiles# cd ..
root@sangai:/srv/tmp# /opt/wmf-mariadb103/bin/mariabackup --prepare
/opt/wmf-mariadb103/bin/mariabackup based on MariaDB server 10.3.10-MariaDB Linux (x86_64) 
mariabackup: cd to /srv/tmp/xtrabackup_backupfiles/
mariabackup: This target seems to be not prepared yet.
mariabackup: using the following InnoDB configuration for recovery:
mariabackup:   innodb_data_home_dir = .
mariabackup:   innodb_data_file_path = ibdata1:10M:autoextend
mariabackup:   innodb_log_group_home_dir = .
2018-10-09 17:37:04 0x7fc43d3c0740 InnoDB: Using Linux native AIO
mariabackup: Starting InnoDB instance for recovery.
mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2018-10-09 17:37:04 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-10-09 17:37:04 0 [Note] InnoDB: Uses event mutexes
2018-10-09 17:37:04 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-10-09 17:37:04 0 [Note] InnoDB: Number of pools: 1
2018-10-09 17:37:04 0 [Note] InnoDB: Using SSE2 crc32 instructions
2018-10-09 17:37:04 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2018-10-09 17:37:04 0 [Note] InnoDB: Completed initialization of buffer pool
2018-10-09 17:37:04 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2018-10-09 17:37:04 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1630086
Last binlog file , position 0
181009 17:37:05 completed OK!
root@sangai:/srv/tmp# ls -la
total 0
drwxr-xr-x 3 mysql mysql  36 Oct  9 17:37 .
drwxr-xr-x 4 root  root   32 Oct  9 17:28 ..
drwx------ 5 root  root  272 Oct  9 17:37 xtrabackup_backupfiles
root@sangai:/srv/tmp# cd xtrabackup_backupfiles/
root@sangai:/srv/tmp/xtrabackup_backupfiles# ls -la
total 12332
drwx------ 5 root  root       272 Oct  9 17:37 .
drwxr-xr-x 3 mysql mysql       36 Oct  9 17:37 ..
-rw-r----- 1 root  root     16384 Oct  9 17:35 aria_log.00000001
-rw-r----- 1 root  root        52 Oct  9 17:35 aria_log_control
-rw-r----- 1 root  root       297 Oct  9 17:35 backup-my.cnf
-rw-r----- 1 root  root       970 Oct  9 17:35 ib_buffer_pool
-rw-r----- 1 root  root  12582912 Oct  9 17:37 ibdata1
-rw-r----- 1 root  root         0 Oct  9 17:37 ib_logfile0
drwx------ 2 root  root      4096 Oct  9 17:35 mysql
drwx------ 2 root  root        20 Oct  9 17:35 performance_schema
drwx------ 2 root  root        20 Oct  9 17:35 test
-rw-r--r-- 1 root  root         3 Oct  9 17:37 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root  root        99 Oct  9 17:37 xtrabackup_checkpoints
-rw-r----- 1 root  root       400 Oct  9 17:35 xtrabackup_info
root@sangai:/srv/tmp/xtrabackup_backupfiles# cat xtrabackup_info 
uuid = ff7d37f9-cbd8-11e8-a2ac-74d02bcbf0c0
name = 
tool_name = mariabackup
tool_command = --backup -u root
tool_version = 10.3.10-MariaDB
ibbackup_version = 10.3.10-MariaDB
server_version = 10.3.10-MariaDB
start_time = 2018-10-09 17:35:47
end_time = 2018-10-09 17:35:49
lock_time = 0
binlog_pos = 
innodb_from_lsn = 0
innodb_to_lsn = 1630086
partial = N
incremental = N
format = file
compressed = N
jcrespo added a comment.Oct 9 2018, 3:45 PM

Basic streaming works, too:

root@sangai:/srv/tmp# /opt/wmf-mariadb103/bin/mariabackup --backup --user=root --stream=xbstream | pigz -c | pv > backup.xbstream.gz
181009 17:44:32 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /run/mysqld/mysqld.sock
Using server version 10.3.10-MariaDB
/opt/wmf-mariadb103/bin/mariabackup based on MariaDB server 10.3.10-MariaDB Linux (x86_64) 
mariabackup: uses posix_fadvise().
mariabackup: cd to /srv/sqldata/
mariabackup: open files limit requested 0, set to 1024
mariabackup: using the following InnoDB configuration:
mariabackup:   innodb_data_home_dir = 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
mariabackup:   innodb_log_group_home_dir = ./
2018-10-09 17:44:32 0x7fc7c0931740 InnoDB: Using Linux native AIO
2018-10-09 17:44:32 0 [Note] InnoDB: Number of pools: 1
mariabackup: Generating a list of tablespaces
2018-10-09 17:44:32 0 [Warning] InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
181009 17:44:32 >> log scanned up to (1630095)
181009 17:44:32 [01] Streaming ibdata1
181009 17:44:32 [01]        ...done
181009 17:44:32 [01] Streaming ./mysql/innodb_table_stats.ibd
181009 17:44:32 [01]        ...done
181009 17:44:32 [01] Streaming ./mysql/innodb_index_stats.ibd
181009 17:44:32 [01]        ...done
181009 17:44:32 [01] Streaming ./mysql/transaction_registry.ibd
181009 17:44:32 [01]        ...done
181009 17:44:32 [01] Streaming ./mysql/gtid_slave_pos.ibd
181009 17:44:32 [01]        ...done
181009 17:44:33 >> log scanned up to (1630095)
181009 17:44:33 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
181009 17:44:33 Executing FLUSH TABLES WITH READ LOCK...
181009 17:44:33 Starting to backup non-InnoDB tables and files
181009 17:44:33 [01] Streaming ./mysql/db.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/db.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/db.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/host.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/host.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/host.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/user.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/user.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/user.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/roles_mapping.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/roles_mapping.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/roles_mapping.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/func.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/func.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/func.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/plugin.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/plugin.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/plugin.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/servers.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/servers.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/servers.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/tables_priv.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/tables_priv.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/tables_priv.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/columns_priv.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/columns_priv.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/columns_priv.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_topic.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_topic.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_topic.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_category.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_category.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_category.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_relation.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_relation.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_relation.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_keyword.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_keyword.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/help_keyword.MYD to <STDOUT>
181009 17:44:33 [01]        ...done                                                                               ]
181009 17:44:33 [01] Streaming ./mysql/time_zone_name.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_name.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_name.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition_type.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition_type.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_transition_type.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_leap_second.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_leap_second.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/time_zone_leap_second.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proc.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proc.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proc.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/procs_priv.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/procs_priv.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/procs_priv.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/general_log.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/general_log.CSM to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/general_log.CSV to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/slow_log.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/slow_log.CSM to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/slow_log.CSV to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/event.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/event.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/event.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/innodb_table_stats.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/innodb_index_stats.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/transaction_registry.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proxies_priv.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proxies_priv.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/proxies_priv.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/table_stats.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/table_stats.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/table_stats.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/column_stats.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/column_stats.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/column_stats.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/index_stats.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/index_stats.MYI to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/index_stats.MYD to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./mysql/gtid_slave_pos.frm to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./performance_schema/db.opt to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming ./test/db.opt to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 Finished backing up non-InnoDB tables and files
181009 17:44:33 [01] Streaming aria_log_control to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 [01] Streaming aria_log.00000001 to <STDOUT>
181009 17:44:33 [01]        ...done
181009 17:44:33 Waiting for log copy thread to read lsn 1630095
181009 17:44:34 >> log scanned up to (1630095)
181009 17:44:34 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
mariabackup: The latest check point (for incremental): '1630086'
mariabackup: Stopping log copying thread.181009 17:44:34 >> log scanned up to (1630095)
 171KiB 0:00:02 [ 133KiB/s] [   <=>                                                                               ]
181009 17:44:35 >> log scanned up to (1630095)
181009 17:44:35 Executing UNLOCK TABLES
181009 17:44:35 All tables unlocked
181009 17:44:35 [00] Streaming ib_buffer_pool to <STDOUT>
181009 17:44:35 [00]        ...done
181009 17:44:35 Backup created in directory '/srv/tmp/xtrabackup_backupfiles/'
181009 17:44:35 [00] Streaming backup-my.cnf
181009 17:44:35 [00]        ...done
181009 17:44:35 [00] Streaming xtrabackup_info
181009 17:44:35 [00]        ...done
mariabackup: Redo log (from LSN 1630086 to 1630095) was copied.
181009 17:44:35 completed OK!
 202KiB 0:00:02 [90.2KiB/s] [    <=>                                                                              ]
root@sangai:/srv/tmp# ls -la
total 204
drwxr-xr-x 3 mysql mysql     62 Oct  9 17:44 .
drwxr-xr-x 4 root  root      32 Oct  9 17:28 ..
-rw-r--r-- 1 root  root  207786 Oct  9 17:44 backup.xbstream.gz
drwx------ 5 root  root     272 Oct  9 17:37 xtrabackup_backupfiles
Marostegui added a comment.Oct 10 2018, 6:19 AM

Great news so far!!

jcrespo mentioned this in T210292: Implement a proof of concept of a snapshot cycle automation for a mediawiki section database.Nov 23 2018, 5:27 PM
jcrespo closed this task as Resolved.Nov 30 2018, 12:09 PM

Resolved, it is most likely going to be mariabackup/xtrabackup, it just has not been proved on a production load. Followup on T210292

Jgreen mentioned this in T276100: improve civicrm dump/restore strategy .Mar 1 2021, 2:38 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits