Re: ibdata1 keeps growing forever....
Ok, Fanos I can help you.
With innodb storage engine, all data (from all tables and all databases) is located by default in ibdata1 so the first thing is.
Why is innodb growing?
To answer your question you have to look the data, so run the following script to see how are those 40 GB distributed and then you can take actions to stop it.
# Show each table size in GB sort by table sizes
SELECT TABLE_SCHEMA,TABLE_NAME,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'TABLEsizeGB' FROM information_schema.tables
GROUP BY TABLE_SCHEMA,TABLE_NAME
ORDER BY TABLEsizeGB DESC;
Also can run the following script to see the size of every DB.
# Show each DB size in GB sort by DB sizes
SELECT TABLE_SCHEMA,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'DBsizeGB' FROM information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY DBsizeGB DESC;
If you find where is your data booster you can stop it and delete unused data.
The second thing is how to shrink ibdata1.
If you delete some data to recover space your ibdata file will remain the same size so the only way to reclaim that space is to set innodb_file_per_table option.
To do so:
STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)
STEP 02) Drop all databases (except mysql, information_schema and performance_schema schemas)
STEP 03) Shutdown mysql
STEP 04) Add the following lines to /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1 (see update below before deleting!)
At this point, there should only be the mysql schema in /var/lib/mysql
STEP 06) Restart mysql
This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1
STEP 07) Reload SQLData.sql into mysql
ibdata1 shoul remain just 10 MB.
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table
mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
___________________________
Ing. Jesús Alfredo Uzcanga
Twitter: @JesusUzcanga
We learn the 20% of what we HEAR,
the 50% of what we SEE,
the 80% of what we DO and
the 95% of what we TEACH.
____________________________________________________________
https://www.linkedin.com/in/jauzcanga/