Archive for the ‘MySQL’ category

Configure MySQL Remote access with SSL Certificate in CentOS7

October 15th, 2017

 
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

Configure MySQL Connection in HeidiSQL

 

 

 

 

 

 

11.2

Tick option Use SSL in HeidiSQL

Tick option Use SSL in HeidiSQL

 

 

 

 

 

 

11.3

Re-check make sure that the connect is on SSL

Re-check make sure that the connect is on SSL

mysql gone away when import

March 24th, 2017

I was faced with this problem in my local and I’m using xampp. I was found out that┬ámax_allowed_packet

original configuration for xampp is only 1MB. After I changed the setting and to 512M.

I work smoothly for import my database file *.sql to MySQL Server in local machine. It is very

basic useful for me and also share to everyone.

please enjoy below basic configuration for general you can customize with your own.


[mysqld]
port= 3306
socket = "F:/xampp/mysql/mysql.sock"
basedir = "F:/xampp/mysql"
tmpdir = "F:/xampp/tmp"
datadir = "F:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 256M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
general_log = 1
general_log_file = 'F:\xampp\mysql\data\general_log.log'

Facebook Auto Publish Powered By : XYZScripts.com