How To Set Up MySQL Database Replication With SSL Encryption On Debian Lenny

Follow me on Twitter
Last edited 08/18/2010

This tutorial describes how to set up database replication in MySQL using an SSL connection for encryption (to make it impossible for hackers to sniff out passwords and data transferred between the master and slave). MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I will show how to replicate the database exampledb from the server server1.example.com (master) with the IP address 192.168.0.100 to the server server2.example.com (slave) with the IP address 192.168.0.101. Both systems are running Debian Lenny; however, the configuration should apply to almost all distributions with little or no modifications. The database exampledb with tables and data is already existing on the master, but not on the slave.

I'm running all the steps in this tutorial with root privileges, so make sure you're logged in as root.

 

2 Installing MySQL 5 And Enabling SSL Support

If MySQL 5 isn't already installed on server1 and server2, install it now:

server1/server2:

aptitude install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user - this password is valid for the user root

Installing A Web, Email And MySQL Database Cluster (Mirror) On Debian 5.0 With ISPConfig 3

com>
Last edited 08/04/2010

This tutorial describes the installation of a clustered Web, Email, Database and DNS server to be used for redundancy, high availability and load balancing on Debian 5 with the ISPConfig 3 control panel. GlusterFS will be used to mirror the data between the servers and ISPConfig for mirroring the configuration files. I will use a setup of two servers here for demonstration purposes but the setup can scale to a higher number of servers with only minor modifications in the GlusterFS configuration files.

There is currently one limitation in the MySQL cluster setup. The MySQL daemon has locking problems during the initial innodb check when the second server gets started. The current workaround that I use here is to start MySQL with myisam only. I've found several reports of successfully running MySQL servers with innodb on GlusterFS, so it must be possible with some finetuning of the GlusterFS and / or MySQL configuration file to use innodb as well. I will try to find a solution for the locking issues and update this tutorial. If someone knows a solution for innodb on GlusterFS, please contact me. If you want to use the second server only as hot standby system, then you should be able to use innodb as long as you start MySQL on the second server only when the first server is disconnected.

This is currently a proof of concept setup, so there is no experience how this setup scales in production systems yet. The only part that might cause problems is the shared MySQL data directory. Another solution for accessing MySQL databases from several servers simultaneously is to use a MySQL-cluster setup (http://www.mysql.com/products/database/cluster/) or MySQL master / master replication (http://www.howtoforge.com/mysql_master_master_replication).

 

1 Setting Up The Two Base Systems

In this setup there will be one master server (which runs the ISPConfig control panel interface) and one slave server which mirrors the web (apache), email (postfix and dovecot) and database (MySQL) services of the master server.

To install the clustered setup, we need two servers with a Debian 5.0 minimal install. The base setup is described in the following tutorial in the steps 1 - 6:

http://www.howtoforge.com/perfect-server-debian-lenny-ispconfig3

Install only steps 1 - 6 of the perfect server tutorial and not the other steps as they differ for a clustered setup!

In my example I use the following hostnames and IP addresses for the two servers:

Master Server

Hostname: server1.example.tld
IP address: 192.168.0.105

Slave server

Hostname: server2.example.tld
IP address: 192.168.0.106

Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IPs and hostnames of your servers.

 

2 Installing The Two Servers

The following steps have to be executed on the master and on the slave server. If a specific step is only for the master or slave, then I've added a note in the description in red.

vi /etc/hosts

127.0.0.1 localhost192.168.0.105 server1.example.tld192.168.0.106 server2.example.tld# The following lines are desirable for IPv6 capable hosts::1 localhost ip6-localhost ip6-loopbackfe00::0 ip6-localnetff00::0 ip6-mcastprefixff02::1 ip6-allnodesff02::2 ip6-allroutersff02::3 ip6-allhosts

Set the hostname of the server:

echo server1.example.tld > /etc/hostname
/etc/init.d/hostname.sh start

User server1.example.tld on the first server and server2.example.tld on the second server.

Edit the sources.list file...

vi /etc/apt/sources.list

... and ensure that it contains the following two lines. The first one is for the debian volatile repository to get updated pacakges for the ClamAV antivirus software and SpamAssassin and the second one is for the backports repository which contains current GlusterFS packages.

deb http://volatile.debian.org/debian-volatile lenny/volatile main contrib non-freedeb http://www.backports.org/debian/ lenny-backports main contrib non-free

Run...

apt-get install debian-backports-keyring
apt-get update

... to install the backports repository key and update the apt package database; then run ...

apt-get upgrade

... to install the latest updates (if there are any).

It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run...

apt-get -y install ntp ntpdate

... and your system time will always be in sync.

Install postfix, dovecot and mysql with one single command:

apt-get -y install postfix postfix-mysql postfix-doc mysql-client mysql-server openssl getmail4 rkhunter binutils dovecot-imapd dovecot-pop3d sudo

Enter the new password for mysql when requested by the installer and answer the next questions as decsribed below:

Create directories for web-based administration ? <-- No
General type of configuration? <-- Internet site
Mail name? <-- server1.mydomain.tld
SSL certificate required <-- Ok

We want MySQL to listen on all interfaces, not just localhost, therefore we edit /etc/mysql/my.cnf and comment out the line bind-address

Installing A Multiserver Setup With Dedicated Web, Email, DNS And MySQL Database Servers On Debian 5.0 With ISPConfig 3

com>
Last edited 08/10/2010

This tutorial describes the installation of an ISPConfig 3 multiserver setup with dedicated web, email, database and two DNS servers all managed trough a single ISPConfig 3 control panel. The setup described below uses five servers and can be extended easily to to a higher number of servers by just adding more servers. E.g. if you want to have two mailservers, do the setup steps from chapter 2 on both of these servers. If you want to set up more web servers, then install ISPConfig on all other web servers in expert mode except of the first one.

 

1 Installing The Five Debian Base Systems

In this setup there will be one master server (which runs the web server and ISPConfig control panel interface) and four slave servers for database, email and DNS.

To install the clustered setup, we need five servers (or virtual servers) with a Debian 5.0 minimal install. The base setup is described in the following tutorial in the steps 1 - 6:

http://www.howtoforge.com/perfect-server-debian-lenny-ispconfig3

Install only steps 1 - 6 of the perfect server tutorial and not the other steps as they differ for a clustered setup!

In my example I use the following hostnames and IP addresses for the five servers:

Web Server

Hostname: web.example.tld
IP address: 192.168.0.105

Mail Server

Hostname: mail.example.tld
IP address: 192.168.0.106

DB Server

Hostname: db.example.tld
IP address: 192.168.0.107

DNS Server (primary)

Hostname: ns1.example.tld
IP address: 192.168.0.108

DNS Server (secondary)

Hostname: ns2.example.tld
IP address: 192.168.0.109

Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IP's and hostnames of your servers.

 

2 Installing The Web Server

Edit the hosts file and add the IP addresses and hostnames for all servers. The hostnames and IP addresses have to be adjusted to match your setup.

vi /etc/hosts

127.0.0.1 localhost192.168.0.105 web.example.tld192.168.0.106 mail.example.tld192.168.0.107 db.example.tld192.168.0.108 ns1.example.tld192.168.0.109 ns2.example.tld # The following lines are desirable for IPv6 capable hosts::1 localhost ip6-localhost ip6-loopbackfe00::0 ip6-localnetff00::0 ip6-mcastprefixff02::1 ip6-allnodesff02::2 ip6-allroutersff02::3 ip6-allhosts

Set the hostname of the server:

echo web.example.tld > /etc/hostname
/etc/init.d/hostname.sh start

Edit the sources.list file...

vi /etc/apt/sources.list

... and ensure that it contains the following line to enable the volatile repository.

deb http://volatile.debian.org/debian-volatile lenny/volatile main contrib non-free

Run...

apt-get update

... to update the apt package database; then run...

apt-get upgrade

... to install the latest updates (if there are any).

It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run...

apt-get -y install ntp ntpdate

... and your system time will always be in sync.

Install the MySQL server. A MySQL server instance is necessary on every server as ISPConfig uses it to sync the configuration between the servers.

apt-get -y install mysql-client mysql-server

Enter the new password for MySQL when requested by the installer.

We want MySQL to listen on all interfaces on the master server, not just localhost, therefore we edit /etc/mysql/my.cnf and comment out the line bind-address