zabbix's db mysql export and import

[wilp]

Hi!

I have a question.

I want to export zabbix's mysql db from one zabbix server (call SRV-A) and import it in enother zabbix server (SRV-B).

Both SRV-A and SRV-B have same configuration:
zabbix appliance 4.0
MySQL Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)
but SRV-A have production db and SRV-B have empty db.
on both SRV-A and SRV-B zabbix-server's service is running


Well.

Actions on SRV-A:

I can see size of production mysql db

appliance@zabbix:~/mysql_db_bkp$ sudo su
root@zabbix:/home/appliance/mysql_db_bkp# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6332
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema "databases name", sum(data_length + index_length)/1024/1024  "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| databases name     | DВ size in MB  |
+--------------------+----------------+
| information_schema |     0.15625000 |
| mysql              |     2.64630795 |
| performance_schema |     0.00000000 |
| sys                |     0.01562500 |
| zabbix             |  4259.20312500 |
+--------------------+----------------+

OK.


I executed command for export mysql db from mysql server and to archived to gz format.

sudo mysqldump zabbix --add-drop-table --add-locks --extended-insert --single-transaction --quick -u zabbix -p | gzip > mysql_db_bkp/zbx_db_bkp_20190808.db.gz

or to do by article

https://www.zabbix.com/documentation/4.0/manual/appliance

sudo mysqldump zabbix | bzip2 -9 > dbdump.bz2

get backup file.

ls -lah

-rw-rw-r-- 1 appliance appliance 250M Aug  8 07:19 zbx_db_bkp_20190808.db.gz

Actions on SRV-B:

Get file zbx_db_bkp_20190808.db.gz from SRV-A to SRV-B.

I have to stop zabbix-server's service

zbx_db_bkp_20190808.db.gz

And I executed revers command:

sudo zcat zbx_db_bkp_20190808.db.gz | mysql zabbix -u zabbix -p

or to do by article

https://www.zabbix.com/documentation/4.0/manual/appliance

bzcat dbdump.bz2 | sudo mysql zabbix

Well.

I can see size of mysql db on SRV-B:

root@zabbix:/home/appliance# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7196
Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema "databases name", sum(data_length + index_length)/1024/1024  "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| databases name     | DВ size in MB  |
+--------------------+----------------+
| information_schema |     0.15625000 |
| mysql              |     2.61459637 |
| performance_schema |     0.00000000 |
| sys                |     0.01562500 |
| zabbix             |  3425.34375000 |
+--------------------+----------------+
5 rows in set (0.27 sec)

mysql> exit
Bye

My question:
Why does mysql db SRV-B less than mysql db SRV-A ?

Many thanks for your answers!

[wilp]

I mean where disappeared 1Gb of data?
Is all data exporting wtih this methods?:

sudo mysqldump zabbix --add-drop-table --add-locks --extended-insert --single-transaction --quick -u zabbix -p | gzip > mysql_db_bkp/zbx_db_bkp_20190808.db.gz

or

sudo mysqldump zabbix | bzip2 -9 > dbdump.bz2

[zux]

try optimizing the original tables, see if there is difference after that.
overall, I would use count(*) to check if all data is transferred.

[wilp]

How can i use count(*) in mysql for zabbix's db?
Could you show me example please?

I find this solution:

mysql

SELECT SUM(TABLE_ROWS)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'zabbix';

[zux]

select count(*) from items;
select count(*) from hosts;

This will give you an idea if the amount of hosts and items is the same.
You can use this on other tables also.