MySQL Master/Master Config

This is a HOWTO for setting up a Master/Master MySQL configuration. This can provide a level of fault tolerance with a hot standby, load balancing, or even high availability fault tolerance can be achived with the addition of keepalive or something similar.


#Master 1/Slave 2 ip: 192.168.1.2 (ServerA)
#Master 2/Slave 1 ip : 192.168.1.3 (ServerB)

#Step 1
#On Master 1 (ServerA), make changes in my.cnf:
-----------------
[mysqld]
datadir=/d2/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
bind-address = 192.168.1.2 #enable tcp access
server-id=1 #server id

log-bin=/d2/mysql/db1-bin-log #Where to store the bin logs for replication TO ServerB
log-bin-index=/d2/mysql/db1-bin-log.index

binlog-do-db=redmine1 #DB to replicate
binlog-ignore-db=mysql #DB's not to replicate
binlog-ignore-db=test

master-host = 192.168.1.3 #Set Master info for ServerA
master-user = replication
master-password = *****************
master-port = 3306

relay-log=/d2/mysql/db1-relay-log #where to store the relay logs for replication FROM ServerB
relay-log-index=/d2/mysql/db1-relay-log.index

#[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
------------------

#Step 2 (granting access to replcation users on both boxes)
#On master 1 (ServerA), create a replication slave account on master1 for master2:
mysql -u root -p
mysql> grant replication slave on *.* to 'replication'@'192.168.1.3' identified by '**************';

#Create a replication slave account on master2(ServerB) for master1:
mysql -u root -p
mysql> grant replication slave on *.* to 'replication'@192.168.1.2 identified by '****************';

#Step 3
#Now edit my.cnf on Slave1 or Master2 (ServerB):
--------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
bind-address = 192.168.1.3
server-id=2

log-bin=/var/lib/mysql/db2-bin-log
log-bin-index=/var/lib/mysql/db2-bin-log.index

binlog-do-db=redmine1
binlog-ignore-db=mysql
binlog-ignore-db=test

master-host = 192.168.1.2
master-user = replication
master-password = *****************
master-port = 3306

relay-log=/var/lib/mysql/db2-relay-log
relay-log-index=/var/lib/mysql/db2-relay-log.index

#[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
--------------------

#Step 4
#Restart mysqld on both servers.

sudo /etc/init.d/mysqld restart

#Step 5
#Start slave 1 and slave 2 (both servers)

mysql -u root -p
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #Make sure this isn't blank
Master_Host: 192.168.1.2
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db1-bin-log.000014
Read_Master_Log_Pos: 404
Relay_Log_File: db2-relay-log.000029
Relay_Log_Pos: 543
Relay_Master_Log_File: db1-bin-log.000014
Slave_IO_Running: Yes #Make sure this is yes
Slave_SQL_Running: Yes #Make sure this is yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 404
Relay_Log_Space: 543
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

#Step 6
#Check on master status (both boxes):
mysql> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| db2-bin-log.000001 | 1214 | redmine1 | mysql,test |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Nagios administrator

Need Nagios installed and configured?

Monitor Web/Mail/FTP/SSH/DNS/SMB Servers etc...
Monitor Devices (Printers, Switches/Routers, Firewalls)
SNMP
PNP integration for graphing performance data, be able to view uptime availability over time.
Phone/Pager/Email/Backberry alerts!
Different Time periods for alerts, work day, weekends, 24x7 etc.
Contact groups
Escalations
Interface with climate/motion/light sensors.
Windows Integration (NSClient++)
Security Considerations

Also ask me about using an Amazon EC2 instance for an affordable alternative to expensive external solutions. For as little as $100/year you could have an external monitoring solution on your own dedicated server, fully customizable and secure.

Please email with contact info and I'll give you a call, or send me an email and I can provide my contact information.

- Resume/References available upon request. Currently work as a full time administrator managing 100's of servers and several Nagios instances including fail-over redundant configurations.

Packages and Install for The "Perfect Webserver"

Packages for apache, mysql, passenger, and php


Apache
-------------

sudo yum install -y httpd.x86_64
sudo yum install -y mod_ssl.x86_64

MySQL
-------------
sudo yum install -y mysql-devel.x86_64 mysql.x86_64 mysql-server.x86_64


PHP
------------
sudo yum install -y php-mysql.x86_64
sudo yum install -y php.x86_64


Deps
----------
sudo yum install -y httpd-devel apr-devel

Ruby
------------
sudo yum install -y ruby
sudo yum install -y ruby-devel ruby-docs ruby-ri ruby-irb ruby-rdoc


Ruby Gems
-------------
wget http://rubyforge.org/frs/download.php/60718/rubygems-1.3.5.tgz
tar xzvf rubygems-1.3.5.tgz
sudo ruby setup.rb
sudo gem update
sudo gem update --system
sudo gem install -v=2.1.2 rails
sudo gem list

Passenger
-------------
gem install passenger


add to httpd.conf:

LoadModule passenger_module /usr/lib64/ruby/gems/1.8/gems/passenger-2.2.5/ext/apache2/mod_passenger.so
PassengerRoot /usr/lib64/ruby/gems/1.8/gems/passenger-2.2.5
PassengerRuby /usr/bin/ruby

Include conf/sites-enabled/*.conf


Create: in sites-enabled/


ServerName www.yourhost.com
DocumentRoot /somewhere/public # <-- be sure to point to 'public'!

Getting bonded networking connections working in xen

create mybond in the /etc/xen/scripts dir

------------------
#!/bin/sh
dir=$(dirname "$0")
"$dir/network-bridge" "$@" vifnum=0 netdev=bond0
"$dir/network-bridge" "$@" vifnum=1 netdev=bond1
-------------------

edit /etc/xen/xend-config.sxp

comment out:
#(network-script network-bridge)

and add:
(network-script mybond)

restart xen

Xen Centos Host Freebsd Guest

Centos installs xen 3.0.1 which is old as hell, and 3.4 is way better. Also Freebsd will not install inside of 3.0.1 without a lot of extra work. So easy work around is to install this repo in yum.

http://www.gitco.de/repo/

$ cd /etc/yum.repos.d
$ wget http://www.gitco.de/repo/CentOS5-GITCO_x86_64.repo
$ sudo yum groupremove Virtualization
$ sudo yum groupinstall Virtualization
$ sudo reboot

[root@xen1 xen]# cat freebsd.hvm
name = "FreeBSD7"
builder = "hvm"
memory = "1024"
disk = ['file:/d2/images/bsdsmtpgateway/disk1.img,hda,w','file:/d2/iso/7.2-RELEASE-i386-dvd1.iso,hdc:cdrom,r']
vif = [ "mac=00:16:3e:70:66:ee,bridge=xenbr0" ]
device_model = "/usr/lib64/xen/bin/qemu-dm"
kernel = "/usr/lib/xen/boot/hvmloader"
vnc=1
boot="d"
vcpus=1
acpi="0"
pae="0"
serial = "pty" # enable serial console
on_reboot = 'restart'
on_crash = 'restart'


$ xm create freebsd.hvm

$ sudo lsof -i -n -P | grep qemu #find out what port vnc is listening on.
qemu-dm 6045 root 14u IPv4 18110 TCP 127.0.0.1:5900 (LISTEN)


From remote box;

$ ssh -L 5900:localhost:5900 hostname
$ vncviewer localhost

Converting a Mantis database in mysql from latin1 to utf8, so it can be imported into postgres by redmine:migrate_from_mantis

vi my.cnf
---------------------------------
character-set-server=utf8
default-collation=utf8_unicode_ci

[client]
default-character-set=utf8
---------------------------------

sudo service mysqld restart
mysqldump -u root -p --opt --default-character-set=latin1 --skip-extended-insert > mantis-latin1.sql
iconv -t LATIN1 -f UTF8 -c mantis-latin1.sql > latest.mantis-utf8.sql
sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci/g' latest.mantis-utf8.sql > latest.mantis-utf8.sql2
vi latest.mantis-utf8.sql2
:set encoding=utf-8
:set guifont=-misc-fixed-medium-r-normal--18-120-100-100-c-90-iso10646-1
save w!

mysql -u root -p utf8mantisdb --default-character-set=utf8 < latest.mantis-utf8.sql2

sudo rake redmine:migrate_from_mantis RAILS_ENV="production"

Using apache allow/deny with a load balancer forwarding the clients IP as X-Forwarded-For

In this example, I have a HAproxy load balancer setup, and its forwarding the clients IP so you see that instead of the load balancer in the log files.

It is fowarded by changing the log format to:

LogFormat "%{X-Forwarded-For}i %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%{X-Forwarded-For}i %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

Where You substitute %{X-Forwarded-For}i for %U

In order for apache to use this value to allow/deny people you need to set it like the following:

SetEnvIfNoCase ^X-Forwarded-For ^1\.2\.3\.4 officeip


Order deny,allow
Deny from all
Allow from env=officeip