Connect to MySQL Server 5.7 from PHP 7.0 using SSL

It took me from late morning to evening to be able to connect to MySQL Server 5.7 from PHP 7.0 over SSL on an EC2 machine with Amazon Linux OS image. There were many steps and each had its own challenge. Below I am mentioning the steps and how I worked around the problems.

Step 1: Upgrading PHP from 5.6 to 7.0. First one has to remove the existing version of PHP and then install a newer version. I tried installing version 7.1 first but I ran into dependency issues like libpng15 for which the easiest way to install is to build from source. To avoid falling into this dependency cycle, I tried installing version 7.0 and this one installed smoothly.

# Remove PHP 5.6
[ec2-user@ ~]$ sudo yum remove php5*

# Install PHP 7.0
[ec2-user@ ~]$ sudo yum install php70 php70-mysqlnd php70-imap php70-gd \
    php70-pecl-memcache php70-pecl-apcu 

Step 2: Upgrading MySQL server from 5.6 to 5.7. The default repos did not have MySQL 5.7. I also could not install it by adding repos, as eventually it would fail due to absence of systemd command on Amazon Linux. These approaches would work on RHEL, CentOS and other standard distributions of Unix/Linux which have the systemd command. So MySQL 5.7 generic binary package bundle had to be downloaded from the MySQL download website, and installed manually. The downside of this approach is that it being a standalone binary package, it includes every dependency and thus it is huge, totaling 2.5G of disk space. For full details of the setup please refer to MySQL Installation page. Note that the MySQL server script is named mysql.server.

# Download 
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

# Setup
cd /usr/local
tar -zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.19-linux-glibc2.12-x86_64 mysql

# I have not mentioned all the steps before this, refer to MySQL documentation
sudo cp support-files/mysql.server /etc/init.d/mysql.server

# MySQL server automatic startup
sudo service mysql.server start
sudo chkconfig --add mysql.server

Step 3: Configuring MySQL Server for SSL. Running the mysql_ssl_rsa_setup generates the necessary certificate files in the default directory named 'data' inside the MySQL installation directory, which in my case was /usr/local/mysql. To configure the server add the certificate files configuration to my.cnf and then restart MySQL server.

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

sudo service mysql.server restart

Step 4: Connecting to MySQL Server over SSL. First I tried connecting over the command line. I first created a new user (ssl_user) in MySQL server which had access only through SSL. I tried without success to connect to the server by providing client certificate options in my.cnf. I had to give each certificate file explicitly as arguments in the command line to successfully connect with ssl_user.

# On mysql command line logged in as root
CREATE USER 'ssl_user'@'localhost' IDENTIFIED BY 'password' REQUIRE X509;

# On linux prompt to connect as ssl_user
mysql --ssl-ca=/etc/ssl/mysql/ca.pem \
    --ssl-cert=/etc/ssl/mysql/client-cert.pem \
    --ssl-key=/etc/ssl/mysql/client-key.pem \
    -u ssl_user -p

One can verify that SSL is in use after logging in by using the status command. If SSL is not being used, the SSL field will say 'Not in use', here it says 'Cipher in use is DHE-RSA-AES256-SHA'.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          35
Current database:
Current user:           ssl_user@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
...

Step 5: Connecting from PHP over SSL. I tried a lot of variations of code, but none worked. All the parameters were fine, and with those same parameters I was able to connect from command line but not from PHP. Two changes were critical for the connection. For the host address, localhost did not work, but 127.0.0.1 worked. There are detailed discussions with reasons behind this on Stackoverflow. The last parameter of the msqli::real_connect() method, namely flags had to be changed to MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT. I was earlier using MYSQLI_CLIENT_SSL. Below is the PHP code snippet which worked for me. Please note it's bad coding practice to keep passwords in plain text.