MYSQL DB instance is commonly used for Transactional data storage. In Google cloud platform we can spawn an MYSQL instance with very click and minimal configs.
Cloud SQL is a VM that’s hosted on Google Compute Engine and runs MySQL binary. How to setup Cloud SQL MYSQL Instance in GCP?
Generally a website enforce https protocol for sensitive data transfer over network. Similarly from web-server to DB we can send encrypted data using SSL(Secure Sockets Layer). When we are establishing a secure connection as a client, we need three things:
- The server’s CA certificate
- A client certificate
- A client private key : Non-recoverable.
With above three SSL entity MySQL client knows what to do with them to establish a secure connection.
Cloud SQL services provide out of the box ways to create above entity and enforce secure connection and send data in encrypted form. Follow below steps to enable secure connections to MYSQL instance using Transport Layer Security (SSL/TLS) protocol.
- From Connections panel, go to Security Tab and click on checkbox "Allow only SSL connections". It will restart MYSQL instance and prepare it for SSL enablement.
- Click on "create client certificate", three different certificates are generated. Please note "client-key.pem" is non-recoverable, only once it can be generated. Download and Keep a copy of the same.
Once all three client certificates are downloaded & saved locally, we are ready to make a secure connection to MYSQL DB instance.
Connect to SSL enabled MYSQL Instance from DBeaver(UI based tool)
1. Try connecting without making SSL related change and notice, client reports - Not accessible(although it does not say why?)
2. Configure SSL related certificate in client and Test connection.
3. Run few commands to validate from DBeaver client
Connect MYSQL instance from Native MYSQL client
- Install MYSQL client using home-brew.
n0r0082@m-c02z31rnlvdt ~ % brew install mysql-client ==> Downloading https://ghcr.io/v2/homebrew/core/mysql-client/manifests/8.0.28 ######################################################################## 100.0% ..... ....... ==> Summary 🍺 /usr/local/Cellar/mysql-client/8.0.28: 168 files, 154.2MB ==> Running `brew cleanup mysql-client`... - Connect to MYSQL Instance with SSL certificates using following command and perform DB operation.
n0r0082@m-c02z31rnlvdt ~ % mysql -u root --password=PASSWORD -h 35.225.161.77 \ --ssl-ca=../GCP/certs/server-ca.pem \ --ssl-cert=../GCP/certs/client-cert.pem \ --ssl-key=../GCP/certs/client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3250 Server version: 5.7.36-google-log (Google) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | devInlineTxn | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.06 sec) mysql> SHOW GRANTS FOR readonly; +--------------------------------------+ | Grants for readonly@% | +--------------------------------------+ | GRANT USAGE ON *.* TO 'readonly'@'%' | +--------------------------------------+ 1 row in set (0.05 sec) mysql> SHOW STATUS LIKE 'Ssl_cipher'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 | +---------------+-----------------------------+ 1 row in set (0.05 sec)
Cloud SQL in production environment - Access control: a security measure using CIDR
In order to access Cloud Service instance we have opened it for all incoming request using CIDR notation - 0.0.0.0/0.
- If we have to restrict it to specific IP address we can use 104.121.18.32/32. Here /32 means Ip address should have exact match.
- For large set of Ip address in a network we can use wild card match. We can configure CIDR notation like 10.240.0.0/16, here /16 means the last two numerals are wildcards
-----======---------