MySQL won't start after restore

MySQL & MariaDB Percona XtraBackup
1

Using xtrabackup 8.0.27-19.

When I run the following commands, MySQL (version 8.0.28) fails to start. While I only need a backup of “mydb”, I’m including the others because xtrabackup wants /var/lib/mysql to be empty before doing a restore and if I’m not mistaken, all databases will be gone when I empty that dir?

Error.log out put is included. I’m guessing the problem is related to the error log entry “–initialize specified but the data directory has files in it. Aborting” and that the data directory is unusable. It has files in it because of the restore … so I’m not sure how to work around this?

xtrabackup --backup --target-dir=/home/ubuntu/xtrabkp --databases=‘mydb mysql sys performance_schema’ -u myuser -p --no-server-version-check

xtrabackup --prepare --target-dir=/home/ubuntu/xtrabkp --databases=‘mydb mysql sys performance_schema’ -u myuser -p --no-server-version-check

/etc/init.d/mysql stop

rm -rf /var/lib/mysql/*

xtrabackup --copy-back --target-dir=/home/ubuntu/xtrabkp

chown -R mysql.mysql /var/lib/mysql

/etc/init.d/mysql start > failed!

2022-04-06T13:13:01.654779Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28) initializing of server in progress as process 312240
2022-04-06T13:13:01.656669Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2022-04-06T13:13:01.656698Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
2022-04-06T13:13:01.656776Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-06T13:13:01.657029Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.
2022-04-06T13:13:02.078755Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28) starting as process 312246
2022-04-06T13:13:02.093155Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-06T13:13:02.368578Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-06T13:13:02.371143Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement ‘CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci’ failed with error code = 1049, error message = ‘System schema directory does not exist.’.
2022-04-06T13:13:02.371444Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-06T13:13:02.371665Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-06T13:13:02.894743Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.
2022-04-06T13:13:03.288161Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28) starting as process 312276
2022-04-06T13:13:03.298126Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-06T13:13:03.542432Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-06T13:13:03.543969Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement ‘CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci’ failed with error code = 1049, error message = ‘System schema directory does not exist.’.
2022-04-06T13:13:03.544214Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-06T13:13:03.544451Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-06T13:13:04.066367Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.

1 Like
2

Hello, backing up 8.0.28 with xtrabackup 8.0.27 might not work properly as xtrabackup version has to be equal or newer. You need to wait til compatible xtrabackup version is released for a supported solution.
That being said, to restore a partial backup try to first start with the empty datadir and --initialize option and only after that do the copy back. The process is described in more detail here:
https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/partial_backups.html

1 Like
3

I would test your backup process first by taking an entire server backup (ie: remove --databases) and restore that. if that works, only backup your specific database (all system related databases are included automatically IIRC).

1 Like