IT Support Diary Everyday IT support issues and solutions

Moving Zabbix MySQL DB to new hardware

04.21.2009 · Posted in Linux

This document assumes that linux (Ubuntu) has already been installed on the new MySQL server.

Login to new MySQL server – IP Address with account Username
Install MySQL: enter the following command line.
apt-get install mysql-server

Assign password to root account and take note of it.
Install ssh: enter the following command line.
apt-get install ssh-server

 

Login to old MySQL server – IP Address with account Username

Dump Zabbix database with following command line.

mysqldump -u root -p zabbix > /tmp/zabbix_db_dump
Enter the password when prompted.

When complete copy the dump file across to the new MySQL server using scp, the command line is:

Scp /tmp/zabbix_db_dump Username@IP Address:/tmp

 

Enter the password when prompted; the file will be copied to /tmp
Login to new MySQL server – IP Address

Create the zabbix database. Enter the following from the command line.

mysql -u root -p

Enter the password when prompted.

In MySQL issue the following command

Create database zabbix;

Exit MySQL session. You will then import the tables and data with the following command line.

mysql -u root -p zabbix < /tmp/zabbix_db_dump

Enter the password when prompted.

Now you will create the zabbix account that can only be used from the Zabbix Server (IP Address). Enter the following from the command line.

mysql -u root -p zabbix

Enter the password when prompted.

From the MySQL prompt run the following commands.

create user ‘zabbix’@’IP Address’ identified by ‘Password’;

use zabbix;

grant all privileges on zabbix.* to ‘zabbix’@’IP Address’;

exit;

 

In order to connect remote, the zabbix server must be configured to allow remote connections. Edit the /etc/mysql/my.cnf file. Search for bind-address and comment out this line. Save and exit.

Now, restart mysql daemon. Enter the following on the command line.

sudo /etc/init.d/mysql restart.

Login to Zabbix Server

Test the connection to the new MySQL server. Enter the following on the command line:

mysql -u zabbix -p -h IP Address

Enter the password when prompted. You should get a MySQL session.

The next step is to configure the Zabbix Server to point to and use the new zabbix database on the new MySQL server. First, stop Zabbix Agent and Server. Enter the following at the command line.

sudo /etc/init.d/zabbix-agent stop

sudo /etc/init.d/zabbix-server stop

Edit the following file: /etc/zabbix/zabbix_server.conf.Search for DBHost. Update the line to:

DBHost=IP Address

Save and quit. Restart bot the Agent and Server. Issue the following from the command line.

sudo /etc/init.d/zabbix-server start

sudo /etc/init.d/zabbix-agent start

 

The web interface for zabbix now has to be configured to point to the new MySQL database and Server. Edit the following file: /home/zabbix/public_html/zabbix.conf.php. Search for $DB[“SERVER”] and change the ip address to IP Address.

Login to old MySQL server – IP Address with account Username

All that remains is to turn off the old MySQL server. Issue the following from the command line.

sudo /etc/init.d/mysql stop

Leave a Reply