Expat-IT Tech Bits

Home

Contact

Links

Search this site:

Categories:

/ (287)
  Admin/ (122)
    Apache/ (10)
      HTTPS-SSL/ (4)
      PHP/ (3)
      performance/ (2)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (6)
    Monitoring/ (2)
      munin/ (2)
    SSH/ (6)
    SSL/ (1)
    Samba/ (1)
    VPN-options/ (6)
      OpenVPN/ (1)
      SSH-Proxy/ (3)
      Tinc/ (1)
      sshuttle/ (1)
    backups/ (17)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (2)
    commandLine/ (24)
      files/ (8)
      misc/ (10)
      network/ (6)
    crontab/ (1)
    databases/ (15)
      MSSQL/ (2)
      MySQL/ (8)
      Oracle/ (3)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (11)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (7)
      puppet/ (1)
    iptables/ (3)
    tripwire/ (1)
    virtualization/ (9)
      VMware/ (1)
      virtualBox/ (8)
  Coding/ (14)
    bash/ (1)
    gdb/ (1)
    git/ (3)
    php/ (5)
    python/ (4)
      Django/ (2)
  Education/ (1)
  Hosting/ (27)
    Amazon/ (18)
      EBS/ (3)
      EC2/ (10)
      S3/ (1)
      commandline/ (4)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (30)
    Android/ (1)
    Awesome/ (3)
    CPUfreq/ (1)
    China/ (2)
    Debian/ (8)
      APT/ (3)
      WPA/ (1)
    audio/ (1)
    encryption/ (3)
    fonts/ (1)
    misc/ (6)
    remoteDesktop/ (1)
    router-bridge/ (3)
  SW/ (45)
    Micro$soft/ (1)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (28)
      Drupal/ (9)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (7)
      WebERP/ (2)
      WordPress/ (1)
      eGroupware/ (1)
    chat/ (1)
    email/ (1)
    fileSharing/ (2)
      btsync/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (15)
    IMchat/ (2)
    circumvention/ (2)
    cryptoCurrency/ (1)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (2)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (14)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Archives:

  • 2016/07
  • 2016/05
  • 2016/02
  • 2016/01
  • 2015/12
  • 2015/11
  • 2015/06
  • 2015/01
  • 2014/12
  • 2014/11
  • 2014/10
  • 2014/09
  • 2014/07
  • 2014/04
  • 2014/02
  • 2014/01
  • 2013/12
  • 2013/10
  • 2013/08
  • 2013/07
  • 2013/06
  • 2013/05
  • 2013/04
  • 2013/02
  • 2013/01
  • 2012/12
  • 2012/10
  • 2012/09
  • 2012/08
  • 2012/07
  • 2012/06
  • 2012/05
  • 2012/04
  • 2012/03
  • 2012/01
  • 2011/12
  • 2011/11
  • 2011/10
  • 2011/09
  • 2011/08
  • 2011/07
  • 2011/06
  • 2011/05
  • 2011/04
  • 2011/02
  • 2010/12
  • 2010/11
  • 2010/10
  • 2010/09
  • 2010/08
  • 2010/07
  • 2010/06
  • 2010/05
  • 2010/04
  • 2010/03
  • 2010/02
  • 2010/01
  • 2009/12
  • 2009/11
  • 2009/10
  • 2009/09
  • 2009/08
  • 2009/07
  • 2009/06
  • 2009/05
  • 2009/04
  • 2009/03
  • 2009/02
  • 2009/01
  • 2008/12
  • 2008/11
  • 2008/10
  • 2008/09
  • Subscribe XML RSS Feed

    Creative Commons License
    This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
    PyBlosxom

    This site has no ads. To help with hosting, crypto donations are accepted:
    Bitcoin: 1JErV8ga9UY7wE8Bbf1KYsA5bkdh8n1Bxc
    Zcash: zcLYqtXYFEWHFtEfM6wg5eCV8frxWtZYkT8WyxvevzNC6SBgmqPS3tkg6nBarmzRzWYAurgs4ThkpkD5QgiSwxqoB7xrCxs

    Thu, 06 Nov 2014


    /Admin/databases/MySQL: Setup MySQL to use SSL for Remote Connections

    Some good advice in an age of people, companies, and governments avaricious to acquire / store / use / sell your personal information: use encryption wherever possible when communicating over networks.

    Here[1] is a nice concise guide to the basics of getting SSL working on MySQL[2].

    First login to MySQL and check for SSL support:

    # mysql -p
    Enter password:
    mysql> show variables like '%ssl%';

    You should see "DISABLED" at this point, since you have not set it up yet. (If the response says anything other then "DISABLED" or "YES", then your MySQL server has probably been compiled without SSL support. Not a problem on Debian....)

    Then Enable SSL Support in the Server:

    FOR MySQL 5.5 YOU MUST USE A VERSION OF OPENSSL LESS THAN 1.0 TO CREATE THE FOLLOWING CERTIFICATES.[3] Otherwise, when you try to login with the MySQL client using SSL, you will see this kind of error:

    # mysql -uuser -ppassword --ssl-ca=/etc/mysql/ca-cert.pem
    ERROR 2026 (HY000): SSL connection error: protocol version mismatch

    I found an Ubuntu Lucid server which had a sufficiently old version of openssl to do the job.

    First create the CA certificate:

    cd /etc/mysql
    openssl genrsa 2048 > ca-key.pem
    openssl req -new -x509 -nodes -days 3601 -key ca-key.pem > ca-cert.pem

    Now create the server certificate:

    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout mysql-server-key.pem > mysql-server-req.pem
    openssl x509 -req -in mysql-server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > mysql-server-cert.pem

    Now fix up the permissions of the SSL certs if necessary, and add this to the [mysqld] block of your /etc/mysql/my.cnf:

    ssl-ca=/etc/mysql/ca-cert.pem
    ssl-cert=/etc/mysql/mysql-server-cert.pem
    ssl-key=/etc/mysql/mysql-server-key.pem

    Note that client certificates are not necessary unless you WANT the server to authenticate the client. Also note that on Debian MySQL logging seems to go to syslog, not to the visible /var/log/mysql* log files.

    After restarting MySQL,

    mysql> show variables like 'have_ssl';

    should result in a "YES".

    Now Get MySQL clients Working:

    Test a client using SSL on MySQL localhost. Create a temporary user for the test:

    mysql> GRANT ALL on databasename.* TO 'ssluser'@'localhost' IDENTIFIED BY 'thispassword' REQUIRE SSL;
    From a terminal on the MySQL server, try logging in with this user:
    mysql -ussluser -p --ssl-ca=/etc/mysql/cacert.pem

    Once logged in, issue this MySQL command:

    mysql> SHOW STATUS LIKE 'Ssl_cipher';

    If you get anything other than a blank in the 'Value' column, SSL is working! Delete the test user:

    mysql> DELETE FROM mysql.user WHERE user='ssluser' and host='localhost';
    And still on the MySQL server, create a user for remote access, from a specific IP address only:
    mysql> GRANT ALL on databasename.* TO 'SSLremote'@'153.129.49.127' IDENTIFIED BY 'thispassword' REQUIRE SSL;
    On the remote client (IP address 153.129.49.127) presumably your desktop, try to login over SSL:
    mysql -uSSLremote -pthispassword -hwww.mysqlserverhost.com --ssl-ca=/home/user/cacert.pem

    If it works, mission accomplished!

    [1] http://chartio.com/docs/datasources/connections/mysql-ssl
    [2] https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html
    [3] http://www.tokiwinter.com/secure-mysql-replication-over-ssl/

    posted at: 03:50 | path: /Admin/databases/MySQL | permanent link to this entry