Php MySQL SSL холболт

Setup SSL on MySQL

1. Generate SSL certificates according to the <a rel="nofollow" href="http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html" target="_blank">example 1</a>. 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.3.3, 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
)

Establish secure connection from Python

1. Install MySQL-python package.

2. Create the script:

[root@centos6 ~]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = {‘cert': ‘/etc/mysql-ssl/client-cert.pem’, ‘key': ‘/etc/mysql-ssl/client-key.pem’}
conn = MySQLdb.connect(host=’127.0.0.1′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()

3. Test it:

[root@centos6 ~]# python mysql-ssl.py
(‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)

Notes

Alternative local SSL connection setup
If you connect locally to the server enabled for SSL you can also establish a secure connection this way:
1. Create ca.pem:

cd /etc/mysql-ssl/
cat server-cert.pem client-cert.pem > ca.pem

2. Have only the following ssl- lines in /etc/my.cnf under [client]:

# SSL
ssl-ca=/etc/mysql-ssl/ca.pem


Error with “ssl-ca” on local connections
If you left the line “ssl-ca=/etc/mysql-ssl/ca-cert.pem” under [client] section in /etc/my.cnf on the server enabled for SSL and try to establish local SSL connection, you will get “ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)”.

Discrepancy in documentation
<a rel="nofollow" href="http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html" target="_blank">http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html</a> says “A client can connect securely like this: shell> mysql –ssl-ca=ca-cert.pem” which does not work with “REQUIRE SSL”. You still have to supply the client cert and key for any or a combined client+server cert for a local secure connection.

ssl-cipher=CAMELLIA128-SHA

You're using the old MySQL extension ("mysql_connect"), which is no longer under development (<a href="http://php.net/manual/en/mysqlinfo.api.choosing.php">maintenance only</a>). Since you're using PHP 5, you may want to use <a href="http://php.net/manual/en/book.mysqli.php">MySQLi</a>, the MySQL Improved Extension. Among other things, it has an object-oriented interface, support for prepared/multiple statements and has enhanced debugging capabilities. You can read more about converting to MySQLi <a href="http://forge.mysql.com/wiki/Converting_to_MySQLi">here</a>; more about the mysqli class itself <a href="http://www.php.net/manual/en/class.mysqli.php">here</a>.</p>

Here is some sample code that may help you get started:

<?php ini_set ('error_reporting', E_ALL); ini_set ('display_errors', '1'); error_reporting (E_ALL|E_STRICT);

$db = mysqli_init(); mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$db->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL); $link = mysqli_real_connect ($db, 'ip', 'user', 'pass', 'db', 3306, NULL, MYSQLI_CLIENT_SSL); if (!$link) {

   die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");

} else {

   $res = $db->query('SHOW TABLES;');
   print_r ($res);
   $db->close();

} ?>

If <a href="http://www.php.net/manual/en/ref.pdo-mysql.php">PDO_MYSQL</a> is really what you want, then you need to do something like this:

<?php $pdo = new PDO('mysql:host=ip;dbname=db', 'user', 'pass', array(

   PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql/ssl/client-key.pem',
   PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/client-cert.pem',
   PDO::MYSQL_ATTR_SSL_CA    =>'/etc/mysql/ssl/ca-cert.pem'
   )

); $statement = $pdo->query("SHOW TABLES;"); $row = $statement->fetch(PDO::FETCH_ASSOC); echo htmlentities($row['_message']); ?>

However, only recent versions of PHP have SSL support for PDO, and SSL options are silently ignored in (at least) version 5.3.8: see the <a href="https://bugs.php.net/bug.php?id=55870">bug report</a>.

Good luck!

sudo yum remove php-mysql sudo yum install php-mysqlnd sudo reboot