Not the answer you need?
Register and ask your own question!

If I do a partial backup my functions are lost!

smashingxsmashingx EntrantCurrent User Role Beginner
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?

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • smashingxsmashingx Entrant Current User Role Beginner
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • smashingxsmashingx Entrant Current User Role Beginner
    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?
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.