Re: rescue Inno tables from an abandoned data directory?
Hi Martin, On 4/12/2016 07:23, Martin Mueller wrote: I abandoned a MySQL 5.22 database that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables. I was able to copy the ISAM tables into a new 5.6 version, and they work. Assuming you mean 5.5.22 or 5.6.22, then sometimes you can recover a table without partitions with its own .ibd file (file-per-table) using the transportable tablespace features: 1. Install a fresh copy of 5.6 2. Create the table (using a normal CREATE TABLE statement). If you don't know the table definition use mysqlfrm from MySQL Utilities (https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlfrm.html) 3. Discard the tablespace (ALTER TABLE DISCARD TABLESPACE) 4. Copy the .ibd file (make sure you work with a copy) into the new 5.6 instance (e.g. for the table db1.t1 copy to /db1/t1.ibd) 5. Import the tablespace (ALTER TABLE IMPORT TABLESPACE) There is also an example in https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html The import in step 5. will complain that there is no .cfg file from a proper tablespace copy, but InnoDB will do a best effort to import it, and I don't think I've seen it fail if the tablespace has been valid. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: rescue Inno tables from an abandoned data directory?
Hi Martin On 4/12/2016 07:23, Martin Mueller wrote: I abandoned a MySQL 5.22 database that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables. I was able to copy the ISAM tables into a new 5.6 version, and they work. I understand that INNO tables are different because different tables share a common table space. The MySQL documentation refers to a “cold backup,” where you copy the separate files after a “slow shutdown.” It doesn’t tell you what to do with them after you’ve put them in a “safe place.” The recommendation here is that if your DB is in an inconsistent state, the last thing you want to do is break it even further. The flat OS files copy of the DD simply gives us a fallback should our recover operations on the DD make matters worse, eg via innodb_force_recovery etc In my case, I can reproduce Time machine backups of data directories at varying times. At one point I was able to replace the non-working installation with an earlier installation, but then it failed unpredictably. The steps you took and an associated error log would assist me in assisting you. Also accurate information on the version helps, 5.5.22? 5.1.22 ? as mentioned there is no 5.22. In general this approach can be taken; If the DB successfully started , then your first port of call is to perform this action again with the same DD snapshot and attempt a logical dump of any tables you wish to recover. Without knowing what state the db is in , I would recommend starting with [mysqld] innodb_force_recovery = 1 read_only skip_slave_start https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_skip-slave-start and seeing if you can access / dump the tables in question. If not , iterate through 2,3,4,5,6 ( in order! ) to see if that assists. If this doesn't work, try the latest version of the current tree you are on. ie if 5.5 https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-53.html failing that, hail mary it with latest 5.7, you may get lucky with TT https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_transportable_tablespace GL Are the Inno tables on Time Machine useless, or can I rescue data from them? I’ll be grateful for help -- Regards Ronan McGlue MySQL Support
Re: rescue Inno tables from an abandoned data directory?
On 12/3/2016 14:23, Martin Mueller wrote: I abandoned a MySQL 5.22 database There's been 5.0m 5,1, 5,4 (briefly), 5.5, 5.6 and now 5.7. No 5,.2. that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables. You mean MyISAM? I was able to copy the ISAM tables into a new 5.6 version, and they work. I understand that INNO tables are different because different tables share a common table space. Not just that. The MySQL documentation refers to a “cold backup,” where you copy the separate files after a “slow shutdown.” It doesn’t tell you what to do with them after you’ve put them in a “safe place.” This refers to https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/innodb-backup.html? Copy into identical folders on the new machine, or to corresponding folders named in a new my.cnf. In my case, I can reproduce Time machine backups of data directories at varying times. At one point I was able to replace the non-working installation with an earlier installation, but then it failed unpredictably. Are the Inno tables on Time Machine useless, or can I rescue data from them? Mysqlbackup (Enterprise) and Percona Xtrabackup are known to do reliable hot backups. PB - I’ll be grateful for help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: rescue Inno tables from an abandoned data directory?
Am 03.12.2016 um 21:23 schrieb Martin Mueller: In my case, I can reproduce Time machine backups of data directories at varying times. At one point I was able to replace the non-working installation with an earlier installation, but then it failed unpredictably. Are the Inno tables on Time Machine useless, or can I rescue data from them? backup of files like innodb while the daemons are running is in general problemtaic, not only for time mahine, also for LVM snapsots hence we use for 10 years now replication slaves which are stopped, the datadir rsynced and the slave started again to avoid all that other stuff just for having a relieable and consistent snapshot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
rescue Inno tables from an abandoned data directory?
I abandoned a MySQL 5.22 database that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables. I was able to copy the ISAM tables into a new 5.6 version, and they work. I understand that INNO tables are different because different tables share a common table space. The MySQL documentation refers to a “cold backup,” where you copy the separate files after a “slow shutdown.” It doesn’t tell you what to do with them after you’ve put them in a “safe place.” In my case, I can reproduce Time machine backups of data directories at varying times. At one point I was able to replace the non-working installation with an earlier installation, but then it failed unpredictably. Are the Inno tables on Time Machine useless, or can I rescue data from them? I’ll be grateful for help
