I want to vent a little and hopefully help someone who might be in a similar situation.
We had an internal inventory database running on a MySQL 5.6 server. The hardware was getting old, so the management decided to migrate the software to a managed instance in the cloud. It was a relatively straightforward procedure: I dumped the database and imported the .sql file to the new server. It had worked for me dozens of times before, and I was confident that it would work again.
It would not.
The first thing I did was trigger mysqldump, but the script killed itself after working for about 40 minutes. It did not show any errors; it simply dumped an incomplete .sql file. It took me an hour to realize that the second I tried to import this file, it would only import 60 percent of the tables in the database before throwing an error.
The next step was to try and export the database again, table by table, to see which tables were faulty. Three tables had corrupt InnoDB pages and could not be dumped at all, regardless of the flags I used.
My next idea was to try and copy the database files directly, but the versions of MySQL were different, and the files were incompatible. The next 40 minutes were spent double-checking everything while my colleagues asked me if I had any success yet.
I was humiliated when I finally admitted that the only way to migrate the DB was to use a commercial utility that could somehow read the corrupt .sql file and migrate the data anyway. Kernel MySQL Migration tool, recommended by one of my colleagues, did a pretty good job. It let me preview the database objects that could be read before spending money on a full migration. The tools actually worked, and the entire process took about 20 minutes. All database objects, including the tables that mysqldump could not dump, were migrated successfully. I was surprised by how well the utility worked and even considered buying it.
This story is not to convince you that you need to pay for a MySQL migration tool. If your database is not corrupt, feel free to use a command line utility. In my case, the problem was that my source DB was old and faulty. There were errors, and important objects such as tables could not be read at all.
Still, I want to ask our community members if they have encountered similar issues and what methods they used to migrate databases with corrupt objects.
We had an internal inventory database running on a MySQL 5.6 server. The hardware was getting old, so the management decided to migrate the software to a managed instance in the cloud. It was a relatively straightforward procedure: I dumped the database and imported the .sql file to the new server. It had worked for me dozens of times before, and I was confident that it would work again.
It would not.
The first thing I did was trigger mysqldump, but the script killed itself after working for about 40 minutes. It did not show any errors; it simply dumped an incomplete .sql file. It took me an hour to realize that the second I tried to import this file, it would only import 60 percent of the tables in the database before throwing an error.
The next step was to try and export the database again, table by table, to see which tables were faulty. Three tables had corrupt InnoDB pages and could not be dumped at all, regardless of the flags I used.
My next idea was to try and copy the database files directly, but the versions of MySQL were different, and the files were incompatible. The next 40 minutes were spent double-checking everything while my colleagues asked me if I had any success yet.
I was humiliated when I finally admitted that the only way to migrate the DB was to use a commercial utility that could somehow read the corrupt .sql file and migrate the data anyway. Kernel MySQL Migration tool, recommended by one of my colleagues, did a pretty good job. It let me preview the database objects that could be read before spending money on a full migration. The tools actually worked, and the entire process took about 20 minutes. All database objects, including the tables that mysqldump could not dump, were migrated successfully. I was surprised by how well the utility worked and even considered buying it.
This story is not to convince you that you need to pay for a MySQL migration tool. If your database is not corrupt, feel free to use a command line utility. In my case, the problem was that my source DB was old and faulty. There were errors, and important objects such as tables could not be read at all.
Still, I want to ask our community members if they have encountered similar issues and what methods they used to migrate databases with corrupt objects.