Tailor sequence 1:1 to your environment by replacing keywords 'ZabbixDB', 'ZabbixUser', 'ZabbixPassword'!
Steps to perform while Zabbix server is running
Enter screen mode:
screen -S zabbixMaintenance -L
Authorize in DB client:
mysql zabbixDB
Measure size of all tables:
SELECT table_name,table_rows,data_length,index_length,round(((data_length + index_length) / 1024 / 1024 / 1024),2) "Size in GB" FROM information_schema.tables WHERE table_schema = "zabbixDB" ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESC LIMIT 8;
Rename current data tables to 'old' and create back the same table. One batch(line) consists with 2 SQL statements. Run line by line:
RENAME TABLE history_uint TO history_uint_old; CREATE TABLE history_uint LIKE history_uint_old;
RENAME TABLE history_str TO history_str_old; CREATE TABLE history_str LIKE history_str_old;
RENAME TABLE history_text TO history_text_old; CREATE TABLE history_text LIKE history_text_old;
RENAME TABLE history_log TO history_log_old; CREATE TABLE history_log LIKE history_log_old;
RENAME TABLE history TO history_old; CREATE TABLE history LIKE history_old;
RENAME TABLE trends_uint TO trends_uint_old; CREATE TABLE trends_uint LIKE trends_uint_old;
RENAME TABLE trends TO trends_old; CREATE TABLE trends LIKE trends_old;
SHOW TABLES;
--it should be exactly 173 tables
exit
Zabbix still works but graphs are now empty.
Download schema of 'old' tables. Each command will take around 1 second. Run in bash:
cd
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB history_uint_old > schema.history_uint_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB history_str_old > schema.history_str_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB history_text_old > schema.history_text_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB history_log_old > schema.history_log_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB history_old > schema.history_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB trends_uint_old > schema.trends_uint_old.sql
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB trends_old > schema.trends_old.sql
Make sure 7 files are there:
ls -1 *_old.sql
ls -1 *_old.sql | wc -l
Download data from 'old' historical tables. Each command will take multiple minutes to complete, possibly hours. None of the commands should produce something in output. Check you are still on screen by exiting it with 'ctrl+a' and 'd'. Then resume it back:
screen -r zabbixMaintenance
Run:
cd
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB history_uint_old | gzip --fast > data.history_uint_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB history_str_old | gzip --fast > data.history_str_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB history_text_old | gzip --fast > data.history_text_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB history_log_old | gzip --fast > data.history_log_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB history_old | gzip --fast > data.history_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB trends_uint_old | gzip --fast > data.trends_uint_old.sql.gz
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB trends_old | gzip --fast > data.trends_old.sql.gz
Make sure there are 7 different files with respectable size:
ls -lh *.sql.gz
ls -lh *.sql.gz | wc -l
Have a look if there is content inside dump:
zcat data.history_uint_old.sql.gz | head -24
zcat data.history_str_old.sql.gz | head -24
zcat data.history_text_old.sql.gz | head -24
zcat data.history_log_old.sql.gz | head -24
zcat data.history_old.sql.gz | head -24
zcat data.trends_uint_old.sql.gz | head -24
zcat data.trends_old.sql.gz | head -24
We have now 14 files in total:
ls -1 *.sql*
ls -1 *.sql* | wc -l
Open MySQL client:
mysql zabbixDB
Drop 'old' tables (the ones we have downloaded in compressed format):
DROP TABLE history_uint_old;
DROP TABLE history_str_old;
DROP TABLE history_text_old;
DROP TABLE history_log_old;
DROP TABLE history_old;
DROP TABLE trends_uint_old;
DROP TABLE trends_old;
SHOW TABLES;
it should be exactly 166 tables
List size of biggest tables:
SELECT table_name,table_rows,data_length,index_length,round(((data_length + index_length) / 1024 / 1024 / 1024),2) "Size in GB" FROM information_schema.tables WHERE table_schema = "zabbixDB" ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESC LIMIT 8;
exit
Database should be relatively small now.
Turn down backend and frontend so no one is accessing the database anymore:
systemctl stop zabbix-server rh-nginx116-nginx rh-php72-php-fpm
Make sure no server is running
ps aux | grep "[z]abbix_server"
Create a dump of schema:
mysqldump --flush-logs --single-transaction --create-options --no-data zabbixDB > schema.sql
Create a data dump of remaining data. Next command should complete in less than 10 minutes:
mysqldump --flush-logs --single-transaction --no-create-info zabbixDB | gzip --fast > data.sql.gz
Now we have 16 files in total:
ls -lh *.sql*
ls -lh *.sql* | wc -l
Stop MariaDB, make sure it not running:
systemctl stop mariadb
systemctl status mariadb
Erase data directory:
ls -1 /var/lib/mysql
rm -rf /var/lib/mysql/*
ls -1 /var/lib/mysql
Make sure SELinux is permissive or off:
getenforce
setenforce 0
getenforce
Navigate to Linux user 'mysql' and initialize a fresh DB engine so all new files are owned by linux user 'mysql':
su - mysql -s /bin/bash
mysql_install_db
exit
ls -1 /var/lib/mysql
In output, it should print only:
aria_log.00000001
aria_log_control
mysql
performance_schema
test
Install database tuning for MariaDB 5.5:
cd /etc/my.cnf.d
cat << 'EOF' > zabbix.cnf
[mysqld]
innodb_flush_method = O_DIRECT
innodb_file_per_table=1
open_files_limit = 65535
max_connections = 2000
query_cache_size = 0
query_cache_type = 0
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 256K
optimizer_switch=index_condition_pushdown=off
EOF
Adjust 'mariadb' service limits of 'LimitNOFILE':
mkdir -p /etc/systemd/system/mariadb.service.d
cd /etc/systemd/system/mariadb.service.d
echo -e "[Service]\nLimitNOFILE=65535"> limits.conf
systemctl daemon-reload
Let DB engine utilize more system memory. For a machine with 4GB of RAM create config:
cd /etc/my.cnf.d
cat << 'EOF' > memory.cpu.cnf
[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_log_file_size = 256M
EOF
Start DB engine:
systemctl start mariadb
# it will now create more files
ls -1 /var/lib/mysql
Remind yourself what credentials should be used to access database:
grep ^DB /etc/zabbix/zabbix_server.conf
Authorize in MySQL:
mysql
Create database and user based on credentials we saw:
CREATE DATABASE `zabbixDB` CHARACTER SET `utf8` COLLATE `utf8_bin`;
CREATE USER 'zabbixUser'@'localhost' IDENTIFIED BY 'zabbixPassword';
GRANT ALL PRIVILEGES ON `zabbixDB`.* TO 'zabbixUser'@'localhost';
exit
Insert schema into new database 'zabbixDB':
cd
cat schema.sql | mysql -u'zabbixUser' -p'zabbixPassword' zabbixDB
Validate the occurrence of partitions per day, it should be definitely more than one file/line in output:
cd /var/lib/mysql/zabbixDB && ls -1 history_uint#*
Insert data:
cd
zcat data.sql.gz | mysql -u'zabbixUser' -p'zabbixPassword' zabbixDB
Start back Zabbix components:
systemctl start zabbix-server
tail -99f /var/log/zabbix/zabbix_server.log
Start web server:
systemctl start rh-nginx116-nginx rh-php72-php-fpm
Bring back old data in the background. Prepare schema:
cd
ls -1 schema.*_old.sql
cat schema.*_old.sql | mysql -u'zabbixUser' -p'zabbixPassword' zabbixDB
Insert data back. The next commands definitely must be executed through 'screen' utility because it will take hours and we don't want an interruption of SSH session to break the operation. Check you are still on screen by exiting it with 'ctrl+a' and 'd'. Then resume it back:
screen -r zabbixMaintenance
Run:
cd
ls -1 data.*_old.sql.gz
zcat data.*_old.sql.gz | mysql -u'zabbixUser' -p'zabbixPassword' zabbixDB
This will take some hours. You can exit zabbixMaintenance screen with 'ctrl+a', then press 'd'. Come back later:
screen -r zabbixMaintenance
Set 'old' table as current and move back recent data. Authorize in MySQL:
mysql zabbixDB
Execute:
RENAME TABLE history_uint TO history_uint_tmp; RENAME TABLE history_uint_old TO history_uint; INSERT IGNORE INTO history_uint SELECT * FROM history_uint_tmp;
RENAME TABLE history_str TO history_str_tmp; RENAME TABLE history_str_old TO history_str; INSERT IGNORE INTO history_str SELECT * FROM history_str_tmp;
RENAME TABLE history_text TO history_text_tmp; RENAME TABLE history_text_old TO history_text; INSERT IGNORE INTO history_text SELECT * FROM history_text_tmp;
RENAME TABLE history_log TO history_log_tmp; RENAME TABLE history_log_old TO history_log; INSERT IGNORE INTO history_log SELECT * FROM history_log_tmp;
RENAME TABLE history TO history_tmp; RENAME TABLE history_old TO history; INSERT IGNORE INTO history SELECT * FROM history_tmp;
RENAME TABLE trends_uint TO trends_uint_tmp; RENAME TABLE trends_uint_old TO trends_uint; INSERT IGNORE INTO trends_uint SELECT * FROM trends_uint_tmp;
RENAME TABLE trends TO trends_tmp; RENAME TABLE trends_old TO trends; INSERT IGNORE INTO trends SELECT * FROM trends_tmp;
exit
Now we can tell how much space each day consumes:
cd /var/lib/mysql/zabbixDB && ls -lh history_uint#*
Check if there is old data and recent data together in frontend. Graphs should be now complete. Except for 5-10 minutes downtime.
Drop 'tmp' tables:
mysql zabbixDB
Execute:
DROP TABLE history_uint_tmp;
DROP TABLE history_str_tmp;
DROP TABLE history_text_tmp;
DROP TABLE history_log_tmp;
DROP TABLE history_tmp;
DROP TABLE trends_uint_tmp;
DROP TABLE trends_tmp;
SHOW TABLES;
exit
It should be exactly 166 tables
Remove input files:
cd && ls -1 *.sql*
rm -rf *.sql*
Validate live variables:
mysql
Execute:
select @@open_files_limit, @@innodb_file_per_table, @@max_connections, @@join_buffer_size, @@sort_buffer_size, @@read_buffer_size, @@query_cache_type, @@innodb_buffer_pool_size, @@innodb_buffer_pool_instances, @@innodb_log_file_size, @@innodb_log_buffer_size, @@innodb_flush_method, @@innodb_flush_log_at_trx_commit, @@optimizer_switch\G
It should print:
@@open_files_limit: 65535
@@innodb_file_per_table: 1
@@max_connections: 2000
@@join_buffer_size: 1048576
@@sort_buffer_size: 2097152
@@read_buffer_size: 262144
@@query_cache_type: OFF
@@innodb_buffer_pool_size: 4294967296
@@innodb_buffer_pool_instances: 1
@@innodb_log_file_size: 5242880
@@innodb_log_buffer_size: 8388608
@@innodb_flush_method: O_DIRECT
@@innodb_flush_log_at_trx_commit: 1
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
Secure database engine and install new 'root' password:
mysql_secure_installation
No comments:
Post a Comment