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= --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= --apply-log /mnt/statsbackup/stats2//
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?