Database seems to be growing too quick

[ingus.vilnis]

You were fundamentally missing the mysqldump command vs mysql. that single transaction is just addition good to have.

[fsousa]

In my previous post I've pasted the wrong command, the right command I insert is: mysqldump --single-transaction -u root -p zabbix > db_zabbix.sql

[ingus.vilnis]

Then you don't need to run it again. Single transaction should have no effect whether you are having some table in the dump or not.

[fsousa]

I don't understand why my ibd files aren't dumped.

[ingus.vilnis]

In case you recover to empty database I think you needed to create the DB schema first (as from the Zabbix installation steps but without data) and then import the backup.

[fsousa]

Thank you Ingus. I will try that

[fsousa]

Hi Ingus,

I didn't any feedback because I've some troubles to put my database in my test environment.

Here the things I've tried:

• dump my database and then recover to empty database (DB schema created): without success, because my ibd files didn't appear
• rsync my zabbix in /var/lib/mysql/ to my other server: without success , when I tried to select any table I get "Empty set"

[ingus.vilnis]

Hi,

I believe you should look up some tutorial how to restore complete DB from backup.

[fsousa]

Hi,

I found the solution for my problem, I've to enable the innodb_file_per_table.

Enable innodb_file_per_table vi /etc/my.cnf

[mysqld]
innodb_file_per_table = 1

Apparently when I imported the dump the ibd files per table aren't created.

I started to import again my database dump and I've the idb files per table.

Thank you.

[ingus.vilnis]

What version of MySQL are you using that this setting is still off by default?

[fsousa]

Hi,

mysql --version
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

[ingus.vilnis]

Ok... at least you got this little issue resolved.

[fsousa]

Yes

I began the import of my dump yesterday and then I will try the partitioning of my tables.

[fsousa]

Hi,

After 2 days partioning my history table I got the following error:

[SOLVED] ERROR 1206 (HY000): The total number of locks exceeds the lock table size

I google it and it seems are related with innodb_buffer_pool_size parameter in my.cnf. But I'm not sure the value I have to put here

[ingus.vilnis]

Hi,

This article will give you a good insight.

https://www.percona.com/blog/2014/11/14/optimizing-mysql-zabbix/