0

Bit of tough situation. Running MySQL 5.5 on Ubuntu 12.04. Looking to add some functionality to our internal site, I installed Kaltura. In doing so, my existing MySQL databases seemed to lose all their data. The databases were still listed, but with no tables. Of course, the backup software that was supposed to be installed 5 months ago was not, so the most recent backup I have is the copy of the entire /var/lib/mysql folder after figuring out this problem. Based on the size of the ibdata1 file, I believe everything is still there.

I've gone through several recovery recommendations but nothing quite works. Eventually, mysql wouldn't start, so I did a clean install of that and copied over the folder I have, but again, the mysql service won't start. Not really sure where to go from here. (Yes I know, backup, backup, backup). Any help would be greatly appreciated.

Thanks

Edit by @RolandoMySQLDBA 2013-01-11 11:44 EDT

Please run this and paste below this line

Request #1 : ls -l /var/lib/mysql

Request #2 : cat /etc/my.cnf | grep innodb

Request #3 : If you can login to mysql, run show engines;

Response to @RolandoMySQLDBA

Request #1 : ls -l /var/lib/mysql

drwx------ 2 mysql mysql      4096 Jan 11 11:34 asdf
-rw-r--r-- 1 mysql mysql         0 Jan 11 11:34 debian-5.1.flag
-rw-r--r-- 1 mysql mysql         0 Jan 11 11:34 debian-5.5.flag
-rw-r----- 1 mysql mysql  18874368 Jan 11 11:34 ibdata1
-rw-r----- 1 mysql mysql 220200960 Jan 11 11:34 ibdata1.bak
-rw-r--r-- 1 mysql mysql 220200960 Jan 11 11:34 ibdata1.recovery
-rw-r----- 1 mysql mysql   5242880 Jan 11 11:35 ib_logfile0
-rw-r----- 1 mysql mysql   5242880 Jan 11 11:34 ib_logfile0.bak
-rw-r----- 1 mysql mysql   5242880 Jan 11 11:34 ib_logfile1
-rw-r----- 1 mysql mysql   5242880 Jan 11 11:34 ib_logfile1.bak
drwx------ 2 mysql mysql     16384 Jan 11 11:34 kaltura
drwx------ 2 mysql mysql    167936 Jan 11 11:34 kalturadw
drwx------ 2 mysql mysql      4096 Jan 11 11:34 kalturadw_bisources
drwx------ 2 mysql mysql      4096 Jan 11 11:34 kalturadw_ds
drwx------ 2 mysql mysql      4096 Jan 11 11:34 kalturalog
drwx------ 2 mysql mysql      4096 Jan 11 11:34 kaltura_sphinx_log
drwx------ 2 mysql mysql      4096 Jan 11 11:34 mysql
-rw-r----- 1 mysql mysql         6 Jan 11 11:34 mysql_upgrade_info
drwx------ 2 mysql mysql      4096 Jan 11 11:34 performance_schema
drwx------ 2 mysql mysql      4096 Jan 11 11:34 phpmyadmin
-rw-r----- 1 mysql mysql         4 Jan 11 11:34 ptl-cloud.pid
drwx------ 2 mysql mysql      4096 Jan 11 11:34 ptlEquipmentTracker
drwx------ 2 mysql mysql      4096 Jan 11 11:34 ptlTestDB
drwx------ 2 mysql mysql      4096 Jan 11 11:34 ptlTestDBArchive1
drwx------ 2 mysql mysql      4096 Jan 11 11:34 ptltestdbrec
drwx------ 2 mysql mysql      4096 Jan 11 11:34 rawr
drwx------ 2 mysql mysql      4096 Jan 11 11:34 test
drwx------ 2 mysql mysql      4096 Jan 11 11:34 testing
drwx------ 2 mysql mysql      4096 Jan 11 11:34 VirtualStripChart

Request #2 : cat /etc/my.cnf | grep innodb

cat: /etc/my.cnf: No such file or directory

I ran cat /etc/mysql/my.cnf | grep innodb and it ran fine with no feedback

Request #3 : If you can login to mysql, run show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
6
  • Provide, please, full log of startup fail of mysql.
    – dr-evil
    Jan 11, 2013 at 13:08
  • Not to kick you when you're down, but I'm hung up on the "backup software that was supposed to be installed" line; A cronjob consisting of a mysqldump (installed with mysql, unless special effort was taken to exclude it) and an rsync (to get it to another box) (with some logger items for good measure) is literally all you need
    – gWaldo
    Jan 11, 2013 at 13:24
  • /var/log/mysql/mysql.log is empty. What other log could have helpful information?
    – dwpriest
    Jan 11, 2013 at 13:28
  • Oh I'm well aware of that. Lets just say the person who was asked to set that up, said it was running, when it wasn't.
    – dwpriest
    Jan 11, 2013 at 13:31
  • Fair enough. Moving forward, I'd slap a (nagios) check to ensure that the file is a non-zero size, and that the file modify date is not more than 24 hours old. Bonus points if you're a graph/chart/dashboard-happy company and you plot the file age and size.
    – gWaldo
    Jan 11, 2013 at 13:33

1 Answer 1

0

I found the issue. It was a combination of using the wrong ibdata1 file, and then also, i had copied the files into the /var/lib/mysql/mysql folder by mistake. After fixing both those things, everything went back to normal.

What I don't understand is why things broke in the first place? I installed Kaltura, things broke, and as far as I can tell, just ended up copying and pasting the files back into the mysql directory. So it's like I changed nothing.

Hmmmmmm......

But thanks to everyone for their help! The comments on here and the answers to other related questions gave me a much better understanding of InnoDB, backups, and the file structure. Thanks!

-Dan

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .