Restoring Particular database using Xtrabackup

MySQL & MariaDB Percona XtraBackup
,
1

I have backed up a particular database using the command

xtrabackup --user=<username> --password=<password> --backup --databases=<database_name> --target-dir=<target-dir-path>

Now to restore, I’ve followed the following steps

  • Stop MySQL Server systemctl stop mysqld.service
  • Delete if any content in /var/lib/mysql before restore.
  • prepare the mysql backup taken , if not prepared.
  • Run the restore command, xtrabackup --copy-back --target-dir=<path-to-backed-up-data>
  • Change the permission of the directory /var/lib/mysql to mysql using the command chown -R mysql:mysql ( Need root permission at system level ),
  • Start the mysql server systemctl start mysqld.service

Now when I start back the MySQL server, I get an error.

My analysis is that the databases flag passed during the backup command only passed the database_name directory and not the MySQL and sys databases which store the meta data.
Due to the absence of these databases, my SQL server is not bought back up correctly.

Is this analysis correct? Are there any other steps to restore a particular database?

Thank you.

2

Hi, thanks for the details, and your assumption is correct.
Let me quote from the doc:

The –databases option accepts a space-separated list of the databases and tables to backup in the databasename[.tablename] format. In addition to this list, make sure to specify the mysql, sys, and performance_schema databases. These databases are required when restoring the databases using xtrabackup –copy-back.

$ xtrabackup --databases='mysql sys performance_schema test ...'

Best.

1 Like