Yegor's blog

Small blog about system administration.

HOWTO: setup MySQL with SSL, SSL replication and how to establish secure connections from the console

Setup SSL on MySQL

1. Generate SSL certificates. Use the different Common Name for server and client certificates.
2. For the reference, I store the generated files under /etc/mysql-ssl/
3. Add the following lines to /etc/my.cnf under [mysqld] section:

# SSL
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem



4. Restart MySQL.
5. Create an user to permit only SSL-encrypted connection:
GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;

Establish secure connection from console

1. If the client is on a different node, copy /etc/mysql-ssl/ from the server to that node.
2. Add the following lines to /etc/my.cnf under [client]:

# SSL
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem



3. Test a secure connection:
[root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA

Setup SSL replication

1. Establish a secure connection from the console on slave like described above, to make sure SSL works fine.
2. On Master add “REQUIRE SSL” to the replication user:
GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;
3. Change master options and restart slave:
STOP SLAVE;
CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem';
SHOW SLAVE STATUSG
START SLAVE;
SHOW SLAVE STATUSG

Establish secure connection from PHP

1. Install php and php-mysql packages. I use the version >=5.4.x, otherwise, it may not work.
2. Create the script:
[root@centos6 ~]# cat mysqli-ssl.php
$conn=mysqli_init();
mysqli_ssl_set($conn, ‘/etc/mysql-ssl/client-key.pem’, ‘/etc/mysql-ssl/client-cert.pem’, NULL, NULL, NULL);
if (!mysqli_real_connect($conn, ‘127.0.0.1’, ‘ssluser’, ‘pass’)) { die(); }
$res = mysqli_query($conn, ‘SHOW STATUS like “Ssl_cipher”‘);
print_r(mysqli_fetch_row($res));
mysqli_close($conn);
3. Test it:
[root@centos6 ~]# php mysqli-ssl.php
Array
(
[0] => Ssl_cipher
[1] => DHE-RSA-AES256-SHA
)

No comments :

Post a Comment

HOWTO: Configure Logging and Log Rotation in Nginx

One of the easiest ways to save yourself trouble with your web server is to configure appropriate logging today. Logging information on your server gives you access to the data that will help you troubleshoot and assess situations as they arise.


The Error_log Directive

Nginx uses a few different directives to control system logging. The one included in the core module is called "error_log".

Error_log Syntax

The "error_log" directive is used to handle logging general error messages. If you are coming from Apache, this is very similar to Apache's "ErrorLog" directive.
The error_log directive takes the following syntax:
error_log log_file [ log_level ]
The "log_file" in the example specifies the file where the logs will be written. The "log_level" specifies the lowest level of logging that you would like to record.

The Access_log Directive

The access_log directive uses some similar syntax to the error_log directive, but is more flexible. It is used to configure custom logging.
The access_log directive uses the following syntax:
access_log /path/to/log/location [ format_of_log buffer_size ];
The default value for access_log is the "combined" format we saw in the log_format section. You can use any format defined by a log_format definition.
The buffer size is the maximum size of data that Nginx will hold before writing it all to the log. You can also specify compression of the log file by adding "gzip" into the definition:
access_log location format gzip;
Unlike the error_log directive, if you do not want logging, you can turn it off by specifying:
access_log off;
It is not necessary to write to "/dev/null" in this case.

Log Rotation

As log files grow, it becomes necessary to manage the logging mechanisms to avoid filling up disk space. Log rotation is the process of switching out log files and possibly archiving old files for a set amount of time.
Nginx does not provide tools to manage log files, but it does include mechanisms that make log rotation simple.
if ($time_iso8601 ~ "^(\d{4})-(\d{2})-(\d{2})") {
    set $year $1;
    set $month $2;
    set $day $3;
}
access_log /var/log/nginx/$year-$month-$day-access.log;

Conclusion

Proper log configuration and management can save you time and energy in the event of a problem with your server. Having easy access to the information that will help you diagnose a problem can be the difference between a trivial fix and a persistent headache.
It is important to keep an eye on server logs in order to maintain a functional site and ensure that you are not exposing sensitive information. This guide should serve only as an introduction to your experience with logging.

No comments :

Post a Comment

Ubuntu/Debian - Encrypted incremental backups with duplicity on Amazon S3

An example on how to use duplicity to perform encrypted incremental backups on Amazon S3.

Getting started

If you've never heard about duplicity before, you should check the documentation.

Install duplicity

First, you need to install duplicity, I always install it from source since the duplicity package is not often updated.
$ sudo apt-get install python-dev librsync-dev
$ cd /opt
$ sudo wget https://code.launchpad.net/duplicity/0.6-series/0.6.20/+download/duplicity-0.6.20.tar.gz
$ sudo tar xvzf duplicity-0.6.20.tar.gz
$ cd duplicity-0.6.20
$ python sudo setup.py install
But you can install it with apt-get
$ sudo apt-get install duplicity
Next you can also install s3cmd from S3 Tools, it's a command line tool for managing your S3 buckets, but it's not required.
$ sudo apt-get install s3cmd
$ s3cmd --configure

Encrypted Backups

Before backing up the data, you need to think about encryption, duplicity makes use of gpg and handles both private/public key pair (a gpg key) and symmetric encryption (a passphrase).
I use passsphrases since I'll never lose it and I don't have to backup a gpg key.

My backup script

Since you need to specify many args to perform the differents actions, I crafted a bash script that make working with duplicity easier, duptools.

Features

  • Backup multiple directories
  • Send email report on backup
  • Quickly list file and show bucket status
  • Restore your files easily

Duptools

#!/bin/bash
export AWS_ACCESS_KEY_ID=YOUR_ACCESS_KEY
export AWS_SECRET_ACCESS_KEY=YOUR_SECRET_ACCESS_KEY
export PASSPHRASE=YOU_PASSHRASE

# directories, space separated
SOURCE="/home/yegorg/backup /home/yegorg/bin /home/yegorg/documents"
BUCKET=s3+http://mybucket
LOGFILE=/home/yegorg/tmp/duplicity.log
# set email to receive a backup report
EMAIL=""

backup() {
  INCLUDE=""
  for CDIR in $SOURCE
  do
    TMP=" --include  ${CDIR}"
    INCLUDE=${INCLUDE}${TMP}
  done
  # perform an incremental backup to root, include directories, exclude everything else, / as reference.
  duplicity --full-if-older-than 30D $INCLUDE --exclude '**' / $BUCKET > $LOGFILE
  if [ -n "$EMAIL" ]; then
    mail -s "backup report" $EMAIL < $LOGFILE
  fi
}

list() {
  duplicity list-current-files $BUCKET
}

restore() {
  if [ $# = 2 ]; then
    duplicity restore --file-to-restore $1 $BUCKET $2
  else
    duplicity restore --file-to-restore $1 --time $2 $BUCKET $3
  fi
}

status() {
  duplicity collection-status $BUCKET
}

if [ "$1" = "backup" ]; then
  backup
elif [ "$1" = "list" ]; then
  list
elif [ "$1" = "restore" ]; then
  if [ $# = 3 ]; then
    restore $2 $3
  else
    restore $2 $3 $4
  fi
elif [ "$1" = "status" ]; then
  status
else
  echo "
  duptools - manage duplicity backup

  USAGE:

  ./duptools.sh backup 
  ./duptools.sh list
  ./duptools.sh status
  ./duptools.sh restore file [time] dest
  "
fi

export AWS_ACCESS_KEY_ID=
export AWS_SECRET_ACCESS_KEY=
export PASSPHRASE=

Installation

Set up config vars at the top of the script and make the script executable.

Backup

$ ./duptools.sh backup

List/Status

$ ./duptools.sh list
$ ./duptools.sh status

Restore

Be careful while restoring not to preprend a slash to the path.
Restoring a single file to tmp
$ ./duptools.sh restore home/yegorg/bin/setupscreen tmp/setupscreen
Restoring an older version of a directory to tmp (interval or full date)
$ ./duptools.sh  restore home/yegorg/bin 1D3h5s tmp/bin
$ ./duptools.sh  restore home/yegorg/bin 2012/7/5 tmp/bin

No comments :

Post a Comment