I have a 30GB innoDB-only database that keeps having Lost connection to MySQL server during query when dumping
when running mysqldump -u root -p --compress --max_allowed-packet=1G --net-buffer-length=32704 --single-transaction largedb> /home/backup/largedb.sql.gz
from the source server
I have net read and write timeouts set to 31536000
in my.cnf
, and beyond this I cannot find any other information on why it would be losing connection.
So I decided that I'll just turn off mysql and rsync them. For tables under 1GB this worked out perfectly.
I first created the database and the table struct then ran ALTER TABLE {table} DISCARD TABLESPACE
move the .frm and .ibd files into the database folder and then ran ALTER TABLE {table} IMPORT TABLESPACE
which fixed all the tables except the main one.
The main table is 30GB and when importing the tablespace it says Error in query (1034): Incorrect key file for table 'main'; try to repair it
There is no additional information in error.log
. I googled and it seems to suggest if you get this error but its an innoDB then you might be running out of diskspace in your tmpdir. My tmpdir is /tmp
and I have 400GB free.
ANALYZE on table results in
InnoDB: Tablespace has been discarded for table 'main'
InnoDB: Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Operation failed
CHECK on table results in
db.main: InnoDB: Tablespace has been discarded for table 'main'
db.main: Tablespace has been discarded for table 'main'
db.main: Corrupt
Can anyone help me either get mysqldump
to stop losing connection or how I can repair the innoDB table?
I am about to turn the table into MyISAM and rsync it
I am running Percona Mysql 8 (based off mysql 8).
Best Answer
You cannot rsync a running database, but you know that since you said you stopped it.
I'd you rsync with the mysqld not running (or from a snapshot), you have to rsync the ib_logfile* and ibdata* files from the datadir. Then there is no need to
DISCARD/IMPORT TABLESPACE
either.For a copy without shutting down the database, xtrabackup is probably the most efficient solution.
If you have existing databases on the target, you will need to
mysqldump --no-data dbname
and import on the target in order to re-create the table structure metadata, but you will haveFLUSH TABLE ... FOR EXPORT
in the source before copying across. Then they will be clean and can be imported withALTER TABLE ... IMPORT TABLESPACE