If I do a partial backup my functions are lost!

MySQL & MariaDB Percona XtraBackup
1

I’m following these steps to backup a database only:

To backup the database I do:

innobackupex --user=root --databases='database' /home/user/innobackupex

Preparing the backup:

innobackupex --apply-log --export /home/user/innobackupex

then to copy all the files the the file directory I do:


rsync -avrP /home/user/innobackupex /var/lib/mysql/

MY database got restored but when I try to list my functions they are gone! SHOW FUNCTION STATUS

Now, if I tried to do a full backup this does’t happen.

Any idea why this is happening?

2

For user defined functions, the info resides inside the mysql.func table. You may want to use mysqldump to dump these functions using the --routines option and reload it to the other database or recreate the function on the new database.

3

Thank you for your answer, I’m still confused. Why if I do a full backup I can see the functions and why not when I do a partial backup? Is that a xtrabackup bug then? And the point of using xtrabackup is to not use mysqldump so I don’t see that as a solution.

4

The thing is, when you execute the backup with --databases=“your-database” it will only backup that particular database and would not backup the ‘mysql’ database. You will need to run something like:

innobackupex --user=root --databases="db1 db2 mysql" /path/to/backups

so that the mysql database will also be backed up and during restore functions will also be restored as well.

5

Isn’t that going to backup all the functions for all databases as well? Even for the databases I’m not trying to back up?

6

To test, execute innobackupex --databases="db" /path/to/backup, check the backup folder and you’ll notice that the “mysql” system database will not be included.