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

xtrabackup corrupting backup

bradlangbradlang EntrantInactive User Role Beginner
Hi, I'm attempting to use xtrabackup to migrate a 3.1TB mysql database.

I'm following http://www.percona.com/doc/percona-xtrabackup/howtos/setting _up_replication.html

From vm with Ubuntu 10.04 mysql 5.0.95 with file per database to a vm with Ubuntu 10.04 mysql 5.0.96, using xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64)

I run:
sudo screen -S transfer
innobackupex --user=xtrabackup --password=<password> --defaults-file=/etc/mysql/my.cnf /mnt/statsbackup/stats2
(/mnt/statsbackup is NFS to the new server)

Here, it runs without errors, but after all of the data is transferred, it just keeps copying logs and never creates the _checkpoint file, so I have to kill the screen session and xtrabackup_51 manually.

innobackupex --user=xtrabackup --password=<password> --apply-log /mnt/statsbackup/stats2/<date>/

Here, it throws an error that the checkpoint file doesn't exist, but seems to apply the log anyway.

I then copy database directories into place on new server, and since for some reason, it doesn't copy over my .frm files, so scp over them for each database.

I then run
chown -R mysql:mysql /dir/mysql/
sudo find /dir/mysql/ -type d -print0 | xargs -0 sudo chmod 0750
sudo find /dir/mysql/ -type f -print0 | xargs -0 sudo chmod 0660
to make file permissions consistent

Finally,

sudo service mysql start

mysql -u root -p
starts the databases and I can see them all in mysql with

show databases;
and show all of their tables with

show tables in db_1;
but for the 2 largest databases (3.1TB and 100GB), I can't see inside the tables with

use db_1;

select * from `table_1` limit 0 , 10;

Instead throws

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 26
Current database: db_1

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 27
Current database: db_1

ERROR 2006 (HY000): MySQL server has gone away


Googling this points me to believe the databases are corrupted after trying many fixes including increasing the timeout (it was already 28800). Could this have been caused by xtrabackup failing to finish and create the checkpoint file or not copying the .frm files itself? I've attempted this multiple times on multiple ubuntu servers I've built from scratch. Any suggestions or a bug fix for xtrabackup?

Comments

  • bradlangbradlang Entrant Inactive User Role Beginner
    the apply log step throws this:

    innobackupex --user=xtrabackup --password=<password> --apply-log /mnt/statsbackup/stats2/2012-05-15_18-13-14/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona Inc 2009-2012. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    option_ibbackup_binary is autodetect, trying to connect to MySQL
    Connected to MySQL with pid 518
    120517 14:16:10 innobackupex: Connection to database server closed
    Connected successfully
    120517 14:16:10 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='xtrabackup' --unbuffered --
    120517 14:16:10 innobackupex: Connected to database with mysql child process (pid=520)
    120517 14:16:16 innobackupex: Connection to database server closed
    IMPORTANT: Please check that the apply-log run completes successfully.
    At the end of a successful apply-log run innobackupex
    prints "completed OK!".



    120517 14:16:17 innobackupex: Starting ibbackup with command: xtrabackup_51 --prepare --target-dir=/mnt/statsbackup/pkstats2/2012-05-15_18-13-14

    xtrabackup_51 version 2.0.0 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
    xtrabackup: cd to /mnt/statsbackup/pkstats2/2012-05-15_18-13-14
    xtrabackup: Error: cannot open ./xtrabackup_checkpoints
    xtrabackup: error: xtrabackup_read_metadata()
    xtrabackup: This target seems not to have correct metadata...
    xtrabackup: xtrabackup_logfile detected: size=27136081920, start_lsn=(1584 1187562287)
    xtrabackup: Temporary instance for recovery is set as followings.
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 1
    xtrabackup: innodb_log_file_size = 27136081920
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: The InnoDB memory heap is disabled
    120517 14:17:06 InnoDB: Initializing buffer pool, size = 100.0M
    120517 14:17:06 InnoDB: Completed initialization of buffer pool
    InnoDB: Log scan progressed past the checkpoint lsn 1584 1187562287
    120517 14:17:06 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Doing recovery: scanned up to log sequence number 1584 1192804864 (0 %)
    InnoDB: Doing recovery: scanned up to log sequence number 1584 1198047744 (0 %)
    InnoDB: Doing recovery: scanned up to log sequence number 1584 1203290624 (0 %)
    InnoDB: Doing recovery: scanned up to log sequence number 1584 1208533504 (0 %)
    InnoDB: Doing recovery: scanned up to log sequence number 1584 1213776384 (0 %)
    120517 14:17:17 InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed

    ...
    More apply batch of log records to the database...

    Which throws a few errors but seems to continue on just fine...
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.