0

I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads.

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • I think yes, it can get bigger without any inserts. There could be some temporary table or log tables. For example log tables for your slow queries. But this depends on your database settings. –  Mar 05 '15 at 19:17
  • You might get more useful help if you include more information about your database that might help diagnose the cause, such as your configuration and maybe an excerpt from your logs (if anything looks interesting). –  Mar 05 '15 at 21:34
  • The InnoDB storage engine in particular will increase its metadata files over time without freeing space from them, but it sounds like you are referring to the size of a SQL dump file, and now the table files on disk? – Michael Mar 05 '15 at 21:39
  • 1
    How did you do the backup? mysqldump only dumps data, no logs, no overhead, etc. So, it will not change in size. – Rick James Mar 06 '15 at 00:53

2 Answers2

0

It's possible it could still grow, although it would be slowly if you aren't inserting and you aren't making changes. If it is being changed and you are logging changes with your recovery option, it will still grow the log. You may want to check all the jobs that touch that database as well and switch it to simple recovery if you haven't already done so.

Muab Nhoj
  • 411
  • 2
  • 8
0

Back in August 2014, I answered this question : How to start mysqld with read-only data directory?

I mentioned how you could make the InnoDB Architecture completely read-only.

You can also read MySQL Documentation (Configuring InnoDB for Read-Only Operation)

The only reason ibdata1 would grow would be MVCC (needed to support transaction isolation). I wrote about such uncontrolled growth of ibdata1 in my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

Turning its features removes any need to write anything related to MVCC inside ibdata1.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520