Move MySQL DB to New Folder on Remote DB Server

[gabeReal]

Greetings,

I have been searching through the forum for a solution but have only found bits a pieces and no solid documentation on this process. I am hoping someone can help point me in the right direction.
I successfully setup Zabbix 4.2.6 across two Cent OS 7 servers. One server is for the Zabbix front end and the other server is for the Zabbix MySQL DB. Everything works great by default but now I would like to move the Zabbix DB from the default location /var/lib/mysql to a new partition to the /data directory on the DB server. Here are the steps I have tried so far:

1. I have copied the contents from the default MySQL directory /var/lib/mysql/* to /data on the DB server using this command:

cp -R -p /var/lib/mysql/* /data

2. I modified the /etc/my.cnf file on the DB server by adding the following sections:

[mysqld]
datadir=/data
socket=/data/mysql.sock

[client]
port=3306
socket=/data/mysql.sock

Note: [mysqld] and [client] sections were missing in the default file. Here is what it looked like before it was modified:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Now it looks like this:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
datadir=/data
socket=/data/mysql.sock

[client]
port=3306
socket=/data/mysql.sock

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

3. I changed the DB socket paths in the php.ini file on the front end server:

[root@zabbix etc]# grep "mysql.sock" /etc/php.ini
pdo_mysql.default_socket=/data/mysql.sock
mysql.default_socket =/data/mysql.sock
mysqli.default_socket =/data/mysql.sock

Note: I found these instructions here:

https://www.zabbix.com/forum/zabbix-help/52032-move-var-lib-mysql-and-update-my-cnf-zabbix-gui-reports-error

4. I changed the DB socket path in the zabbix_server.conf file on the DB server:

[root@zabbixdb etc]$ sudo grep "mysql.sock" /etc/zabbix/zabbix_server.conf
DBSocket=/data/mysql.sock

5. I restarted httpd on the front end server and mysql and zabbix-server on the DB server

Now the Zabbix-server daemon on the DB server will not start and I get a “Zabbix server is not running: the information displayed may not be current” error in the front end GUI.

I have verified that the MySQL data directory is pointed to the /data location on the DB server:

MariaDB [(none)]> SELECT @@datadir;
+-----------+
| @@datadir |
+-----------+
| /data/ |
+-----------+
1 row in set (0.000 sec)

I have also verified that all files in the /data location are owned by mysql:mysql user:

[zabbixdb@zabbixdb data]$ ls -l
total 188516
-rw-rw----. 1 mysql mysql 6 Aug 30 11:27 zabbixdb.pid
-rwxr-xr-x. 1 mysql mysql 40960 Aug 30 11:27 aria_log.00000001
-rwxr-xr-x. 1 mysql mysql 52 Aug 30 11:27 aria_log_control
-rw-rw----. 1 mysql mysql 13887 Aug 30 11:27 ib_buffer_pool
-rwxr-xr-x. 1 mysql mysql 79691776 Aug 30 13:12 ibdata1
-rwxr-xr-x. 1 mysql mysql 50331648 Aug 30 13:12 ib_logfile0
-rwxr-xr-x. 1 mysql mysql 50331648 Aug 30 13:12 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 Aug 30 11:27 ibtmp1
drwx------. 2 mysql mysql 16384 Jul 29 11:32 lost+found
-rwxr-xr-x. 1 mysql mysql 0 Jul 11 12:12 multi-master.info
drwxr-xr-x. 2 mysql mysql 4096 Jul 11 12:11 mysql
srwxrwxrwx. 1 mysql mysql 0 Aug 30 11:27 mysql.sock
drwxr-xr-x. 2 mysql mysql 4096 Jul 11 12:11 performance_schema
drwxr-xr-x. 2 mysql mysql 12288 Aug 29 15:34 zabbix

In /var/log/zabbix/zabbix_server.conf I get the following errors:


15744:20190830:131809.470 database is down: reconnecting in 10 seconds

[Z3001] connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")

Obviously Zabbix is not pointing to the new /data directory. If I revert all changes Zabbix works great. I think I am missing a step or two somewhere along the line. I would be grateful for any advice

[gabeReal]

I was able to resolve my issue. It turns out I modified the php.ini on the front end server and not the DB server. Once I added the socket locations to the php.ini file on the DB server and restarted Zabbix came right up.

[smbtech]

I was running out of space on the default DB location, using the VMware appliance (Ubuntu platform). I have created and mounted a new disk and called it /database. I then followed this article to move 'mysql' folder to tne new disk:

https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-18-04.

There is plenty of information about this on CentOS platform, I haven't seen anything pertaining to the VMware appliance/Ubuntu, so here it is.
I had to modify it a bit so it work with Zabbix. The modify doc is attached.

This works when moving the database to a different location ON THE SAME SERVER!