I have been using MySQL for a while. However recently I have made a switch to use percona-server as a replacement in production.
Initial testing efforts indicates that percona-server is performing as expected or better compared to the MySQL version currently in use in production.
As part of the I am also trying to explore xtrabackup to automate backup and recovery. However I am running into errors with xtrabackup and my limited knowledge of internals of MySQL is not helping me to resolve the issue.
Versions I am using.
mysql Ver 8.0.16-7 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release ‘7’, Revision ‘613e312’)
xtrabackup version 8.0.7 based on MySQL server 8.0.16 Linux (x86_64) (revision id: 069e0e6)
I am running Ubuntu 18.04.2 LTS (Kernel: Linux 4.15.0-1044-aws) Architecture: x86-64
When I try to run the stats, it is giving me following error.
# xtrabackup --user=root --password=password --stats
xtrabackup: recognized server arguments: --innodb_flush_log_at_trx_commit=2 --innodb_log_file_size=512M --innodb_log_buffer_size=384M --innodb_flush_method=O_DIRECT --innodb_io_capacity=6000 --innodb_autoextend_increment=256 --innodb_buffer_pool_size=147505M --datadir=/data/db_data/mysql
xtrabackup: recognized client arguments: --user=root --password=* --stats=1
xtrabackup version 8.0.7 based on MySQL server 8.0.16 Linux (x86_64) (revision id: 069e0e6)
xtrabackup: cd to /data/db_data/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912
xtrabackup: Starting 'read-only' InnoDB instance to gather index statistics.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 1504 files.
Disabling background log and ibuf IO write threads.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 250495028680 in the system tablespace does not match the log sequence number 743733964969 in the ib_logfiles!
Can't initiate database recovery, running in read-only-mode.
Plugin initialization aborted with error Read only transaction.
xtrabackup: innodb_init(): Error occured.
Also getting error an error when trying to perform the backup.
......
Error number 24 means 'Too many open files'
Some operating system error numbers are described at http://dev.mysql.com/doc/refman/8.0/en/operating-system-error-codes.html
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
./undo_002 can't be opened in read mode.
UNDO tablespace ./undo_002 must be readable!
xtrabackup: error: xb_load_tablespaces() failed with error code 57
On other instance where I am running a small instance of same application and schema etc…
For stats I am getting following error.
xtrabackup: recognized server arguments: --innodb_flush_log_at_trx_commit=2 --innodb_log_file_size=512M --innodb_log_buffer_size=384M --innodb_flush_method=O_DIRECT --innodb_io_capacity=6000 --innodb_autoextend_increment=256 --innodb_buffer_pool_size=1171M --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stats=1
xtrabackup version 8.0.7 based on MySQL server 8.0.16 Linux (x86_64) (revision id: 069e0e6)
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912
xtrabackup: Starting 'read-only' InnoDB instance to gather index statistics.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 1504 files.
Disabling background log and ibuf IO write threads.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
The log sequence number 19633880 in the system tablespace does not match the log sequence number 54014749 in the ib_logfiles!
Can't initiate database recovery, running in read-only-mode.
Plugin initialization aborted with error Read only transaction.
xtrabackup: innodb_init(): Error occured.
Where as when I try to issue the backup I get following error.
xtrabackup --user=root --password=password --backup --target-dir=$(pwd)
xtrabackup: recognized server arguments: --innodb_flush_log_at_trx_commit=2 --innodb_log_file_size=512M --innodb_log_buffer_size=384M --innodb_flush_method=O_DIRECT --innodb_io_capacity=6000 --innodb_autoextend_increment=256 --innodb_buffer_pool_size=1171M --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/root/backup
xtrabackup version 8.0.7 based on MySQL server 8.0.16 Linux (x86_64) (revision id: 069e0e6)
190903 23:28:32 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
190903 23:28:32 version_check Connected to MySQL server
190903 23:28:32 version_check Executing a version check against the server...
190903 23:28:32 version_check Done.
190903 23:28:32 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.16-7
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1048576
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 = 536870912
xtrabackup: using O_DIRECT
Number of pools: 1
InnoDB: Error: log file ./ib_logfile0 is of different size 50331648 bytes
InnoDB: than specified in the .cnf file 8796093022208 bytes!
Two machines, two separate set of errors.
Any help or pointers will be appreciated.
Keep in mind that there are no issues with running percona server.
Some additional information about schema,
- It is running in non strict mode SQL_MODE = ‘’ (due to old db schema 5.5 we have that needs to be changed and also data and application has to be changed)
- There are tables that use partitions and substations
- Partitions do get truncated periodically.
- Files per tables is on
