Xtrabackup script for daily backup is not working for JIRA database backup mysql 5.7

MySQL & MariaDB
,
1

Hi,

I used How To Configure MySQL Backups with Percona XtraBackup on Ubuntu 16.04 | DigitalOcean 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

Still same issue.

Please suggest.
Regards,
Nidhi

2

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.

1 Like
3

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

1 Like
4

xtrabackup --open-files-limit=10240

1 Like
5

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

1 Like
6

I don’t see that parameter in the log above, so it doesn’t look like it accepted it. Try the flag.

1 Like
7

Hi Matt, How can I add the flag?

1 Like
8

works when I add ulimit -n 2000000 in the command line but not with my.cnf

1 Like
9

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.

1 Like
10

I explained above how to add the flag to xtrabackup. To persist the ulimit, check out this article:

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.

1 Like
11

Hi,

Did that same way

* 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

1 Like
12

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.

1 Like
13

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.

1 Like
14

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.

1 Like
15

yes did that, id not work. let me check OS thanks.

1 Like