0

I recently had a 8GB ibdata1 file in /var/lib/mysql because one of my databases previously got spammed by 8GB worth of data. I deleted that spammed database, and running the following query in MySQL terminal

SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024  "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

yielded 100MB worth of combined data in my database. However my ibdata1 file was still a hefty 8GB. So then I followed the instructions of the accepted answer here to regenerate my ibdata1 and log files:

How to shrink/purge ibdata1 file in MySQL

This worked.

However, as soon as I reboot my Linux server, many of the databases (but not all) in my MySQL server suddenly have zero data. I repeated the instructions in the accepted answer with and without innodb_file_per_table in the appropriate place in my my.conf file, but it still doesn't prevent the destruction of MySQL databases after a Linux reboot. I notice that my ibdata1 file never exceeds 10MB anymore.

Right now, I've re-imported all my databases, and although things are working, i'm worried about losing all my data the moment the server needs to reboot.

Why is this happening? How do I prevent the destruction of MySQL databases after a Linux server reboot?

Community
  • 1
  • 1
John
  • 32,403
  • 80
  • 251
  • 422
  • Does the schema of the table still limit its size? Also, is 8GB of disk space really worth all this effort? – Origin Jan 18 '15 at 06:55
  • What is the engine you are using? – Burhan Khalid Jan 18 '15 at 06:55
  • the schema of tables do not have a limit to size. The engine is mostly innodb. Some are myisam. – John Jan 18 '15 at 07:04
  • I don't think your database problem is related to the reboot operation. I think it is most probably related to purge operation. Did you delete mysql database during purge? – cool Jan 18 '15 at 14:57

0 Answers0