This guide details how to perform partial restores of specific MySQL databases or even individual tables using innobackupex. This method allows for online import without requiring a database restart.

Prerequisites:

• The restore process can utilize files from a full backup or specifically backed-up databases.
• Restoring a single database involves discarding and then importing all its associated tablespaces.
• To restore a single database, the target MySQL instance must have the database and its table structures pre-created. Otherwise, a full database backup and restore is necessary.

1. Performing a Backup with innobackupex

To backup a specific database:

innobackupex --defaults-file=/etc/my.cnf --user=root --password='your_password' --host='your_host_ip' --databases=canal_manager /data/backup/canal_manager

To back up and compress multiple databases:

innobackupex --defaults-file=/etc/my.cnf --user=root --password='your_password' --host='your_host_ip' --databases="db1 db2" --compress /data/backup/multiple_dbs

2. Applying Transaction Logs

Note: The copy-back or move-back operations are not required at this stage for partial restores.

First, decompress the backup if it was compressed:

innobackupex --decompress /path/to/backup/directory

Then, apply the logs to the decompressed backup directory:

innobackupex --apply-log /path/to/decompressed/backup

3. Exporting Table Structures from the Source Database

Use mysqldump to extract the schema, including triggers, routines, and events, but excluding the data:

mysqldump --no-data -uroot -p'your_password' -h source_host_ip --databases database_name --triggers --routines --events > /path/to/schema_export.sql

4. Creating the Database and Table Structures on the Target

On the target MySQL server, execute the SQL file to create the database and all its tables. The database name can be altered during this step, providing a method for renaming databases during a restore.

SOURCE /path/to/schema_export.sql;

5. Discarding Tablespaces for Target Tables

To prepare for importing tablespace data, you need to detach the existing (empty) tablespaces from the tables on the target server. This can be done by generating and executing SQL commands.

Generate SQL to discard tablespaces for a specific database:

SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' DISCARD TABLESPACE;')
FROM information_schema.tables
WHERE table_schema = 'target_database_name'
INTO OUTFILE '/tmp/discard_tablespaces.sql';

Execute the generated SQL file:

SOURCE /tmp/discard_tablespaces.sql;

If you encounter errors related to secure-file-priv, ensure it's configured appropriately (e.g., secure-file-priv=/tmp in your MySQL configuration) and restart the MySQL service.

Example manual SQL for discarding tablespaces:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE target_database_name.table1 DISCARD TABLESPACE;
ALTER TABLE target_database_name.table2 DISCARD TABLESPACE;
-- ... other tables
SET FOREIGN_KEY_CHECKS=1;

6. Moving Tablespace and Data Files

Copy the relevant tablespace files (.ibd files) and any other necessary data files from the backed-up and log-applied directory to the target MySQL data directory for the specific database.

cp /path/to/applied/log/backup/database_name/* /var/lib/mysql/target_database_name/

Ensure the file ownership and permissions are correct for the MySQL user.

7. Importing Tablespaces and Verifying Data

Now, attach the copied tablespace files back to their respective tables on the target server.

Generate SQL to import tablespaces:

SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' IMPORT TABLESPACE;')
FROM information_schema.tables
WHERE table_schema = 'target_database_name' AND table_type = 'BASE TABLE'
INTO OUTFILE '/tmp/import_tablespaces.sql';

Execute the generated SQL file:

SOURCE /tmp/import_tablespaces.sql;

Example manual SQL for importing tablespaces:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE target_database_name.table1 IMPORT TABLESPACE;
ALTER TABLE target_database_name.table2 IMPORT TABLESPACE;
-- ... other tables
SET FOREIGN_KEY_CHECKS=1;

8. Confirming Data Integrity

After the import, verify that the data has been correctly restored by querying the tables or performing application-level checks.