How to backup / What to backup

[Thib]

Heyo,

I have a few questions for you.

Yesterday, i had to reboot my zabbix server.
I had the surprise to discover by default, InnoDB files are limited to 10Mb max, thats why i began to become crazy when i saw my 4Gb ibdata file resized to 10MB.

And of course, the crontab doing a mysqldump on the database had time to execute, generating me a 1kb SQL file. Good job!

So first question, i added this to my.cnf in order not to have ibdata files = 10MB
skip-innodb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:20000M
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/

Can you please confirm me thats okay?

Second question, i was able to restore an old ibdata file, that i moved in /var/lib/mysql/ folder (&& chown/chgrp mysql).

But i still got errors coming from files in /var/lib/mysql/zabbix/*
I was told all that files were corrupted etc etc
In my crontab, do i have to include ALL this folder to be backedup?
I can suppose the /var/lib/mysql/zabbix/ folder contains all the database about Zabbix data, what's inside the ibdata file?

Last question, actually i can see my ibdata is 4.2Gb sized.
I've seen when i do a mysqldump, the SQL generated file is ~ 1Gb
After a dump, can i remove the ibdata file and import the .sql generated ?
(will it have impact on /var/lib/mysql/zabbix/ folder/files?)

Thanks !

Thib.

[alixen]

Hi,

Heyo,

I have a few questions for you.

Yesterday, i had to reboot my zabbix server.
I had the surprise to discover by default, InnoDB files are limited to 10Mb max, thats why i began to become crazy when i saw my 4Gb ibdata file resized to 10MB.

I don't know how your mysql was installed but it not a default configuration on systems I know (mainly Debian).
Default value is:
innodb_data_file_path=ibdata1:10M:autoextend
keyword autoextend is important.

And of course, the crontab doing a mysqldump on the database had time to execute, generating me a 1kb SQL file. Good job!

So first question, i added this to my.cnf in order not to have ibdata files = 10MB
skip-innodb
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:20000M
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/

Can you please confirm me thats okay?

skip-innodb is NOT OK !
You are turning off innodb support.
If you don't add autoextend to innodb_data_file_path, your database will never grow beyond 20 GB.
It will be corrupted again.

Second question, i was able to restore an old ibdata file, that i moved in /var/lib/mysql/ folder (&& chown/chgrp mysql).

But i still got errors coming from files in /var/lib/mysql/zabbix/*
I was told all that files were corrupted etc etc
In my crontab, do i have to include ALL this folder to be backedup?
I can suppose the /var/lib/mysql/zabbix/ folder contains all the database about Zabbix data, what's inside the ibdata file?

Files in /var/lib/mysql/zabbix/ define table schemas (*.frm).
Table contents are stored in ibdata1 segments.

Last question, actually i can see my ibdata is 4.2Gb sized.
I've seen when i do a mysqldump, the SQL generated file is ~ 1Gb
After a dump, can i remove the ibdata file and import the .sql generated ?
(will it have impact on /var/lib/mysql/zabbix/ folder/files?)

If you want to remove ibdata, you should:
stop mysql server
remove ibdata
start mysql server (a new ibdata will be created)
import database dump

Thanks !

Thib.

Regards,
Alixen

[Thib]

skip-innodb is NOT OK !
You are turning off innodb support.
If you don't add autoextend to innodb_data_file_path, your database will never grow beyond 20 GB.
It will be corrupted again.

Okay for the skip-innodb, now commented
I volontary did not set "autoextend", because of the size of my DD, 10Go. Sure it wont grow beyond 20Go

Files in /var/lib/mysql/zabbix/ define table schemas (*.frm).
Table contents are stored in ibdata1 segments.

Okay, so do i NEED to backup these files? Or the mysqldump executed on "zabbix" database already dumped it?

If you want to remove ibdata, you should:
stop mysql server
remove ibdata
start mysql server (a new ibdata will be created)
import database dump

It wont affect Zabbix' database?
(is the IBDATA file totally independant of Zabbix?)

Thanks!

[alixen]

Hi,

Okay for the skip-innodb, now commented
I volontary did not set "autoextend", because of the size of my DD, 10Go. Sure it wont grow beyond 20Go

Even in this case, I would set autoextend just in case.
It is better to grab a few MB than lose a 20 GB DB just because mysql cannot extend it.

Okay, so do i NEED to backup these files? Or the mysqldump executed on "zabbix" database already dumped it?

mysqldump takes care of that.
The file produced by mysqldump contains all SQL instructions necessary to recreate a database.
If you look inside it, you will see a bunch of 'create table' that will create DB schema and 'insert' that will populate all tables.

It wont affect Zabbix' database?
(is the IBDATA file totally independant of Zabbix?)

Mysql stores all data from tables managed by InnoDB in ibdata file.
So if you have databases other than zabbix on this server, their data will also go in ibdata file.

Regards,
Alixen

[subba5678]

Hi Alexin,

I have the same problem , I have set up maximum size ibdata to be 5.8 G .The Ibdata file is still growing . Please find the details of the innodb settings and help me out.

log-error=/var/log/zabbix.err
pid-file=/var/run/mysqld/mysqld.pid

#[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1

# InnoDB settings
innodb_data_home_dir = /zabbix
innodb_data_file_path = ibdata1:5.8G;ibdata2:5.8G:autoextend:max:5.8G
innodb_log_group_home_dir = /zabbix
innodb_log_arch_dir = /zabbix
innodb_file_per_table = ON
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash




Thanks,
Subbu