search

MariaDB / mariadb-docker

Docker Official Image packaging for MariaDB
https://mariadb.org
GNU General Public License v2.0
770 stars 438 forks source link

Guidance on restoring mariabackup #390

Closed rvernica closed 2 years ago

rvernica commented 3 years ago

Assuming a common setup with /var/lib/mysql in a separate volume, some guidance would be helpful on how to restore a backup made mariabackup. The fist step is to stop MariaDB but that kills the container as well. Ideally, the restore would be done using the same Docker image (but not necessarily the same container) as the one running the MariaDB since I has the exact MariaDB version corresponding to the backup. The guidance should not assume that mariabackup is available on the host.

grooverdan commented 3 years ago

You're right. Its a little lacking in documentation:

How does this look as some steps:

The backup:

$ podman volume create mdbdata
mdbdata

$ podman volume create mdbbackup
mdbbackup

$ podman run -d -e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 -v mdbdata:/var/lib/mysql -v mdbbackup:/backup  mariadb:10.5
28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43

$ podman exec 28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43 chown mysql: /backup

$ podman exec 28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43 mysql -e 'create database bob; create table bob.t ( i int primary key)'

$ podman exec -i  28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43 gosu mysql mariabackup --backup --target-dir=/backup --user=root
[00] 2021-09-04 02:10:51 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /run/mysqld/mysqld.sock
[00] 2021-09-04 02:10:51 Using server version 10.5.12-MariaDB-1:10.5.12+maria~focal
mariabackup based on MariaDB server 10.5.12-MariaDB debian-linux-gnu (x86_64)
[00] 2021-09-04 02:10:51 uses posix_fadvise().
[00] 2021-09-04 02:10:51 cd to /var/lib/mysql/
[00] 2021-09-04 02:10:51 open files limit requested 0, set to 1048576
[00] 2021-09-04 02:10:51 mariabackup: using the following InnoDB configuration:
[00] 2021-09-04 02:10:51 innodb_data_home_dir = 
[00] 2021-09-04 02:10:51 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-09-04 02:10:51 innodb_log_group_home_dir = ./
[00] 2021-09-04 02:10:51 InnoDB: Using Linux native AIO
2021-09-04  2:10:51 0 [Note] InnoDB: Number of pools: 1
[00] 2021-09-04 02:10:51 mariabackup: Generating a list of tablespaces
[00] 2021-09-04 02:10:51 DDL tracking : create 5 "./bob/t.ibd"
[00] 2021-09-04 02:10:51 >> log scanned up to (46874)
[01] 2021-09-04 02:10:51 Copying ibdata1 to /backup/ibdata1
[01] 2021-09-04 02:10:52         ...done
[01] 2021-09-04 02:10:52 Copying ./bob/t.ibd to /backup/bob/t.ibd
[01] 2021-09-04 02:10:52         ...done
[01] 2021-09-04 02:10:52 Copying ./mysql/innodb_table_stats.ibd to /backup/mysql/innodb_table_stats.ibd
[01] 2021-09-04 02:10:52         ...done
[01] 2021-09-04 02:10:52 Copying ./mysql/transaction_registry.ibd to /backup/mysql/transaction_registry.ibd
[01] 2021-09-04 02:10:52         ...done
[01] 2021-09-04 02:10:52 Copying ./mysql/innodb_index_stats.ibd to /backup/mysql/innodb_index_stats.ibd
[01] 2021-09-04 02:10:52         ...done
[01] 2021-09-04 02:10:52 Copying ./mysql/gtid_slave_pos.ibd to /backup/mysql/gtid_slave_pos.ibd
[01] 2021-09-04 02:10:52         ...done
[00] 2021-09-04 02:10:52 >> log scanned up to (46874)
[00] 2021-09-04 02:10:52 Acquiring BACKUP LOCKS...
[00] 2021-09-04 02:10:53 Starting to backup non-InnoDB tables and files
[01] 2021-09-04 02:10:53 Copying ./bob/db.opt to /backup/bob/db.opt
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./bob/t.frm to /backup/bob/t.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/plugin.MAI to /backup/mysql/plugin.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_name.frm to /backup/mysql/time_zone_name.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/columns_priv.MAI to /backup/mysql/columns_priv.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/roles_mapping.frm to /backup/mysql/roles_mapping.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/gtid_slave_pos.frm to /backup/mysql/gtid_slave_pos.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/event.frm to /backup/mysql/event.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/global_priv.frm to /backup/mysql/global_priv.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition_type.MAI to /backup/mysql/time_zone_transition_type.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_leap_second.MAD to /backup/mysql/time_zone_leap_second.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_leap_second.MAI to /backup/mysql/time_zone_leap_second.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_category.frm to /backup/mysql/help_category.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone.MAI to /backup/mysql/time_zone.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proxies_priv.MAD to /backup/mysql/proxies_priv.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/db.opt to /backup/mysql/db.opt
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_category.MAD to /backup/mysql/help_category.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_keyword.MAD to /backup/mysql/help_keyword.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/user.frm to /backup/mysql/user.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_name.MAI to /backup/mysql/time_zone_name.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition.MAI to /backup/mysql/time_zone_transition.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/general_log.CSV to /backup/mysql/general_log.CSV
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/func.MAI to /backup/mysql/func.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_topic.MAD to /backup/mysql/help_topic.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/plugin.MAD to /backup/mysql/plugin.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/func.MAD to /backup/mysql/func.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/slow_log.CSV to /backup/mysql/slow_log.CSV
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition_type.MAD to /backup/mysql/time_zone_transition_type.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/slow_log.CSM to /backup/mysql/slow_log.CSM
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/columns_priv.MAD to /backup/mysql/columns_priv.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/tables_priv.MAI to /backup/mysql/tables_priv.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/db.MAD to /backup/mysql/db.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/innodb_table_stats.frm to /backup/mysql/innodb_table_stats.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/index_stats.MAD to /backup/mysql/index_stats.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proxies_priv.frm to /backup/mysql/proxies_priv.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/event.MAI to /backup/mysql/event.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/global_priv.MAD to /backup/mysql/global_priv.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/procs_priv.frm to /backup/mysql/procs_priv.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/roles_mapping.MAD to /backup/mysql/roles_mapping.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/func.frm to /backup/mysql/func.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proc.frm to /backup/mysql/proc.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/general_log.CSM to /backup/mysql/general_log.CSM
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proc.MAI to /backup/mysql/proc.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/tables_priv.frm to /backup/mysql/tables_priv.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/roles_mapping.MAI to /backup/mysql/roles_mapping.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/plugin.frm to /backup/mysql/plugin.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/event.MAD to /backup/mysql/event.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/columns_priv.frm to /backup/mysql/columns_priv.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_relation.frm to /backup/mysql/help_relation.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/table_stats.MAI to /backup/mysql/table_stats.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition.frm to /backup/mysql/time_zone_transition.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone.MAD to /backup/mysql/time_zone.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/column_stats.MAD to /backup/mysql/column_stats.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_keyword.MAI to /backup/mysql/help_keyword.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_category.MAI to /backup/mysql/help_category.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/general_log.frm to /backup/mysql/general_log.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/transaction_registry.frm to /backup/mysql/transaction_registry.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/innodb_index_stats.frm to /backup/mysql/innodb_index_stats.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/column_stats.frm to /backup/mysql/column_stats.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_relation.MAD to /backup/mysql/help_relation.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_topic.MAI to /backup/mysql/help_topic.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/servers.MAI to /backup/mysql/servers.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/global_priv.MAI to /backup/mysql/global_priv.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/tables_priv.MAD to /backup/mysql/tables_priv.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/procs_priv.MAD to /backup/mysql/procs_priv.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_topic.frm to /backup/mysql/help_topic.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition_type.frm to /backup/mysql/time_zone_transition_type.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/index_stats.frm to /backup/mysql/index_stats.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_name.MAD to /backup/mysql/time_zone_name.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/table_stats.MAD to /backup/mysql/table_stats.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/table_stats.frm to /backup/mysql/table_stats.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/servers.frm to /backup/mysql/servers.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_leap_second.frm to /backup/mysql/time_zone_leap_second.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proc.MAD to /backup/mysql/proc.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/procs_priv.MAI to /backup/mysql/procs_priv.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/index_stats.MAI to /backup/mysql/index_stats.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_relation.MAI to /backup/mysql/help_relation.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/servers.MAD to /backup/mysql/servers.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/db.MAI to /backup/mysql/db.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/column_stats.MAI to /backup/mysql/column_stats.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone_transition.MAD to /backup/mysql/time_zone_transition.MAD
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/slow_log.frm to /backup/mysql/slow_log.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/time_zone.frm to /backup/mysql/time_zone.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/help_keyword.frm to /backup/mysql/help_keyword.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/db.frm to /backup/mysql/db.frm
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./mysql/proxies_priv.MAI to /backup/mysql/proxies_priv.MAI
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./performance_schema/db.opt to /backup/performance_schema/db.opt
[01] 2021-09-04 02:10:53         ...done
[00] 2021-09-04 02:10:53 Finished backing up non-InnoDB tables and files
[01] 2021-09-04 02:10:53 Copying ./aria_log_control to /backup/aria_log_control
[01] 2021-09-04 02:10:53         ...done
[01] 2021-09-04 02:10:53 Copying ./aria_log.00000001 to /backup/aria_log.00000001
[01] 2021-09-04 02:10:53         ...done
[00] 2021-09-04 02:10:53 Waiting for log copy thread to read lsn 46874
[00] 2021-09-04 02:10:53 >> log scanned up to (46874)
[00] 2021-09-04 02:10:54 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
[00] 2021-09-04 02:10:54 mariabackup: The latest check point (for incremental): '45106'
mariabackup: Stopping log copying thread.[00] 2021-09-04 02:10:54 >> log scanned up to (46874)

[00] 2021-09-04 02:10:54 >> log scanned up to (46874)
[00] 2021-09-04 02:10:54 Executing BACKUP STAGE END
[00] 2021-09-04 02:10:54 All tables unlocked
[00] 2021-09-04 02:10:54 Copying ib_buffer_pool to /backup/ib_buffer_pool
[00] 2021-09-04 02:10:54         ...done
[00] 2021-09-04 02:10:54 Backup created in directory '/backup/'
[00] 2021-09-04 02:10:54 Writing backup-my.cnf
[00] 2021-09-04 02:10:54         ...done
[00] 2021-09-04 02:10:54 Writing xtrabackup_info
[00] 2021-09-04 02:10:54         ...done
[00] 2021-09-04 02:10:54 Redo log (from LSN 45106 to 46874) was copied.
[00] 2021-09-04 02:10:54 completed OK!

$ podman exec -i  28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43 gosu mysql mariabackup --prepare --target-dir=/backup
mariabackup based on MariaDB server 10.5.12-MariaDB debian-linux-gnu (x86_64)
[00] 2021-09-04 02:11:37 cd to /backup/
[00] 2021-09-04 02:11:37 open files limit requested 0, set to 1048576
[00] 2021-09-04 02:11:37 This target seems to be not prepared yet.
[00] 2021-09-04 02:11:37 mariabackup: using the following InnoDB configuration for recovery:
[00] 2021-09-04 02:11:37 innodb_data_home_dir = .
[00] 2021-09-04 02:11:37 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-09-04 02:11:37 innodb_log_group_home_dir = .
[00] 2021-09-04 02:11:37 InnoDB: Using Linux native AIO
[00] 2021-09-04 02:11:37 Starting InnoDB instance for recovery.
[00] 2021-09-04 02:11:37 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2021-09-04  2:11:37 0 [Note] InnoDB: Uses event mutexes
2021-09-04  2:11:37 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-04  2:11:37 0 [Note] InnoDB: Number of pools: 1
2021-09-04  2:11:37 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-09-04  2:11:37 0 [Note] InnoDB: Using Linux native AIO
2021-09-04  2:11:37 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2021-09-04  2:11:37 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-04  2:11:37 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=45106,45106
2021-09-04  2:11:37 0 [Note] InnoDB: Starting final batch to recover 20 pages from redo log.
[00] 2021-09-04 02:11:38 Last binlog file , position 0
[00] 2021-09-04 02:11:38 completed OK!

The restore:

$ podman kill 28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43
28a3176fcc23e1566b12d40f7309afac6734a3e9fd0e6b55436de9a524815f43

$ podman volume create mdbdatarestore
mdbdatarestore

$ podman run --rm -i -v mdbdatarestore:/var/lib/mysql -v mdbbackup:/backup mariadb:10.5 gosu mysql mariabackup --copy-back --target-dir=/backup
mariabackup based on MariaDB server 10.5.12-MariaDB debian-linux-gnu (x86_64)
[01] 2021-09-04 02:16:32 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./aria_log_control to /var/lib/mysql/aria_log_control
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./bob/db.opt to /var/lib/mysql/bob/db.opt
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./bob/t.ibd to /var/lib/mysql/bob/t.ibd
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./bob/t.frm to /var/lib/mysql/bob/t.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/plugin.MAI to /var/lib/mysql/mysql/plugin.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/columns_priv.MAI to /var/lib/mysql/mysql/columns_priv.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/roles_mapping.frm to /var/lib/mysql/mysql/roles_mapping.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/gtid_slave_pos.frm to /var/lib/mysql/mysql/gtid_slave_pos.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/global_priv.frm to /var/lib/mysql/mysql/global_priv.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition_type.MAI to /var/lib/mysql/mysql/time_zone_transition_type.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_leap_second.MAD to /var/lib/mysql/mysql/time_zone_leap_second.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_leap_second.MAI to /var/lib/mysql/mysql/time_zone_leap_second.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone.MAI to /var/lib/mysql/mysql/time_zone.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/proxies_priv.MAD to /var/lib/mysql/mysql/proxies_priv.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/db.opt to /var/lib/mysql/mysql/db.opt
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_category.MAD to /var/lib/mysql/mysql/help_category.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_keyword.MAD to /var/lib/mysql/mysql/help_keyword.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_name.MAI to /var/lib/mysql/mysql/time_zone_name.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition.MAI to /var/lib/mysql/mysql/time_zone_transition.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/func.MAI to /var/lib/mysql/mysql/func.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_topic.MAD to /var/lib/mysql/mysql/help_topic.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/plugin.MAD to /var/lib/mysql/mysql/plugin.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/func.MAD to /var/lib/mysql/mysql/func.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition_type.MAD to /var/lib/mysql/mysql/time_zone_transition_type.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/columns_priv.MAD to /var/lib/mysql/mysql/columns_priv.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/tables_priv.MAI to /var/lib/mysql/mysql/tables_priv.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/db.MAD to /var/lib/mysql/mysql/db.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/index_stats.MAD to /var/lib/mysql/mysql/index_stats.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/event.MAI to /var/lib/mysql/mysql/event.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/global_priv.MAD to /var/lib/mysql/mysql/global_priv.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/roles_mapping.MAD to /var/lib/mysql/mysql/roles_mapping.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/proc.MAI to /var/lib/mysql/mysql/proc.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/roles_mapping.MAI to /var/lib/mysql/mysql/roles_mapping.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/event.MAD to /var/lib/mysql/mysql/event.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/transaction_registry.ibd to /var/lib/mysql/mysql/transaction_registry.ibd
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/table_stats.MAI to /var/lib/mysql/mysql/table_stats.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone.MAD to /var/lib/mysql/mysql/time_zone.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/column_stats.MAD to /var/lib/mysql/mysql/column_stats.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_keyword.MAI to /var/lib/mysql/mysql/help_keyword.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_category.MAI to /var/lib/mysql/mysql/help_category.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/transaction_registry.frm to /var/lib/mysql/mysql/transaction_registry.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/innodb_index_stats.frm to /var/lib/mysql/mysql/innodb_index_stats.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/column_stats.frm to /var/lib/mysql/mysql/column_stats.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_relation.MAD to /var/lib/mysql/mysql/help_relation.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_topic.MAI to /var/lib/mysql/mysql/help_topic.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/servers.MAI to /var/lib/mysql/mysql/servers.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/global_priv.MAI to /var/lib/mysql/mysql/global_priv.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/tables_priv.MAD to /var/lib/mysql/mysql/tables_priv.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/procs_priv.MAD to /var/lib/mysql/mysql/procs_priv.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/index_stats.frm to /var/lib/mysql/mysql/index_stats.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_name.MAD to /var/lib/mysql/mysql/time_zone_name.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/table_stats.MAD to /var/lib/mysql/mysql/table_stats.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/table_stats.frm to /var/lib/mysql/mysql/table_stats.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/proc.MAD to /var/lib/mysql/mysql/proc.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/procs_priv.MAI to /var/lib/mysql/mysql/procs_priv.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/index_stats.MAI to /var/lib/mysql/mysql/index_stats.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/help_relation.MAI to /var/lib/mysql/mysql/help_relation.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/servers.MAD to /var/lib/mysql/mysql/servers.MAD
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/db.MAI to /var/lib/mysql/mysql/db.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/column_stats.MAI to /var/lib/mysql/mysql/column_stats.MAI
[01] 2021-09-04 02:16:32         ...done
[01] 2021-09-04 02:16:32 Copying ./mysql/time_zone_transition.MAD to /var/lib/mysql/mysql/time_zone_transition.MAD
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/gtid_slave_pos.ibd to /var/lib/mysql/mysql/gtid_slave_pos.ibd
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./mysql/proxies_priv.MAI to /var/lib/mysql/mysql/proxies_priv.MAI
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
[01] 2021-09-04 02:16:33         ...done
[01] 2021-09-04 02:16:33 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2021-09-04 02:16:33         ...done
[00] 2021-09-04 02:16:33 completed OK!

$ podman run -d -v mdbdatarestore:/var/lib/mysql  mariadb:10.5
e5b030ffd45968e30e43bbe0a7eb89c854c9d8984e1c92fac96482331718439c

$ podman exec -ti e5b030ffd45968e30e43bbe0a7eb89c854c9d8984e1c92fac96482331718439c mysql -e 'show tables' bob
+---------------+
| Tables_in_bob |
+---------------+
| t             |
+---------------+

$ podman exec -ti e5b030ffd45968e30e43bbe0a7eb89c854c9d8984e1c92fac96482331718439c mysql -e 'show create table t' bob
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------+

So gosu is an exe in the container that's effectively a su minimal implementation to change to the mysql user. Do I need to clarify anything above?

grooverdan commented 3 years ago 
$  podman exec -i e5b030ffd45968e30e43bbe0a7eb89c854c9d8984e1c92fac96482331718439c  cat /var/lib/mysql/xtrabackup_info
uuid = 558f781f-0d25-11ec-8d68-c2ab7eae5b3a
name = 
tool_name = mariabackup
tool_command = --backup --target-dir=/backup --user=root
tool_version = 10.5.12-MariaDB
ibbackup_version = 10.5.12-MariaDB
server_version = 10.5.12-MariaDB-1:10.5.12+maria~focal
start_time = 2021-09-04 02:10:51
end_time = 2021-09-04 02:10:54
lock_time = 0
binlog_pos = 
innodb_from_lsn = 0
innodb_to_lsn = 45106
partial = N
incremental = N
format = file
compressed = N

So mariadbbackup is already in the image. The mariadb container entrypoint without a cmd --arg or mysqld or mariadbd or blank just runs the exe without starting the server.

rvernica commented 3 years ago

Looks great. Good point about the entrypoint cmd.

Silversurfer79 commented 3 years ago

So what happens if you restore the database from a backup, is the version of the DB also reverted? Im trying find a simple way to do this in Portainer. I have 3 MariaDBs and i want to upgrade them to the latest version without breaking my wordpress sites. Thanks in advance, so lost here...

grooverdan commented 3 years ago

The restore of the data, is only the data, and is restored how it was backed up. You can run a later MariaDB version on the data by changing the container image on the startup.

You should run podman exec {container} mysql_upgrade after upgrading.

I haven't had the pleasure of using Portainer yet. I wish you a successful and simple upgrade.

Silversurfer79 commented 3 years ago

@grooverdan will test this weekend and report back ;-)

grooverdan commented 2 years ago

@Silversurfer79 how did it go?

Silversurfer79 commented 2 years ago

@Silversurfer79 how did it go?

Not great, I may have missed something clearly. I guess I need to look at this a little more. The WordPress frontend would not connect no matter what I tried to the MDB, as I did the DB update via command line.

So, I built a brand-new WP and MDB setup - all connect, basic with no config, takes about 5min. As I use a WP plugin called UpdraftPlus Backup/Restore, simply logged in with new admin account and installed the about plugin and did a full restore (DB, Plugins, Themes, Uploads and Other) and I was backup and ruining in 2-3 minutes.

I’m sure there is a better way to do it, because I’m still not 100% on the dependency between WP and MDB and the version... When I update things like Sonarr or Radarr, I simple selected Recreate and Pull the latest image, 2 seconds later its running. Now that said, containers only have Environment Variables (which are static) in Portainer which are reused and have no other decencies, but with WP and MDB they are dependent on each other.

I’m not sure if I’m using the correct terminology here, but I hope this makes sense ;-)

grooverdan commented 2 years ago

@Silversurfer79 , I'm sorry I'm not familiar enough with WordPress's dependency on MariaDB and if this is coded on installation. I'm also not familiar with the wordpress plugins mentioned. The nature of the dependency needs to be described more, but probably in a Wordpress forum. I'm happy to watch the discussion there after you link it here, and fill gaps if I see them.

For Mariabackup:

An updated version of this is now public https://hub.docker.com/_/mariadb

Please raise a new issue if anything needs correcting. Or if its just a documentation issue where you know the fix, you can edit https://github.com/docker-library/docs/blob/master/mariadb/content.md directly.