I am having issues taking reliable backups using innobackupex. The database is good sized at ~2.2 TB using the following command for backup:
innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=USERNAME --password=PASSWORD --parallel=4 --compress --compress-threads=2 --no-timestamp --slave-info --safe-slave-backup --tmpdir=/tmp /mysqlbackups
The backup completes in just about 3 hours and 20 minutes. I have made multiple attempts at restoring this backup to another server, however it crashes miserably. Below is a snapshot of the errors on the new server.
160623 17:39:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160623 17:39:32 [Note] Plugin ‘FEDERATED’ is disabled.
160623 17:39:32 InnoDB: The InnoDB memory heap is disabled
160623 17:39:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160623 17:39:32 InnoDB: Compressed tables use zlib 1.2.3
160623 17:39:32 InnoDB: Using Linux native AIO
160623 17:39:32 InnoDB: Initializing buffer pool, size = 35.0G
160623 17:39:51 InnoDB: Completed initialization of buffer pool
160623 17:39:53 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1024 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
160623 17:39:57 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 1024 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
160623 17:40:01 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
160623 17:40:10 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
160623 17:40:13 InnoDB: Error: page 7 log sequence number 4551671899677
InnoDB: is in the future! Current system log sequence number 4476102782476.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: [url]http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html[/url]
InnoDB: for more information.
The page log sequence errors continue on for quite some time, hundreds of them. At some point the following:
InnoDB: Last MySQL binlog file position 0 538520993, file name /binlogs/signnow-bin.002470
InnoDB: Cleaning up trx with id 6167655F6E756D62
InnoDB: Cleaning up trx with id 2272657175697265
InnoDB: Cleaning up trx with id 19068005B
InnoDB: Cleaning up trx with id 1905EC85C
The host server is a slave server and is running CentOS 6.5 with Percona 5.5.49 with xtrabackup is 2.3.4. Below is the configuration in my.cnf.
[mysqld]
datadir = /var/lib/mysql
server-id = 102
read_only = 1
performance_schema = on
innodb_file_per_table = 1
innodb_buffer_pool_size = 50G
innodb_additional_mem_pool_size = 32m
innodb_log_file_size = 1024m
innodb_log_buffer_size = 64m
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
read_buffer_size = 1m
sort_buffer_size = 1m
key_buffer_size = 8m
max_connections = 200
max_allowed_packet = 128m
query_cache_size = 1024
query_cache_type = 1
query_cache_strip_comments = 1
tmp-table-size = 256M
max-heap-table-size = 256M
thread-cache-size = 50
table-open-cache = 2048
binlog_format = row
log-bin = /binlogs/signnow-bin
expire_logs_days = 7
relay_log = /binlogs/mysql-slave0-relay-bin
relay_log_index = /binlogs/mysql-slave0-relay-bin.index
auto_increment_offset = 1
auto_increment_increment = 2
long_query_time = 1
slow-query-log = 1
slow_query_log_file = /mnt/mysql/log-slow-queries.log
log_slave_updates = true
[mysql]
max_allowed_packet = 128m
[mysqldump]
max_allowed_packet = 128m
The new server config matches the above except that it’s not a slave, so a different server-id and has less RAM so smaller buffer_pool_size. The database is getting restored with the following:
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --parallel=4 --decompress --no-timestamp --slave-info --safe-slave-backup --tmpdir=/mnt /mnt2/
Interestingly, using --defaults-file or not it will only decompress into the current location and not to /var/lib/mysql. However using rsync I copy the files to their location, though I have also tried mounting the drive at /var/lib/mysql to no avail.