MySQL replication on RHEL

I recently configured MySQL for replication after first enabling SSL connections between the two systems that would be involved with replication. I have to say that MySQL documentation is excellent and all these notes are simply based on what is available on the MySQL site. I have included links to as many of the relevant sections of the documentation as possible.

For reference, here is the MySQL manual on enabling SSL: 5.5.7.2. Using SSL Connections

Before beginning, it is a good idea to create a directory for the SSL output files and make sure all the files end up there.

MySQL’s RHEL5 packages from mysql.com support SSL by default, but to check you can run:

---

$ mysqld --ssl --help
mysqld  Ver 5.0.67-community-log for redhat-linux-gnu on i686 (MySQL Community Edition (GPL))Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Starts the MySQL database server

Usage: mysqld [OPTIONS]

For more help options (several pages), use mysqld --verbose --help

---

The command will create an error if there is no SSL support.

Next, check that the MySQL server has SSL enabled. The below output means that the server supports SSL but it is not enabled. Enabling it can be done at the command line or in the configuration file, which will be detailed later.

---

$ mysql -u username -p -e"show variables like 'have_ssl'"
Enter password:
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+

---

Documentation on setting up certificates:
5.5.7.4. Setting Up SSL Certificates for MySQL

First, generate the CA key and CA certificate:

---

$ openssl genrsa 2048 > mysql-ca-key.pem
Generating RSA private key, 2048 bit long modulus
............................................+++
............+++

$ openssl req -new -x509 -nodes -days 356 -key mysql-ca-key.pem > mysql-ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:California
Locality Name (eg, city) [Newbury]:Burbank
Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps
Organizational Unit Name (eg, section) []:Acme IRT
Common Name (eg, your name or your server's hostname) []:mysql.acme.com
Email Address []:acme-irt@acme.com

---

Create the server certificate:

---

$ openssl req -newkey rsa:2048 -days 365 -nodes -keyout mysql-server-key.pem >
mysql-server-req.pem

Generating a 2048 bit RSA private key
.............................+++
.............................................................+++
writing new private key to 'mysql-server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:California
Locality Name (eg, city) [Newbury]:Burbank
Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps
Organizational Unit Name (eg, section) []:Acme IRT
Common Name (eg, your name or your server's hostname) []:mysql.acme.com
Email Address []:acme-irt@acme.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:

$ openssl x509 -req -in mysql-server-req.pem -days 356 -CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem
-set_serial 01 > mysql-server-cert.pem
Signature ok
subject=/C=US/ST=California/L=Burbank/O=Acme Road Runner Traps/OU=Acme IRT/CN=
mysql.acme.com/emailAddress=acme-irt@acme.com
Getting CA Private Key

---

Finally, create the client certificate:

---

$ openssl req -newkey rsa:2048 -days 356 -nodes -keyout mysql-client-key.pem >
mysql-client-req.pem
Generating a 2048 bit RSA private key
................+++
.................+++
writing new private key to 'mysql-client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:California
Locality Name (eg, city) [Newbury]:Burbank
Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps
Organizational Unit Name (eg, section) []:Acme IRT
Common Name (eg, your name or your server's hostname) []:mysql.acme.com
Email Address []:acme-irt@acme.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:

$ openssl x509 -req -in mysql-client-req.pem -days 356 -CA mysql-ca-cert.pem
-CAkey mysql-ca-key.pem -set_serial 01 > mysql-client-cert.pem
Signature ok
subject=/C=US/ST=California/L=Burbank/O=Acme Road Runner Traps/OU=Acme
IRT/CN=mysql.acme.com/emailAddress=acme-irt@acme.com
Getting CA Private Key

[nr@mysqld mysqlcerts]$ ls
mysql-ca-cert.pem      mysql-client-key.pem   mysql-server-key.pem
mysql-ca-key.pem       mysql-client-req.pem   mysql-server-req.pem
mysql-client-cert.pem  mysql-server-cert.pem

---

To enable SSL when starting mysqld, the following should be in /etc/my.cnf under the [mysqld] section. For this example, I put the files in /etc/mysql/openssl:

---

ssl-ca="/etc/mysql/openssl/mysql-ca-cert.pem"
ssl-cert="/etc/mysql/openssl/mysql-server-cert.pem"
ssl-key="/etc/mysql/openssl/mysql-server-key.pem"

---

To use any client, for instance mysql from the command line or the GUI MySQL Administrator, copy the client cert and key to a dedicated folder on the local box along with ca-cert. You will have to configure the client to use the client certificate, client key, and CA certificate.

To connect with the mysql client using SSL, copy the client certificates to a folder, for instance /etc/mysql, then under the [client] section in /etc/my.cnf:

---

ssl-ca="/etc/mysql/openssl/mysql-ca-cert.pem"
ssl-cert="/etc/mysql/openssl/mysql-client-cert.pem"
ssl-key="/etc/mysql/openssl/mysql-client-key.pem"

---

In MySQL Administrator, the following is an example you would put into the Advanced Parameters section if you want to connect using SSL.

---

SSL_CA U:/keys/mysql-ca-cert.pem
SSL_CERT U:/keys/mysql-client-cert.pem
SSL_KEY U:/keys/mysql-client-key.pem
USE_SSL Yes

---

Replication

Before configuring replication, I made sure to review the MySQL replication documentation.

16.1.1.1. Creating a User for Replication
Because MySQL stores the replication user’s name and password using plain text in the master.info file, it’s recommended to create a dedicated user that only has the REPLICATION SLAVE privilege. The replication user needs to be created on the master so the slaves can connect with that user.

---

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.50' IDENTIFIED BY ‘password’;

---

16.1.1.2. Setting the Replication Master Configuration
Edit my.cnf to uncomment the “log-bin” line. Also uncomment “server-id = 1”. The server-id can be anything between 1 and 2^32 but must be unique.

Also add “expire_logs_days” to my.cnf. If you don’t, the binary logs could fill up the disk partition because they are not deleted by default!

---

expire_log_days = 4

---

16.1.1.3. Setting the Replication Slave Configuration
Set server-id to something different from the master in my.cnf. Although not required, enabling binary logging on the slave is also recommended for backups, crash recovery, and in case the slave will also be a master to other systems.

16.1.1.4. Obtaining the Master Replication Information
I flush the tables to disk and lock them to temporarily prevent changes.

---

# mysql -u root -p -A dbname
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

---

If the slave already has data from master, then you may want to copy over data manually to simplify things, 16.1.1.6. Creating a Data Snapshot Using Raw Data Files. However, you can also use mysqldump, as shown in Section 16.1.1.5, “Creating a Data Snapshot Using mysqldump”.

Once the data is copied over to the slave, I get the current log position.

---

Mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 16524487 |              |                  |
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

mysql> UNLOCK TABLES;

---

16.1.1.10. Setting the Master Configuration on the Slave
Finally, configure the slave. The log file and log position tell the slave where to begin replication. All changes after that log position will be replicated to the slave.

---

mysql> CHANGE MASTER TO
->     MASTER_HOST=’192.168.1.50’,
->     MASTER_USER=’repl’,
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE=’ mysql-bin.000002’,
->     MASTER_LOG_POS=16524487,
->     MASTER_SSL=1,
->     MASTER_SSL_CA = '/etc/mysql/openssl/mysql-ca-cert.pem',
->     MASTER_SSL_CAPATH='/etc/mysql/openssl/',
->     MASTER_SSL_CERT = '/etc/mysql/openssl/mysql-server-cert.pem',
->     MASTER_SSL_KEY = '/etc/mysql/openssl/mysql-server-key.pem';
Query OK, 0 rows affected (0.74 sec)  

mysql> START SLAVE;

---

Replication can start!
The slave status can be checked via the following command:

---

mysql> show slave status;

---

Tweet

Querying Session Data Based on Snort Rule IPs

Sometimes Snort rules can contain useful information but are not practical to use in production. The Bleeding Snort rules recently added a set of rules to detect connection attempts to known compromised hosts. If you take a look at the rules, you'll see that it is essentially a large list of IP addresses that are known to be compromised.

When I first ran these rules on a Snort sensor, they definitely gave me some alerts requiring action. However, the performance of the sensor really suffered, particularly as the set of IP addresses grew. Since the rules were causing packet loss, I wanted to disable them.

I decided to write a script to grab the IP addresses from the Bleeding rule, then load the addresses into my database to compare with stored sancp data. If you are monitoring your network but not storing session data acquired with tools like sancp or argus, you should be. In my case, I am running Sguil, which uses sancp.

First, I had to write the script to grab the IP addresses. Since I just finished an introduction to Perl class in school and had many projects that required string matching, I figured that was the way to go. Since I'm a beginner, I would be more than happy if anyone can offer improvements to the following script. In particular, one thing I have not worked on yet is a method for expanding CIDR notation to individual IP addresses. The bleeding-compromised.rules do contain some networks in CIDR notation rather than just individual IP addresses, and for now the script simply strips the notation resulting in an incomplete IP list.

Edit: I posted an updated script that replaces the one in this post. I would suggest using the updated version rather than this one.

#!/usr/bin/perl
#
# Script to pull IP address from Snort rules file
# by nr
# 2007-08-30

# Set filenames
$rulefile = "/nsm/rules/bleeding-compromised.rules";
$ip_outfile = "iplist.txt";

# Print error unless successful open of file to read
die "Can't open rulefile.\n" unless open RULEFILE, "<", "$rulefile"; # Open file to write open IPLIST, ">", "$ip_outfile";

# Put each rule from rules file into array
chomp(@rule = <RULEFILE>);

# For each rule
foreach $rule (@rule) {
# Match only rules with IP addresses so we don't get comments etc
# This string match does not check for validity of IP addresses
if ( $rule =~ /\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/ ) {
    # Remove everything before [ character
    $rule =~ s/.*\[//g;
    # Remove everything after ] character
    $rule =~ s/\].*//g;
    # Split the remaining data using the commas
    # and put it into ip_address array
    @ip_address = split /\,/, $rule;

    # For each IP address in array
    foreach $ip_address (@ip_address) {
        # Remove CIDR notation (means those IP ranges are missed - need to fix)
        $ip_address =~ s/\/.*//g;
        # Print to output file one IP per line
        print IPLIST "$ip_address\n";
        }
    }
}

# Close filehandles
close RULEFILE;
close IPLIST;

Now I have a file called "iplist.txt" with the list of IP addresses, one per line. Next, I log into MySQL and load the list into a temporary database and table. The table really only needs one column of the CHAR or VARCHAR data type. (See the MySQL documentation for creating tables or databases).

LOAD DATA LOCAL INFILE '/home/nr/iplist.txt' INTO TABLE temp.ipaddr;

Then I have to convert the IP addresses to the INT data type using the INET_ATON function so they can be matched against my sancp session data. I created the table "sguildb.ipaddresses" for cases like this where I want to load external IP address and then run a query. The "temp.ipaddr" table has one column called "new_ip". The "sguildb.ipaddresses" table also has one column, but called "dst_ip".

INSERT INTO sguildb.ipaddresses SELECT INET_ATON(new_ip) FROM temp.ipaddr;

In MySQL 5.x, you can combine the previous two steps to add and convert the data in one step. I'm currently running 4.1, so I have not investigated the exact syntax.

Finally, I can query my sancp session data for connections going to any of the IP addresses in sguildb.ipaddresses, which are the IP addresses from the Snort rule.

SELECT sancp.sid,INET_NTOA(sancp.src_ip),sancp.src_port,INET_NTOA(sancp.dst_ip),
sancp.dst_port,sancp.start_time FROM sancp INNER JOIN ipaddresses ON
(sancp.dst_ip = ipaddresses.dst_ip) WHERE sancp.start_time >= DATE_SUB(UTC_DATE(),
INTERVAL 24 HOUR) AND sancp.dst_port = '80';

This query will return the sensor ID, source IP, source port, destination IP, destination port, and session start time from the sancp table wherever the sancp.dst_ip matches the ipaddresses.dst_ip where I stored the IP addresses from the Snort rule. Notice that it will query the last 24 hours for port 80 connections only. Depending on the type of activity you are looking for, you could change ports or remove the port match entirely.

This whole process could be automated further by running the mysql commands directly from the Perl script so the compromised IP addresses are updated in the database when the script is run.

The final SQL query to match the compromised IP addresses with sancp destination IP addresses can easily be turned into a cronjob. For instance, if querying for the past 24 hours then run the cronjob once a day. Once the results are returned, if running Sguil with full content logging it is easy to then query for the individual connections you're interested in and view the ASCII transcripts or the packet captures.

Tweet