Mysql – Migrating Zabbix host to new server

Alright, you have two mistakes here : The first one : the log have to be in /var/log (well or somewhere else but not into /tmp) :

edit the file /etc/zabbix/zabbix_server.conf and look for this entry :

# Name of log file
# If not set, syslog is used

LogFile=/var/log/zabbix-server/zabbix_server.log

And check you have the same error. For the database access, you can launch :

dpkg-reconfigure zabbix-server

so you will be able to set a new password. OR, edit the file : /etc/zabbix/zabbix_server.conf again, at the end of the file, you will see :

# Database user

DBUser=zabbix

# Database password
# Comment this line if no password used

DBPassword=foo

# Connect to MySQL using Unix socket?

#DBSocket=/tmp/mysql.sock

Note that you will also have to edit the file /etc/zabbix/dbconfig.php :

$DB["TYPE"]      = "mysql";
$DB["SERVER"]    = "localhost";
$DB["PORT"]      = "0";
$DB["DATABASE"]  = "zabbix";
$DB["USER"]      = "zabbix";
$DB["PASSWORD"]  = "foo";
$ZBX_SERVER      = "127.0.0.1";
$ZBX_SERVER_PORT = "10051";

so the password match.

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.