Php MySQL SSL холболт - Түүх

06:00, 19 Наймдугаар сар 2015 дээрх Almas

2015-08-19T06:00:13Z

05:59, 19 Наймдугаар сар 2015 дээрх Almas

2015-08-19T05:59:40Z

Almas: Шинэ хуудас:

Setup SSL on MySQL

1. Generate SSL certificates according to the

2015-08-19T05:58:29Z

Шинэ хуудас: <h1 id="setup">Setup SSL on MySQL</h1>1. Generate SSL certificates according to the <a rel="nofollow" href="http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html" …

Шинэ хуудас

<h1 id="setup">Setup SSL on MySQL</h1>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: <code> # 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 </code>4. Restart MySQL.5. Create an user to permit only SSL-encrypted connection:<blockquote>GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;</blockquote><h1 id="conn">Establish secure connection from console</h1>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]: <code> # SSL ssl-cert=/etc/mysql-ssl/client-cert.pem ssl-key=/etc/mysql-ssl/client-key.pem </code>3. Test a secure connection:<blockquote>[root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL SSL: Cipher in use is DHE-RSA-AES256-SHA</blockquote><h1 id="repl">Setup SSL replication</h1>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:<blockquote>GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;</blockquote>3. Change master options and restart slave:<blockquote>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</blockquote><h1 id="php">Establish secure connection from PHP</h1>1. Install php and php-mysql packages. I use the version >=5.3.3, otherwise, it may not work.2. Create the script:<blockquote>[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);</blockquote>3. Test it:<blockquote>[root@centos6 ~]# php mysqli-ssl.php Array ( [0] => Ssl_cipher [1] => DHE-RSA-AES256-SHA )</blockquote><h1 id="python">Establish secure connection from Python</h1>1. Install MySQL-python package.2. Create the script:<blockquote>[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()</blockquote>3. Test it:<blockquote>[root@centos6 ~]# python mysql-ssl.py (‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)</blockquote><h1 id="nodes">Notes</h1>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:<blockquote>cd /etc/mysql-ssl/ cat server-cert.pem client-cert.pem > ca.pem</blockquote>2. Have only the following ssl- lines in /etc/my.cnf under [client]: <code> # SSL ssl-ca=/etc/mysql-ssl/ca.pem </code>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>.

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

<pre style="" class="default prettyprint prettyprinted"><code><?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();
}
?></code></pre>

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:

<pre style="" class="default prettyprint prettyprinted"><code><?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']);
?></code></pre>

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

[[Ангилал:Linux]][[Ангилал:Сисадмин]]

@@ 9-р мөр: / 9-р мөр: @@
 <p>Here is some sample code that may help you get started:</p>
-<pre style="" class="default prettyprint prettyprinted"><span class="pun">&lt;?</span><span class="pln">php
+<span class="pun">&lt;?</span><span class="pln">php
 ini_set </span><span class="pun">(</span><span class="str">'error_reporting'</span><span class="pun">,</span><span class="pln"> E_ALL</span><span class="pun">);</span><span class="pln">
 ini_set </span><span class="pun">(</span><span class="str">'display_errors'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1'</span><span class="pun">);</span><span class="pln">
@@ 27-р мөр: / 27-р мөр: @@
      $db</span><span class="pun">-&gt;</span><span class="pln">close</span><span class="pun">();</span><span class="pln">
 </span><span class="pun">}</span><span class="pln">
-</span><span class="pun">?&gt;</span></pre>
+</span><span class="pun">?&gt;</span>
 <p>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:</p>
-<pre style="" class="default prettyprint prettyprinted"><span class="pun">&lt;?</span><span class="pln">php
+<span class="pun">&lt;?</span><span class="pln">php
 $pdo </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> PDO</span><span class="pun">(</span><span class="str">'mysql:host=ip;dbname=db'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'user'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'pass'</span><span class="pun">,</span><span class="pln"> array</span><span class="pun">(</span><span class="pln">
      PDO</span><span class="pun">::</span><span class="pln">MYSQL_ATTR_SSL_KEY    </span><span class="pun">=&gt;</span><span class="str">'/etc/mysql/ssl/client-key.pem'</span><span class="pun">,</span><span class="pln">
@@ 41-р мөр: / 41-р мөр: @@
 $row </span><span class="pun">=</span><span class="pln"> $statement</span><span class="pun">-&gt;</span><span class="pln">fetch</span><span class="pun">(</span><span class="pln">PDO</span><span class="pun">::</span><span class="pln">FETCH_ASSOC</span><span class="pun">);</span><span class="pln">
 echo htmlentities</span><span class="pun">(</span><span class="pln">$row</span><span class="pun">[</span><span class="str">'_message'</span><span class="pun">]);</span><span class="pln">
-</span><span class="pun">?&gt;</span></pre>
+</span><span class="pun">?&gt;</span>
 <p>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>.</p>

@@ 9-р мөр: / 9-р мөр: @@
 <p>Here is some sample code that may help you get started:</p>
-<pre style="" class="default prettyprint prettyprinted"><code><span class="pun">&lt;?</span><span class="pln">php
+<pre style="" class="default prettyprint prettyprinted"><span class="pun">&lt;?</span><span class="pln">php
 ini_set </span><span class="pun">(</span><span class="str">'error_reporting'</span><span class="pun">,</span><span class="pln"> E_ALL</span><span class="pun">);</span><span class="pln">
 ini_set </span><span class="pun">(</span><span class="str">'display_errors'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1'</span><span class="pun">);</span><span class="pln">
@@ 27-р мөр: / 27-р мөр: @@
      $db</span><span class="pun">-&gt;</span><span class="pln">close</span><span class="pun">();</span><span class="pln">
 </span><span class="pun">}</span><span class="pln">
-</span><span class="pun">?&gt;</span></code></pre>
+</span><span class="pun">?&gt;</span></pre>
 <p>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:</p>
-<pre style="" class="default prettyprint prettyprinted"><code><span class="pun">&lt;?</span><span class="pln">php
+<pre style="" class="default prettyprint prettyprinted"><span class="pun">&lt;?</span><span class="pln">php
 $pdo </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> PDO</span><span class="pun">(</span><span class="str">'mysql:host=ip;dbname=db'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'user'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'pass'</span><span class="pun">,</span><span class="pln"> array</span><span class="pun">(</span><span class="pln">
      PDO</span><span class="pun">::</span><span class="pln">MYSQL_ATTR_SSL_KEY    </span><span class="pun">=&gt;</span><span class="str">'/etc/mysql/ssl/client-key.pem'</span><span class="pun">,</span><span class="pln">
@@ 41-р мөр: / 41-р мөр: @@
 $row </span><span class="pun">=</span><span class="pln"> $statement</span><span class="pun">-&gt;</span><span class="pln">fetch</span><span class="pun">(</span><span class="pln">PDO</span><span class="pun">::</span><span class="pln">FETCH_ASSOC</span><span class="pun">);</span><span class="pln">
 echo htmlentities</span><span class="pun">(</span><span class="pln">$row</span><span class="pun">[</span><span class="str">'_message'</span><span class="pun">]);</span><span class="pln">
-</span><span class="pun">?&gt;</span></code></pre>
+</span><span class="pun">?&gt;</span></pre>
 <p>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>.</p>