SSL ENCRYPTED MYSQL REPLICATION

Set up the master server

Setting up the master consists of the following steps:

1. Generate SSL certificates.
2. Edit MySQL configuration my.cnf.
3. Restart MySQL server process.
4. Set up database replication privileges (and force SSL).
5. Perform initial database backup to start replication from.
6. Finish up.

Set up the slave server

The steps for the slave server are:

1. Setup the SSL certificates.
2. Edit MySQL server configuration my.cnf.
3. Restart MySQL server.
4. Configure and start slave server.
5. Finish up.

Detailed steps:

we generate the CA certificate:
$ mkdir ~/mysql-tutorial/ && cd ~/mysql-tutorial/
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 \  -key ca-key.pem -out ca-cert.pem

Create the server certificate, remove its passphrase and sign it:
$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout server-key.pem -out server-req.pem

$ openssl rsa -in server-key.pem -out server-key.pem

$ openssl x509 -req -in server-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem


Create the client certificate, remove its passphrase and sign it

$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout client-key.pem -out client-req.pem

$ openssl rsa -in client-key.pem -out client-key.pem

$ openssl x509 -req -in client-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Install the certificates into to /etc/mysql/ directory:
$ sudo cp *.pem /etc/mysql/

Skip the bind address
assign the id to mysql server and enable the bin log And enable SSL by editing and add the following lines to my.cnf :
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Restart  the mysql server:

sudo service mysql restart

mysql -u root -p -h <host-name>

GRANT REPLICATION SLAVE ON *.*
TO '<slave_user>'@’%'
IDENTIFIED BY '<slave_password>'
REQUIRE SSL;
FLUSH PRIVILEGES;
USE <database name>;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

note the log position and log file name
example. binlog  filename mysql-bin.000003  position :1122
Take the mysql dump  
mysqldump -h localhost -u root -p --opt <database> > ~/<database>.sql

UNLOCK TABLES;
quit;

scp ~/<database>.sql  <username>@<ipaddress>:/path/to/paste/

On The Slave

Edit the slave server

Add the id and skip the networking and bind address of the server.
Id of both the server should not be same

STOP SLAVE;
CREATE DATABASE IF NOT EXISTS <database>;
USE <database>;
SOURCE /path/to/<database>.sql;

CHANGE MASTER TO
MASTER_HOST='<master’s ip>',
MASTER_USER='slave_user',
MASTER_PASSWORD='<password>',
MASTER_CONNECT_RETRY=60,
MASTER_LOG_FILE='<binlog file name>',
MASTER_LOG_POS=<position>,
MASTER_SSL=1,
MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
MASTER_SSL_KEY='/etc/mysql/client-key.pem';
START SLAVE;
SHOW SLAVE STATUS \G;

Wordpress installation on linux

Installing the Wordpress

Ubuntu	Redhat
sudo apt-get install apache2	yum –y install httpd
sudo aptitude install mysql-server-5.1	yum –y install mysql-server-5.1
sudo aptitude install php5 phpmyadmin	yum –y install php php-mysql
sudo apt-het install unzip wget	yum –y install unzip wget
sudo service apache2 restart         sudo service mysql restart	chkconfig httpd on chkconfig mysqld on /etc/init.d/mysqld start /etc/init.d/httpd start
wget http://wordpress.org/latest.zip or wget  http://wordpress.org/latest.tar.gz	wget http://wordpress.org/latest.zip or wget  http://wordpress.org/latest.tar.gz

sudo mv latest.zip /var/www
or
sudo mv wordpress-3.0.1.tar.gz /var/www

cd /var/www

sudo unzip latest.zip
or
sudo tar -zxvf latest.tar.gz

ls –al
ps aux | grep apache



You will see the Apache user group on the left.
Now change your WordPress folder to the same user group, you can do this in a parent folder or subfolder but for sanity it usually best for parent folder, use -R to recursively do it

And change the permission

To
chmod   -R 755 /var/www/<path to word press folser>

Note if www is your wordpress folder then just use the command upto www

sudo rm -f latest.zip
or
sudo rm -f wordpress-3.0.1.tar.gz

For creating a database using phpMyAdmin, follow these steps:

o Start by choosing a name for your WordPress database (like ‘wordpress‘ or ‘blog‘), enter it in the Create new database field, and click Create (choose the right Connection Collation for you or use utf8_general_ci).

o Click the Home icon in the upper left to return to the main page, then click Privileges. If a user relating to WordPress does not already exist in the list of users, create one:

o Click Add a new User.

o Chose a username for WordPress (like ‘wordpress‘) and enter it in the User name field. (Be sure Use text field: is selected from the dropdown.)

o Choose a strong password password (use strongpasswordgenerator.com), and enter it in the Password field. (Be sure Use text field: is selected from the dropdown.) Re-enter the password in the Re-type field.

o Write down the username and password you chose.

o Leave all options under Global privileges at their defaults.

o Click Go.

o Return to the Privileges screen and click the Edit privileges icon (on the right-most column) on the user you’ve just created for WordPress. In the Database-specific privileges section, select the database you’ve just created for WordPress under the Add privileges to the following database drop down. The page will refresh with privileges for that database. Click Check All to select all privileges, and click Go.

o On the resulting page, make note of the host name listed after Server: at the top of the page. (This will usually be localhost.)

cd /vacr/www/wordpress

sudo cp wp-config-sample.php wp-config.php

Edit this new file, wp-config.php, with your favorite editor and change the values under the section labeled

MySQL settings

o DB_NAME: Database Name used by WordPress

o DB_USER: Username used to access Database

o DB_PASSWORD: Password used by Username to access Database

o DB_HOST: The hostname of your Database Server

And also put your Authentication Unique Keys and Salts under the section labeled Authentication Unique

Keys using WordPress ’online generator’ to obtain them:

Put all the data in the configuration  file.

Linux install and configure pound reverse proxy for Apache http / https web server

Install Pound Software

Type the following command to install pound:
$ sudo apt-get install pound

Sample configuration: HTTP Proxy

Forward all incoming request at 202.54.1.5 port 80 request to 192.168.1.5 Apache server running at 8080 port:
Open /etc/pound/pound.cfg file:
# vi /etc/pound/pound.cfg


To translate HTTP requests to a local internal HTTP server, enter (make sure 192.168.1.5 Apache running listing on port 8080):

ListenHTTP
        Address 202.54.1.5
        Port    80
        Service
                 BackEnd
                      Address 192.168.1.5
                      Port    8080
                 End
         End
End


Save and close the file. Restart pound:
# /etc/init.d/pound restart
Following example will distribute the all HTTP/HTTPS requests to two Web servers:
ListenHTTP
         Address 202.54.1.5
         Port    80
End
ListenHTTPS
        Address 202.54.1.5
        Port    443
        Cert    "/etc/ssl/local.server.pem"
End
Service
                 BackEnd
                     Address 192.168.1.5
                     Port    80
                 End
                 BackEnd
                     Address 192.168.1.6
                     Port    80
                 End
End
For testing purpose you may generate self signed ssl certificate (/etc/ssl/local.server.pem), by entering the following command:
# cd /etc/ssl && openssl req -x509 -newkey rsa:1024 -keyout local.server.pem -out local.server.pem -days 365 -nodes

Pound log file

By default pound log message using syslog:
# tail -f /var/log/messages
# grep pound /var/log/messages

Sample complete configuration file

## Minimal sample pound.cfg
######################################################################
## global options:
User "www-data"
Group "www-data"
#RootJail "/chroot/pound"
## Logging: (goes to syslog by default)
## 0 no logging
## 1 normal
## 2 extended
## 3 Apache-style (common log format)
LogLevel 1
## check backend every X secs:
Alive 30
## use hardware-accelleration card supported by openssl(1):
#SSLEngine ""
######################################################################
## listen, redirect and ... to:
# Here is a more complex example: assume your static images (GIF/JPEG) are to be served from  #a  single  back-end  192.168.0.10.  In
#       addition,  192.168.0.11  is  to  do  the  hosting for www.myserver.com with #URL-based sessions, and 192.168.0.20 (a 1GHz PIII) and
#       192.168.0.21 (800Mhz Duron) are for all other requests (cookie-based sessions).  The #logging will be done by the back-end servers.
#       The configuration file may look like this:
             # Main listening ports
             ListenHTTP
                 Address 202.54.1.10
                 Port    80
                 Client  10
             End
             ListenHTTPS
                 Address 202.54.1.10
                 Port    443
                 Cert    "/etc/pound/pound.pem"
                 Client  20
             End
             # Image server
             Service
                 URL ".*.(jpg|gif)"
                 BackEnd
                     Address 192.168.1.10
                     Port    80
                 End
             End
            # Virtual host www.myserver.com
             Service
                 URL         ".*sessid=.*"
                 HeadRequire "Host:.*www.nixcraft.com.*"
                 BackEnd
                     Address 192.168.1.11
                     Port    80
                 End
                 Session
                     Type    PARM
                     ID      "sessid"
                     TTL     120
                 End
             End
             # Everybody else
             Service
                 BackEnd
                     Address 192.168.1.20
                     Port    80
                     Priority 5
                 End
                 BackEnd
                     Address 192.168.1.21
                     Port    80
                     Priority 4
                 End
                 Session
                     Type    COOKIE
                     ID      "userid"
                     TTL     180
                 End
             End