RDS MySQL / MariaDB SSL Connection by using Workbench and command line

In this blog , i am going to explain how to connect RDS MySQL With SSL Connections from MySQL Workbench and MySQL / MariaDB client utility by using command line

--> Make sure RDS MySQL / MariaDB instance is ready for usage.

Connection Established from MySQL / MariaDB client by using cmd line

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u admin -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com
Enter password: *************
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 25.Server version: 8.0.23 Source distribution.Copyright (c) 2000, 2018, 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> show variables like '%ssl%';

+-------------------------------------+------------------------------

| Variable_name | Value

| admin_ssl_crl |

| admin_ssl_crlpath |

| admin_ssl_key |

| have_openssl | YES

| have_ssl | YES

| performance_schema_show_processlist | OFF

| ssl_ca | /rdsdbdata/rds-metadata/ca-cert.pem

| ssl_capath |

| ssl_cert | /rdsdbdata/rds-metadata/server-cert.pem

| ssl_cipher | ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384:

| ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:

| AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA:AES128-SHA:

| DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-DSS-AES256-SHA

| ssl_crl |

| ssl_crlpath |

| ssl_fips_mode | OFF

| ssl_key | /rdsdbdata/rds-metadata/server-key.pem

+-------------------------------------+------------------------------

18 rows in set (0.33 sec)

mysql> show variables like '%CA%';
+-----------------------------------------------+--------------------
| Variable_name | Value |
+-----------------------------------------------+--------------------
| admin_ssl_ca | |
| admin_ssl_capath | |
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
| default_authentication_plugin | mysql_native_password |
| group_concat_max_len | 1024 |
| group_replication_consistency | EVENTUAL |
| have_query_cache | NO |
| host_cache_size | 194 |
| innodb_dedicated_server | OFF |
| 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 | ON |
| 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 |
| replication_optimize_for_static_plugin_config | OFF |
| replication_sender_observe_commit_only | OFF |
| schema_definition_cache | 256 |
| ssl_ca | /rdsdbdata/rds-metadata/ca-cert.pem |
| ssl_capath | |
| stored_program_cache | 256 |
| stored_program_definition_cache | 256 |
| table_definition_cache | 2000 |
| table_open_cache | 4000 |
| table_open_cache_instances | 16 |
| tablespace_definition_cache | 256 |
| thread_cache_size | 8 |
+-----------------------------------------------+--------------------
44 rows in set (0.25 sec)

mysql> show variables like '%ssl%';
+-------------------------------------+------------------------------
| Variable_name | Value
+-------------------------------------+------------------------------| admin_ssl_ca |
| admin_ssl_capath |
| admin_ssl_cert |
| admin_ssl_cipher |
| admin_ssl_crl |
| admin_ssl_crlpath |
| admin_ssl_key |
| have_openssl | YES
| have_ssl | YES
| performance_schema_show_processlist | OFF
| ssl_ca | /rdsdbdata/rds-metadata/ca-cert.pem
| ssl_capath |
| ssl_cert | /rdsdbdata/rds-metadata/server-cert.pem
| ssl_cipher | ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA:AES128-SHA:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-DSS-AES256-SHA |
| ssl_crl |
| ssl_crlpath |
| ssl_fips_mode | OFF
| ssl_key | /rdsdbdata/rds-metadata/server-key.pem
+-------------------------------------+------------------------------
18 rows in set (0.33 sec)

mysql> CREATE DATABASE ssl_db;
Query OK, 1 row affected (0.29 sec)

mysql> GRANT ALL PRIVILEGES ON ssl_db.* TO 'test_ssl'@'%';
Query OK, 0 rows affected (0.21 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.28 sec)

mysql> status;
--------------
mysql Ver 8.0.11-commercial for Win64 on x86_64 (MySQL Enterprise Server - Commercial)
Connection id: 25
Current database:
Current user: admin@27.7.129.170
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 8.0.23 Source distribution
Protocol version: 10
Connection: ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 1 hour 6 min 14 sec
Threads: 3 Questions: 11761 Slow queries: 0 Opens: 262 Flush tables: 3 Open tables: 163 Queries per second avg: 2.959
--------------

Connection establish with test_ssl user which we create above commands.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u test_ssl -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com --ssl-ca C:\Users\vrao0001\Downloads\cert\ --ssl-mode=VERIFY_IDENTITY
Enter password: ********
ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u test_ssl -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com --ssl-ca C:\Users\vrao0001\Downloads\cert\us-east-1-bundle.pem --ssl-mode=VERIFY_IDENTITY
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 30.Server version: 8.0.23 Source distribution.Copyright (c) 2000, 2018, 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 8.0.11-commercial for Win64 on x86_64 (MySQL Enterprise Server - Commercial)
Connection id: 30
Current database:
Current user: test_ssl@27.7.129.170
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 8.0.23 Source distribution
Protocol version: 10
Connection: ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 1 hour 12 min 33 sec
Threads: 3 Questions: 12038 Slow queries: 0 Opens: 262 Flush tables: 3 Open tables: 163 Queries per second avg: 2.765
--------------

Now the below screen shots for MySQL / MariaDB connection from MySQL Workbench Tool.

We need to download the Corresponding Region RDS where you deployed.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

Performance schema Enabled :-

mysql> SELECT id, user, host, connection_type FROM performance_schema.threads pst INNER JOIN performance_schema.processlist isp ON pst.processlist_id=isp.id;

Empty set (0.22 sec)

We should enable performance_schema = ON in parameter group

mysql> show variables like 'performance%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema | OFF |
| performance_schema_accounts_size | 0 |
| performance_schema_digests_size | 0 |
| performance_schema_error_size | 0 |
| performance_schema_events_stages_history_long_size | 0 |
| performance_schema_events_stages_history_size | 0 |
| performance_schema_events_statements_history_long_size | 0 |
| performance_schema_events_statements_history_size | 0 |
| performance_schema_events_transactions_history_long_size | 0 |
| performance_schema_events_transactions_history_size | 0 |
| performance_schema_events_waits_history_long_size | 0 |
| performance_schema_events_waits_history_size | 0 |
| performance_schema_hosts_size | 0 |
| performance_schema_max_cond_classes | 0 |
| performance_schema_max_cond_instances | 0 |
| performance_schema_max_digest_length | 0 |
| performance_schema_max_digest_sample_age | 60 |
| performance_schema_max_file_classes | 0 |
| performance_schema_max_file_handles | 0 |
| performance_schema_max_file_instances | 0 |
| performance_schema_max_index_stat | 0 |
| performance_schema_max_memory_classes | 0 |
| performance_schema_max_metadata_locks | 0 |
| performance_schema_max_mutex_classes | 0 |
| performance_schema_max_mutex_instances | 0 |
| performance_schema_max_prepared_statements_instances | 0 |
| performance_schema_max_program_instances | 0 |
| performance_schema_max_rwlock_classes | 0 |
| performance_schema_max_rwlock_instances | 0 |
| performance_schema_max_socket_classes | 0 |
| performance_schema_max_socket_instances | 0 |
| performance_schema_max_sql_text_length | 0 |
| performance_schema_max_stage_classes | 0 |
| performance_schema_max_statement_classes | 0 |
| performance_schema_max_statement_stack | 0 |
| performance_schema_max_table_handles | 0 |
| performance_schema_max_table_instances | 0 |
| performance_schema_max_table_lock_stat | 0 |
| performance_schema_max_thread_classes | 0 |
| performance_schema_max_thread_instances | 0 |
| performance_schema_session_connect_attrs_size | 0 |
| performance_schema_setup_actors_size | 0 |
| performance_schema_setup_objects_size | 0 |
| performance_schema_show_processlist | OFF |
| performance_schema_users_size | 0 |
+----------------------------------------------------------+-------+
45 rows in set (0.21 sec)

Modified and restarted the DB instance ..

After that

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u admin -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com
Enter password: *************

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 Server version: 8.0.23 Source distribution Copyright (c) 2000, 2018, 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> show variables like 'performance%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema | ON |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 10000 |
| performance_schema_error_size | 0 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_max_cond_classes | 100 |
| performance_schema_max_cond_instances | -1 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_digest_sample_age | 60 |
| performance_schema_max_file_classes | 80 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | -1 |
| performance_schema_max_index_stat | -1 |
| performance_schema_max_memory_classes | 450 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_mutex_classes | 300 |
| performance_schema_max_mutex_instances | -1 |
| performance_schema_max_prepared_statements_instances | -1 |
| performance_schema_max_program_instances | -1 |
| performance_schema_max_rwlock_classes | 60 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| performance_schema_max_sql_text_length | 1024 |
| performance_schema_max_stage_classes | 175 |
| performance_schema_max_statement_classes | 218 |
| performance_schema_max_statement_stack | 10 |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| performance_schema_max_thread_classes | 100 |
| performance_schema_max_thread_instances | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_show_processlist | OFF |
| performance_schema_users_size | -1 |
+----------------------------------------------------------+-------+
45 rows in set (0.22 sec)

Now it's enabled ..

mysql> show create user 'test_ssl'@'%';
+--------------------------------------------------------------------
| CREATE USER for test_ssl@%
+--------------------------------------------------------------------
| CREATE USER 'test_ssl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*ADECB5326E53845F4054621F266F3B065D3C779B' REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+--------------------------------------------------------------------
1 row in set (0.22 sec)

mysql> show create user 'admin'@'%';
+--------------------------------------------------------------------
| CREATE USER for admin@%
+--------------------------------------------------------------------
| CREATE USER 'admin'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*84A39D41433A3044C57427018E7C05C80B363C48' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+--------------------------------------------------------------------
1 row in set (0.21 sec)

mysql> CREATE USER 'non_ssl'@'%' IDENTIFIED BY 'non_ssl';
Query OK, 0 rows affected (0.31 sec)

mysql> GRANT SELECT ON *.* to 'non_ssl'@'%';
Query OK, 0 rows affected (0.26 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.24 sec)

mysql> show create user 'non_ssl'@'%';
+--------------------------------------------------------------------
| CREATE USER for non_ssl@% +--------------------------------------------------------------------
| CREATE USER 'non_ssl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B13EAF79AC5DEAB4035C5EECC4DB251EBF434467' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+--------------------------------------------------------------------
1 row in set (0.30 sec)
mysql> SHOW GRANTS for 'non_ssl'@'%';
+--------------------------------------+
| Grants for non_ssl@% |
+--------------------------------------+
| GRANT SELECT ON *.* TO `non_ssl`@`%` |
+--------------------------------------+
1 row in set (0.27 sec)

Open another session connection to DB with SSL

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u test_ssl -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com --ssl-ca C:\Users\vrao0001\Downloads\cert\us-east-1-bundle.pem --ssl-mode=VERIFY_IDENTITY
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10.Server version: 8.0.23 Source distribution Copyright (c) 2000, 2018, 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 8.0.11-commercial for Win64 on x86_64 (MySQL Enterprise Server - Commercial)
Connection id: 10
Current database:
Current user: test_ssl@27.7.129.170
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter: ;
Server version: 8.0.23 Source distribution
Protocol version: 10
Connection: ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 4 min 2 sec
Threads: 4 Questions: 213 Slow queries: 0 Opens: 185 Flush tables: 3 Open tables: 104 Queries per second avg: 0.880
--------------

Note :- Here it is showing non_ssl user with SSL_TLS which i created user with normal [ With out REQUIRE SSL Keyword while creating the User ].

By default once SSL is enabled in RDS [ By default = ON ] , All connections will be establish SSL [ SSL User and Non SSL User ] from MySQL 8.0 Onwards .

Thank you for visiting my blog.....

Comments

AswiDecember 21, 2021 at 3:50 PM
Maria DB technology is supported by Genexdbs, which can handle big data for large organizations and corporate users. Reinvent your digital transformation with Genex & effectively tackle unprecedented challenges & opportunities. Well-versed in all versions of the service, our analysts eliminate the need for complex, expensive & separate data integrations through our MySQL databases powered by Oracle and Percona, making them the best places for building & running your dream MySQL applications
https://genexdbs.com/

Search This Blog

MySQL / MariaDB DBA

RDS MySQL / MariaDB SSL Connection by using Workbench and command line

Comments

Post a Comment

Popular posts from this blog

MariaDB Galera Cluster Configuration and Validation with Different Types of testings

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

MariaDB Database Multi-instance implementation in single machine