How to install/backup Mysql 5.5 on CentOS 6 for zabbix database

Jephe Wu - http://linuxtechres.blogspot.com


Objective: upgrade Mysql to version 5.5 on CentOS 6 for zabbix installation
Environment: CentOS 6.2 64bit


Steps:

1. install epel and remi repository from http://blog.famillecollet.com/pages/Config-en

# wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm
# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
# rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm

2. install mysql and mysql-server
# yum --enablerepo=remi,remi-test install mysql mysql-server



3. make it automatic start on reboot
# service mysqld start
# chkconfig --list mysqld
# chkconfig mysqld on

4. Secure mysql 
# mysql_secure_installation
answer yes to disable root login remotely, drop test database, remove anonymous users etc

5. change mysql admin password
# mysqladmin -u root password password
note: assume your use 'password' as your root password.

6. create zabbix user and database
# mysql [-h localhost] -u root -ppassword
# mysql> create database zabbix;
# mysql> create user 'zabbix'[@'1.2.3.4'] identified by 'zabbix';
# mysql> grant all on zabbix.* to zabbix[@'1.2.3.4'];
# mysql> flush privileges;
# mysql> exit;

Note: [..] is optional

7. update firewall rules for iptables
update iptables firewall rules if necessary.

8. other options
use Percona mysql 5.5 at http://www.percona.com/software/

9. backup mysql - backup database 'jephe'

#!/bin/sh

WEEK=`date +%w`
mysqldump jephe --add-drop-table --add-locks --extended-insert --single-transaction --quick -u jephe -ppassword | bzip2 > /data/backup/jephe_mysql.sql.$WEEK.bz2