# Xtrabackup script for daily backup is not working for JIRA database backup mysql 5.7 **URL:** \ **Category:** MySQL & MariaDB\ **Tags:** mysql, percona\ **Created:** [May 3, 2022, 7:07pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530 "2022-05-03T19:07:46Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 3, 2022, 7:07pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/1 "2022-05-03T19:07:46Z")
Hi, I used [How To Configure MySQL Backups with Percona XtraBackup on Ubuntu 16.04 | DigitalOcean](https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-backups-with-percona-xtrabackup-on-ubuntu-16-04) to setup daily backup for database. It works well for Confluence and Bitbucket witj same mysql 5.7 version but not for JIRA. For JIRA, while running backup script. I face issue mentioned in the below link. I did resolutions from [Using Percona XtraBackup on a MySQL Instance with a Large Number of Tables - Percona Database Performance Blog](https://www.percona.com/blog/2016/12/28/using-percona-xtrabackup-mysql-instance-large-number-tables/) Still same issue. Please suggest. Regards, Nidhi ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 3, 2022, 7:56pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/2 "2022-05-03T19:56:39Z")
Hi @nsharma, What is the exact error message you are getting? Can you also show how you are executing xtrabackup? Also, please run `SELECT COUNT(*) FROM information_schema.INNODB_TABLES` so we can see how many tables are on your system. ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 3, 2022, 8:53pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/3 "2022-05-03T20:53:43Z")
This is the error root@cakel-ev-atjds1:/home/t1\_nsharma@sierrawireless.local# vi /srv/backup/daily/Tue/backup-progress.log xtrabackup: recognized server arguments: --parallel=2 xtrabackup: recognized client arguments: --user=backup --password=\* --backup=1 --extra-lsndir=/srv/backup/daily/Tue --compress --stream=xbstream --encrypt=AES256 --encrypt-key-file=/srv/backup/daily/encryption\_key --compress-threads=2 --encrypt-threads=2 --slave-info=1 --target-dir=/srv/backup/daily/Tue 220503 13:49:51 version\_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql\_read\_default\_group=xtrabackup’ as ‘backup’ (using password: YES). 220503 13:49:51 version\_check Connected to MySQL server 220503 13:49:51 version\_check Executing a version check against the server… 220503 13:49:52 version\_check Done. 220503 13:49:52 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: not set Using server version 5.7.37-0ubuntu0.18.04.1 xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86\_64) (revision id: 90fe9d0) xtrabackup: uses posix\_fadvise(). xtrabackup: cd to /srv/mysql/ xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb\_data\_home\_dir = . xtrabackup: innodb\_data\_file\_path = ibdata1:12M:autoextend xtrabackup: innodb\_log\_group\_home\_dir = ./ xtrabackup: innodb\_log\_files\_in\_group = 2 xtrabackup: innodb\_log\_file\_size = 268435456 InnoDB: Number of pools: 1 220503 13:50:01 \>\> log scanned up to (130292127954) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 4793 for eazybi\_jiramb4\_dwh\_159/jira\_issues\_measures, old maximum was 0 220503 13:50:02 \>\> log scanned up to (130292128343) 220503 13:50:03 \>\> log scanned up to (130292130711) 220503 13:50:04 \>\> log scanned up to (130292130841) InnoDB: Operating system error number 24 in a file operation. InnoDB: Error number 24 means ‘Too many open files’ InnoDB: Some operating system error numbers are described at [http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html](http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html) InnoDB: File ./eazybi\_jiramb4\_dwh\_151/jira\_customfield\_10610.ibd: ‘open’ returned OS error 124. Cannot continue operation InnoDB: Cannot continue operation. There are databases for eazybi plugin as well as JIRA database. executing like this sudo -u backup backup-mysql.sh mysql\> SELECT COUNT(_) FROM INNODB\_SYS\_TABLES; ±---------+ | COUNT(_) | ±---------+ | 4929 | ±---------+ 1 row in set (0.24 sec) mysql\> ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 3, 2022, 8:57pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/4 "2022-05-03T20:57:44Z")
`xtrabackup --open-files-limit=10240` ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 3, 2022, 9:03pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/5 "2022-05-03T21:03:20Z")
I had mentioned limit in my.cnf file and restarted mysql, it did not resolve the issue, Is it the correct way? [mysqld] bind-address = 0.0.0.0 transaction-isolation = READ-COMMITTED innodb\_file\_per\_table max\_allowed\_packet=256M innodb\_large\_prefix=ON innodb\_log\_file\_size = 256M innodb\_file\_format=Barracuda innodb\_buffer\_pool\_size=8G innodb\_buffer\_pool\_instances=8 query\_cache\_type=1 query\_cache\_size=536870912 max\_connections=200 character\_set\_server=utf8mb4 [xtrabackup] open-files-limit=2000000 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 3, 2022, 9:16pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/6 "2022-05-03T21:16:24Z")
I don’t see that parameter in the log above, so it doesn’t look like it accepted it. Try the flag. ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 4, 2022, 1:51pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/7 "2022-05-04T13:51:48Z")
Hi Matt, How can I add the flag? ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 4, 2022, 7:09pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/8 "2022-05-04T19:09:34Z")
works when I add ulimit -n 2000000 in the command line but not with my.cnf ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 4, 2022, 7:41pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/9 "2022-05-04T19:41:28Z")
I tried to add like this also in my cnf file [mysqld] bind-address = 0.0.0.0 transaction-isolation = READ-COMMITTED innodb\_file\_per\_table max\_allowed\_packet=256M innodb\_large\_prefix=ON innodb\_log\_file\_size = 256M innodb\_file\_format=Barracuda innodb\_buffer\_pool\_size=8G innodb\_buffer\_pool\_instances=8 query\_cache\_type=1 query\_cache\_size=536870912 max\_connections=200 character\_set\_server=utf8mb4 open\_files\_limit=10240 ~ Still does not work. I would require to setup this backup to be generated daily for mysql database, how can I add this ulimit -n 2000000 as persistent change so that we dont need to add it everytime from console. Solution said to add it in my.cnf but it does not work or add in /etc/security/limits.conf: Both solutions dont work. please suggest. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 4, 2022, 8:08pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/10 "2022-05-04T20:08:14Z")
I explained above how to add the flag to xtrabackup. To persist the ulimit, check out this article: > **[How to Set ulimit Value Permanently](https://linuxhint.com/permanently_set_ulimit_value/)** > > In Linux, ulimit is a built-in tool to manage resource allocation at global, group, and user levels. The ulimit tool offers a powerful way of managing resources. Whatever you do, make sure that the limit you’re about to implement is entered... You need to make sure you add the ulimit modifier to the correct user that is taking the backups. You then need to logout/login for the change to take affect. ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 4, 2022, 9:38pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/11 "2022-05-04T21:38:14Z")
Hi, Did that same way ```auto * soft nofile 4096 * hard nofile 4096 ``` also mysql soft nofile 20000 Also I tried setting opne file limit for mysql mysql\> SHOW VARIABLES LIKE ‘open\_files\_limit’; ±-----------------±-------+ | Variable\_name | Value | ±-----------------±-------+ | open\_files\_limit | 102400 | ±-----------------±-------+ 1 row in set (0.00 sec) still it fails it is only working when from prompt I set ulimit -n 102400 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 4, 2022, 9:44pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/12 "2022-05-04T21:44:07Z")
When you set ulimit -n 102400, which user are you? Are you `mysql` OS user? or are you `root` OS user? Please understand that this backup is NOT mysql. This backup is xtrabackup which runs OUTSIDE of mysql and therefore is not bound by the OS settings of the OS mysql user. ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 5, 2022, 1:01am UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/13 "2022-05-05T01:01:46Z")
While setting this ulimit, I am root user. Okay so that means it wont work with the changes in the my.cnf file which is configuration file for mysql. IN that case where would that need to be mentioned so that change is permanent. I did not face the issue with file limit in other apps while creating xtrabackup backuo job and running it. For xtrabackup, it should accept the changes in limits.conf file in security as mentioned in the documents, but it does not work. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 5, 2022, 2:06am UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/14 "2022-05-05T02:06:54Z")
Did you log out/log in after making the change in security-limits? Before you try xtrabackup, check that the setting applied by using `ulimit -n`. If you see that it hasn’t changed, then you need to do some research on your OS and figure out why this setting is not taking affect. ---
**Author:** ![nsharma](https://avatars.discourse-cdn.com/v4/letter/n/a4c791/32.png) [@nsharma](https://forums.percona.com/u/nsharma)\ **Post date:** [May 5, 2022, 3:57pm UTC](https://forums.percona.com/t/xtrabackup-script-for-daily-backup-is-not-working-for-jira-database-backup-mysql-5-7/15530/15 "2022-05-05T15:57:38Z")
yes did that, id not work. let me check OS thanks.