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).

  • why not setup replication and when the replica catches up - cut it off?
    – akuzminsky
    May 16 '20 at 18:09
  • @akuzminsky I am a pretty new to the DBA world. Replication is my plan in the future but seemed like a lot of overhead to setup when I just needed to move servers for the time being. However, if you have a good tutorial for replication please link it
    – ParoX
    May 16 '20 at 20:38
  • It might go faster if you write across the network instead of hitting the disk you are reading from.
    – Rick James
    Jun 2 '20 at 23:26

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

  • wont rsync the ib_logfile* and ibdata* mess up the tablespace of my other innodb database and files? The two servers have differing databases but I only want to transfer over 1 common database
    – ParoX
    May 16 '20 at 16:30
  • Ah, that changes things. I will update the answer. May 16 '20 at 16:42
  • I have little doubt your suggestion would of worked (so I will accept it) but I ended up just using xtrabackup (wanted to learn it anyways) which I was able to do a partial backup and restore to get just the table I needed.
    – ParoX
    May 16 '20 at 20:36
  • for those who's reading and that might suggest using xtrabackup. there is an existing bug with partial backups at least for PS MySQL 8.0.x and PXB 8.0.x. (eg jira.percona.com/browse/PXB-2132). however partial backups in 5.7 and older versions still work. May 16 '20 at 22:16
  • To export a single table, see "transportable tablespaces"; make sure the page you find is up to version 8.0 (either Percona or not).
    – Rick James
    May 20 '20 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.