Move /var/lib/mysql, and update my.cnf zabbix gui reports error

[kgbeast]

My var/lib/mysql default for mariadb was running out of space, and i wanted to move mysqldb to another partition with enough space. I used rsync to copy, and renamed the original /var/lib/mysql to mysql.bak to revert if there were issues (thank god i did!)
The move went well, updated my.cnf and zabbix config files, mariadb and zabbix started fine.
However the web front-end keep on reporting "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was forced to revert back to the original /var/lib/mysql but am having space issues, and want to move the databases (mysql and zabbix) - is there a way to do this? i thought my procedure would be fine - everything worked except the gui kept on reporting the problem while the back-ends were up and running.

[kaspars.mednis]

was the socket location moved to another place ?
is zabbix_server on the same host and connecting through the socket ?

https://www.zabbix.com/documentation/3.4/manual/appendix/config/zabbix_server

DBHost Database host name.
In case of MySQL localhost or empty string results in using a socket.

there is line in zabbix_server.conf

### Option: DBSocket
#       Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket=/tmp/mysql.sock

Regards,
Kaspars

[kgbeast]

Thanks for the quick reply. Yes, i modified this to the new location of the mysql. However made no difference. zabbix server, mysql(mariadb) running fine, but only front-end reporting the db sockets as down. weird.
I am fast running out of space on my /var volume, and need to move the mysql and zabbix to / partition that has 1tb free.

[SBO]

It may sound a dumb question, but I've seen worse so.. Did you uncomment the line and restarted Zabbix server after that ?

[kaspars.mednis]

Have you restarted the zabbix_server after config change ?
Have you removed the # symbol in config line ?
Maybe there are some error messages in /var/log/zabbix/zabbix_server.log ?
if you think its a frontend issue, you can always run frontend setup wizard once more , just add setup.php at the end of your url like _http://your.zabbix.server/zabbix/setup.php

Regards,
Kaspars

[Cynchie]

kaspars.mednis your comment is a life saver! I was trying to figure out how to edit the frontend setup since I previously was using Mysql and now wanted to use Postgress.

Thank you very much!

[kgbeast]

I retried the procedure.
i moved mysql to /database
i can see the mysql.sock created correctly once i restarted. mysql and zabbix server up and running.

mysql moved here

[root@zims mysql]# pwd
/database/mysql

mysql.soc in correct place
[root@zims mysql]# ls -lart
total 73814068
drwx------ 2 mysql mysql 4096 Dec 5 2016 performance_schema
drwx------ 2 mysql mysql 4096 Dec 5 2016 mysql
drwx------ 2 mysql mysql 8192 Dec 5 2016 zabbix
-rw-rw---- 1 mysql mysql 52 Oct 27 16:40 aria_log_control
-rw-rw---- 1 mysql mysql 16384 Oct 27 16:40 aria_log.00000001
drwxr-xr-x 3 root root 26 Oct 27 16:49 ..
srwxrwxrwx 1 mysql mysql 0 Oct 27 18:23 mysql.sock
drwxr-xr-x 5 mysql mysql 4096 Oct 27 18:23 .
-rw-rw---- 1 mysql mysql 5242880 Oct 27 18:26 ib_logfile0
-rw-rw---- 1 mysql mysql 75575066624 Oct 27 18:26 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Oct 27 18:26 ib_logfile1
[root@zims mysql]#

my.cnf updated with moved location
[root@zims mysql]# grep -i sock /etc/my.cnf
#socket=/var/lib/mysql/mysql.sock
socket=/database/mysql/mysql.sock
socket=/database/mysql/mysql.sock
[root@zims mysql]#

db location and sock changed
[root@zims mysql]# grep -i sock /etc/zabbix/zabbix_server.conf
# If set to localhost, socket is used for MySQL.
# If set to empty string, socket is used for PostgreSQL.
### Option: DBSocket
# Path to MySQL socket.
# DBSocket=/tmp/mysql.sock
DBSocket=/database/mysql/mysql.sock
# Database port when not using local socket. Ignored for SQLite.

zabbix started
[root@zims mysql]# service zabbix-server status
Redirecting to /bin/systemctl status zabbix-server.service
● zabbix-server.service - Zabbix Server
Loaded: loaded (/usr/lib/systemd/system/zabbix-server.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2017-10-27 18:23:41 SAST; 4min 0s ago
Process: 1102 ExecStop=/bin/kill -SIGTERM $MAINPID (code=exited, status=0/SUCCESS)
Process: 5384 ExecStart=/usr/sbin/zabbix_server -c $CONFFILE (code=exited, status=0/SUCCESS)
Main PID: 5388 (zabbix_server)
CGroup: /system.slice/zabbix-server.service
├─5388 /usr/sbin/zabbix_server -c /etc/zabbix/zabbix_server.conf
├─5395 /usr/sbin/zabbix_server: configuration syncer [synced configuration in 0.600130 sec, idle 60 sec]
├─5396 /usr/sbin/zabbix_server: db watchdog [synced alerts config in 0.001429 sec, idle 60 sec]
├─5397 /usr/sbin/zabbix_server: poller #1 [got 0 values in 0.000005 sec, idle 1 sec]
├─5399 /usr/sbin/zabbix_server: poller #2 [got 0 values in 0.000005 sec, idle 1 sec]
├─5400 /usr/sbin/zabbix_server: poller #3 [got 0 values in 0.000005 sec, idle 1 sec]
├─5401 /usr/sbin/zabbix_server: poller #4 [got 5 values in 0.000314 sec, idle 1 sec]
├─5402 /usr/sbin/zabbix_server: poller #5 [got 3 values in 0.017798 sec, idle 1 sec]
├─5403 /usr/sbin/zabbix_server: unreachable poller #1 [got 1 values in 16.011820 sec, getting values]
├─5404 /usr/sbin/zabbix_server: trapper #1 [processed data in 0.000000 sec, waiting for connection]
├─5405 /usr/sbin/zabbix_server: trapper #2 [processed data in 0.001883 sec, waiting for connection]
├─5406 /usr/sbin/zabbix_server: trapper #3 [processed data in 0.080122 sec, waiting for connection]
├─5407 /usr/sbin/zabbix_server: trapper #4 [processed data in 0.000000 sec, waiting for connection]
├─5408 /usr/sbin/zabbix_server: trapper #5 [processed data in 0.003340 sec, waiting for connection]
├─5409 /usr/sbin/zabbix_server: icmp pinger #1 [pinging hosts]
├─5410 /usr/sbin/zabbix_server: alerter [sent alerts: 0 success, 0 fail in 0.000537 sec, idle 30 sec]
├─5414 /usr/sbin/zabbix_server: housekeeper [startup idle for 30 minutes]
├─5416 /usr/sbin/zabbix_server: timer #1 [processed 1 triggers, 0 events in 0.000324 sec, 0 maintenances in 0.000000 sec, idle 30 s...
├─5419 /usr/sbin/zabbix_server: http poller #1 [got 0 values in 0.000527 sec, idle 5 sec]
├─5422 /usr/sbin/zabbix_server: discoverer #1 [processed 0 rules in 0.000575 sec, idle 60 sec]
├─5423 /usr/sbin/zabbix_server: history syncer #1 [synced 0 items in 0.000001 sec, idle 1 sec]
├─5425 /usr/sbin/zabbix_server: history syncer #2 [synced 86 items in 0.004944 sec, idle 1 sec]
├─5427 /usr/sbin/zabbix_server: history syncer #3 [synced 0 items in 0.000001 sec, idle 1 sec]
├─5429 /usr/sbin/zabbix_server: history syncer #4 [synced 0 items in 0.000001 sec, idle 1 sec]
├─5431 /usr/sbin/zabbix_server: escalator #1 [processed 0 escalations in 0.007158 sec, idle 3 sec]
├─5432 /usr/sbin/zabbix_server: snmp trapper [processed data in 0.000009 sec, idle 1 sec]
├─5434 /usr/sbin/zabbix_server: proxy poller #1 [exchanged data with 0 proxies in 0.000004 sec, idle 5 sec]
├─5435 /usr/sbin/zabbix_server: self-monitoring [processed data in 0.000006 sec, idle 1 sec]
├─5436 /usr/sbin/zabbix_server: task manager [processed 0 task(s) in 0.000306 sec, idle 5 sec]
├─5950 sh -c /usr/sbin/fping -C3 2>&1 </tmp/zabbix_server_5409.pinger;/usr/sbin/fping6 -C3 2>&1 </tmp/zabbix_server_5409.pinger;
└─5952 /usr/sbin/fping -C3

Oct 27 18:23:40 zims.mtn.co.za systemd[1]: Starting Zabbix Server...
Oct 27 18:23:41 zims.mtn.co.za systemd[1]: zabbix-server.service: Supervising process 5388 which is not our child. We'll most likely n...t exits.
Oct 27 18:23:41 zims.mtn.co.za systemd[1]: Started Zabbix Server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@zims mysql]#
[root@zims mysql]#
[root@zims mysql]#
[root@zims mysql]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2017-10-27 18:23:15 SAST; 4min 46s ago
Process: 5118 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 5085 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 5117 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─5117 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─5300 /usr/libexec/mysqld --basedir=/usr --datadir=/database/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mari...

Oct 27 18:23:11 zims.mtn.co.za systemd[1]: Starting MariaDB database server...
Oct 27 18:23:12 zims.mtn.co.za mysqld_safe[5117]: 171027 18:23:12 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Oct 27 18:23:12 zims.mtn.co.za mysqld_safe[5117]: 171027 18:23:12 mysqld_safe Starting mysqld daemon with databases from /database/mysql/
Oct 27 18:23:15 zims.mtn.co.za systemd[1]: Started MariaDB database server.
[root@zims mysql]#

[kaspars.mednis]

Are you still getting the error in frontend ?

After the moving procedure, are there any errors in /var/log/zabbix/zabbix_server.log file ?

you can check latest values in zabbix history. If the zabbix_server is collecting data, this value must be very close to current time

mysql
use zabbix
select from_unixtime(max(clock)) from history;

Regards,
Kaspars

[kgbeast]

Still no joy

Was spending way too much time to fix this, so i reverted back to my old database location, and everything worked. my /var partition is now at 2% space left so i am forced to move the DB again.
Moved the DB again, and getting the same results, and just getting plain frustrated with zabbix.
mariadb/mysql db moves fine, everything starts up fine after the move, including zabbix.
Any attempt at connecting to the front-end displays an error looking for the mysql.sock in the original /var directory. tring to execute the stup.php also just displays the same error.
My zabbix is now down and will stay down until i can fix this. I have been on the verge of getting my company to buy into a large scale deployment of zabbix, but now at a dead stop.

[kaspars.mednis]

I just may have found it... Zabbix frontend uses php, php uses /etc/php.ini for configuration, except for options that are specific to zabbix and defined in /etc/httpd/conf.d/zabbix.conf

there are no variables about mysql sock in zabbix.conf ,but in /etc/php.ini is the following section

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
; http://php.net/mysql.default-socket
mysql.default_socket =

i suggest to try modifying this, then restart httpd

Regards,
Kaspars

[Openwheel]

kgbeast did you get socket moved?

I too have this issue.
I edited conf/zabbix.conf.php with
$DB["SERVER"] = 'localhost:/path/to/mysql.sock';

and
php.ini file with
mysql.default_socket = /path/to/mysql.sock

but the GUI errors looking for
/var/lib/mysql/mysql.sock

Its almost like it is hard coded.....
Running CentOS7 / Zabbix 4

zabbix_server and MySQL/mariadb talking just fine

[SDPCR]

kgbeast or Openwheel

Did either of you find a solution to this? I'm facing the same issue.

As a workaround, I created a symbolic link and the GUI is working for me now.

ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

[aib]

When I moved mysql database in different folder I had same issue.
I found many places where mysql.sock must be edited.
1) /etc/my.cnf configuration file has two sections where I edited directory name for mysql.sock:
- section [mysqld]
socket=/home/mysql/mysql.sock
- section [client]
socket=/home/mysql/mysql.sock

2) /etc/php.ini also has more than one line for mysql.sock:
#grep "mysql.sock" /etc/php.ini
pdo_mysql.default_socket=/home/mysql/mysql.sock
mysql.default_socket = /home/mysql/mysql.sock
mysqli.default_socket = /home/mysql/mysql.sock

3) And zabbix_server.conf also must be edited
# grep "mysql.sock" /etc/zabbix/zabbix_server.conf
# DBSocket=/tmp/mysql.sock
DBSocket=/home/mysql/mysql.sock

In your case I guess you did not update all lines in php.ini file. Or did not restart httpd daemon after updating php.ini.
Just short explanation - Zabbix server does not provide HTTP-server functionality. It is HTTPdaemon or Apache which showing http-pages to you.

[saquib akhtar]

Perfect solution for moving mysql/mariadb data directory on centos 7 with zabbix 4.2

[saquib akhtar]

My var/lib/mysql default for mariadb was running out of space, and i wanted to move mysqldb to another partition with enough space. I used rsync to copy, and renamed the original /var/lib/mysql to mysql.bak to revert if there were issues (thank god i did!)
The move went well, updated my.cnf and zabbix config files, mariadb and zabbix started fine.
However the web front-end keep on reporting "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was forced to revert back to the original /var/lib/mysql but am having space issues, and want to move the databases (mysql and zabbix) - is there a way to do this? i thought my procedure would be fine - everything worked except the gui kept on reporting the problem while the back-ends were up and running.

Solution https://www.zabbix.com/forum/zabbix-...471#post363471 is perfect

[raul.recinosm]

dont work for me this

[raul.recinosm]

Hello all, this week i am try to this procudure, but not work for me, my solution was read this tutorial from digital ocean to move mysql to another partiton and them just create an symlink, no need change the zabbix_server.conf

OS: Ubuntu 18
Zabbix: 4.0

simlink: ln -s /data/mysql_datadir/mysql.sock /var/lib/mysql/mysql.sock

tutorial: https://www.digitalocean.com/communi...n-ubuntu-16-04

I hope this help you.