This tutorial use for you want to connect your app, web app, or desktop application from remote. by default MySQL or MariaDB connect via TCP/IP( mean it not encrypted). I hope that this would help you for this case.
assume that directory mysqlkeys for storing all serverkey and clientkey
1. Access to directory /var/lib/mysql/ (because we have plan to store all key in this path)
#cd /var/lib/mysql/
2. Create directory mysqlkeys and give permsion of mysqlkeys to mysql user and group
#mkdir mysqlkeys #chown -Rf mysql. mysqlkeys
3. Access to directory mysqlkeys
#cd mysqlkeys
4. Run the following commands to create the CA keys:
#openssl genrsa 2048 > ca-key.pem #openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem (filling information of certicate)
5. Run the following commands to create the server SSL key and certificate.
#openssl genrsa 2048 > server-key.pem (optional) #openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem (filling information of certificate) #openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem #openssl rsa -in server-key.pem -out server-key.pem
3. Run the following commands to create the client SSL key and certificate:
#openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem (filling information of certificate) #openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem #openssl rsa -in client-key.pem -out client-key.pem
4. Run the following command to update the file permissions of the /mysql_keys directory and its files:
#chown -Rf mysql. *.pen ( give permission mysql user and group for directory all file *.pem)
5. Open the /etc/my.cnf file with your preferred text editor.
#cd /etc/ #vim my.cnf
6. Insert the following lines in the [mysql] section of the my.cnf file:
[mysqld] ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=/mysql_keys/ca-cert.pem ssl-cert=/mysql_keys/server-cert.pem ssl-key=/mysql_keys/server-key.pem
8. enable remote access
[mysqld] bind-address = * or (your server ip address) #skip-networking
7. Insert the following lines in the [client] section of the my.cnf file: ( incase you don’t have tag [client] you can add this line)
[client] ssl-cert=/mysql_keys/client-cert.pem ssl-key=/mysql_keys/client-key.pem
8. Save your changes to the /etc/my.cnf file and exit your text editor by press key esc –> :wq
9.restart mysql in CentOS 7
#systemctl restart mysql #systemctl status mysql
10. check ssl status in mysql
#mysql -u root -p Enter password: mysql> STATUS; SSL: Cipher in use is DHE-RSA-AES256-SHA
11. Testing from Client Software HeidiSQL
11.1
Configure MySQL Connection in HeidiSQL
11.2
Tick option Use SSL in HeidiSQL
11.3
Re-check make sure that the connect is on SSL
