Using Cloudsqlproxy to initiate connection to mysql from the shell

The cloud sql proxy is a really useful way of managing connectivity to cloudSQL instances. The proxy provides secure access to your Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL.

Accessing Cloud SQL instances using the Cloud SQL Proxy offers these advantages:

  • Secure connections: The proxy automatically encrypts traffic to and from the database using TLS 1.2 with a 128-bit AES cipher; SSL certificates are used to verify client and server identities.
  • Easier connection management: The proxy handles authentication with Cloud SQL, removing the need to provide static IP addresses.

There are a couple of pre-requisites before this will work:

  • Only Second generation instances can benifit from this feature.
  • Cloud SQL Admin API must be enabled
  • The instance must have  either a public IPv$ address or be configured to use a private IP.
  • The proxy needs to be given GCP authentication credentials, either an individual or a service account.

Full details of exactly what to do are here – https://cloud.google.com/sql/docs/mysql/sql-proxy 

Some notes on what I did on macOS sierra:

  1. Created a Service Account, gave it the Cloud SQL Client role and then generated a new private key (json key)
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64

Unpack the dmg file, then give execute permissions on the proxy file.

chmod +x cloud_sql_proxy

Then start the proxy:

./cloud_sql_proxy -instances=project:region:instance=tcp:3306 \
-credential_file=/Users/dbamohsin/sslkeys/cloudsqlproxy-env.json

The proxu will now start listening on your local machine for connection attempts against port 3306:

2018/11/09 11:53:10 Listening on 127.0.0.1:3306 for project:region:instance
2018/11/09 11:53:10 Ready for new connections

Then in another terminal window, connect as normal to a mysql instance using the localhost IP:

mysqlsh -udbamohsin -p -h127.0.0.1

This should make a connection to the cloudSQL instance using the proxy and the connection will show in the proxy log:

2018/11/09 11:53:42 New connection for "project:region:instance"
Advertisements

NOLOCK equivalent in MySQL

SQL Server WITH (NOLOCK) looks like this:

SELECT * FROM TABLE WITH (nolock)

To achieve the same with MySQL, we change the session isolation mode using the SET SESSION command.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

This statement will work similar to WITH (NOLOCK) i.e READ UNCOMMITTED data. We can also set the isolation level for all connections globally:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

In addition, two system variables related to isolation also level exist in MySQL:

--global isolation level
SELECT @@global.tx_isolation; 
--session isolation level
SELECT @@tx_isolation; 

Or set the isolation level inside a transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

MySQL Isolation levels documentation is here

Export table to CSV in MySQL

Using the OUTFILE option, i ran the following:

--This select is for the headings
SELECT 
'id', 'created_at', 'searches_id', 'user_id', 'msisdn', 
'frequency', 'status', 'is_queue', 'mo_id', 'deleted_at', 
'restarted_at', 'last_visited_at', 'last_checked_at' 
UNION
--This select is for the data
SELECT * INTO OUTFILE '/tmp/savedtable.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM savethistable;

The Union is to allow for headings. To get a list of columns in a table do:

show columns from savethistable;

If you don’t specify a path then default path is whatever is specified as the datadir in the my.cnf configuration file.

Eg:

[dbamohsin@myserver /tmp]$ less /etc/my.cnf

################# MASTER #######################

[mysqld]

datadir=/var/site_db/master

Another way of doing this:

show variables like 'data%';

Introduction to MySQL

Decided to learn a bit more about MySQL as my team at work will soon be taking on some MySQL instances.

Installation: http://dev.mysql.com/downloads/ (MySQL 5.5)

How to Install: http://www.wikihow.com/Install-the-MySQL-Database-Server-on-Your-Windows-PC

The installation on windows is fairly painless. took around 5-10 minutes to install with simple click through windows.

Administration via a GUI?: I started using the MySQL Workbench (5.2 CE) which ships with the installation. Other tools are available including probably the most widely known which is the MAAT Kit (http://www.maatkit.org/)

Workbench home screen:

image

Workbench simple Query Editor:

image

Admin Page:

image

Command Line: the command line interface initially didn’t work for me. To resolve this i had to add the MySQL bin directory (C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin) into the environment variables on my Machine.

To do this on vista Right click my Computer | Properties | Advanced System Settings [Left pane] | Environment Variables…

Connecting via CMD

mysql -u root -p -h localhost

Some other Simple commands:

show databases;

use database;

show tables;

Reference Manual: http://dev.mysql.com/doc/refman/5.5/en/index.html