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

Unable to read/write to restored with mariabackup database

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2.12
    • N/A
    • Backup
    • None
    • CentOS Linux 7.4

    Description

      Unable to read/write to restored with mariabackup database

      expected :perform read/write sql operations to restored with mariabackup database

      how to repeat;
      1 Create and load database
      2. Perform mariabackup with option --backup
      3. Perform mariabackup with option --prepare
      4. Drop the created database
      5, Restore database with mariabackup and option --move-back or --copy-back
      6. Perform some read/write sql operation to the restored database – it's returned ERROR 1005 (HY000): Can't create table `boxoffice_mdb`.`a` (errno: 13 "Permission denied")

      1.2. Perform mariabackup with option --backup
      [root@t4w6 ~]# mariabackup -u root -p1 --backup --databases=boxoffice_mdb --target-dir /var/mariadb/backup/
      180116 19:06:23 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
      Using server version 10.2.12-MariaDB
      mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
      mariabackup: uses posix_fadvise().
      mariabackup: cd to /var/lib/mysql/
      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-01-16 19:06:23 140574305568896 [Note] InnoDB: Number of pools: 1
      180116 19:06:23 >> log scanned up to (347323590)
      mariabackup: Generating a list of tablespaces
      Skipping db: ./mysql
      Skipping db: ./test
      Skipping db: ./performance_schema
      2018-01-16 19:06:23 140574305568896 [Warning] InnoDB: Allocated tablespace ID 4 for boxoffice_mdb/dim_date, old maximum was 0
      180116 19:06:23 [01] Copying ./ibdata1 to /var/mariadb/backup/ibdata1
      180116 19:06:23 [01] ...done
      180116 19:06:23 [01] Copying ./boxoffice_mdb/dim_date.ibd to /var/mariadb/backup/boxoffice_mdb/dim_date.ibd
      180116 19:06:23 [01] ...done
      180116 19:06:23 [01] Copying ./boxoffice_mdb/dim_person.ibd to /var/mariadb/backup/boxoffice_mdb/dim_person.ibd
      180116 19:06:23 [01] ...done
      180116 19:06:24 [01] Copying ./boxoffice_mdb/dim_showtime.ibd to /var/mariadb/backup/boxoffice_mdb/dim_showtime.ibd
      180116 19:06:24 [01] ...done
      180116 19:06:24 >> log scanned up to (347323590)
      180116 19:06:24 [01] Copying ./boxoffice_mdb/dim_theater_mdc.ibd to /var/mariadb/backup/boxoffice_mdb/dim_theater_mdc.ibd
      180116 19:06:24 [01] ...done
      180116 19:06:24 [01] Copying ./boxoffice_mdb/dim_theater.ibd to /var/mariadb/backup/boxoffice_mdb/dim_theater.ibd
      180116 19:06:24 [01] ...done
      180116 19:06:24 [01] Copying ./boxoffice_mdb/dim_ticket_type.ibd to /var/mariadb/backup/boxoffice_mdb/dim_ticket_type.ibd
      180116 19:06:24 [01] ...done
      180116 19:06:25 [01] Copying ./boxoffice_mdb/dim_title.ibd to /var/mariadb/backup/boxoffice_mdb/dim_title.ibd
      180116 19:06:25 [01] ...done
      180116 19:06:25 [01] Copying ./boxoffice_mdb/fact_crew.ibd to /var/mariadb/backup/boxoffice_mdb/fact_crew.ibd
      180116 19:06:25 [01] ...done
      180116 19:06:25 [01] Copying ./boxoffice_mdb/fact_tickets.ibd to /var/mariadb/backup/boxoffice_mdb/fact_tickets.ibd
      180116 19:06:26 [01] ...done
      180116 19:06:27 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
      180116 19:06:27 Executing FLUSH TABLES WITH READ LOCK...
      180116 19:06:27 Starting to backup non-InnoDB tables and files
      Skipping db: ./mysql
      Skipping db: ./test
      Skipping db: ./performance_schema
      180116 19:06:27 [01] Copying ./boxoffice_mdb/db.opt to /var/mariadb/backup/boxoffice_mdb/db.opt
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_date.frm to /var/mariadb/backup/boxoffice_mdb/dim_date.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_person.frm to /var/mariadb/backup/boxoffice_mdb/dim_person.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_showtime.frm to /var/mariadb/backup/boxoffice_mdb/dim_showtime.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_theater_mdc.frm to /var/mariadb/backup/boxoffice_mdb/dim_theater_mdc.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_theater.frm to /var/mariadb/backup/boxoffice_mdb/dim_theater.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_ticket_type.frm to /var/mariadb/backup/boxoffice_mdb/dim_ticket_type.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/dim_title.frm to /var/mariadb/backup/boxoffice_mdb/dim_title.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/fact_crew.frm to /var/mariadb/backup/boxoffice_mdb/fact_crew.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying ./boxoffice_mdb/fact_tickets.frm to /var/mariadb/backup/boxoffice_mdb/fact_tickets.frm
      180116 19:06:27 [01] ...done
      180116 19:06:27 Finished backing up non-InnoDB tables and files
      180116 19:06:27 [01] Copying aria_log_control to /var/mariadb/backup/aria_log_control
      180116 19:06:27 [01] ...done
      180116 19:06:27 [01] Copying aria_log.00000001 to /var/mariadb/backup/aria_log.00000001
      180116 19:06:27 [01] ...done
      180116 19:06:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
      mariabackup: The latest check point (for incremental): '347323581'
      mariabackup: Stopping log copying thread.

      180116 19:06:27 >> log scanned up to (347323590)
      180116 19:06:28 Executing UNLOCK TABLES
      180116 19:06:28 All tables unlocked
      180116 19:06:28 [00] Copying ib_buffer_pool to /var/mariadb/backup/ib_buffer_pool
      180116 19:06:28 [00] ...done
      180116 19:06:28 Backup created in directory '/var/mariadb/backup/'
      180116 19:06:28 [00] Writing backup-my.cnf
      180116 19:06:28 [00] ...done
      180116 19:06:28 [00] Writing xtrabackup_info
      180116 19:06:28 [00] ...done
      mariabackup: Redo log (from LSN 347323581 to 347323590) was copied.
      180116 19:06:28 completed OK!

      3. Perform mariabackup with option --prepare
      [root@t4w6 ~]# mariabackup -u root -p1 --prepare --databases=boxoffice_mdb --target-dir /var/mariadb/backup/
      mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
      mariabackup: cd to /var/mariadb/backup/
      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:12M:autoextend
      mariabackup: innodb_log_group_home_dir = .
      mariabackup: Starting InnoDB instance for recovery.
      mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Uses event mutexes
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Compressed tables use zlib 1.2.7
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Number of pools: 1
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Using generic crc32 instructions
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Completed initialization of buffer pool
      2018-01-16 19:08:34 139835872372480 [Note] InnoDB: page_cleaner coordinator priority: -20
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Highest supported file format is Barracuda.
      2018-01-16 19:08:34 139836222560384 [Note] InnoDB: Starting crash recovery from checkpoint LSN=347323581
      mariabackup: Recovered WSREP position: 714e1bbe-face-11e7-a64d-120419d03514:103
      180116 19:08:34 completed OK!

      4. Drop the created database
      MariaDB [(none)]> drop database boxoffice_mdb ;
      Query OK, 9 rows affected (0.82 sec)

      5.Restore database with mariabackup and option -move-back or --copy-back specifying-the datadir
      mysql -u root -p1 information_schema -B -N -e 'SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = "datadir" '

      [root@t4w6 ~]# mariabackup -u root -p1 --move-back --databases=boxoffice_mdb --target-dir /var/mariadb/backup/ --datadir=/var/lib/mysql/boxoffice_mdb
      mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
      180116 19:25:11 [01] Moving ibdata1 to /var/lib/mysql/boxoffice_mdb/ibdata1
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_date.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_date.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_person.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_person.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_showtime.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_showtime.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_theater_mdc.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_theater_mdc.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_theater.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_theater.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_ticket_type.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_ticket_type.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_title.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_title.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/fact_crew.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/fact_crew.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/fact_tickets.ibd to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/fact_tickets.ibd
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/db.opt to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/db.opt
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_date.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_date.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_person.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_person.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_showtime.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_showtime.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_theater_mdc.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_theater_mdc.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_theater.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_theater.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_ticket_type.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_ticket_type.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/dim_title.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/dim_title.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/fact_crew.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/fact_crew.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./boxoffice_mdb/fact_tickets.frm to /var/lib/mysql/boxoffice_mdb/boxoffice_mdb/fact_tickets.frm
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./aria_log_control to /var/lib/mysql/boxoffice_mdb/aria_log_control
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./aria_log.00000001 to /var/lib/mysql/boxoffice_mdb/aria_log.00000001
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./ib_buffer_pool to /var/lib/mysql/boxoffice_mdb/ib_buffer_pool
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./xtrabackup_info to /var/lib/mysql/boxoffice_mdb/xtrabackup_info
      180116 19:25:11 [01] ...done
      180116 19:25:11 [01] Moving ./xtrabackup_galera_info to /var/lib/mysql/boxoffice_mdb/xtrabackup_galera_info
      180116 19:25:11 [01] ...done
      180116 19:25:11 completed OK!

      6. Perform some read/write sql operation to the restored database – it's returned ERROR 1005 (HY000): Can't create table `boxoffice_mdb`.`a` (errno: 13 "Permission denied")

      MariaDB [(none)]> show tables from boxoffice_mdb ;
      ERROR 1018 (HY000): Can't read dir of './boxoffice_mdb/' (errno: 13 "Permission denied")
      MariaDB [(none)]>
      MariaDB [(none)]> drop database boxoffice_mdb ;
      ERROR 6 (HY000): Error on delete of './boxoffice_mdb/db.opt' (Errcode: 13 "Permission denied")
      MariaDB [(none)]> use boxoffice_mdb ;
      Database changed
      MariaDB [boxoffice_mdb]> create table a ( a int) ;
      ERROR 1005 (HY000): Can't create table `boxoffice_mdb`.`a` (errno: 13 "Permission denied")

      Attachments

        Activity

          People

            wlad Vladislav Vaintroub
            winstone Zdravelina Sokolovska (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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