Transferring innodb table 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&gt; /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).
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 have FLUSH TABLE ... FOR EXPORT in the source before copying across. Then they will be clean and can be imported with ALTER TABLE ... IMPORT TABLESPACE