Configure MySQL Remote access with SSL Certificate in CentOS7

October 15th, 2017 by Nov Piseth No comments »

 
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

The mail server could not deliver mail to mymail@mydomain.com. The account or domain may not exist, they may be blacklisted, or missing the proper dns entries

June 6th, 2017 by Nov Piseth No comments »

I’m using CentOS with cPanel accidentally I got this error in my log.

I just try to check the following.

  1. make sure MX record is correct
  2. rDNS is working fine (optional)
  3. add mydomain.com into /etc/localdomain

and it working fine for me.

add time zone in centos 7 and time greater than local time 1 hour

May 17th, 2017 by Nov Piseth No comments »

When I use CentOS 7 and change localtime by command:

#timedatectl list-timezones
#timedatectl set-timezone Asia/Phnom_Penh
and the time show as below:
[root@linux ~]# timedatectl
Local time: Wed 2017-05-17 11:25:12 +07
Universal time: Wed 2017-05-17 04:25:12 UTC
RTC time: Wed 2017-05-17 04:25:12
Time zone: Asia/Phnom_Penh (+07, +0700)
NTP enabled: no
NTP synchronized: no
RTC in local TZ: no
DST active: n/a

but the real time in local country is Wed 2017-05-17 10:25:12 +07
I was found that I did not install chrony service.

# yum install -y chrony
#systemctl start chronyd
#systemctl  chronyd

after that I get the real time fit system and local country time as below command

[root@linux ~]# timedatectl
Local time: Wed 2017-05-17 11:25:12 +07
Universal time: Wed 2017-05-17 04:25:12 UTC
RTC time: Wed 2017-05-17 04:25:12
Time zone: Asia/Phnom_Penh (+07, +0700)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a

I hope this shared help you all.

Outlook Could’n access to IMAP/POP3

May 1st, 2017 by Nov Piseth No comments »

Email Client (outlook) Could not access to IMAP or POP3 (CentOS7 with Virtualmin)
I was found the problem when I installed CentOS 7.x 64bits with Virtualmin
( please search wiki for keywork virtualmin for more detail)
Error Found:

I was check the log on server with email log I found below error:

Apr 27 06:16:20 box185 dovecot: imap-login:
Login: user=<piseth.mydomain.com>, method=PLAIN,
rip=xx.xxx.xx.xx, lip=yy.yyy.yyy.yyy, mpid=5259, TLS, session=<ZTcbOB5OtQAxnC8O>
Apr 27 06:16:20 box185 dovecot: imap: Error: user piseth.mydomain.com:
Mail access for users with UID 501 not
permitted (see first_valid_uid in config file, uid from userdb lookup).
Apr 27 06:16:20 box185 dovecot: imap: Error: Invalid user settings.
Refer to server log for more information.

Solution:
I was logged in to root account of virtualmin and Click Webmin à
Servers à Dovecot IMAP/POP3 Server à User Login
and Options at option call Minimum valid UID change from 1000 to Default.

Note:

I think CentOS 6.x with Virtualmin have no problem.
this is my solution but you can find other solution better than this.

mysql gone away when import

March 24th, 2017 by Nov Piseth No comments »

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