Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30670

Partial backup/restore operation fail with mariabackup after mariadb upgrade

    XMLWordPrintable

Details

    Description

      Since mariadb upgrade from 10.5 to 10.10.3 on our server, partial backup/restore script not working anymore.

      The script is used to restore db to another host (dev station).

      Assume that:

      • mariadb version is the same on 2 hosts
      • source host have slave functionnality enabled.
      • mysql_upgrade was executed on source and destination host
      • all tables of databases have innodb engine

      Our script have several phases that run successfully on mariadb 10.5 :

      • we import schema from source host and import it for destination host to be up to date
        on source : mysqldump -u$GX_USER --password=$GX_PASS --no-data $schema > /var/tmp/dump_schema_$schema.sql"
        on destination : mysql -uroot --password=$LOCAL_ROOT_PASS -e "CREATE DATABASE $schema;";mysql -uroot --password=$LOCAL_ROOT_PASS $schema < /var/tmp/dump_schema_$schema.sql
      • for every db to backup and restore, we retrieve table list and discard tablespace is enabled by : SET FOREIGN_KEY_CHECKS=0;ALTER TABLE ${DB}.${TABLE} DISCARD TABLESPACE;SET FOREIGN_KEY_CHECKS=1;
      • for every db to backup we execute mariabackup tools to backup all needed databases:
        mariabackup --binlog-info=OFF --backup --target-dir=${PARTIAL_BACKUP_DIR} --socket /var/run/mysqld/mysqld.sock --user=${USER} --password=${PASS} --databases=${DB} --tables-exclude=${GX_EXCLUDED_TABLES};
        mariabackup --prepare --export --target-dir=${PARTIAL_BACKUP_DIR};
      • mariadb stoped on destination host
      • for every db to backup, we copy idb and cfg to destination host : cp -rf ${PARTIAL_BACKUP_DIR}/${DB}/*. {cfg,ibd}

        /var/lib/mysql/${DB}/

      • ensure mariadb list dir permissions fixed by chown -R mysql:mysql /var/lib/mysql
      • mariadb started on destination host
      • On destination host, we import tablespace by executing command : echo "set FOREIGN_KEY_CHECKS=0;ALTER TABLE ${DB}.${TABLE} IMPORT TABLESPACE;set FOREIGN_KEY_CHECKS=1;ALTER TABLE ${DB}.${TABLE} engine = innodb" | mysql -S /var/run/mysqld/mysqld.sock -uroot --password=$LOCAL_ROOT_PASS -D ${DB}
      • remove all cfg files on destination host rm /var/lib/mysql/${DB}/*.cfg

      Some of db tables generate errors when we import tablespace (not all) : ERROR 1034 (HY000) at line 1: Index for table 'acces' is corrupt; try to repair it.

      We think the problem is during mariabackup -export -prepare phase because mariabackup -backup seems to be ok (we saw [00] 2023-02-17 08:09:26 completed OK!) and prepare stage seems not completing successfully :Not show ok status at then end of process :

      mariabackup based on MariaDB server 10.10.3-MariaDB debian-linux-gnu (x86_64)
      		 
      [00] 2023-02-17 08:09:26 mariabackup: auto-enabling --innodb-file-per-table due to the --export option
      		 
      [00] 2023-02-17 08:09:26 cd to /var/tmp/gestixi_data/
      		 
      [00] 2023-02-17 08:09:26 open files limit requested 0, set to 1024
      		 
      [00] 2023-02-17 08:09:26 This target seems to be not prepared yet.
      		 
      [00] 2023-02-17 08:09:26 mariabackup: using the following InnoDB configuration for recovery:
      		 
      [00] 2023-02-17 08:09:26 innodb_data_home_dir = .
      		 
      [00] 2023-02-17 08:09:26 innodb_data_file_path = ibdata1:12M:autoextend
      		 
      [00] 2023-02-17 08:09:26 innodb_log_group_home_dir = .
      		 
      [00] 2023-02-17 08:09:26 InnoDB: Using Linux native AIO
      		 
      [00] 2023-02-17 08:09:26 Starting InnoDB instance for recovery.
      		 
      [00] 2023-02-17 08:09:26 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Number of transaction pools: 1
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Using Linux native AIO
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Initializing buffer pool, total size = 100.000MiB, chunk size = 100.000MiB
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Completed initialization of buffer pool
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
      		 
      2023-02-17  8:09:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=7212435476868
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Cannot open datafile for read-only: './gestixi/paylineWebPayment.ibd' OS error: 71
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Could not find a valid tablespace file for gestixi/paylineWebPayment. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/gtid_slave_pos.ibd' OS error: 71
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      		 
      2023-02-17  8:09:27 0 [ERROR] InnoDB: Could not find a valid tablespace file for mysql/gtid_slave_pos. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 2 row operations to undo
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Trx id counter is 2440583605
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Starting final batch to recover 85 pages from redo log.
      		 
      2023-02-17  8:09:27 0 [Note] InnoDB: Last binlog file './binlog.000028', position 5012056
      		 
      [00] 2023-02-17 08:09:28 Last binlog file ./binlog.000028, position 5012056
      		 
      [00] 2023-02-17 08:09:28 Prepare export : executing "/usr/bin/mariabackup" --mysqld --defaults-file=./backup-my.cnf --defaults-group-suffix= --datadir=. --innodb --innodb-fast-shutdown=0 --loose-partition --innodb_purge_rseg_truncate_frequency=1 --innodb-buffer-pool-size=104857600 --console --log-error= --skip-log-bin --bootstrap < mariabackup_prepare_for_export.sql

      No error reported in /var/log/mysql/error.log when tablespace are imported

      We tried to :

      • lauch mysqlcheck on dest host to verify table status without errors but optimize report an index error
      • test on mariadb 10.11 on 2 hosts if issue concern mariabackup version (same result)
      • reset dest host mariadb configuration
      • innodb param tuning in my.cnf configuration on source host
      • increase free space on source and destination hosts for temporary files when mariabackup is executed
      • check file system issue

      But the error still remain.

      Do you have any idea about our problem ?
      Do you think its a bug because script working perfectly before upgrade ?

      Thanks

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23394 mariadb --prepare --export fails with "Upgrade after crash is not supported" and error 11

          • Major - Major loss of function.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29050 mariabackup issues error messages during InnoDB tablespaces export on partial backup preparing

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              Unassigned Unassigned
              antoine38 Antoine
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.