Setup for SSL MySQL Enterprise Database Version 5.7.17
Title: Procedure for MySQL Database Installation ( 5.7.xx – Enterprise) and setup for SSL on RHEL
Purpose of the Document
Procedure for MySQL Database Installation ( 5.7.xx – Enterprise) and setup for SSL on RHEL
Procedure
=====================================================================
MySQL Database Installation –RHEL 7.x – DB Version 5.7.x – Enterprise Edition
=====================================================================
The below procedure is related to MySQL Database Installation Version 5.7.x on RHEL 7.x. ( Enterprise Edition)
(1) Check the OS version of RHEL by using below command.
[root@DB_Server ~]# cat /etc/redhat-release
(2) Create local group for dba
groupadd -g 1020 dba
(3) Create local user for mysql
useradd -g dba -u 888 -d /export/home/mysql -m mysql or useradd -g dba -u 888 -d /data/mysql -m mysql or useradd -g dba -u 888 -d /u01/app/mysql -m mysql
(4) Set the password for mysql user .Password is need to set same as username (mysql) : i.e passwd mysql
(5) Copy the s/w ( RPM’s) from local machine to server as a mysql user through winscp.
(6) After copy the s/w in to mysql home directory run gunzip and tar commands for extracting the files.
gunzip <bundle software.gzip> tar -xvf <filename.tar>
(7) Go to unzip folder then install Mysql Database by using RPM.We should install below RPM’s for MySQL Database 5.7.x
rpm -ivh <mysql-commercial-common>
rpm -ivh <mysql-commercial-client>
rpm -ivh <mysql-commercial-server>
rpm -ivh <mysql-commercial-libs>
Error : - While installing above packages if you get any below error ,Follow the steps and again re-run command for installation packages.
file /usr/share/mysql/charsets/Index.xml from install of mysql-commercial-common-5.7.21-1.1.el7.x86_64 with file from package MariaDB-common-10.1.20-1.el7.centos.x86_64
Solution : -
Run below command and see what are all packages installed
rpm -qa | grep -i MariaDB
MariaDB-common-10.1.20-1.el7.centos.x86_64
MariaDB-shared-10.1.20-1.el7.centos.x86_64
Remove above two packages.
rpm -e MariaDB-common-10.1.20-1.el7.centos.x86_64 MariaDB-shared-10.1.20-1.el7.centos
error: Failed dependencies:
libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64
libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64
rpm -e MariaDB-common-10.1.20-1.el7.centos.x86_64 MariaDB-shared-10.1.20-1.el7.centos --nodeps
(8) As a root user, we should run below command to install all meta data tables which is related to MySQL Database.
(i) Switch the user as a mysql : su - mysql
(ii) Go to /usr/sbin folder and run below command to install meta data tables of Mysql.
/usr/sbin/mysqld --initialize
Note : Automatically all meta data tables will create under /var/lib/mysql folder.
(iii) Start the Mysql Database by using below command as a root user from command line.
systemctl status mysqld
(iv) Here no need to set the password for mysql super user “root” .Please check the default password of mysql super user by using below command.
grep 'temporary password' /var/log/mysqld.log
From above command, you will get random password while installing the mysql database.
(v) Then connect to the mysql database from command line as a root user
mysql -u root Password : < give temporary password >
(vi) Please run below commands after connect mysql database.
mysql> flush privileges;
mysql> select version();
mysql> show databases;
mysql> use mysql; mysql> select user,host, authentication_string from user; →make sure there are no users that are blank
mysql> update user set authentication_string=PASSWORD('password') where user='root' and host='DB_Server.hostname.com'; → change the host name for each host.
mysql> flush privileges;
mysql> insert into user (host,user,password,select_priv) values ('%','root',password('password'),'Y');
mysql> flush privileges;
mysql> grant all on *.* to 'root'@'%' with grant option;
mysql> flush privileges;
mysql> select user,host, authentication_string from user; → make sure all the changes you have done are available.
Now exit and login again mysql with new password and check.
mysql -u root -p
Password : < new password>
(vii) After login in to mysql with new password you should ALTER the root
password for mysql by using below commands.
mysql>alter user 'root'@'localhost' identified by ‘root'; flush privileges;
Or
SET PASSWORD=PASSWORD('root'); flush privileges;
After login in to mysql with new password you should ALTER the root password for mysql by using below commands.
mysql> alter user 'root'@'localhost' identified by 'Root@2016';
Note : Here why we given Root@2016 means ,validate_plugin was installed automatically time of DB installation. So we should give users password is lower case ,upper case ,special symbols and numeric values.
(viii) Now login in to new password.
Edit /etc/my.cnf
# These are commonly set, remove the # and set as required.
basedir = /usr
datadir = /var/lib/mysql
port = 3306
server_id = 1
socket = /var/lib/mysql/mysql.sock
Under the tab [mysqld] , Add these two lines
character_set_server = utf8
collation_server = utf8_general_ci
(ix) now to verify the current settings login as root user root/password of mysql user
mysql –u root –p
mysql> show variables like '%char%';
mysql> show variables like '%collat%';
(x) Now shutdown the database and start it à for this login as root.
systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld
systemctl status mysqld
systemctl {start|stop|restart|status} mysqld
(xi) Now connect to mysql database using linux mysql user to check the
changes.
mysql –u root –p
mysql> show variables like '%char%';
mysql> show variables like '%collat%';
It should show as utf8 on all the values.
=====================================================================
CA Certificates From MySQL Server Level
=====================================================================
Create new directory under /etc folder and create the CA certificate
[root@DB_Server New_CN_CHANGES]# pwd
/etc/SSL_CERT/New_CN_CHANGES
[root@DB_Server New_CN_CHANGES]# ls -ltr [root@DB_Server New_CN_CHANGES]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
.+++
.........+++
e is 65537 (0x10001)
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
[root@DB_Server New_CN_CHANGES]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.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) [XX]:IN
State or Province Name (full name) []:KA
Locality Name (eg, city) [Default City]:BA
Organization Name (eg, company) [Default Company Ltd]:Company_Name
Organizational Unit Name (eg, section) []:CLOUD
Common Name (eg, your name or your server's DB_Server) []:MyCA
Email Address []:
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
=====================================================================
Server Certificates - From MySQL Server Level
=====================================================================
[root@DB_Server New_CN_CHANGES]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
.............................................................+++
...+++writing new private key to '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) [XX]:IN
State or Province Name (full name) []: KA
Locality Name (eg, city) [Default City]: BA
Organization Name (eg, company) [Default Company Ltd]: Company_Name
Organizational Unit Name (eg, section) []: CLOUD
Common Name (eg, your name or your server's DB_Server) []: MySQL Database host Name
Email Address []:
Please enter the following 'extra' attributes to be sent with your certificate request
A challenge password []:root123
An optional company name []:
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1704 Feb 1 12:12 server-key.pem
[root@DB_Server New_CN_CHANGES]# openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
[root@DB_Server New_CN_CHANGES]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=IN/ST=KA/L=BA/O=Company_Name/OU=CLOUD/CN= MySQL Database host Name
Getting CA Private Key
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:14 server-cert.pem
=====================================================================
Client Certificates – From MySQL Server Level
=====================================================================
[root@DB_Server New_CN_CHANGES]# openssl req -newkey rsa:2048 -days 3600 -nodes -
keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
..........................................................................................................................................
writing new private key to '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) [XX]:IN
State or Province Name (full name) []:KA
Locality Name (eg, city) [Default City]:BA
Organization Name (eg, company) [Default Company Ltd]:Company_Name
Organizational Unit Name (eg, section) []:CLOUD
Common Name (eg, your name or your server's DB_Server) []:MyClient
Email Address []:
Please enter the following 'extra' attributes to be sent with your certificate request
A challenge password []:root123
An optional company name []:
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:14 server-cert.pem
-rw-r--r-- 1 root root 1704 Feb 1 12:18 client-key.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:18 client-req.pem
[root@DB_Server New_CN_CHANGES]# openssl rsa -in client-key.pem -out client-key.pem writing RSA key
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:14 server-cert.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:18 client-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:19 client-key.pem
[root@DB_Server New_CN_CHANGES]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=IN/ST=KA/L=BA/O=Company_Name/OU=CLOUD/CN=MyClient
Getting CA Private Key
[root@DB_Server New_CN_CHANGES]# pwd
/etc/SSL_CERT/New_CN_CHANGES
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:14 server-cert.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:18 client-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:19 client-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:19 client-cert.pem
=====================================================================
Verification from MySQL Server Level
=====================================================================
[root@DB_Server New_CN_CHANGES]# pwd
/etc/SSL_CERT/New_CN_CHANGES
[root@DB_Server New_CN_CHANGES]# ls -ltr
-rw-r--r-- 1 root root 1679 Feb 1 12:08 ca-key.pem
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:12 server-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:14 server-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:14 server-cert.pem
-rw-r--r-- 1 root root 1025 Feb 1 12:18 client-req.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:19 client-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:19 client-cert.pem
[root@DB_Server New_CN_CHANGES]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
=====================================================================
Adding Certifications path in to my.cnf file and restart the Database from MySQL Server level
=====================================================================
[root@DB_Server ~]# vi /etc/my.cnf
[root@DB_Server ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@DB_Server ~]# cat /etc/my.cnf | grep -i ssl
#ssl-ca=/etc/SSL_CERT/ca-cert.pem
#ssl-cert=/etc/SSL_CERT/server-cert.pem
#ssl-key=/etc/SSL_CERT/server-key.pem
#ssl_capath = /etc/SSL_CERT/New_CN_CHANGES
ssl-ca=/etc/SSL_CERT/New_CN_CHANGES/ca.pem
ssl-cert=/etc/SSL_CERT/New_CN_CHANGES/server-cert.pem
ssl-key=/etc/SSL_CERT/New_CN_CHANGES/server-key.pem
=====================================================================
Checking the values and variables from MySQL Server level
=====================================================================
[root@DB_Server ~]# mysql -u root -p
Enter password: root123
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4.Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 6 min 5 sec
Threads: 1 Questions: 17 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.046
--------------
mysql>show variables like 'ssl_%';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| ssl_ca | /etc/SSL_CERT/New_CN_CHANGES/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/SSL_CERT/New_CN_CHANGES/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/SSL_CERT/New_CN_CHANGES/server-key.pem |
+---------------+----------------------------------------------+
7 rows in set (0.01 sec)
mysql> show variables like '%CA%';
+--------------------------------------+-----------------------------
| Variable_name | Value |
+--------------------------------------+-----------------------------
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| default_authentication_plugin | mysql_native_password |
| group_concat_max_len | 1024 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_lru_scan_depth | 1024 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_replication_delay | 0 |
| innodb_stats_auto_recalc | ON |
| innodb_undo_log_truncate | OFF |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| local_infile | ON |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| ssl_ca | /etc/SSL_CERT/New_CN_CHANGES/ca.pem |
| ssl_capath | |
| stored_program_cache | 256 |
| table_definition_cache | 1400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 16 |
| thread_cache_size | 9 |
+--------------------------------------+-----------------------------
38 rows in set (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
+---------------+-----------------------+
1 row in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Ssl_version';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_version | |
+---------------+-------+
1 row in set (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'have_%ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
+---------------+-------+
2 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name | Value |
+-----------------------+--------------------------+
| Ssl_server_not_after | Dec 11 06:44:52 2026 GMT |
| Ssl_server_not_before | Feb 1 06:44:52 2017 GMT |
+-----------------------+--------------------------+
2 rows in set (0.01 sec)
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%ssl%';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/SSL_CERT/New_CN_CHANGES/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/SSL_CERT/New_CN_CHANGES/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/SSL_CERT/New_CN_CHANGES/server-key.pem |
+---------------+----------------------------------------------+
9 rows in set (0.00 sec)
=====================================================================
Create SSL Users / Non SSL Users from MySQL Server Level
=====================================================================
From Database level connect to the mysql and create SSL / Non SSL users by using below commands.
GRANT ALL PRIVILEGES ON SchemaName.* TO 'UserName'@'%' IDENTIFIED BY 'Password' REQUIRE SSL; flush privileges; ===➔ SSL User Creation
GRANT ALL PRIVILEGES ON SchemaName.* TO 'UserName'@'%' IDENTIFIED BY 'Password'; flush privileges; ===➔ Non SSL User Creation
=====================================================================
Test connection from MySQL Server Level : - DB_Server - Within the server
=====================================================================
[root@DB_Server ~]# mysql --ssl-ca=/etc/SSL_CERT/New_CN_CHANGES/ca.pem --ssl-cert=/etc/SSL_CERT/New_CN_CHANGES/client-cert.pem --ssl-key=/etc/SSL_CERT/New_CN_CHANGES/client-key.pem -u devuser -p
Enter password: root123
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Connection id: 5
Current database:
Current user: devuser@localhost
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 hour 13 min 43 sec
Threads: 1 Questions: 22 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.004
--------------
mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
+---------------+-----------------------+
1 row in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Ssl_version';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Ssl_version | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
1 row in set (0.01 sec)
=====================================================================
Test connection from client to MySQL Database - Client_Host_Name (Client) to DB_Server (Server)
=====================================================================
The client needs these files:
ca.pem
client-cert.pem
client-key.pem
Note : The two ca.pem files MUST be the same.
(1) Copied ca.pem,client-cert.pem and client-key.pem in to client machine /etc/SSL_CERT path
[root@Client_Host_Name CN_Changes]# pwd
/etc/SSL_CERT/CN_Changes
[root@Client_Host_Name CN_Changes]# ls -ltr
-rw-r--r-- 1 root root 1289 Feb 1 12:09 ca.pem
-rw-r--r-- 1 root root 1675 Feb 1 12:19 client-key.pem
-rw-r--r-- 1 root root 1168 Feb 1 12:19 client-cert.pem
[root@Client_Host_Name ~]# mysql --ssl-ca=/etc/SSL_CERT/CN_Changes/ca.pem --ssl-cert=/etc/SSL_CERT/CN_Changes/client-cert.pem --ssl-key=/etc/SSL_CERT/CN_Changes/client-key.pem -h DB_Server -u devuser -p
Enter password: devuser
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6.Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Connection id: 6
Current database:
Current user: devuser@10.65.43.123
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version: 10
Connection: DB_Server via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hour 27 min 44 sec
Threads: 1 Questions: 30 Slow queries: 0 Opens: 110 Flush tables: 1 Open tables: 103 Queries per second avg: 0.005
--------------
mysql> SHOW SESSION STATUS LIKE 'Ssl_version';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Ssl_version | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DEV |
+--------------------+
2 rows in set (0.00 sec)
From ODBC we are able to connecting with VERIFY-CA option using ODBC 5.3
Comments
Post a Comment