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?
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.
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.
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.
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?
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.