[MDEV-14976] Service mysql fail to start after restoring from mariabackup Created: 2018-01-17  Updated: 2021-01-28  Resolved: 2018-03-04

Status: Closed
Project: MariaDB Server
Component/s: Backup
Affects Version/s: 10.2.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS Linux 7.4


Issue Links:
Blocks
is blocked by MDEV-24723 Feature Request: mariabackup should m... Open
Relates
relates to MDEV-14937 Test final version of MariaDB Backup ... Closed
relates to MDEV-15211 Mariabackups give "table 'X' doesn't ... Closed

 Description   

Service mysql fail to start after restoring from mariabackup

expected : Service mysql start after recovering with mariabackup

note : Workaround – manually change the owner of the restored by mariabackup tool data
to mysql and restart mysql service

although it would be expected that the tool is preserving the ownership
of backup

how to repeat;
1 Create and load database
2. Perform mariabackup with option --backup
3. Perform mariabackup with option --prepare
4. Stop mysql service
5. Remove the content of the dir /var/lib/mysql/
6. Restore database with mariabackup and option --copy-back
7. Start mysql service - fails

1.2 Perform mariabackup with option --backup

[root@t4w6 ~]#  mariabackup -u ff -p1 --backup  --target-dir /var/backuup1
180117 18:14:45 Connecting to MySQL server host: localhost, user: ff, 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-17 18:14:45 140548353980544 [Note] InnoDB: Number of pools: 1
180117 18:14:45 >> log scanned up to (1628412)
mariabackup: Generating a list of tablespaces
2018-01-17 18:14:45 140548353980544 [Warning] InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
180117 18:14:45 [01] Copying ./ibdata1 to /var/backuup1/ibdata1
180117 18:14:46 [01]        ...done
180117 18:14:46 [01] Copying ./mysql/innodb_table_stats.ibd to /var/backuup1/mysql/innodb_table_stats.ibd
180117 18:14:46 [01]        ...done
180117 18:14:46 [01] Copying ./mysql/innodb_index_stats.ibd to /var/backuup1/mysql/innodb_index_stats.ibd
180117 18:14:46 [01]        ...done
180117 18:14:46 [01] Copying ./mysql/gtid_slave_pos.ibd to /var/backuup1/mysql/gtid_slave_pos.ibd
180117 18:14:46 [01]        ...done
180117 18:14:46 [01] Copying ./ff/ff.ibd to /var/backuup1/ff/ff.ibd
180117 18:14:46 [01]        ...done
180117 18:14:46 [01] Copying ./ff1/ff1.ibd to /var/backuup1/ff1/ff1.ibd
180117 18:14:46 [01]        ...done
180117 18:14:46 >> log scanned up to (1628412)
180117 18:14:46 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180117 18:14:46 Executing FLUSH TABLES WITH READ LOCK...
180117 18:14:46 Starting to backup non-InnoDB tables and files
180117 18:14:46 [01] Copying ./mysql/db.frm to /var/backuup1/mysql/db.frm
180117 18:14:46 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/db.MYI to /var/backuup1/mysql/db.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/db.MYD to /var/backuup1/mysql/db.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/host.frm to /var/backuup1/mysql/host.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/host.MYI to /var/backuup1/mysql/host.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/host.MYD to /var/backuup1/mysql/host.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/user.frm to /var/backuup1/mysql/user.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/user.MYI to /var/backuup1/mysql/user.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/user.MYD to /var/backuup1/mysql/user.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/roles_mapping.frm to /var/backuup1/mysql/roles_mapping.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/roles_mapping.MYI to /var/backuup1/mysql/roles_mapping.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/roles_mapping.MYD to /var/backuup1/mysql/roles_mapping.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/func.frm to /var/backuup1/mysql/func.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/func.MYI to /var/backuup1/mysql/func.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/func.MYD to /var/backuup1/mysql/func.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/plugin.frm to /var/backuup1/mysql/plugin.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/plugin.MYI to /var/backuup1/mysql/plugin.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/plugin.MYD to /var/backuup1/mysql/plugin.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/servers.frm to /var/backuup1/mysql/servers.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/servers.MYI to /var/backuup1/mysql/servers.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/servers.MYD to /var/backuup1/mysql/servers.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/tables_priv.frm to /var/backuup1/mysql/tables_priv.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/tables_priv.MYI to /var/backuup1/mysql/tables_priv.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/tables_priv.MYD to /var/backuup1/mysql/tables_priv.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/columns_priv.frm to /var/backuup1/mysql/columns_priv.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/columns_priv.MYI to /var/backuup1/mysql/columns_priv.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/columns_priv.MYD to /var/backuup1/mysql/columns_priv.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_topic.frm to /var/backuup1/mysql/help_topic.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_topic.MYI to /var/backuup1/mysql/help_topic.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_topic.MYD to /var/backuup1/mysql/help_topic.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_category.frm to /var/backuup1/mysql/help_category.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_category.MYI to /var/backuup1/mysql/help_category.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_category.MYD to /var/backuup1/mysql/help_category.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_relation.frm to /var/backuup1/mysql/help_relation.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_relation.MYI to /var/backuup1/mysql/help_relation.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_relation.MYD to /var/backuup1/mysql/help_relation.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_keyword.frm to /var/backuup1/mysql/help_keyword.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_keyword.MYI to /var/backuup1/mysql/help_keyword.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/help_keyword.MYD to /var/backuup1/mysql/help_keyword.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_name.frm to /var/backuup1/mysql/time_zone_name.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_name.MYI to /var/backuup1/mysql/time_zone_name.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_name.MYD to /var/backuup1/mysql/time_zone_name.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone.frm to /var/backuup1/mysql/time_zone.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone.MYI to /var/backuup1/mysql/time_zone.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone.MYD to /var/backuup1/mysql/time_zone.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition.frm to /var/backuup1/mysql/time_zone_transition.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition.MYI to /var/backuup1/mysql/time_zone_transition.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition.MYD to /var/backuup1/mysql/time_zone_transition.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition_type.frm to /var/backuup1/mysql/time_zone_transition_type.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/backuup1/mysql/time_zone_transition_type.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/backuup1/mysql/time_zone_transition_type.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_leap_second.frm to /var/backuup1/mysql/time_zone_leap_second.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/backuup1/mysql/time_zone_leap_second.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/backuup1/mysql/time_zone_leap_second.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/proc.frm to /var/backuup1/mysql/proc.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/proc.MYI to /var/backuup1/mysql/proc.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/proc.MYD to /var/backuup1/mysql/proc.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/procs_priv.frm to /var/backuup1/mysql/procs_priv.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/procs_priv.MYI to /var/backuup1/mysql/procs_priv.MYI
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/procs_priv.MYD to /var/backuup1/mysql/procs_priv.MYD
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/general_log.frm to /var/backuup1/mysql/general_log.frm
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/general_log.CSM to /var/backuup1/mysql/general_log.CSM
180117 18:14:47 [01]        ...done
180117 18:14:47 [01] Copying ./mysql/general_log.CSV to /var/backuup1/mysql/general_log.CSV
180117 18:14:47 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/slow_log.frm to /var/backuup1/mysql/slow_log.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/slow_log.CSM to /var/backuup1/mysql/slow_log.CSM
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/slow_log.CSV to /var/backuup1/mysql/slow_log.CSV
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/event.frm to /var/backuup1/mysql/event.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/event.MYI to /var/backuup1/mysql/event.MYI
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/event.MYD to /var/backuup1/mysql/event.MYD
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/innodb_table_stats.frm to /var/backuup1/mysql/innodb_table_stats.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/innodb_index_stats.frm to /var/backuup1/mysql/innodb_index_stats.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/proxies_priv.frm to /var/backuup1/mysql/proxies_priv.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/proxies_priv.MYI to /var/backuup1/mysql/proxies_priv.MYI
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/proxies_priv.MYD to /var/backuup1/mysql/proxies_priv.MYD
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/table_stats.frm to /var/backuup1/mysql/table_stats.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/table_stats.MYI to /var/backuup1/mysql/table_stats.MYI
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/table_stats.MYD to /var/backuup1/mysql/table_stats.MYD
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/column_stats.frm to /var/backuup1/mysql/column_stats.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/column_stats.MYI to /var/backuup1/mysql/column_stats.MYI
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/column_stats.MYD to /var/backuup1/mysql/column_stats.MYD
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/index_stats.frm to /var/backuup1/mysql/index_stats.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/index_stats.MYI to /var/backuup1/mysql/index_stats.MYI
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/index_stats.MYD to /var/backuup1/mysql/index_stats.MYD
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./mysql/gtid_slave_pos.frm to /var/backuup1/mysql/gtid_slave_pos.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./ff/db.opt to /var/backuup1/ff/db.opt
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./ff/ff.frm to /var/backuup1/ff/ff.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./test/db.opt to /var/backuup1/test/db.opt
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./performance_schema/db.opt to /var/backuup1/performance_schema/db.opt
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./ff1/db.opt to /var/backuup1/ff1/db.opt
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying ./ff1/ff1.frm to /var/backuup1/ff1/ff1.frm
180117 18:14:48 [01]        ...done
180117 18:14:48 Finished backing up non-InnoDB tables and files
180117 18:14:48 [01] Copying aria_log_control to /var/backuup1/aria_log_control
180117 18:14:48 [01]        ...done
180117 18:14:48 [01] Copying aria_log.00000001 to /var/backuup1/aria_log.00000001
180117 18:14:48 [01]        ...done
180117 18:14:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
mariabackup: The latest check point (for incremental): '1628403'
mariabackup: Stopping log copying thread.
 
180117 18:14:48 >> log scanned up to (1628412)
180117 18:14:48 Executing UNLOCK TABLES
180117 18:14:48 All tables unlocked
180117 18:14:48 [00] Copying ib_buffer_pool to /var/backuup1/ib_buffer_pool
180117 18:14:48 [00]        ...done
180117 18:14:48 Backup created in directory '/var/backuup1/'
180117 18:14:48 [00] Writing backup-my.cnf
180117 18:14:48 [00]        ...done
180117 18:14:48 [00] Writing xtrabackup_info
180117 18:14:48 [00]        ...done
mariabackup: Redo log (from LSN 1628403 to 1628412) was copied.
180117 18:14:48 completed OK!

3. Perform mariabackup with option --prepare

[root@t4w6 ~]#  mariabackup -u ff -p1 --prepare  --target-dir /var/backuup1
mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
mariabackup: cd to /var/backuup1/
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-17 18:21:00 140441091446912 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Uses event mutexes
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Compressed tables use zlib 1.2.7
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Number of pools: 1
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Using generic crc32 instructions
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Completed initialization of buffer pool
2018-01-17 18:21:00 140440263194368 [Note] InnoDB: page_cleaner coordinator priority: -20
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-17 18:21:00 140441091446912 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1628403
180117 18:21:00 completed OK!

4. Stop mysql service

[root@t4w6 ~]# service mysql stop
Stopping mysql (via systemctl):                            [  OK  ]

5. Remove the content of the dir /var/lib/mysql/

[root@t4w6 ~]#  rm -rf /var/lib/mysql/*
[root@t4w6 ~]#

6. Restore database with mariabackup and option --copy-back

[root@t4w6 ~]#  mariabackup -u ff -p1 --copy-back  --target-dir /var/backuup1
mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
180117 18:23:58 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
180117 18:23:58 [01]        ...done
180117 18:23:58 [01] Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
180117 18:23:58 [01]        ...done
180117 18:23:58 [01] Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
180117 18:23:58 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/gtid_slave_pos.ibd to /var/lib/mysql/mysql/gtid_slave_pos.ibd
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/roles_mapping.frm to /var/lib/mysql/mysql/roles_mapping.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/roles_mapping.MYI to /var/lib/mysql/mysql/roles_mapping.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/roles_mapping.MYD to /var/lib/mysql/mysql/roles_mapping.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/plugin.MYI to /var/lib/mysql/mysql/plugin.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/plugin.MYD to /var/lib/mysql/mysql/plugin.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/servers.MYD to /var/lib/mysql/mysql/servers.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_topic.MYI to /var/lib/mysql/mysql/help_topic.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_topic.MYD to /var/lib/mysql/mysql/help_topic.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_category.MYI to /var/lib/mysql/mysql/help_category.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_relation.MYI to /var/lib/mysql/mysql/help_relation.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_relation.MYD to /var/lib/mysql/mysql/help_relation.MYD
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_keyword.MYI to /var/lib/mysql/mysql/help_keyword.MYI
180117 18:23:59 [01]        ...done
180117 18:23:59 [01] Copying ./mysql/help_keyword.MYD to /var/lib/mysql/mysql/help_keyword.MYD
180117 18:23:59 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_name.MYI to /var/lib/mysql/mysql/time_zone_name.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_name.MYD to /var/lib/mysql/mysql/time_zone_name.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone.MYI to /var/lib/mysql/mysql/time_zone.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone.MYD to /var/lib/mysql/mysql/time_zone.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition.MYI to /var/lib/mysql/mysql/time_zone_transition.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/lib/mysql/mysql/time_zone_transition_type.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/lib/mysql/mysql/time_zone_transition_type.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/lib/mysql/mysql/time_zone_leap_second.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/lib/mysql/mysql/time_zone_leap_second.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/innodb_index_stats.frm to /var/lib/mysql/mysql/innodb_index_stats.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD
180117 18:24:00 [01]        ...done
180117 18:24:00 [01] Copying ./mysql/table_stats.frm to /var/lib/mysql/mysql/table_stats.frm
180117 18:24:00 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/table_stats.MYI to /var/lib/mysql/mysql/table_stats.MYI
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/table_stats.MYD to /var/lib/mysql/mysql/table_stats.MYD
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/column_stats.frm to /var/lib/mysql/mysql/column_stats.frm
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/column_stats.MYI to /var/lib/mysql/mysql/column_stats.MYI
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/column_stats.MYD to /var/lib/mysql/mysql/column_stats.MYD
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/index_stats.frm to /var/lib/mysql/mysql/index_stats.frm
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/index_stats.MYI to /var/lib/mysql/mysql/index_stats.MYI
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/index_stats.MYD to /var/lib/mysql/mysql/index_stats.MYD
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./mysql/gtid_slave_pos.frm to /var/lib/mysql/mysql/gtid_slave_pos.frm
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff/ff.ibd to /var/lib/mysql/ff/ff.ibd
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff/db.opt to /var/lib/mysql/ff/db.opt
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff/ff.frm to /var/lib/mysql/ff/ff.frm
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff1/ff1.ibd to /var/lib/mysql/ff1/ff1.ibd
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff1/db.opt to /var/lib/mysql/ff1/db.opt
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ff1/ff1.frm to /var/lib/mysql/ff1/ff1.frm
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./aria_log_control to /var/lib/mysql/aria_log_control
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
180117 18:24:01 [01]        ...done
180117 18:24:01 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180117 18:24:01 [01]        ...done
180117 18:24:01 completed OK!
[root@t4w6 ~]#
[root@t4w6 ~]#

7. Start mysql service - fails

[root@t4w6 ~]# service mysql start
Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]

Workaround :

[root@t4w6 ~]#  chown -R  mysql:mysql /var/lib/mysql
[root@t4w6 ~]# service mysql start
Starting mysql (via systemctl):                            [  OK  ]



 Comments   
Comment by Zdravelina Sokolovska (Inactive) [ 2018-01-17 ]

when start mysql service the files and subdirs of dir /var/lib/mysql are owner by mysql ;

after recovering with the tool mariabackup the files and subdirs of dir /var/lib/mysql are found with root ownership ; that's breaks mysql service to start ;

as Workaround may change manually the owner of /var/lib/mysql content
chown -R mysql:mysql /var/lib/mysql

then service mysq is started successfully

]# service mysql start
Starting mysql (via systemctl): [ OK ]

Comment by Zdravelina Sokolovska (Inactive) [ 2018-01-19 ]

that problem appears also when use mariabackup tool with the option --rsync (default is cp) ; the ownerchip mysql:mysql of backuped and restored files and directories is not preserved

Comment by Vladislav Vaintroub [ 2018-01-23 ]

wlad wrote:

This is expected behavior.
if you copy files (or copy-back), they are owned by the user who did the copy.
if service runs under different user, file file ownership needs manual fixing, just like you describe under "workaround", chown -R mysql:mysql

winstone wrote:

the toll does the data restoring and files and dirs should be owned by mariabackup toll .
after mariadb-server installation from user the files and dirs in /var/lib/mysql are owned by mysql
service mysql is started successfully and the same is expected after backup/restote date .

Comment by Vladislav Vaintroub [ 2018-01-23 ]

winstone, you can add your own comment, rather than editing mine
No, file permissions are not given to the tools, they are given to users. If you run tool as user mysql, you should get correct permissions.

Comment by Vladislav Vaintroub [ 2018-01-23 ]

Let me add one more comment on file permissions. Our tools (those are written in C/C++), do not mess up with permissions. We do not know under which account the server will run, whether it is installed as service etc, whether user or group mysql exist. therefore permission fixups are currently left to the end user, or tools that end user will own.

Comment by Zdravelina Sokolovska (Inactive) [ 2018-01-23 ]

---the request is actually exactly about the tool to backup/restote date preserving ownerchip if the datadir;
---In addition referring to the user guide – user is prompted to do or not to do the opposite
https://mariadb.com/kb/en/library/full-backup-and-restore-with-mariadb-backup/
In sections Backing up Database/Restoring from Backups is not mentioned anything about ---- e.g chown -R mysql:mysql or sth else is not recommended
But in section Restoring with Other Tools :rsync -avrP /var/mariadb/backup /var/lib/mysql/ , chown -R mysql:mysql is recommended in case that rsync with -a option -e.g "archive" mode
would have preserved mysql owner and group permissions \ – eg. chown -R mysql:mysql is not needed in that section

Comment by Zdravelina Sokolovska (Inactive) [ 2018-01-23 ]

current behavior :User has to check the ownership of the datadir after restoring with maribackup tool if mysql service fail to start and overwrite ownership it manually ;

Generated at Thu Feb 08 08:17:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.