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: a curse on OS sierra and MySQL?
Well, the command sudo /usr/local/mysql/support-files/mysql.server stop generates the error message: ERROR! MySQL server PID file could not be found! On the other hand, sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist stops the server and sudo /usr/local/mysql/support-files/mysql.server start starts it On 12/3/16, 4:40 PM, "Peter Brawley" wrote: On 12/3/2016 13:58, Martin Mueller wrote: > I was able to install a version of MySQL 5.6 on OS Sierra. It appears that the “launchdaemon’ method works while the mysql.server start/stop method does not work. In retrospect I should have seen that, but I also think that the official documentation could and should be more explicit about what is a significant change in Apple’s start/stop routines. If you mean that seriously, it needs to be more specific. PB - > > On 12/3/16, 12:43 PM, "Peter Brawley" wrote: > > On 12/2/2016 17:58, Martin Mueller wrote: > > Alas, running the stop and start commands under sudo makes zero difference. > > ?! The cited page recommends more than sudo starts and stops, eg ... > > |unset TMPDIR mysql_install_db | > > Did you try that? Did you check the pid setting in my.cnf, eg > pid-file=/var/run/mysqld/mysqld.pid? I believe you need to ensure that > the pid file specified in my.cnf exists and that the mysql daemon owns > it ... > > mkdir /var/run/mysqld > touch /var/run/mysqld/mysqld.pid > chown -R mysql:mysql /var/run/mysqld > > Also see > https://urldefense.proofpoint.com/v2/url?u=http-3A__superuser.com_questions_159486_how-2Dto-2Dkill-2Dprocess-2Din-2Dmac-2Dos-2Dx-2Dand-2Dnot-2Dhave-2Dit-2Drestart-2Don-2Dits-2Down&d=CwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=Rp61bfD4ngoSU50qebNy37Nmv34OSqdiU4Sigj8b9zI&s=RDN0din-b9O7hEkNJOKe1CbYe_5MipeeuN2oeOMsWfI&e= > > > This is a very frustrating problem, and I hope somebody in the MySQl documentation department will take a look at it. It’s cleary a problem that has been around for years because the Web is full of complaints and tips. But there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. And there is nothing in the MySQL documentation that draws attention to the probem. > > > > In my case, I’m double frustrated because some months ago my MySQL application broke around this problem, and then a couple of weeks ago it cured itself when I somewhat arbitrarily picked up an earlier version of my installation from Time Machine. > That suggests the problem arose from a change in your app, or a setting > change that occurred in a MySQL upgrade. To show that this is a common > problem that MySQL docs ought to address, you'll need to identify the > setting that's gone awry. > > PB > > > -- MySQL General Mailing List For list archives: https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql&d=CwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=I5PKsknY5e1wjZZG11zhg1tGbZKrqgs0FExanPNtMkk&s=40lzUebvOmuxTUq-dnXHbwXgaFEyIyYqf93pjPGQibU&e= To unsubscribe: https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql&d=CwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=I5PKsknY5e1wjZZG11zhg1tGbZKrqgs0FExanPNtMkk&s=40lzUebvOmuxTUq-dnXHbwXgaFEyIyYqf93pjPGQibU&e= -- 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?
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: a curse on OS sierra and MySQL?
On 12/3/2016 13:58, Martin Mueller wrote: I was able to install a version of MySQL 5.6 on OS Sierra. It appears that the “launchdaemon’ method works while the mysql.server start/stop method does not work. In retrospect I should have seen that, but I also think that the official documentation could and should be more explicit about what is a significant change in Apple’s start/stop routines. If you mean that seriously, it needs to be more specific. PB - On 12/3/16, 12:43 PM, "Peter Brawley" wrote: On 12/2/2016 17:58, Martin Mueller wrote: > Alas, running the stop and start commands under sudo makes zero difference. ?! The cited page recommends more than sudo starts and stops, eg ... |unset TMPDIR mysql_install_db | Did you try that? Did you check the pid setting in my.cnf, eg pid-file=/var/run/mysqld/mysqld.pid? I believe you need to ensure that the pid file specified in my.cnf exists and that the mysql daemon owns it ... mkdir /var/run/mysqld touch /var/run/mysqld/mysqld.pid chown -R mysql:mysql /var/run/mysqld Also see https://urldefense.proofpoint.com/v2/url?u=http-3A__superuser.com_questions_159486_how-2Dto-2Dkill-2Dprocess-2Din-2Dmac-2Dos-2Dx-2Dand-2Dnot-2Dhave-2Dit-2Drestart-2Don-2Dits-2Down&d=CwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=Rp61bfD4ngoSU50qebNy37Nmv34OSqdiU4Sigj8b9zI&s=RDN0din-b9O7hEkNJOKe1CbYe_5MipeeuN2oeOMsWfI&e= > This is a very frustrating problem, and I hope somebody in the MySQl documentation department will take a look at it. It’s cleary a problem that has been around for years because the Web is full of complaints and tips. But there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. And there is nothing in the MySQL documentation that draws attention to the probem. > > In my case, I’m double frustrated because some months ago my MySQL application broke around this problem, and then a couple of weeks ago it cured itself when I somewhat arbitrarily picked up an earlier version of my installation from Time Machine. That suggests the problem arose from a change in your app, or a setting change that occurred in a MySQL upgrade. To show that this is a common problem that MySQL docs ought to address, you'll need to identify the setting that's gone awry. PB -- 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
Re: a curse on OS sierra and MySQL?
On 12/2/2016 17:58, Martin Mueller wrote: Alas, running the stop and start commands under sudo makes zero difference. ?! The cited page recommends more than sudo starts and stops, eg ... |unset TMPDIR mysql_install_db | Did you try that? Did you check the pid setting in my.cnf, eg pid-file=/var/run/mysqld/mysqld.pid? I believe you need to ensure that the pid file specified in my.cnf exists and that the mysql daemon owns it ... mkdir /var/run/mysqld touch /var/run/mysqld/mysqld.pid chown -R mysql:mysql /var/run/mysqld Also see http://superuser.com/questions/159486/how-to-kill-process-in-mac-os-x-and-not-have-it-restart-on-its-own This is a very frustrating problem, and I hope somebody in the MySQl documentation department will take a look at it. It’s cleary a problem that has been around for years because the Web is full of complaints and tips. But there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. And there is nothing in the MySQL documentation that draws attention to the probem. In my case, I’m double frustrated because some months ago my MySQL application broke around this problem, and then a couple of weeks ago it cured itself when I somewhat arbitrarily picked up an earlier version of my installation from Time Machine. That suggests the problem arose from a change in your app, or a setting change that occurred in a MySQL upgrade. To show that this is a common problem that MySQL docs ought to address, you'll need to identify the setting that's gone awry. PB
Re: a curse on OS sierra and MySQL?
2016/12/02 18:58 ... Martin Mueller: Alas, running the stop and start commands under sudo makes zero difference. This is a very frustrating problem, and I hope somebody in the MySQl documentation department will take a look at it. It’s cleary a problem that has been around for years because the Web is full of complaints and tips. But there doesn’t seem to be any convergence a bout a diagnosis or a likely cure. And there is nothing in the MySQL documentation that draws attention to the probem. In my case, I’m double frustrated because some months ago my MySQL application broke around this problem, and then a couple of weeks ago it cured itself when I somewhat arbitrarily picked up an earlier version of my installation from Time Machine. But after a couple of weeks it suddenly failed in the same way although I had done nothing on the system administration end. I’m not a programmer, but I’ve worked with lots of programs, and MySQL, which is wonderful when it works, is absolutely the worst in the obscure and poorly documented steps that take you from the code to an installation that works. At least that is the case with OS 10. Just now I looked at the Peter-recommended page, and toward the end saw references (mcsharry) to sudden change of owner & permission--it sounds somewhat like your problem. Looked into that? (maybe a MySQL bug is involved.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
